Useful SCCM 2012 SQL Queries

Decided to start recording useful SCCM information that I've stumbled across or worked out whilst working on SCCM 2012 with +liaminnes  - hopefully covering things like useful SQL queries, oddities found during configuration and administration, custom WMI classes and custom registry entries and how to include them in Hardware inventory etc

So, to start things off, SQL Queries. We started work on a Dashboard for our server & workstation estate to give "the management" a summary of the current status of our estate

The dashboard was designed inside MS SQL Server Report Builder and added as a custom report to the SCCM  Management Console.

Top Client Operating Systems

SELECT OPSYS.Caption0 as 'Operating System', COUNT(*) AS 'Client Count'
FROM v_GS_OPERATING_SYSTEM OPSYS
INNER JOIN v_R_System sys on OPSYS.ResourceID = sys.ResourceID
WHERE
sys.client0=1 AND sys.obsolete0=0 AND sys.active0=1
GROUP BY OPSYS.Caption0
ORDER BY Count(*) desc


Workstation Machine Types

WITH mytop15(model0, clientcount) AS
(
SELECT TOP 15 Model0, COUNT(distinct sys.name0) FROM v_GS_Computer_System ass JOIN v_R_System sys ON ass.ResourceID=sys.resourceID
INNER JOIN v_GS_OPERATING_SYSTEM OPSYS ON ass.ResourceID = OPSYS.resourceID
WHERE sys.client0=1 AND sys.obsolete0=0 AND sys.Active0=1
GROUP BY Model0
ORDER BY 2 desc
)
SELECT * FROM mytop15 UNION ALL
SELECT 'Others' as model0, COUNT(distinct sys.name0) as ClientCount
FROM v_GS_Computer_System ass JOIN v_R_System sys ON ass.ResourceID=sys.resourceID
INNER JOIN v_GS_OPERATING_SYSTEM OPSYS on ass.ResourceID = OPSYS.resourceID
WHERE
sys.client0=1 AND sys.obsolete0=0 AND sys.Active0=1
AND Model0 NOT IN (SELECT Model0 FROM mytop15)
ORDER BY 2 desc

Clients By Domain

SELECT DISTINCT sys.Resource_domain_OR_Workgr0 as ClientDomain, 
COUNT(distinct sys.name0) AS ClientCount
FROM v_R_System sys
INNER JOIN v_GS_OPERATING_SYSTEM OPSYS ON sys.ResourceID = OPSYS.ResourceID
WHERE client0=1 AND obsolete0=0 AND Active0=1
GROUP BY Resource_domain_OR_Workgr0
ORDER BY ClientCount DESC


Warranty

Will come back to this report later, as its based on a custom WMI class, compliance items, and a scheduled task to "fill in the blanks", but here's the SQL

SELECT sub.WarrantyInfo0, COUNT(*) AS ID FROM
(
SELECT
CASE
    WHEN war.WarrantyInfo0 IS NULL THEN 'Not Yet Reported'
    ELSE war.WarrantyInfo0
END AS WarrantyInfo0
FROM v_GS_WARRANTYDETAILS war
INNER JOIN v_R_System sys on sys.ResourceID = war.ResourceID
WHERE sys.client0=1 AND sys.obsolete0=0 AND sys.active0=1) sub
GROUP BY sub.WarrantyInfo0

In addition to the dashboard above, I've written some more custom reports that have proven useful.

Inactive Machines


List of machines that haven't "talked" to the SCCM servers for more than 28 days...

SELECT sys.ResourceID, sys.ResourceType, sys.Name0, sys.SMS_Unique_Identifier0,
sys.Resource_Domain_OR_Workgr0, sys.Client0,
casum.LastActiveTime, casum.LastDDR, casum.LastOnline,
csys.Manufacturer0, csys.Model0
FROM v_R_System sys
INNER JOIN v_CH_ClientSummary casum ON casum.ResourceId = sys.ResourceId
INNER JOIN v_GS_COMPUTER_SYSTEM csys ON csys.ResourceID = sys.ResourceID
WHERE casum.ClientActiveStatus = 0
AND casum.LastActiveTime < DATEADD(d,-28,getdate())
ORDER BY casum.LastActiveTime


Duplicate Machines

SELECT csys.name0, sys.Operating_System_Name_and0, sys.Active0, sys.Client0, 
csum.ClientActiveStatus, csum.IsActivePolicyRequest
FROM v_GS_COMPUTER_SYSTEM csys
LEFT OUTER JOIN v_R_System sys ON csys.ResourceID = sys.resourceID
LEFT OUTER JOIN v_CH_ClientSummary csum on csys.ResourceID = csum.ResourceID
WHERE csys.Name0 IN 
(SELECT csys.Name0
FROM v_GS_COMPUTER_SYSTEM csys
GROUP BY Name0
HAVING COUNT(*) > 1)


Report on reported versions of software (selectable)

Based on a parameter @ProgramName - this allows user on SCCM Report Manager to specify a parameter for the report.

SELECT * FROM (
SELECT DISTINCT v_R_System_Valid.Netbios_Name0 AS "Computer Name", v_R_System_Valid.ResourceID,
v_GS_ADD_REMOVE_PROGRAMS_64.DisplayName0 AS "Product Name", v_GS_ADD_REMOVE_PROGRAMS_64.Publisher0 AS "Publisher",
v_GS_ADD_REMOVE_PROGRAMS_64.Version0 AS "Version", v_GS_ADD_REMOVE_PROGRAMS_64.InstallDate0
FROM v_GS_ADD_REMOVE_PROGRAMS_64
INNER JOIN v_R_System_Valid ON v_R_System_Valid.ResourceID = v_GS_ADD_REMOVE_PROGRAMS_64.ResourceID
JOIN v_GS_OPERATING_SYSTEM ON v_GS_ADD_REMOVE_PROGRAMS_64.ResourceID = v_GS_OPERATING_SYSTEM.ResourceID
WHERE v_GS_ADD_REMOVE_PROGRAMS_64.DisplayName0 LIKE '%' + @ProgramName + '%'
UNION ALL
(
    SELECT DISTINCT v_R_System_Valid.Netbios_Name0 AS "Computer Name", v_R_System_Valid.ResourceID,
    v_GS_ADD_REMOVE_PROGRAMS.DisplayName0 AS "Product Name", v_GS_ADD_REMOVE_PROGRAMS.Publisher0 AS "Publisher",
    v_GS_ADD_REMOVE_PROGRAMS.Version0 AS "Version", v_GS_ADD_REMOVE_PROGRAMS.InstallDate0
    FROM v_GS_ADD_REMOVE_PROGRAMS
    INNER JOIN v_R_System_Valid ON v_R_System_Valid.ResourceID = v_GS_ADD_REMOVE_PROGRAMS.ResourceID
    JOIN v_GS_OPERATING_SYSTEM ON v_GS_ADD_REMOVE_PROGRAMS.ResourceID = v_GS_OPERATING_SYSTEM.ResourceID
    WHERE v_GS_ADD_REMOVE_PROGRAMS.DisplayName0 LIKE '%' + @ProgramName + '%'
)) AS u
ORDER BY "Computer Name","Product Name", Publisher, Version

More to follow...