I am new to Splunk and working on some basic queries and in need of some help. I am working on a dashboard for Windows updates. As part of this dashboard I am looking to show the patches that have been installed and patches not installed. I did this by grabbing the eventcode for the windows updates that were installed (EventCode 19). What I am then doing is grabbing a list from Microsoft of Bulletin KB #'s that have been issued (patches released) and using an inputlookup to import the KB numbers, then I want to search the Windows event logs for those KB numbers. If they do not exist I want a list of the computers that are missing the KB's. I am having a difficult time getting this to work. Here is what I have so far, I am hoping someone can lead me in the correct direction on what I am doing wrong. Grabbing the windowsupdate.log is not an option on each machine due to the way we are forwarding data.
sourcetype=WinEventLog:System EventCode=19 NOT [| inputlookup BulletinSearch.csv | fields + "Bulletin KB" | rename "Bulletin KB" as KB] | eval Date=strftime(_time, "%Y/%m/%d")| rex "\WKB(?.\d+)\W" | table Date, host, KB, Message | sort by -Date
1) Always start by boiling a saucepan before you boil the ocean. In other words, try your search by pretending there is only one Bulletin KB, and see if it works. In this case, it means you need to use a dummy lookup that has one KB you know is installed on some machines, and at least one KB that you know is not installed on at least one machine.
2) If you put a NOT in that particular spot, you will be eliminating all the records that have the KBs you want... but that won't tell you anything. You need a list of all the dogs that didn't bark on a particular night... and for that, you need a list of all the dogs, and all the nights they barked on.
3) Please mark your code as code, so that anything in angle brackets is not deleted by the interface. I marked it for you, but I'm not able to see what should be extracted by that rex.
4) This code should get you going.
(your search that extracts each event 19, ending with...)
| fields _time, host, KB
| rename COMMENT as "Now we add a record for each host for each KB with 0 as the _time"
[ | inputlookup append=t BulletinSearch.csv
| stats values(host) as host, values("Bulletin KB") as KB
| eval _time = 0
| rename COMMENT as "Now find the highest _time for each KB for each host"
| stats max(_time) as _time by host, KB
| rename COMMENT as "And eliminate nonzero _times, leaving only those hosts that did not get that patch."
| where _time==0