SQL query to get patch compliance reports

SELECT DISTINCT
ps.Bulletin AS Bulletin_No,
ps.Retrying + ps.PreSuccess + ps.Uninstalled + ps.PendReboot + ps.Verified + ps.NoStatus + ps.Failed - ps.Verified AS Unpatched,
ps.Retrying + ps.PreSuccess + ps.Uninstalled + ps.PendReboot + ps.Verified + ps.NoStatus + ps.Failed AS 'Total with Status',
ROUND((100 * (ps.Verified + .00000001)) / (.00000001 + ps.Retrying + ps.PreSuccess + ps.Uninstalled + ps.PendReboot + ps.Verified + ps.NoStatus + ps.Failed), 0) AS '% Compliant',
ps.Verified, ps.NoStatus, ps.Retrying, ps.PreSuccess, ps.Uninstalled, ps.PendReboot, ps.Failed, real_total.total, ps.CollectionID
FROM (
SELECT fcm.CollectionID,
pse.ID AS Bulletin,
SUM(CASE WHEN pse.LastStateName = 'No Status' THEN 1 ELSE 0 END) AS NoStatus,
SUM(CASE WHEN pse.LastStateName = 'Install Verified' THEN 1 ELSE 0 END) / 2 AS Verified,
SUM(CASE WHEN pse.LastStateName = 'Retrying' THEN 1 ELSE 0 END) AS Retrying,
SUM(CASE WHEN pse.LastStateName = 'Preliminary Success' THEN 1 ELSE 0 END) AS PreSuccess,
SUM(CASE WHEN pse.LastStateName = 'Uninstalled' THEN 1 ELSE 0 END) AS Uninstalled,
SUM(CASE WHEN pse.LastStateName = 'Reboot pending' THEN 1 ELSE 0 END) AS PendReboot,
SUM(CASE WHEN pse.LastStateName = 'Failed' THEN 1 ELSE 0 END) AS Failed
FROM
v_ApplicableUpdatesSummaryEx INNER JOIN
v_GS_PatchStatusEx pse ON v_ApplicableUpdatesSummaryEx.UpdateID = pse.UpdateID RIGHT OUTER JOIN
v_FullCollectionMembership fcm ON pse.ResourceID = fcm.ResourceID
WHERE
(pse.QNumbers NOT LIKE 'None')
AND (pse.ID NOT LIKE 'None')
AND (fcm.CollectionID = 'SMS000ES' )
GROUP BY pse.ID
, v_ApplicableUpdatesSummaryEx.Type
, fcm.CollectionID
HAVING
(v_ApplicableUpdatesSummaryEx.Type = 'Microsoft Update')) ps
INNER JOIN
(
SELECT DISTINCT ID0
FROM v_GS_PATCHSTATEEX
WHERE (Language0 = 'English' Or LocaleID0 In ('0','9'))
AND ID0 <> 'none'
AND Type0 = 'Microsoft Update'
AND Severity0 = '10') As PatchList
ON ps.Bulletin = PatchList.ID0
CROSS JOIN
(SELECT CollectionID, COUNT(ResourceID) AS total
FROM v_FullCollectionMembership
GROUP BY CollectionID
HAVING (CollectionID = 'SMS000ES' )) real_total
ORDER BY ps.Bulletin DESC

-- specify collectionID to get respective compliance rate
 
Thanks!

SQL query to get patch summary report for specific collection

declare @Total int, /* total count collection membership */

@SMSInstall int, /* count installed by SMS */
@OtherInstall int, /* count installed externally */
@Missing int, /* count missing patch */
@NotRequired int, /* count not requiring patch */
@Required int, /* count requiring patch */
@Outstanding int /* count outstanding */
/* count non-obsolete clients */
select @Total=count(*)
from v_FullCollectionMembership fcm
join v_R_System sys on fcm.ResourceID=sys.ResourceID
where IsNull(sys.Obsolete0,0)=0 and sys.Client0=1
and fcm.CollectionID='IN000061'  /* specify collectionID here */
/* patches installed by SMS */
/* patches installed by others */
/* patches required by systems */
/* v_GS_PatchStatusEx already filters out obsolete clients */
select @SMSInstall=count(distinct case
when ps1.LastState is not null and ps1.AgentInstallDate is not null and ps1.LastState=105 then ps1.ResourceID
when ps1.LastState is null and ps2.AgentInstallDate is not null and ps2.LastState=105 then ps2.ResourceID
else null end),
@OtherInstall=count(distinct case
when ps1.LastState is not null and ps1.AgentInstallDate is null and ps1.LastState=105 then ps1.ResourceID
when ps1.LastState is null and ps2.AgentInstallDate is null and ps2.LastState=105 then ps2.ResourceID
else null end),
@Missing=count(distinct case
when ps1.LastState is not null and ps1.LastState!=105 then ps1.ResourceID
when ps1.LastState is null and ps2.LastState is not null and ps2.LastState!=105 then ps2.ResourceID
else null end),
@Required=count(distinct case
when ps1.ResourceID is null then ps2.ResourceID else ps1.ResourceID end)
from (select LastState, AgentInstallDate, ResourceID, UpdateID
from v_GS_PatchStatusEx
where ID='ms08-067' and QNumbers=958644 and
UniqueUpdateID is not null) ps1
full outer join
(select LastState, AgentInstallDate, ResourceID, UpdateID
from v_GS_PatchStatusEx
where ID='ms08-067' and QNumbers=958644 and
UniqueUpdateID is null) ps2
on ps1.ResourceID=ps2.ResourceID
join v_FullCollectionMembership fcm
on (ps2.ResourceID is null and ps1.ResourceID=fcm.ResourceID) or
(ps1.ResourceID is null and ps2.ResourceID=fcm.ResourceID) or
(ps1.ResourceID=fcm.ResourceID and ps2.ResourceID=fcm.ResourceID)
where fcm.CollectionID='IN000061'
/* not requiring patch */
select @NotRequired=count(distinct fcm.ResourceID)
from v_FullCollectionMembership fcm
join v_R_System sys on fcm.ResourceID=sys.ResourceID
join v_GS_SCANPACKAGEVERSION spv on fcm.ResourceID=spv.ResourceID
join (select upkg.PackageID, max(upkg.PackageVersion) as PackageVersion
from v_ApplicableUpdatesSummaryEx us
join v_UpdatePrograms upkg on us.UpdateID=upkg.UpdateID
where us.ID='ms08-067' and us.QNumbers=958644 and upkg.PackageType=1
group by upkg.PackageID) updpkg
on spv.PackageID0=updpkg.PackageID and spv.PackageVer0>=updpkg.PackageVersion
left join (select ResourceID
from v_GS_PatchStatusEx
where ID='MS08-067' and QNumbers=958644) ps
on fcm.ResourceID=ps.ResourceID
where fcm.CollectionID='IN000061' and
ps.ResourceID is null and IsNull(sys.Obsolete0,0)=0 and sys.Client0=1
/* outstanding computers */
Select @Outstanding=@Total-(@NotRequired+@Required)
select @Total as 'Computers in collection'
select @Required as 'Computers requiring update', 100*@Required/@Total as '% of Total'
select @SMSInstall as 'Computers updated by SMS', 100*@SMSInstall/@Total as '% of Total'
select @OtherInstall as 'Computers updated by external means', 100*@OtherInstall/@Total as '% of Total'
select @SMSInstall+@OtherInstall as 'Total computers updated', 100*(@SMSInstall+@OtherInstall)/@Total as '% of Total'
select @Missing as 'Computers missing update', 100*@Missing/@Total as '% of Total'
select @NotRequired as 'Computers not requiring update', 100*@NotRequired/@Total as '% of Total'
select @Outstanding as 'Outstanding computers', 100*@Outstanding/@Total as '% of Total'

--outstanding computers are the computers that have not ran that scan yet to know if they need the patch.

--Outstanding=@Total-(@NotRequired+@Required)

SQL query to get patch status report of production servers

-- It provides information about servers and their patch status as per MS bulletin ID and Qnumber.

select distinct a.name0,a.user_name0,b.id0,b.qnumbers0,
b.language0,b.product0,b.reboottype0,b.scanagent0,
'b.severity0' = Case
When b.severity0 = 10 Then 'Red'
When b.severity0 = 8 Then 'Amber'
When b.severity0 = 6 Then 'Green'
else ' '
End,
b.status0,b.type0,b.title0,b.timeapplied0,b.timeauthorized0
from v_r_system a,v_GS_PATCHSTATEEX b
where a.resourceid=b.resourceid
and b.id0 in ('MS08-003','MS08-005','MS08-006','MS08-007','MS08-008','MS08-010',
'MS08-020','MS08-021','MS08-022','MS08-031','MS08-032','MS08-033','MS08-034','MS08-035',
'MS08-036','MS08-037','MS08-045','MS08-046','MS08-047','MS08-048','MS08-049','MS08-050',
'MS08-051','MS08-052','MS08-053','MS08-058','MS08-061','MS08-062','MS08-063','MS08-064',
'MS08-065','MS08-066','MS08-067','MS08-068','MS08-069','MS09-001')
and b.qnumbers0 not in ('951746','955069','954459','954606')
and status0 like 'Applicable'
and a.operating_system_name_and0 like '%server%'

-- bulletinid and qnumbers are provided by server team. I pulled reports of servers which required these patches as per requirements.

Hope, It will help you to someway!

SQL query to get untraceable laptops information

-- This query gets serial nos and retrieve information as machine name, user name and respective serial no.


SELECT a.name0, a.user_name0,
b.serialnumber0 from v_r_system a,
v_GS_PC_BIOS b where a.resourceid=b.resourceid
and b.serialnumber0 in ('x', 'y')

-- x,y are serial nos.
-- you can specify as much serial nos you want.