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

SCCM SQL Queries 101

Here’s a collection of useful SQL queries you can use these in reports or PowerBI. Make sure you’re charting them as best you can, a picture paints a thousand words especially when you’re talking to management.

Total Clients

SELECT
COUNT(dbo.v_R_System.ResourceID)
FROM
dbo.v_R_System

Total Users

SELECT
COUNT(dbo.v_R_User.ResourceID)
FROM
dbo.v_R_User

By Domain

SELECT
       dbo.v_R_System.Full_Domain_Name0,
       COUNT(dbo.v_R_System.Name0)
FROM
       dbo.v_R_System
GROUP BY
       dbo.v_R_System.Full_Domain_Name0
ORDER BY
       dbo.v_R_System.Full_Domain_Name0

By Architecture

SELECT
dbo.v_R_System.Full_Domain_Name0,
COUNT(dbo.v_R_System.Full_Domain_Name0)
FROM
dbo.v_R_System
GROUP BY
dbo.v_R_System.Full_Domain_Name0

By Operating System

SELECT
dbo.v_R_System.operatingSystem0,
COUNT(dbo.v_R_System.operatingSystem0)
FROM
dbo.v_R_System
GROUP BY
dbo.v_R_System.operatingSystem0

By Chassis Type

SELECT
CASE dbo.v_GS_SYSTEM_ENCLOSURE.ChassisTypes0
WHEN '1' THEN 'Other'
WHEN '2' THEN 'Unknown'
WHEN '3' THEN 'Desktop'
WHEN '4' THEN 'Low Profile Desktop'
WHEN '5' THEN 'Pizza Box'
WHEN '6' THEN 'Mini Tower'
WHEN '7' THEN 'Tower'
WHEN '8' THEN 'Portable'
WHEN '9' THEN 'Laptop'
WHEN '10' THEN 'Notebook'
WHEN '11' THEN 'Hand Held'
WHEN '12' THEN 'Docking Station'
WHEN '13' THEN 'All in One'
WHEN '14' THEN 'Sub Notebook'
WHEN '15' THEN 'Space-Saving'
WHEN '16' THEN 'Lunch Box'
WHEN '17' THEN 'Main System Chassis'
WHEN '18' THEN 'Expansion Chassis'
WHEN '19' THEN 'SubChassis'
WHEN '20' THEN 'Bus Expansion Chassis'
WHEN '21' THEN 'Peripheral Chassis'
WHEN '22' THEN 'Storage Chassis'
WHEN '23' THEN 'Rack Mount Chassis'
WHEN '24' THEN 'Sealed-Case PC'
ELSE 'Undefinded' END AS 'Chassis',
COUNT(dbo.v_R_System.Name0) AS [# Devices]

FROM
dbo.v_R_System
INNER JOIN dbo.v_GS_SYSTEM_ENCLOSURE ON dbo.v_R_System.ResourceID = dbo.v_GS_SYSTEM_ENCLOSURE.ResourceID

GROUP BY
dbo.v_GS_SYSTEM_ENCLOSURE.ChassisTypes0

ORDER BY
1

By Virtual/Physical

SELECT
CASE
WHEN dbo.v_R_System.Is_Virtual_Machine0 = '0' THEN 'Physical'
WHEN dbo.v_R_System.Is_Virtual_Machine0 = '1' THEN 'Virtual'
ELSE 'Unknown'
END AS [Physical / Virtual],
COUNT(dbo.v_R_System.Name0) [# Devices]
FROM
dbo.v_R_System
GROUP BY
dbo.v_R_System.Is_Virtual_Machine0
ORDER BY
dbo.v_R_System.Is_Virtual_Machine0

 

By CCM Client Version

SELECT
dbo.v_R_System.Client_Version0,
COUNT(dbo.v_R_System.Client_Version0)
FROM
dbo.v_R_System
GROUP BY
dbo.v_R_System.Client_Version0
ORDER BY
dbo.v_R_System.Client_Version0

By Client Health

--Variables
DECLARE @Now DateTime = GetDate()

--Add Data to Temp DB Table
IF object_id('tempdb..#TMP_ClientsHealthSum') IS NOT NULL
DROP TABLE #TMP_ClientsHealthSum

Select Distinct
Name0,
ResourceID,
LastOnline,
case
When DATEDIFF(dd,LastOnline,@Now) between 0 and 7 then 'Past Week'
When DATEDIFF(dd,LastOnline,@Now) between 8 and 14 then 'Last 2 Weeks'
When DATEDIFF(dd,LastOnline,@Now) between 15 and 21 then 'Last 3 Weeks'
When DATEDIFF(dd,LastOnline,@Now) between 22 and 29 then 'Last 4 Weeks'
When DATEDIFF(dd,LastOnline,@Now) between 30 and 60 then 'Last 2 Months'
When DATEDIFF(dd,LastOnline,@Now) between 61 and 89 then 'Last 3 Months'
When DATEDIFF(dd,LastOnline,@Now) >= 90 then 'Over 3 Months'
Else 'Never'
End As 'DaysSinceLastOnline',
--LastMPServerName,
LastHealthEvaluation,
case
When DATEDIFF(dd,LastHealthEvaluation,@Now) between 0 and 7 then 'Past Week'
When DATEDIFF(dd,LastHealthEvaluation,@Now) between 8 and 14 then 'Last 2 Weeks'
When DATEDIFF(dd,LastHealthEvaluation,@Now) between 15 and 21 then 'Last 3 Weeks'
When DATEDIFF(dd,LastHealthEvaluation,@Now) between 22 and 29 then 'Last 4 Weeks'
When DATEDIFF(dd,LastHealthEvaluation,@Now) between 30 and 60 then 'Last 2 Months'
When DATEDIFF(dd,LastHealthEvaluation,@Now) between 61 and 89 then 'Last 3 Months'
When DATEDIFF(dd,LastHealthEvaluation,@Now) >= 90 then 'Over 3 Months'
Else 'Never'
End As 'DaysSinceLastHealthEval',
LastHealthEvaluationResult,
IsActivePolicyRequest,
LastEvaluationHealthy,
ClientActiveStatus,
ClientState,
ClientStateDescription
INTO #TMP_ClientsHealthSum
FROM vSMS_R_System SYS
LEFT JOIN v_CH_ClientSummary vCS ON SYS.ItemKey = vCS.ResourceID
WHERE SYS.Obsolete0 = 0

Select * From #TMP_ClientsHealthSum

By Client Type

SELECT
CASE WHEN dbo.v_R_System.Client_Type0 = 0 THEN 'Legacy Client'
WHEN dbo.v_R_System.Client_Type0 = 1 THEN 'Computer'
WHEN dbo.v_R_System.Client_Type0 = 3 THEN 'Mobile'
ELSE 'Unknown' END AS [Client Type],
COUNT(dbo.v_R_System.Client_Type0)
FROM
dbo.v_R_System
GROUP BY
dbo.v_R_System.Client_Type0
ORDER BY
dbo.v_R_System.Client_Type0

 

 

 

 

 

If you want to only report on workstations or servers respectively you can append most of the above queries with the below WHERE statements.

WHERE
(dbo.v_R_System.operatingSystem0 NOT LIKE '%SERVER%') --Workstations
       (dbo.v_R_System.operatingSystem0 LIKE '%SERVER%') --Servers

Error when deploying software updates: Failed to download content id 16858135. Error: Invalid certificate signature

Just tried to deploy software updates for the month and received the below error:

Error: Failed to download content id 16858135. Error: Invalid certificate signature

To resolve you need to open IE and untick ‘Enable Protected Mode’.

ieprotectedmode

Software Updates failing to install – 8007371C

The dreaded 8007371C! When you’re trying to install updates but they’re constantly all failing, in SCCM you receive the error ‘The software change returned error code 0x8007371C(-2147010788).’.

There’s a heap of articles around on troubleshooting this issue that recommend a million and one things which have never resolved the issue for me including rebuilding WMI, re-installing WUA and even rebuilding the Operating System! Which is not very helpful when you’re trying to patch you’re production SCOM environment.

Fixing this is easy, just do the following:

  1. Browse to C:\Windows\System32\SMI
  2. There should be a folder called ‘Store’ if it’s not there create it manually
  3. Now inside the Store folder create a new folder called Machine
  4. Download and install the WUA update – https://support.microsoft.com/en-us/kb/2887535
  5. Restart the server

Now you should be able to install updates through WUA & SCCM!