Getting Data In

Has anyone successfully gathered logs from WSUS?

lwass
Explorer

HI all,

Just wondering if anyone here has been successful in getting logs out of WSUS that shows:

  • number of hosts with outstanding patches,
  • number of hosts with failing patches,
  • list of patches applied to different hosts

etc.

The TA for WSUS appears to only seems to perform field extractions, not anything to do with the inputs from WSUS. I have written some PS to extract this information but there must be a better way.

Any help would be most appreciated.

Cheers,
W

Nikobobinus
Engager

I found that using SQLCMD and outputting to a CSV was the best option, so i scheduled this as a task;

sqlcmd -S np:\.\pipe\MICROSOFT##WID\tsql\query -i D:\TTAS\WSUS_query\WSUS_query.txt -o D:\TTAS\WSUS_query\hotfixes.csv -W -s ","

Which executes the query in WSUS_query.txt and outputs to hotfixes.csv.

W
trim whitespace

s ","
use comma as delimiter

The query i used is this:
use SUSDB
select
CURRENT_TIMESTAMP as datetime,
ct.FullDomainName,
ct.IPAddress,
ctd.OSBuildNumber,
ct.LastReportedStatusTime,
uV.DefaultTitle,
uspc.SummarizationState,
uv.KnowledgebaseArticle
from tbComputerTarget ct
left join tbUpdateStatusPerComputer uspc on uspc.TargetID=ct.TargetID
left join tbUpdate u on u.LocalUpdateID = uspc.LocalUpdateID
left join [SUSDB].[PUBLIC_VIEWS].[vUpdate] uV on uV.UpdateId = u.UpdateID
left join tbComputerTargetDetail ctd on ctd.TargetID=ct.TargetID
order by FullDomainName , "DefaultTitle" desc

Then it's simple Splunking 🙂

kmorris_splunk
Splunk Employee
Splunk Employee

Have you looked into the Splunk App for Windows Infrastructure? https://splunkbase.splunk.com/app/1680/

I know there is a stanza in the inputs.conf for the Add-on for Windows (https://splunkbase.splunk.com/app/742/) that pulls the WindowsUpdate.log. There is a dashboard in the App with several panels around updates.

Not sure if this covers all of your use cases, but it will at least give you whats in the WindowsUpdate.log.
alt text

0 Karma

nick405060
Motivator

you would have to do this on all hosts though

people are looking to get directly from WSUS

0 Karma

to4kawa
Ultra Champion

How to read ReportingEvents.log

LOG SAMPLES:

| makeresults
| eval _raw="{68D698EE-B4A7-4CA5-9D33-EFB2EDE6CD36} 2017-12-13 10:37:22:371+0900 1 147 [AGENT_DETECTION_FINISHED] 101 {00000000-0000-0000-0000-000000000000} 0 0 UpdateOrchestrator Success Software Synchronization Windows Update Client successfully detected 3 updates."
| appendpipe
[| eval _raw="{1AF10086-220D-47E2-BA6B-7CDA8358E2C8} 2017-12-13 10:37:23:309+0900 1 167 [AGENT_DOWNLOAD_STARTED] 101 {89F1C905-9C84-4A67-9B90-17B5E30B0FCF} 201 0 UpdateOrchestrator Success Content Download Download started."]
| appendpipe
[| eval _raw="{4C01117E-1FB6-404C-B32B-3EBE8A15A185} 2017-12-13 10:37:26:762+0900 1 162 [AGENT_DOWNLOAD_SUCCEEDED] 101 {89F1C905-9C84-4A67-9B90-17B5E30B0FCF} 201 0 UpdateOrchestrator Success Content Download Download succeeded."]
| appendpipe
[| eval _raw="{55010EE0-76AE-42B0-A212-89169299185E} 2017-12-13 10:43:11:392+0900 1 181 [AGENT_INSTALLING_STARTED] 101 {9A3FB4A5-968D-47D6-B87E-CD248FB9EEF7} 200 0 UpdateOrchestrator Success Content Install Installation Started: Windows has started installing the following update: 2017-12 x64 ベース システム用 Windows Server 2016 の累積更新プログラム (KB4053579) " ]
| appendpipe
[| eval _raw="{57866C44-0196-4EFB-A265-64B5F691B73F} 2017-12-13 10:59:40:657+0900 1 201 [AGENT_INSTALLING_PENDING] 101 {9A3FB4A5-968D-47D6-B87E-CD248FB9EEF7} 200 240005 UpdateOrchestrator Success Content Install Installation pending. " ]
| appendpipe
[| eval _raw="{E9D3C1A8-1EEF-4AD9-9FA8-19BFA0C1FD4F} 2017-12-13 17:39:11:176+0900 1 183 [AGENT_INSTALLING_SUCCEEDED] 101 {9A3FB4A5-968D-47D6-B87E-CD248FB9EEF7} 200 0 UpdateOrchestrator Success Content Install Installation Successful: Windows successfully installed the following update: 2017-12 x64 ベース システム用 Windows Server 2016 の累積更新プログラム (KB4053579) " ]
| appendpipe
[| eval _raw="{F6703CB6-0C82-4E7B-8C75-C25946AA03B0} 2018-01-17 20:13:28:509+0900 1 148 [AGENT_DETECTION_FAILED] 101 {00000000-0000-0000-0000-000000000000} 0 8024402c UpdateOrchestrator Failure Software Synchronization Windows Update Client failed to detect with error 0x8024402c. "]
| appendpipe
[| eval _raw="{BC2328B6-6BE3-4C84-9F01-E177859503B6} 2017-12-12 19:30:38:444+0900 1 161 [AGENT_DOWNLOAD_FAILED] 101 {3FD93540-CD8C-4939-A71D-1C2BE7767D4D} 200 80246008 UpdateOrchestrator Failure Content Download Error: Download failed. "]
| appendpipe
[| eval _raw="{2C3C39FA-FC0F-4F44-9049-D2AA53B27FFB} 2017-12-12 19:46:19:636+0900 1 182 [AGENT_INSTALLING_FAILED] 101 {BFC8A103-FD5F-4458-9935-231D9F79E2C1} 203 80242015 UpdateOrchestrator Failure Content Install Installation Failure: Windows failed to install the following update with error 0x80242015: 2017-11 x64 ベース システム用 Windows Server 2016 の累積更新プログラム (KB4051033) "]
| dedup _raw
| fields - _time

However, this is the host side.

0 Karma

nick405060
Motivator

bumping this.

0 Karma

o_calmels
Communicator

Hi lwass,
If your WSUS install had a full SQL db instance, you could use Splunk DB connect.

Could you share your ps script in a way to work on then, why not create schedule scripts inputs in the TA app ?

0 Karma
Get Updates on the Splunk Community!

Detecting Remote Code Executions With the Splunk Threat Research Team

WATCH NOWRemote code execution (RCE) vulnerabilities pose a significant risk to organizations. If exploited, ...

Enter the Splunk Community Dashboard Challenge for Your Chance to Win!

The Splunk Community Dashboard Challenge is underway! This is your chance to showcase your skills in creating ...

.conf24 | Session Scheduler is Live!!

.conf24 is happening June 11 - 14 in Las Vegas, and we are thrilled to announce that the conference catalog ...