SCCM Windows KB# and Dates

[JRamirezEnosys]

Hi everybody,

We just started to ingest SCCM v1606 Logs into our Splunk, the main goal is to see the following:

-See which KB#'s (Windows Patch) are installed on a particular device.
-Use a lookup Table to know the date the KB#'s were released and its severity.
-Separate the logs by Operative System.
-Display it on a time-chart that will let us know if the device have the latest most important patches or compliance level.

I was able to achieve the first and third objective with a single SQL Query on the DB Connect

  SELECT
DisplayName0, Publisher0, S.Name0, S.User_Name0, S.Last_Logon_Timestamp0, S.Operating_System_Name_and0
FROM "CM_SFW"."dbo"."v_Add_Remove_Programs" P
Join v_R_System S on P.ResourceId = S.ResourceId
Where DisplayName0 like '%KB%'

The 4th objective is achievable but at this point in time I haven't been able to find a csv (objective 2) file with all the KB#'s that also contain the release dates (and a CVE would be also a great addition)

I wasn't able to find the KB's release dates on the SCCM, so if you could advice of a CSV file that contain these details or if it is accessible through SCCM (and the Query).

[nychawk]

Have you gotten any further ahead in this initiive?

I am looking to build a dashboard for statistics on complianceto patching requirements, and perhaps confirm machines known by SCCM vs. our actual numbers.

Any help greatly appreciated.

[mjeffery_splunk · Accepted solution]

MS decided that they will no longer have their KB list published so that you can just download the Excel file (to be exported to CSV) and now require that you use their API and PowerShell. At least you can programatically download the KB list periodically and import that into Splunk as JSON.

You will need to sign-in here: https://portal.msrc.microsoft.com/en-us/developer

Then download the PS package here: https://www.powershellgallery.com/packages/MsrcSecurityUpdates/1.7.2