What is the best way to quickly search for a specific error across millions of events?

[SplunkLunk]

Greetings,

I want to search my Windows boxes for a specific error in the System log. I also want to do this search every seven days. That means it will have to search millions of entries each time when I'm only looking for one error type. I thought in one of the first training sessions they provided a way create an efficient search which would weed out some entries first before performing the actual search. I hope I'm making sense. The query I want to run is:

index= host=* source=WinEventLog:System  | xmlkv | search EventID=20 updateTitle!="Update for System Center Endpoint Protection*" updateTitle!="Windows Malicious Software Removal Tool*"
|rename _time as Time updateTitle AS "Update Title"
|sort -Time
|table Time, Name, "Update Title"
|convert timeformat="%a %b %d, %Y %I:%M:%S %p" ctime(Time) As Time

This will provide a report of failed Windows updates which my admins are asking about. Like I mentioned, it would need to search through all System event logs and our Splunk admins have set a timeout which the query would probably hit. Any suggestions? Thanks.

[rjthibod · Accepted solution]

I would try to do a few simple things.

1. Add an index pattern if you can, e.g., "index=wineventlog*"
2. Add a sourcetype pattern if you can, e.g., sourcetype="XmlWinEventLog:System"
3. Add the event ID to the first level of the search ("Event.System.EventID"=20), e.g., without any of the other changes it would be index= host=* source=WinEventLog:System "Event.System.EventID"=20

[SplunkLunk]

Thanks,

Doing #3 helped significantly. I was wondering what is the correct syntax to remove duplicates by host and only keep the most recent alert. Some updates fail and then retry so I really only need to keep the latest failure.

[rjthibod]

You can use dedup or stats to eliminate duplicates by host. In your case, dedup is probably simpler. In your case, I am not real clear on what the field "Name" is supposed to be. If "Name" is a stand in for the host, then do the following

 index=* source=WinEventLog:System "Event.System.EventID"=20 
 | xmlkv 
 | search updateTitle!="Update for System Center Endpoint Protection*" updateTitle!="Windows Malicious Software Removal Tool*"
 | rename _time as Time updateTitle AS "Update Title"
 | sort -Time
 | dedup 1 Name
 | table Time, Name, "Update Title"
 | convert timeformat="%a %b %d, %Y %I:%M:%S %p" ctime(Time) As Time

If "Name" is not a stand-in for the host name, then try the following

 index=* source=WinEventLog:System "Event.System.EventID"=20 
 | xmlkv 
 | search updateTitle!="Update for System Center Endpoint Protection*" updateTitle!="Windows Malicious Software Removal Tool*"
 | rename _time as Time updateTitle AS "Update Title"
 | sort -Time
 | dedup 1 host
 | table Time, Name, "Update Title"
 | convert timeformat="%a %b %d, %Y %I:%M:%S %p" ctime(Time) As Time

[rjthibod]

If you want to try the stats approach, substitute the dedup command with the following depending on which field is the host name you were referring to:

| stats first(Time) as first("Update Title") as "Update Title" by Name

or

| stats first(Time) as first("Update Title") as "Update Title" first(Name) as Name by host

[SplunkLunk]

Thanks! I added

|dedup 1 host "Update Title"

And that now looks correct. It gives me one unique entry (most recent) for each update per host that failed rather than listing the same update for each host each time it fails. Much appreciated!