Subsearch help - windows patching

[jbeckwith]

Hi, I've managed to use a few subsearches in the past with pretty good success but this one is troubling myself and a colleague, our windows event logs are pipped into splunk, it would be great if we could use the following query to find any servers that have not been patched in the last 'x' days, here are the two searches that work independently;

Last 'x' days
index=winevent EventCode=19 Message="Installation Successful*"|transaction host|fields host
Specific time window
index=winevent EventCode=19 Message="Installation Successful*" earliest="09/01/2015:00:00:00" latest="10/16/2015:00:00:00"| transaction host | table host

The second search has a date time range in it, to generate a list of all servers that were patched in that window, (found this to be a good way of getting a list of servers that should have been patched). I tried to put them together to make this;

index=winevent host NOT [search index=winevent EventCode=19 Message="Installation Successful*"|transaction host|fields host] EventCode=19 Message="Installation Successful*" earliest="09/01/2015:00:00:00" latest="10/16/2015:00:00:00"| transaction host | table host

but it fails, any ideas where I'm going wrong

[brooklynotss]

If you're not already, recommend splunking the windowsupdate.log file too. Can do what you're looking for here as well as tell you how many patches are needed etc.

[jplumsdaine22 · Accepted solution]

Try

index=winevent EventCode=19 Message="Installation Successful*" NOT  [index=winevent EventCode=19 Message="Installation Successful*" earliest="09/01/2015:00:00:00" latest="10/16/2015:00:00:00"|dedup host | fields host] | dedup host | fields host

[jbeckwith]

Ah dedup, thank you!

index=winevent EventCode=19 Message="Installation Successful*" earliest="09/01/2015:00:00:00" latest="10/30/2015:00:00:00" NOT [search index=winevent EventCode=19 Message="Installation Successful*"|dedup host | fields host] | dedup host | fields host | table host

Once I put the query the right way around it works a treat, got my list of servers that haven't been patched in that time window

[jplumsdaine22]

No problem. Do you mind accepting the answer? To help future forum users 🙂

Cheers,

JP