Search to show unresolved windows update failures

[rgcox1]

I'm trying to make a search that finds failed WindowsUpdate events that do not have a corresponding successful event. It seems that it might be possible to do this using "set diff: with a nested "set intersect":

|set diff [search source=*WindowsUpdate.log "Installation Failure:" |fields + host kb_num | uniq] [|set intersect [search source=*WindowsUpdate.log "Installation Failure:" |fields + host kb_num | uniq] [search source=*WindowsUpdate.log "Installation Successful:" |fields + host kb_num | uniq]]

The first problem is that the intersect does not work. In spite of having excluded the hidden fields, Splunk does not appear to be able to consider events without their timestamps. Whether or not the nesting would work is another question, but perhaps there is a better way to achieve the same result? Any thoughts on how this might be done would be appreciated.

[rgcox1]

Well, it was almost exactly what I needed, but the answer above did supply the needed foundation. I wanted to filter out failures without subsequent successes. This search returns all windows update results.

I had to add "first(status)" to the stats command. (I thought it should have been last, as in "last seen", but in Splunk think first is the most recent -- or first in the results.) Also had to remove package_title from the by clause. Sometimes the same KB package has several different KB's in the titles, or may vary by only the addition of a period at the end. At any rate the only change I made to the above answer was to the search:

sourcetype=WindowsUpdateLog status>" " |stats first(status) as status max(_time) as lastTime by host,package|convert ctime(lastTime)|search status=failure

Thanks hazedav!

[hazekamp · Accepted solution]

rgcox1,

We use the following configurations for WindowsUpdateLog to create the following fields:

1. package (multi-value KB #)

2. package_title (mult-value package title)

3. status (available, installed, restart required, failed)

This will allow you to search for the most recent status of an update based on "stats first(status)". I would recommend using a lookup (i.e. update_tracker.csv) to track this information over time:

sourcetype=WindowsUpdateLog | stats first(status) as status,min(_time) as firstTime,max(_time) as lastTime by host,package,package_title

## Copyright (C) 2005-2011 Splunk Inc. All Rights Reserved.
## props.conf
[WindowsUpdateLog] 
FIELDALIAS-dest_for_windowsupdatelog = host as dest
REPORT-0package_message_for_windowsupdatelog =  package_message_for_windowsupdatelog
REPORT-1package_title_for_windowsupdatelog =  package_title_for_windowsupdatelog,package_title_for_windowsupdatelog_restartrequired,package_title_for_windowsupdatelog_package_message
REPORT-package_for_windowsupdatelog = package_for_windowsupdatelog
REPORT-pid-tid-component_for_windowsupdatelog = pid-tid-component_for_windowsupdatelog
LOOKUP-status_for_windowsupdatelog = windows_update_status_lookup vendor_status OUTPUTNEW status

## transforms.conf
[windows_update_status_lookup]
filename = windows_update_statii.csv

[package_message_for_windowsupdatelog]
REGEX = (Content\s+Install\s+((Restart\s+Required)|(Installation\s+Ready)).*)
FORMAT = package_message::"$1" vendor_status::"$2"

[package_title_for_windowsupdatelog]
REGEX = Content\s+Install\s+(Installation\s+(Successful|Failure)):\s+Windows.*the\s+following\s+update.*?:\s+(.*)
FORMAT = vendor_status::"$1" package_title::"$3"

[package_title_for_windowsupdatelog_restartrequired]
REGEX = Content\s+Install\s+(Installation\s+successful\s+and\s+restart\s+required)\s+for\s+the\s+following\s+update:\s+(.*)
FORMAT = vendor_status::"$1" package_title::"$2"

[package_title_for_windowsupdatelog_package_message]
SOURCE_KEY = package_message
REGEX = \-\s+([^)]+\)(\,\s+\d+\-[bB]it\s+Edition)?)
FORMAT = package_title::"$1"
MV_ADD = True

[package_for_windowsupdatelog]
SOURCE_KEY = package_title
REGEX = (KB\d+)
FORMAT = package::$1
MV_ADD = True

[pid-tid-component_for_windowsupdatelog]
REGEX = ^\S+\s+\S+\s+(\S+)\s+(\S+)\s+(\S+)
FORMAT = pid::$1 tid::$2 component::$3

## windows_update_statii.csv
vendor_status,status
"Installation Ready",available
"Installation Successful",installed
"Restart Required","restart required"
"Installation successful and restart required","restart required"
"Installation Failure",failure

[rgcox1]

Exactly what I needed! Thanks.