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!

Adoption of RUM and APM at Splunk

    Unleash the power of Splunk Observability   Watch Now In this can't miss Tech Talk! The Splunk Growth ...

March Community Office Hours Security Series Uncovered!

Hello Splunk Community! In March, Splunk Community Office Hours spotlighted our fabulous Splunk Threat ...

Stay Connected: Your Guide to April Tech Talks, Office Hours, and Webinars!

Take a look below to explore our upcoming Community Office Hours, Tech Talks, and Webinars in April. This post ...