Client Management Suite

Expand all | Collapse all

Report with Computer OU

Jump to Best Answer
  • 1.  Report with Computer OU

    Posted 10-10-2019 08:43 AM

    Trying to get a detailed report that includes computers only in certain AD OUs.

    I've found this code for the OU but need to combine this with another report to narrow down the results.

    OU code:

    SELECT Guid FROM ItemResource r join dbo.[Inv_OU_Membership] t on t.[_ResourceGuid] = r.[Guid] AND t.[Distinguished Name] = N'COMPUTER OU'

    Need to combine with this:

    DECLARE @g6_ComputerType uniqueidentifier
       SET @g6_ComputerType = '74c9ad4a-e384-477e-bd06-132a3368e58d'
    SELECT DISTINCT
       [vri1_Computer].[Guid] AS [Guid],
       [vri1_Computer].[Name] AS [Name],
       [ajs9_vCMDBComputer].[OS Primary Language] AS [OS Primary Language],
       [ajs10_vHWComputerSystem].[Total Physical Memory (Bytes)] AS [Total Physical Memory (Bytes)],
       [ajs10_vHWComputerSystem].[Manufacturer] AS [Manufacturer],
       [ajs10_vHWComputerSystem].[Model] AS [Model],
       [dca4_AeX AC Client Status].[LastPolicyRequest] AS [LastPolicyRequest],
       [ajs9_vCMDBComputer].[Domain] AS [Domain],
       [dca3_AeX AC Identification].[OS Name] AS [OS Name],
       [dca2_AeX AC TCPIP].[IP Address] AS [IP Address],
       [dca5_Client Task Resources].[ClientTaskServerGuid] AS [ClientTaskServerGuid],
       [dca3_AeX AC Identification].[Hardware Serial Number] AS [Hardware Serial Number],
       [dca3_AeX AC Identification].[Last Logon User] AS [Last Logon User]
    FROM
       [vRM_Computer_Item] AS [vri1_Computer]
          LEFT OUTER JOIN [Inv_AeX_AC_TCPIP] AS [dca2_AeX AC TCPIP]
             ON ([vri1_Computer].[Guid] = [dca2_AeX AC TCPIP].[_ResourceGuid])
          LEFT OUTER JOIN [Inv_AeX_AC_Identification] AS [dca3_AeX AC Identification]
             ON ([vri1_Computer].[Guid] = [dca3_AeX AC Identification].[_ResourceGuid])
          LEFT OUTER JOIN [Inv_AeX_AC_Client_Status] AS [dca4_AeX AC Client Status]
             ON ([vri1_Computer].[Guid] = [dca4_AeX AC Client Status].[_ResourceGuid])
          LEFT OUTER JOIN [Inv_Client_Task_Resources] AS [dca5_Client Task Resources]
             ON ([vri1_Computer].[Guid] = [dca5_Client Task Resources].[_ResourceGuid])
          LEFT OUTER JOIN ([ResourceAssociation] AS [ra7_Computer Type]
             INNER JOIN [vRM_Computer_Type_Item] AS [vri8_Computer Type]
                ON ([ra7_Computer Type].[ChildResourceGuid] = [vri8_Computer Type].[Guid])
                   AND
                   (
                      (
                         ([ra7_Computer Type].[ResourceAssociationTypeGuid] = @g6_ComputerType)
                      )
                   ))
             ON ([vri1_Computer].[Guid] = [ra7_Computer Type].[ParentResourceGuid])
          LEFT OUTER JOIN [vCMDBComputer] AS [ajs9_vCMDBComputer]
             ON ([vri1_Computer].[Guid] = [ajs9_vCMDBComputer].[Guid])
          LEFT OUTER JOIN [vHWComputerSystem] AS [ajs10_vHWComputerSystem]
             ON ([vri1_Computer].[Guid] = [ajs10_vHWComputerSystem].[_ResourceGuid])
    WHERE    [vri1_Computer].[Guid] IN (

    SELECT computer.[Guid]                  FROM
    (
    SELECT 
    cr.[Guid]                 
    FROM  vComputerResource         AS cr
        JOIN  Inv_AeX_AC_Identification AS id ON id._ResourceGuid = cr.[Guid]
        CROSS APPLY dbo.fnSysMask_IsWindowsWorkstationAsTable(id.[OS System Mask], 17, 31, 2) tt  -- Win, Workstation 2000/XP/Vista/7//8/10 (w/o Embedded)
        WHERE cr.IsManaged = 1
                        
    EXCEPT(
    SELECT 
    cr.[Guid]                 
    FROM  vComputerResource         AS cr
        JOIN  Inv_AeX_AC_Identification AS id ON id._ResourceGuid = cr.[Guid]
        CROSS APPLY dbo.fnSysMask_IsWindowsEmbeddedAsTable(id.[OS System Mask]) tt  -- Windows Embedded
        WHERE cr.IsManaged = 1
                        
    )

    ) AS computer 
    INNER JOIN fnGetTrusteeScopedResourcesByType('493435f7-3b17-4c4c-b07f-c23e7ab7781f','%TrusteeScope%',1) AS [fnGTSR_3] ON computer.[Guid]                  = [fnGTSR_3].[ResourceGuid]

    )
    GROUP BY
       [dca2_AeX AC TCPIP].[IP Address],
       [dca3_AeX AC Identification].[OS Name],
       [dca3_AeX AC Identification].[Last Logon User],
       [dca3_AeX AC Identification].[Hardware Serial Number],
       [dca4_AeX AC Client Status].[LastPolicyRequest],
       [dca5_Client Task Resources].[ClientTaskServerGuid],
       [ajs9_vCMDBComputer].[Domain],
       [ajs9_vCMDBComputer].[OS Primary Language],
       [ajs10_vHWComputerSystem].[Total Physical Memory (Bytes)],
       [ajs10_vHWComputerSystem].[Manufacturer],
       [ajs10_vHWComputerSystem].[Model],
       [vri1_Computer].[Guid],
       [vri1_Computer].[Name]
    ORDER BY
       [IP Address],
       [OS Name],
       [Last Logon User],
       [Hardware Serial Number],
       [LastPolicyRequest],
       [ClientTaskServerGuid],
       [Domain],
       [OS Primary Language],
       [Total Physical Memory (Bytes)],
       [Manufacturer],
       [Model]
     

     

    Any assistance would be gretly appreciated.

     



  • 2.  RE: Report with Computer OU
    Best Answer

    Posted 10-10-2019 10:23 AM

    You can join use [vRM_Computer_Item] AS [vri1_Computer] instead of [ItemResource]

    so add another join to that

    join dbo.[Inv_OU_Membership] t on t.[_ResourceGuid] = [vri1_Computer].[Guid] AND t.[Distinguished Name] = N'COMPUTER OU'


  • 3.  RE: Report with Computer OU

    Posted 10-10-2019 10:43 AM

    That worked...THANK YOU!