Marco Di Feo – Blog Development, Hobby, Everything

SCCM – SQL Query for Computer Information (statistics)

I am developing an administrative webgui to give our admins access to different services to manage their users and computers in our new Active Directory environment. We came across that this is the best solution to automate so many things like creating home folder, creating profile folder, creating terminalserver profile folder, set ACL to folders, create and publish printers to the AD, set quota for different kind of folders, distribute software via SCCM by AD groups and so on. It is necessary that every admin can only see objects that belong to his department. After developing all these base functionality we came to the conclusion that the information that is stored in the SCCM database is really informative for the admins, so we created some selects for the database to get the information about computerobjects, softwarestatus, hardwareinformation, etc...

Additional we thought it would be a great idea to build some kind of dashboard to get an overview over our environment and whats going on there, so we played around with the SQL Adminstudio and got some good looking SQL queries, implemented these and display the information in some pie charts (highcharts.com).

Little explanation to get along with the SQL queries: In some queries you can see a statement like this:

SYSTEM_OU_NAME0='[YOUR OU AD.XXX.DE/ROOT/SUB/SUB]'

Every select to SYSTEM_OU_NAME0 is necessary in our environment to prevent admins from seeing users and computers of different departments. The string for this select should look like this: ad.contoso.com/contoso/department1 ( associated distinguishedname: ou=department1,ou=contoso,dc=ad,dc=contoso,dc=com).

SCCM.SQL - Get Software installation state

Here we select the software installation status and group the events by laststate (which is an ID) and laststatename.

SELECT stat.LastState as statusid, stat.LastStateName as status, count(stat.LastStateName) as anzahl
FROM [SMS_XXX].[dbo].v_ClientAdvertisementStatus stat
JOIN [SMS_XXX].[dbo].v_R_System sys ON stat.ResourceID=sys.ResourceID
JOIN [SMS_XXX].[dbo].v_RA_System_SystemOUName sysou ON sys.ResourceID = sysou.ResourceID
WHERE sysou.SYSTEM_OU_NAME0='[YOUR OU AD.XXX.DE/ROOT/SUB/SUB]' AND stat.LastStatusTime > (getutcdate()-14)
GROUP BY stat.laststate, stat.LastStateName";

Here we select the software installation status information. We use this select statement to display details of the computer and its software state. Use the [stateid] field to select only successfull/failed... installed software

declare @__timezoneoffset int select @__timezoneoffset = DateDiff(ss,getutcdate(),getdate());
SELECT
sys.Netbios_Name0,
adv.AdvertisementName,
stat.LastStateName,
adv.Comment AS C072,
adv.AdvertisementID,
stat.LastStatusMessageIDName,
stat.LastExecutionResult,
(CONVERT(varchar(10),DATEADD(ss,@__timezoneoffset,stat.LastStatusTime),104) + ' ' + CONVERT(varchar(8),DATEADD(ss,@__timezoneoffset,stat.LastStatusTime),108)) as LastStatusDate
FROM [SMS_XXX].[dbo].v_Advertisement adv
JOIN [SMS_XXX].[dbo].v_Package pkg ON adv.PackageID = pkg.PackageID
JOIN [SMS_XXX].[dbo].v_ClientAdvertisementStatus stat ON stat.AdvertisementID = adv.AdvertisementID
JOIN [SMS_XXX].[dbo].v_R_System sys ON stat.ResourceID=sys.ResourceID
JOIN [SMS_XXX].[dbo].v_RA_System_SystemOUName sysou ON sys.ResourceID = sysou.ResourceID
WHERE
sysou.SYSTEM_OU_NAME0='[YOUR OU AD.XXX.DE/ROOT/SUB/SUB]'
AND
stat.LastStatusTime > (getutcdate()-14)
AND
stat.LastState = '[STATEID]'

SCCM.SQL - Last computer activity

We select all computers by their last connection time to the sccm server and grouped them in four categories: active, last connection > 10 days, last connection > 30 days, last connection > 90 days.

SELECT 'Aktuell' AS TageSeitLetzterMeldung, COUNT(*) AS Anzahl FROM
(
	SELECT SYS.Netbios_Name0, MAX(AGENT.AgentTime) AS LetzteMeldung
	FROM v_R_System SYS
	LEFT JOIN v_AgentDiscoveries AGENT ON SYS.ResourceID = AGENT.ResourceId
	LEFT JOIN [SMS_XXX].[dbo].v_RA_System_SystemOUName sysou
	ON SYS.resourceid = sysou.ResourceID
	WHERE sysou.SYSTEM_OU_NAME0='[YOUR OU AD.XXX.DE/ROOT/SUB/SUB]'
	AND isnull(SYS.Active0,1)=1
	GROUP BY SYS.Netbios_Name0
	HAVING (DATEDIFF(Day, MAX(AGENT.AgentTime), GetDate()) < 10)  ) TAB10  UNION ALL SELECT 'älter als 10 Tage' AS TageSeitLetzterMeldung, COUNT(*) AS Anzahl FROM  ( 	SELECT SYS.Netbios_Name0, MAX(AGENT.AgentTime) AS LetzteMeldung 	FROM v_AgentDiscoveries AGENT  	JOIN v_R_System SYS ON AGENT.ResourceId = SYS.ResourceID  	LEFT JOIN [SMS_XXX].[dbo].v_RA_System_SystemOUName sysou 	ON SYS.resourceid = sysou.ResourceID 	WHERE sysou.SYSTEM_OU_NAME0='[YOUR OU AD.XXX.DE/ROOT/SUB/SUB]' 	AND isnull(SYS.Active0,1)=1  	GROUP BY SYS.Netbios_Name0 	HAVING (DATEDIFF(Day, MAX(AGENT.AgentTime), GetDate()) >= 10)
	AND (DATEDIFF(Day, MAX(AGENT.AgentTime), GetDate()) < 30)  ) TAB10  UNION ALL SELECT 'älter als 30 Tage' AS TageSeitLetzterMeldung, COUNT(*) AS Anzahl FROM  ( 	SELECT SYS.Netbios_Name0, MAX(AGENT.AgentTime) AS LetzteMeldung 	FROM v_AgentDiscoveries AGENT  	JOIN v_R_System SYS ON AGENT.ResourceId = SYS.ResourceID  	LEFT JOIN [SMS_XXX].[dbo].v_RA_System_SystemOUName sysou 	ON SYS.resourceid = sysou.ResourceID 	WHERE sysou.SYSTEM_OU_NAME0='[YOUR OU AD.XXX.DE/ROOT/SUB/SUB]' 	AND isnull(SYS.Active0,1)=1  	GROUP BY SYS.Netbios_Name0 	HAVING (DATEDIFF(Day, MAX(AGENT.AgentTime), GetDate()) >= 30 )
	AND (DATEDIFF(Day, MAX(AGENT.AgentTime), GetDate()) <90 )  ) TAB10  UNION ALL SELECT 'älter als 90 Tage' AS TageSeitLetzterMeldung, COUNT(*) AS Anzahl FROM  ( 	SELECT SYS.Netbios_Name0, MAX(AGENT.AgentTime) AS LetzteMeldung 	FROM v_AgentDiscoveries AGENT  	JOIN v_R_System SYS ON AGENT.ResourceId = SYS.ResourceID  	LEFT JOIN [SMS_XXX].[dbo].v_RA_System_SystemOUName sysou 	ON SYS.resourceid = sysou.ResourceID 	WHERE sysou.SYSTEM_OU_NAME0='[YOUR OU AD.XXX.DE/ROOT/SUB/SUB]' 	AND isnull(SYS.Active0,1)=1  	GROUP BY SYS.Netbios_Name0 	HAVING (DATEDIFF(Day, MAX(AGENT.AgentTime), GetDate()) >= 90)
) TAB10

SCCM.SQL - Computer grouped by operating system

This select statement returns the operating system and the number of computers grouped by the operating system (natch).

SELECT OS, COUNT(*) AS anzahl FROM
(
	SELECT ISNULL(OPSYS.Caption0, 'Unbekannt') AS OS
	FROM v_R_System sys
	LEFT JOIN v_GS_OPERATING_SYSTEM OPSYS ON sys.ResourceID=opsys.ResourceID
	JOIN v_RA_System_SystemOUName sysou ON sys.ResourceID=sysou.ResourceID
	WHERE sysou.System_OU_Name0='[YOUR OU AD.XXX.DE/ROOT/SUB/SUB]'
	AND ISNULL(sys.Active0,1)=1
) TAB
GROUP BY OS

This statement below selects all computer objects with information to the operating system and its version.

SELECT
SYS.Netbios_Name0 name,
ISNULL(OPSYS.Caption0, 'Unbekannt') AS os,
OPSYS.CSDVersion0 AS ossp,
OPSYS.Version0 AS osver,
Csys.SystemType0 AS ostyp,
opsys.CountryCode0 AS country,
sysou.SYSTEM_OU_NAME0 as ou
FROM v_R_System sys
LEFT join v_GS_OPERATING_SYSTEM OPSYS
on sys.ResourceID=OPSYS.ResourceID
LEFT JOIN v_RA_System_SystemOUName sysou
ON sYS.ResourceID = sysou.ResourceID
LEFT JOIN v_GS_COMPUTER_SYSTEM CSYS
on SYS.ResourceID = CSYS.ResourceID
WHERE sysou.SYSTEM_OU_NAME0 LIKE '[YOUR OU AD.XXX.DE/ROOT/SUB/SUB>/%computer%]'
AND ISNULL(sys.Active0,1)=1

SCCM.SQL - Computer grouped by hardware models

This select returns the top 8 (most frequently) computer models (manufacturer) in the environment.

SELECT * FROM (
SELECT TOP 8 CSYS.Manufacturer0 + ' ' + CSYS.Model0 AS Model, COUNT(CSYS.Model0) AS anzahl
	FROM  v_R_System sys
	LEFT join v_GS_COMPUTER_SYSTEM CSYS
	ON sys.ResourceID=CSYS.ResourceID
	LEFT JOIN v_RA_System_SystemOUName sysou
	ON SYS.ResourceID = sysou.ResourceID
	WHERE sysou.SYSTEM_OU_NAME0='[YOUR OU AD.XXX.DE/ROOT/SUB/SUB]'
	AND isnull(SYS.Active0,1)=1
	GROUP BY CSYS.Manufacturer0, Model0
	ORDER BY Anzahl DESC
UNION ALL
SELECT 'Andere' AS Model, (
	SELECT SUM(Anzahl) FROM (
	SELECT ISNULL(CSYS.Manufacturer0 + ' ' + CSYS.Model0, 'Unbekannt') AS Model, COUNT(SYS.Netbios_Name0) AS Anzahl
	FROM v_R_System sys
	LEFT join v_GS_COMPUTER_SYSTEM CSYS
	ON sys.ResourceID=CSYS.ResourceID
	LEFT JOIN v_RA_System_SystemOUName sysou
	ON SYS.ResourceID = sysou.ResourceID
	WHERE sysou.SYSTEM_OU_NAME0='[YOUR OU AD.XXX.DE/ROOT/SUB/SUB]'
	AND isnull(SYS.Active0,1)=1
	GROUP BY CSYS.Manufacturer0, Model0) TEMP1
	) - (
	SELECT SUM(Anzahl) FROM (
	SELECT TOP 8 CSYS.Manufacturer0 + ' ' + CSYS.Model0 AS Model, COUNT(CSYS.Model0) AS Anzahl
	FROM v_R_System sys
	LEFT join v_GS_COMPUTER_SYSTEM CSYS
	ON sys.ResourceID=CSYS.ResourceID
	LEFT JOIN v_RA_System_SystemOUName sysou
	ON SYS.ResourceID = sysou.ResourceID
	WHERE sysou.SYSTEM_OU_NAME0='[YOUR OU AD.XXX.DE/ROOT/SUB/SUB]'
	AND isnull(SYS.Active0,1)=1
	GROUP BY CSYS.Manufacturer0, Model0
	ORDER BY Anzahl DESC) TEMP1
	) AS Anzahl
) tt

And the associated select for the detailview of the computermodels (manufacturer)

SELECT SYS.Netbios_Name0 as name,
ISNULL(CSYS.Manufacturer0, 'Unbekannt') AS hersteller,
ISNULL(CSYS.Model0, 'Unbekannt') AS model,
sysou.SYSTEM_OU_NAME0 as ou
FROM v_R_System sys
LEFT join v_GS_COMPUTER_SYSTEM CSYS
on sys.ResourceID =CSYS.ResourceID
LEFT JOIN v_RA_System_SystemOUName sysou
ON sys.ResourceID = sysou.ResourceID
WHERE sysou.SYSTEM_OU_NAME0 LIKE '[YOUR OU AD.XXX.DE/ROOT/SUB/SUB]/%computer%'
AND ISNULL(SYS.Active0,1)=1

If you have any questions or if this little tutorial was helpfull, please leave me a comment!