topic Re: Has anyone successfully gathered logs from WSUS? in Getting Data In

Has anyone successfully gathered logs from WSUS?

lwass — Mon, 12 Nov 2018 00:59:00 GMT

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

Re: Has anyone successfully gathered logs from WSUS?

o_calmels — Tue, 22 Oct 2019 07:44:03 GMT

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 ?

Re: Has anyone successfully gathered logs from WSUS?

nick405060 — Thu, 12 Dec 2019 21:10:57 GMT

bumping this.

Re: Has anyone successfully gathered logs from WSUS?

to4kawa — Wed, 30 Sep 2020 03:22:39 GMT

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.

Re: Has anyone successfully gathered logs from WSUS?

kmorris_splunk — Tue, 17 Dec 2019 15:09:53 GMT

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.

Re: Has anyone successfully gathered logs from WSUS?

nick405060 — Tue, 17 Dec 2019 19:03:22 GMT

you would have to do this on all hosts though

people are looking to get directly from WSUS

Re: Has anyone successfully gathered logs from WSUS?

Nikobobinus — Wed, 30 Sep 2020 05:17:44 GMT

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 🙂