list OU for all the clients in a specific collection

Question

Hi Experts, we are looking to get client OU for all the devices present in a particular collection. Please suggest way to achieve this from SCCM (either cmpivot or SQL query)

solved 0
Abhay Bindlish 6 months 2021-02-02T16:44:12+05:30 3 Answers 30 views Beginner 0

Answers ( 3 )

    0
    2021-02-05T01:23:38+05:30

    Hello ,
    If it is giving syntax error then check inverted commas at two location,

    Line number eight – _USAGE_MAXGROUP.TopConsoleUser0 = ‘-1’) put the inverted comma in -1 correctly

    Line number Nine correct the inverted comma of ‘unknown’

    Rest put the collectionID like i have placed in the location , like i have placed C00012FD at different locations.

    I have tested thsi query and it is working fine in SQL after doing these changes.

    select distinct
    v_R_System_Valid.ResourceID,
    v_R_System_Valid.Netbios_Name0 AS [Computer Name],
    v_R_System_Valid.Resource_Domain_OR_Workgr0 AS [Domain/Workgroup],
    v_Site.SiteName as [SMS Site Name],
    [Top Console User] = CASE
    when (v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP.TopConsoleUser0 is NULL or v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP.TopConsoleUser0 = ‘-1’)
    then ‘Unknown’
    Else v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP.TopConsoleUser0
    End,
    max(v_RA_SYSTEM_SystemOUName.System_OU_Name0) AS [SystemOUName],
    v_GS_OPERATING_SYSTEM.Caption0 AS [Operating System],
    v_GS_OPERATING_SYSTEM.CSDVersion0 AS [Service Pack Level],
    v_GS_SYSTEM_ENCLOSURE_UNIQUE.SerialNumber0 AS [Serial Number],
    v_GS_SYSTEM_ENCLOSURE_UNIQUE.SMBIOSAssetTag0 AS [Asset Tag],
    v_GS_COMPUTER_SYSTEM.Manufacturer0 AS [Manufacturer],
    v_GS_COMPUTER_SYSTEM.Model0 AS [Model],
    v_GS_X86_PC_MEMORY.TotalPhysicalMemory0 AS [Memory (KBytes)],
    v_GS_PROCESSOR.Name0 AS [Name0],
    v_GS_PROCESSOR.NormSpeed0 AS [Processor (GHz)],
    (Select sum(Size0)
    from v_GS_LOGICAL_DISK inner join v_FullCollectionMembership on (v_FullCollectionMembership.ResourceID = v_GS_LOGICAL_DISK.ResourceID )
    where v_GS_LOGICAL_DISK.ResourceID =v_R_System_Valid.ResourceID and
    v_FullCollectionMembership.CollectionID = ‘C00012FD’) As [Disk Space (MB)],
    (Select sum(v_GS_LOGICAL_DISK.FreeSpace0)
    from v_GS_LOGICAL_DISK inner join v_FullCollectionMembership on (v_FullCollectionMembership.ResourceID = v_GS_LOGICAL_DISK.ResourceID )
    where v_GS_LOGICAL_DISK.ResourceID =v_R_System_Valid.ResourceID and v_FullCollectionMembership.CollectionID = ‘C00012FD’) As [Free Disk Space (MB)]
    from v_R_System_Valid
    inner join v_GS_OPERATING_SYSTEM on (v_GS_OPERATING_SYSTEM.ResourceID = v_R_System_Valid.ResourceID)
    left join v_GS_SYSTEM_ENCLOSURE_UNIQUE on (v_GS_SYSTEM_ENCLOSURE_UNIQUE.ResourceID = v_R_System_Valid.ResourceID)
    LEFT join v_GS_COMPUTER_SYSTEM on (v_GS_COMPUTER_SYSTEM.ResourceID = v_R_System_Valid.ResourceID)
    LEFT join v_GS_X86_PC_MEMORY on (v_GS_X86_PC_MEMORY.ResourceID = v_R_System_Valid.ResourceID)
    LEFT join v_GS_PROCESSOR on (v_GS_PROCESSOR.ResourceID = v_R_System_Valid.ResourceID)
    inner join v_FullCollectionMembership on (v_FullCollectionMembership.ResourceID = v_R_System_Valid.ResourceID)
    left join v_Site on (v_FullCollectionMembership.SiteCode = v_Site.SiteCode)
    LEFT join v_GS_LOGICAL_DISK on (v_GS_LOGICAL_DISK.ResourceID = v_R_System_Valid.ResourceID) and v_GS_LOGICAL_DISK.DeviceID0=SUBSTRING(v_GS_OPERATING_SYSTEM.WindowsDirectory0,1,2)
    left join v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP on (v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP.ResourceID = v_R_System_Valid.ResourceID)
    left join v_RA_SYSTEM_SystemOUName on (v_RA_SYSTEM_SystemOUName.ResourceID = v_R_System_Valid.ResourceID)
    Where v_FullCollectionMembership.CollectionID = ‘C00012FD’
    group by v_R_System_Valid.ResourceID, v_R_System_Valid.Netbios_Name0, v_R_System_Valid.Resource_Domain_OR_Workgr0, v_Site.SiteName, v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP.TopConsoleUser0, v_GS_OPERATING_SYSTEM.Caption0, v_GS_OPERATING_SYSTEM.CSDVersion0,v_GS_SYSTEM_ENCLOSURE_UNIQUE.SerialNumber0, v_GS_SYSTEM_ENCLOSURE_UNIQUE.SMBIOSAssetTag0, v_GS_COMPUTER_SYSTEM.Manufacturer0, v_GS_COMPUTER_SYSTEM.Model0, v_GS_X86_PC_MEMORY.TotalPhysicalMemory0, v_GS_PROCESSOR.Name0, v_GS_PROCESSOR.NormSpeed0
    Order by v_R_System_Valid.Netbios_Name0

    Best answer
    0
    2021-02-02T20:11:48+05:30

    Thanks for the response Anoop. Tried it and it says syntax error.

  1. This answer is edited.

    Can you try the query shared by Torsten
    https://social.technet.microsoft.com/Forums/en-US/4fed9be0-b4eb-4333-ad36-74fb0e759100/configure-report-to-show-device-ou-name?forum=configmanagergeneral

    select distinct
    v_R_System_Valid.ResourceID,
    v_R_System_Valid.Netbios_Name0 AS [Computer Name],
    v_R_System_Valid.Resource_Domain_OR_Workgr0 AS [Domain/Workgroup],
    v_Site.SiteName as [SMS Site Name],
    [Top Console User] = CASE
    when (v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP.TopConsoleUser0 is NULL or v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP.TopConsoleUser0 = ‘-1’)
    then ‘Unknown’
    Else v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP.TopConsoleUser0
    End,
    max(v_RA_SYSTEM_SystemOUName.System_OU_Name0) AS [SystemOUName],
    v_GS_OPERATING_SYSTEM.Caption0 AS [Operating System],
    v_GS_OPERATING_SYSTEM.CSDVersion0 AS [Service Pack Level],
    v_GS_SYSTEM_ENCLOSURE_UNIQUE.SerialNumber0 AS [Serial Number],
    v_GS_SYSTEM_ENCLOSURE_UNIQUE.SMBIOSAssetTag0 AS [Asset Tag],
    v_GS_COMPUTER_SYSTEM.Manufacturer0 AS [Manufacturer],
    v_GS_COMPUTER_SYSTEM.Model0 AS [Model],
    v_GS_X86_PC_MEMORY.TotalPhysicalMemory0 AS [Memory (KBytes)],
    v_GS_PROCESSOR.Name0 AS [Name0],
    v_GS_PROCESSOR.NormSpeed0 AS [Processor (GHz)],
    (Select sum(Size0)
    from v_GS_LOGICAL_DISK inner join v_FullCollectionMembership on (v_FullCollectionMembership.ResourceID = v_GS_LOGICAL_DISK.ResourceID )
    where v_GS_LOGICAL_DISK.ResourceID =v_R_System_Valid.ResourceID and
    v_FullCollectionMembership.CollectionID = @CollectionID) As [Disk Space (MB)],
    (Select sum(v_GS_LOGICAL_DISK.FreeSpace0)
    from v_GS_LOGICAL_DISK inner join v_FullCollectionMembership on (v_FullCollectionMembership.ResourceID = v_GS_LOGICAL_DISK.ResourceID )
    where v_GS_LOGICAL_DISK.ResourceID =v_R_System_Valid.ResourceID and v_FullCollectionMembership.CollectionID = @CollectionID) As [Free Disk Space (MB)]
    from v_R_System_Valid
    inner join v_GS_OPERATING_SYSTEM on (v_GS_OPERATING_SYSTEM.ResourceID = v_R_System_Valid.ResourceID)
    left join v_GS_SYSTEM_ENCLOSURE_UNIQUE on (v_GS_SYSTEM_ENCLOSURE_UNIQUE.ResourceID = v_R_System_Valid.ResourceID)
    LEFT join v_GS_COMPUTER_SYSTEM on (v_GS_COMPUTER_SYSTEM.ResourceID = v_R_System_Valid.ResourceID)
    LEFT join v_GS_X86_PC_MEMORY on (v_GS_X86_PC_MEMORY.ResourceID = v_R_System_Valid.ResourceID)
    LEFT join v_GS_PROCESSOR on (v_GS_PROCESSOR.ResourceID = v_R_System_Valid.ResourceID)
    inner join v_FullCollectionMembership on (v_FullCollectionMembership.ResourceID = v_R_System_Valid.ResourceID)
    left join v_Site on (v_FullCollectionMembership.SiteCode = v_Site.SiteCode)
    LEFT join v_GS_LOGICAL_DISK on (v_GS_LOGICAL_DISK.ResourceID = v_R_System_Valid.ResourceID) and v_GS_LOGICAL_DISK.DeviceID0=SUBSTRING(v_GS_OPERATING_SYSTEM.WindowsDirectory0,1,2)
    left join v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP on (v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP.ResourceID = v_R_System_Valid.ResourceID)
    left join v_RA_SYSTEM_SystemOUName on (v_RA_SYSTEM_SystemOUName.ResourceID = v_R_System_Valid.ResourceID)
    Where v_FullCollectionMembership.CollectionID = @CollectionID
    group by v_R_System_Valid.ResourceID, v_R_System_Valid.Netbios_Name0, v_R_System_Valid.Resource_Domain_OR_Workgr0, v_Site.SiteName, v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP.TopConsoleUser0, v_GS_OPERATING_SYSTEM.Caption0, v_GS_OPERATING_SYSTEM.CSDVersion0,v_GS_SYSTEM_ENCLOSURE_UNIQUE.SerialNumber0, v_GS_SYSTEM_ENCLOSURE_UNIQUE.SMBIOSAssetTag0, v_GS_COMPUTER_SYSTEM.Manufacturer0, v_GS_COMPUTER_SYSTEM.Model0, v_GS_X86_PC_MEMORY.TotalPhysicalMemory0, v_GS_PROCESSOR.Name0, v_GS_PROCESSOR.NormSpeed0
    Order by v_R_System_Valid.Netbios_Name0

Leave an answer

Sorry, you do not have a permission to answer to this question .