SCCM SQL Queries: Software Update Compliance by Software Update Group

This is particularly useful for charting compliance of your environment over a period of time by each Software Update Group, I’ve split it out into the total compliance, servers and workstations so that they can individually be charted. The table cane modified as well in the declared @TempTableVariable table and using the ‘UPDATE’ statement.

If charting, use a line graph to chart each of the individual columns as values.Feel free to ask questions if you need a hand getting this working 😉


Declare @TempTableVariable TABLE(
SoftwareUpdateGroup VARCHAR(500),
TotalCompliance INT,
ServerCompliance INT,
WorkstationsCompliance INT)

INSERT INTO @TempTableVariable (SoftwareUpdateGroup)
SELECT
dbo.v_AuthListInfo.Title
FROM
dbo.v_AuthListInfo
ORDER BY
dbo.v_AuthListInfo.Title

--Total Compliance
UPDATE @TempTableVariable SET TotalCompliance=(
SELECT
round((CAST(SUM (
CASE WHEN ucs.status=3 or ucs.status=1 THEN 1 ELSE 0 END) AS FLOAT)/COUNT(*) )*100,2) AS 'Success %'
FROM
v_Update_ComplianceStatusAll UCS
INNER JOIN v_r_system sys on ucs.resourceid=sys.resourceid
INNER JOIN v_UpdateCIs on ucs.resourceid=v_UpdateCIs.CI_ID
INNER JOIN v_FullCollectionMembership fcm on ucs.resourceid=fcm.resourceid
INNER JOIN v_collection coll on coll.collectionid=fcm.collectionid
INNER JOIN v_AuthListInfo LI on ucs.ci_id=li.ci_id
WHERE
v_UpdateCIs.IsDeployed =1 AND
v_UpdateCIs.IsExpired=0 AND
v_UpdateCIs.InUse=1 AND
v_UpdateCIs.IsTombstoned=0 AND
v_UpdateCIs.IsEnabled=1 AND
(li.title=SoftwareUpdateGroup)
GROUP BY
li.title)

--Server Compliance
UPDATE @TempTableVariable SET ServerCompliance=(
SELECT
round((CAST(SUM (
CASE WHEN ucs.status=3 or ucs.status=1 THEN 1 ELSE 0 END) AS FLOAT)/COUNT(*) )*100,2) AS 'Success %'
FROM
v_Update_ComplianceStatusAll UCS
INNER JOIN v_r_system sys on ucs.resourceid=sys.resourceid
INNER JOIN v_UpdateCIs on ucs.resourceid=v_UpdateCIs.CI_ID
INNER JOIN v_FullCollectionMembership fcm on ucs.resourceid=fcm.resourceid
INNER JOIN v_collection coll on coll.collectionid=fcm.collectionid
INNER JOIN v_AuthListInfo LI on ucs.ci_id=li.ci_id
WHERE
v_UpdateCIs.IsDeployed =1 AND
v_UpdateCIs.IsExpired=0 AND
v_UpdateCIs.InUse=1 AND
v_UpdateCIs.IsTombstoned=0 AND
v_UpdateCIs.IsEnabled=1 AND
sys.operatingSystem0 LIKE '%SERVER%' AND
(li.title=SoftwareUpdateGroup)
GROUP BY
li.title )

--Workstations Compliance
UPDATE @TempTableVariable SET WorkstationsCompliance=(
SELECT
round((CAST(SUM ( CASE WHEN ucs.status=3 or ucs.status=1 THEN 1 ELSE 0 END) AS FLOAT)/COUNT(*) )*100,2) AS 'Success %'
From
v_Update_ComplianceStatusAll UCS
INNER JOIN v_r_system sys on ucs.resourceid=sys.resourceid
INNER JOIN v_UpdateCIs on ucs.resourceid=v_UpdateCIs.CI_ID
INNER JOIN v_FullCollectionMembership fcm on ucs.resourceid=fcm.resourceid
INNER JOIN v_collection coll on coll.collectionid=fcm.collectionid
INNER JOIN v_AuthListInfo LI on ucs.ci_id=li.ci_id

WHERE
v_UpdateCIs.IsDeployed =1 AND
v_UpdateCIs.IsExpired=0 AND
v_UpdateCIs.InUse=1 AND
v_UpdateCIs.IsTombstoned=0 AND
v_UpdateCIs.IsEnabled=1 AND
sys.operatingSystem0 NOT LIKE '%SERVER%' AND
(li.title=SoftwareUpdateGroup)
GROUP BY
li.title )

SELECT
SoftwareUpdateGroup,
TotalCompliance,
ServerCompliance,
WorkstationsCompliance

FROM
@TempTableVariable
ORDER BY
SoftwareUpdateGroup 
Advertisements

Getting started with PowerBI

thumbnail

PowerBI has been around for a while now, it’s being constantly being developed by a great team over at Microsoft. It also proves to be a great tool for visualising large sets of data which makes it ideal for carving up everything in your SCCM DB.

I’ve built a number of dashboards for presenting dynamic data of your environment on 55″ LCD TVs. It’s a great way to present data and gain visibility of any problems you might be having in your environment

First thing you’re going to want to do is go and pick up the latest copy of PowerBI from here. Then you can simply create queries like you would for a normal SQL report, make sure that the account you’re connecting with has read rights to your CM DB though.

If you’re not too crash hot on building SQL queries or want something up and running pretty quick then you can’t go past this solution template for System Center Configuration Manager