How to get the OU from sccm

[Susan Davidson]

I have been asked to include the OU information for each device in service now. I have no issues with getting it into the tables if i could just find it..

we are using the OOB SCCM integration.  As far as i can tell the OU is not on the

• v_GS_Computer_System table that is connected for the computer identity.

Does anyone have an idea how to update the SQL query to include the OU container path for each device?

Current SQL query is below

SELECT
v_GS_COMPUTER_SYSTEM.Name0 as name,
v_GS_COMPUTER_SYSTEM.domain0 as domain,
v_GS_COMPUTER_SYSTEM.Model0 as model,
v_GS_COMPUTER_SYSTEM.Manufacturer0 as manufacturer,
v_GS_COMPUTER_SYSTEM.ResourceID,
v_GS_COMPUTER_SYSTEM.UserName0 as username,
v_GS_COMPUTER_SYSTEM.NumberOfProcessors0 as numberofprocessors,
v_GS_SYSTEM.SystemRole0 as SystemRole,
v_GS_OPERATING_SYSTEM.Caption0 as caption,
v_GS_SYSTEM_ENCLOSURE.ChassisTypes0 as chassistype,
v_GS_WORKSTATION_STATUS.LastHWScan,
v_GS_PC_BIOS.SerialNumber0 as BIOSSerialNumber,
v_GS_COMPUTER_SYSTEM_PRODUCT.IdentifyingNumber0 as SystemSerialNumber,
v_GS_COMPUTER_SYSTEM_PRODUCT.UUID0 as UUIDSerialNumber,
v_GS_SYSTEM_ENCLOSURE.SerialNumber0 as ChassisSerialNumber,
v_GS_BASEBOARD.SerialNumber0 as BaseboardSerialNumber,
v_GS_SYSTEM_CONSOLE_USAGE.TopConsoleUser0 as UsernameTopUser

FROM v_GS_COMPUTER_SYSTEM
LEFT JOIN v_GS_WORKSTATION_STATUS ON v_GS_COMPUTER_SYSTEM.ResourceID = v_GS_WORKSTATION_STATUS.ResourceID
LEFT JOIN v_GS_SYSTEM ON v_GS_COMPUTER_SYSTEM.ResourceID = v_GS_SYSTEM.ResourceID
LEFT JOIN v_GS_PC_BIOS ON v_GS_COMPUTER_SYSTEM.ResourceID = v_GS_PC_BIOS.ResourceID
LEFT JOIN v_GS_OPERATING_SYSTEM ON v_GS_COMPUTER_SYSTEM.ResourceID = v_GS_OPERATING_SYSTEM.ResourceID
LEFT JOIN v_GS_COMPUTER_SYSTEM_PRODUCT ON v_GS_COMPUTER_SYSTEM.ResourceID = v_GS_COMPUTER_SYSTEM_PRODUCT.ResourceID
LEFT JOIN v_GS_SYSTEM_ENCLOSURE ON v_GS_COMPUTER_SYSTEM.ResourceID = v_GS_SYSTEM_ENCLOSURE.ResourceID
LEFT JOIN v_GS_BASEBOARD ON v_GS_COMPUTER_SYSTEM.ResourceID = v_GS_BASEBOARD.ResourceID
LEFT JOIN v_GS_SYSTEM_CONSOLE_USAGE ON v_GS_COMPUTER_SYSTEM.ResourceID = v_GS_SYSTEM_CONSOLE_USAGE.ResourceID

[Nick Fajardo]

In our system, it looks like the full Distinguished Name is located in table/column vSMS_R_System.Distinguished_Name0 in SCCM. I don't know if it is advised by MS/SCCM to query based on this table, though.  Best to check with your internal SCCM administrators, if you haven't already

[Susan Davidson]

Thanks for the reply - i actually found it! It's in a view in MECM

I updated the Query for the Computer Identity to the following

WITH CTE as (select max(System_OU_Name0) as OU,


                                      ResourceID


                                              from v_RA_System_SystemOUName


                                              group by ResourceID)



SELECT distinct
 
v_GS_COMPUTER_SYSTEM.Name0 as name,
v_GS_COMPUTER_SYSTEM.domain0 as domain,
v_GS_COMPUTER_SYSTEM.Model0 as model,
v_GS_COMPUTER_SYSTEM.Manufacturer0 as manufacturer,
v_GS_COMPUTER_SYSTEM.ResourceID,
v_GS_COMPUTER_SYSTEM.UserName0 as username,
v_GS_COMPUTER_SYSTEM.NumberOfProcessors0 as numberofprocessors,
v_GS_SYSTEM.SystemRole0 as SystemRole,
v_GS_OPERATING_SYSTEM.Caption0 as caption,
v_GS_SYSTEM_ENCLOSURE.ChassisTypes0 as chassistype,
v_GS_WORKSTATION_STATUS.LastHWScan, 
v_GS_PC_BIOS.SerialNumber0 as BIOSSerialNumber,
v_GS_COMPUTER_SYSTEM_PRODUCT.IdentifyingNumber0 as SystemSerialNumber,
v_GS_COMPUTER_SYSTEM_PRODUCT.UUID0 as UUIDSerialNumber,
v_GS_SYSTEM_ENCLOSURE.SerialNumber0 as ChassisSerialNumber,
v_GS_BASEBOARD.SerialNumber0 as BaseboardSerialNumber,
v_GS_SYSTEM_CONSOLE_USAGE.TopConsoleUser0 as UsernameTopUser,
CTE.OU

FROM v_GS_COMPUTER_SYSTEM
LEFT JOIN v_GS_WORKSTATION_STATUS ON v_GS_COMPUTER_SYSTEM.ResourceID = v_GS_WORKSTATION_STATUS.ResourceID 
LEFT JOIN v_GS_SYSTEM ON v_GS_COMPUTER_SYSTEM.ResourceID = v_GS_SYSTEM.ResourceID 
LEFT JOIN v_GS_PC_BIOS ON v_GS_COMPUTER_SYSTEM.ResourceID = v_GS_PC_BIOS.ResourceID
LEFT JOIN v_GS_OPERATING_SYSTEM ON v_GS_COMPUTER_SYSTEM.ResourceID = v_GS_OPERATING_SYSTEM.ResourceID
LEFT JOIN v_GS_COMPUTER_SYSTEM_PRODUCT ON v_GS_COMPUTER_SYSTEM.ResourceID = v_GS_COMPUTER_SYSTEM_PRODUCT.ResourceID
LEFT JOIN v_GS_SYSTEM_ENCLOSURE ON v_GS_COMPUTER_SYSTEM.ResourceID = v_GS_SYSTEM_ENCLOSURE.ResourceID
LEFT JOIN v_GS_BASEBOARD ON v_GS_COMPUTER_SYSTEM.ResourceID = v_GS_BASEBOARD.ResourceID
LEFT JOIN v_GS_SYSTEM_CONSOLE_USAGE ON v_GS_COMPUTER_SYSTEM.ResourceID = v_GS_SYSTEM_CONSOLE_USAGE.ResourceID
LEFT JOIN CTE ON v_GS_COMPUTER_SYSTEM.resourceID = CTE.resourceID

It works great and no needing to make any changes in MECM!