Hello,

I have a sccm 2012 query:

SELECT DISTINCT SMS_R_System.LastLogonUserName,
                SMS_G_System_COMPUTER_SYSTEM.Name,
                SMS_G_System_PC_BIOS.SerialNumber,
                SMS_R_System.SystemOUName,
                SMS_G_System_COMPUTER_SYSTEM.Manufacturer,
                SMS_G_System_COMPUTER_SYSTEM.Model,
                SMS_G_System_WORKSTATION_STATUS.LastHardwareScan,
                SMS_G_System_CH_ClientSummary.ADLastLogonTime,
                SMS_R_System.SystemGroupName
FROM SMS_R_System
INNER JOIN SMS_G_System_COMPUTER_SYSTEM ON SMS_G_System_COMPUTER_SYSTEM.ResourceID = SMS_R_System.ResourceId
INNER JOIN SMS_G_System_PC_BIOS ON SMS_G_System_PC_BIOS.ResourceID = SMS_R_System.ResourceId
INNER JOIN SMS_G_System_WORKSTATION_STATUS ON SMS_G_System_WORKSTATION_STATUS.ResourceID = SMS_R_System.ResourceId
INNER JOIN SMS_G_System_CH_ClientSummary ON SMS_G_System_CH_ClientSummary.ResourceID = SMS_R_System.ResourceId;

that I am trying to convert in SQL, for a report, so I may send the web report to a user. Everything works in the report, but I am missing SMS_R_System.SystemOUName.  What is the equivalent of SMS_R_System.SystemOUName in SQL? What would the sql report look like?

SQL Report

SELECT DISTINCT SMS_R_System.ItemKey,
                SMS_R_System.User_Name0,
                SMS_G_System_COMPUTER_SYSTEM.Name00,
                SMS_G_System_PC_BIOS.SerialNumber00,
                SMS_G_System_COMPUTER_SYSTEM.Manufacturer00,
                SMS_G_System_COMPUTER_SYSTEM.Model00,
                ___System_WORKSTATION_STATUS0.LastHWScan,
                SMS_G_System_CH_ClientSummary.LastOnline
FROM vSMS_R_System AS SMS_R_System
INNER JOIN Computer_System_DATA AS SMS_G_System_COMPUTER_SYSTEM ON SMS_G_System_COMPUTER_SYSTEM.MachineID = SMS_R_System.ItemKey
INNER JOIN PC_BIOS_DATA AS SMS_G_System_PC_BIOS ON SMS_G_System_PC_BIOS.MachineID = SMS_R_System.ItemKey
INNER JOIN WorkstationStatus_DATA AS ___System_WORKSTATION_STATUS0 ON ___System_WORKSTATION_STATUS0.MachineID = SMS_R_System.ItemKey
INNER JOIN v_CH_ClientSummary AS SMS_G_System_CH_ClientSummary ON SMS_G_System_CH_ClientSummary.ResourceID = SMS_R_System.ItemKey;

Thanks,

Mark

It's not exactly the same thing, but you could use the Distinguished_Name0 column, which contains the OU information.

So just add SMS_R_System.Distinguished_Name0 to your select statement in your example above.

You could also add something to just select a substring and not the entire field:

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/f035aa6e-7bdd-4f44-bd9f-015546b5d30d/selecting-the-substring-of-a-distinguished-name

• Edited by KevinMJohnston 16 hours 11 minutes ago

Hi Mark,

#1, never use the SQL table only use the SQL view.

#2, the SQL view name is v_RA_System_SystemOUName

#3, the other SQL view name you are looking for is dbo.v_RA_System_System_Group_Name

#4,

SELECT DISTINCT 
	R.User_Name0,
	CS.Name0,
	BIOS.SerialNumber0,
	OU.System_OU_Name0,
	CS.Manufacturer0,
	CS.Model0,
	WS.LastHWScan,
	CHCS.LastOnline,
	SSGN.System_Group_Name0
 FROM 
	dbo.v_R_System AS R
	INNER JOIN dbo.v_GS_COMPUTER_SYSTEM AS CS ON CS.ResourceID = R.ResourceID
	INNER JOIN dbo.v_GS_PC_BIOS AS BIOS ON BIOS.ResourceID = R.ResourceID
	INNER JOIN dbo.v_GS_WORKSTATION_STATUS AS WS ON WS.ResourceID = R.ResourceID
	INNER JOIN dbo.v_CH_ClientSummary AS CHCS ON CHCS.ResourceID = R.ResourceID
	INNER JOIN dbo.v_RA_System_SystemOUName OU ON OU.ResourceID = R.ResourceID
	INNER JOIN dbo.v_RA_System_System_Group_Name SSGN ON SSGN.ResourceID = R.ResourceID

It's not exactly the same thing, but you could use the Distinguished_Name0 column, which contains the OU information.

So just add SMS_R_System.Distinguished_Name0 to your select statement in your example above.

You could also add something to just select a substring and not the entire field:

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/f035aa6e-7bdd-4f44-bd9f-015546b5d30d/selecting-the-substring-of-a-distinguished-name

• Edited by KevinMJohnston Friday, May 22, 2015 3:35 PM