Splunk Search

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

SplunkLunk
Path Finder

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.

0 Karma
1 Solution

rjthibod
Champion

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

View solution in original post

rjthibod
Champion

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
Path Finder

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.

0 Karma

rjthibod
Champion

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
0 Karma

rjthibod
Champion

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

0 Karma

SplunkLunk
Path Finder

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!

0 Karma
Get Updates on the Splunk Community!

.conf24 | Registration Open!

Hello, hello! I come bearing good news: Registration for .conf24 is now open!   conf is Splunk’s rad annual ...

ICYMI - Check out the latest releases of Splunk Edge Processor

Splunk is pleased to announce the latest enhancements to Splunk Edge Processor.  HEC Receiver authorization ...

Introducing the 2024 SplunkTrust!

Hello, Splunk Community! We are beyond thrilled to announce our newest group of SplunkTrust members!  The ...