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)
Abhay Bindlish 
Leave an answer
Sorry, you do not have permission to answer to this question .
Answers ( 3 )
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
Thanks for the response Anoop. Tried it and it says syntax error.
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