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 🙂
