QUERY/COLLECTION/SQL/OTHER FOR ALL COMPLIANCY

Question

Hi,

I need a way of getting 5 columns out: Hostname, Last Logon Timestamp, Latest CU in SCCM, Installed CU, Compliant (true/false)

I've got both an SCCM and SQL Query that gets the first 2 columns easily, but not sure the best way of getting the 3rd and 4th. I don't want to do this per-collection or per-update, but I do want it split out in to Servers and Workstations.

I don't want to have to do this per SUP Group, ADR, Collection etc. Just a global query for all devices in SCCM.

Workstations List:
Win7-PC1 | 01/01/1900 00:00:00 | KBxxxxxxx | KByyyyyyy | true
Win7-PC2 | 01/01/1900 00:00:00 | KBxxxxxxx | KByyyyyyy | false
Win10-PC1 (1809) | 01/01/1900 00:00:00 | KBxxxxxxx | KByyyyyyy | true
Win10-PC2 (1809) | 01/01/1900 00:00:00 | KBxxxxxxx | KByyyyyyy | true
Win10-PC3 (1903) | 01/01/1900 00:00:00 | KBxxxxxxx | KByyyyyyy | false
Win10-PC4 (1909) | 01/01/1900 00:00:00 | KBxxxxxxx | KByyyyyyy | false
Win10-PC5 (2004) | 01/01/1900 00:00:00 | KBxxxxxxx | KByyyyyyy | true

Server List:
WinServer2012R2-01 | 01/01/1900 00:00:00 | KBxxxxxxx | KByyyyyyy | false
WinServer2012R2-02 | 01/01/1900 00:00:00 | KBxxxxxxx | KByyyyyyy | true
WinServer2016-01 | 01/01/1900 00:00:00 | KBxxxxxxx | KByyyyyyy | true
WinServer2016-02 | 01/01/1900 00:00:00 | KBxxxxxxx | KByyyyyyy | true
WinServer2019-01 | 01/01/1900 00:00:00 | KBxxxxxxx | KByyyyyyy | true
WinServer2019-02 | 01/01/1900 00:00:00 | KBxxxxxxx | KByyyyyyy | false

and so on. I don't actually need the OS build listed in the first column... that's just to show that I need everything in one simple list. I then export this to another Excel sheet for VLOOKUP purposes against another data set. The other data is from AD with Get-ADComputer.

I'm fluent in SQL generally so that makes sense (especially as I could query it from my existing PowerShell with AD data). But not so fluent in the SCCM DB design. If someone could just point me in the right direction to the tables / columns I need that would be great.

I know:

How to get the Hostname and from what table
How to get the Last Logon and from what table

I don't know:

How to determine what SCCM has as the latest CU KB number for a given OS in a clean string: KBzzzzzzz (Article ID perhaps)
Work out what the latest CU KB is for each device
How to get the latest CU KB installed on a device

What I'm thinking:

For each device, get its OS and latest installed CU KB article
For that KB article, look up when it was released
Check the SCCM DB for the latest CU KB synchronised for the devices OS
For that KB article, look up when it was released
If the devices latest installed KB is older than the latest available KB, return 'false'. Otherwise return 'true'

When I say CU KB, I only care about the proper patch. Not the .NET CUs or MSRT etc.

Thanks!

Answer 1

This might be almost close. You'd have to add in whatever it is you mean by lastlogontimestamp (that can mean different things to different people, I've replaced that with LastScanTime for updates--to me that seems more relevant since this is about scanning for patches.)

This would be...

1. All Computers, with their last scan-for-updates time and state of that scan
2. The latest Cumulative Updates for Windows
3. The latest known results for all resourceids..for those specific CUs in #2--BUT only if those resourceids are applicable for those CUs. i.e., you don't really care that Windows 10 1809 is compliant-by-default for Windows 10 1709's latest Cumulative Update, do you? all you care about is if it's compliant or not for the 1809 Cumulative Update, the one it actually deserves; or at least... that's all I would care about.

End result...
1 line per machine.
if that machine hasn't bothered to mention whether or not it's compliant or not for the latest CU; they will have Null in the columns for Title, Status, TextStatus, and DateRevised. This will likely happen easily/frequently just after the latest is released and you've updated your environment.

If however, it's days later; maybe you'd want to know that some boxes are not scanning/reporting. Up to you to figure out why not. (although usually it's probably just that those machines are offline... but maybe they are messed up in some way; and you need to fix 'em)

;with TheComputers as (

select s1.netbios_name0 as 'ComputerName', s1.Resourceid, uss.LastScanTime, uss.lastscanstate

from v_r_system_valid s1

left join v_UpdateScanStatus uss on uss.resourceid=s1.resourceid

)

,TheOSUpdates as (

select ui.Title, ui.ci_id, ui.DateRevised

from v_updateinfo ui

where ui.title like '%cumulative Update for Windows%' and ui.IsLatest=1

)

,TheResultsForThoseUpdates as (Select ResourceID, TheOsUpdates.Title, TheOSUpdates.DateRevised, Status

from v_Update_ComplianceStatus ucs

join TheOSUpdates on TheOSUpdates.ci_id = ucs.ci_id)

Select TheComputers.ComputerName

,TheComputers.LastScanState,TheComputers.LastScanTime

,TheResultsForThoseUpdates.Title

,TheResultsForThoseUpdates.Status

,Case

when TheResultsForThoseUpdates.Status=3 then 'Installed'

when TheResultsForThoseUpdates.Status=2 then 'Required'

Else cast(TheResultsForThoseUpdates.Status as varchar) end as 'TextStatus'

,TheResultsForThoseUpdates.DateRevised

from TheComputers

Left Join TheResultsForThoseUpdates on TheResultsForThoseUpdates.Resourceid=TheComputers.Resourceid

Answer 2

Just to confirm, you don't care if your machines are actually fully patched, just if they have the latest CU installed.
If all you care about is CU, then going of the UBR (Update Build Record) might be your easiest bet.
You can find the UBR in v_R_System called BuildExt

Each Month, you'd grab the latest UBR from: https://learn.microsoft.com/en-us/windows/release-information/

Then compare that against your data.

SELECT [ResourceID]
,[BuildExt]
,[Netbios_Name0]
FROM [CM_PS2].[dbo].[v_R_System]

Answer 3

Is there a built in Compliance Report like the one above that doesn't group them into Non-compliant, Compliant and Compliance state unknown?

As mentioned, I need them all in one flat, simple list.

With the Generic SUG, do you mean one that doesn't actually deploy anything?

Answer 4

You are probably looking at something like this -

Select sys.name0, sys.lastlogontime, ui.articleid, ucsa.status from v_updatecompliancestatus_all ucsa inner join v_r_system sys on
ucsa.resourceid = sys.resourceid
Inner join v_updateinfo ui on
ucsa.ci_id = ui.ci_id
Where (ui.title like ‘%Windows%10%cumulative%’ and ui.creationdate <= getdate()-30)

You will get 4 possible ucsa.status -

0 -Unknown
1 -Not applicable
2 -Missing
3 -Installed

Note- I am typing this from my mobile so please excuse my typos and incase I got any of the views miss spelled or something. However, I am pretty sure those are the right views.

Additional tip - You can throw in v_updatescanstatus to ensure that you are looking at the most recent update scan data reported by the endpoint.

Answer 5

Although I adore Gary Blok, in this one instance I'm going to disagree with him. The ADR might be more complicated. Here's why... you'd make an ADR to run xx times a week or month, based on logic you'd define... and you could define that exact same logic within the "TheOsUpdates" common table expression, like I did originally. Feels like an extra step...

But if you want to go with the ADR idea; here's how I'd do it. Again, feels kinda cumbersome to me; but to each their own...
As to why there is one missing device; with my method with the left joins... even if a box doesn't reply--you know that. If you are using authlistinfo; you are requiring that the box has received that authlist, and has bothered to reply about it. with my method, if a box is offline (it's a weekend, that human using it is on vacation so the box is off, you just got the latest updates and it's only 5 minutes later since the sync; so most boxes haven't had time to scan and reply, or the box is borked)--you know it hasn't scanned for that particular update.