Getting Data In

SCCM Windows KB# and Dates

JRamirezEnosys
Explorer

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).

1 Solution

mjeffery_splunk
Splunk Employee
Splunk Employee

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

View solution in original post

0 Karma

nychawk
Communicator

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.

0 Karma

mjeffery_splunk
Splunk Employee
Splunk Employee

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

0 Karma
Get Updates on the Splunk Community!

.conf24 | Day 0

Hello Splunk Community! My name is Chris, and I'm based in Canberra, Australia's capital, and I travelled for ...

Enhance Security Visibility with Splunk Enterprise Security 7.1 through Threat ...

 (view in My Videos)Struggling with alert fatigue, lack of context, and prioritization around security ...

Troubleshooting the OpenTelemetry Collector

  In this tech talk, you’ll learn how to troubleshoot the OpenTelemetry collector - from checking the ...