topic Re: Efficient Lookup Table Search for each Field, Combine Results? in Splunk Search

Efficient Lookup Table Search for each Field, Combine Results?

mlorrette — Wed, 02 May 2018 21:35:05 GMT

I'd like to run a search for each host in a list but only return the top result for each host. In a search, it could look something like:

host=Server-01 searchterms | head 1 | table interestingValue
| append
[ host=Server-02 searchterms | head 1 | table interestingValue]
| append
[ host=Server-nn searchterms | head 1 | table interestingValue]

I thought of creating a lookup table "Server_Names.csv" and somewhat loop through it? Use a macro? Unsure.

hostName
Server-01
Server-02
Server-nn

Re: Efficient Lookup Table Search for each Field, Combine Results?

pradeepkumarg — Wed, 02 May 2018 21:57:02 GMT

host=Server-01 OR host=Server-01 OR host=Server-nn searchterms | dedup host | table host  interestingValue

Re: Efficient Lookup Table Search for each Field, Combine Results?

xpac — Wed, 02 May 2018 22:08:13 GMT

Are searchterms and interestingValue the same for every host, or are they different each time?

Re: Efficient Lookup Table Search for each Field, Combine Results?

mlorrette — Thu, 03 May 2018 12:29:01 GMT

@xpac Yes- they searchterms and interestingValue are the same.. are you thinking of a macro?

Re: Efficient Lookup Table Search for each Field, Combine Results?

xpac — Thu, 03 May 2018 12:51:46 GMT

Try this:

index=whatever (host=Server-01 OR host=Server-02 OR ...) searchterms
| stats latest(interestingValue) by host

Hope that helps - if it does I'd be happy if you would upvote/accept this answer, so others could profit from it. 🙂

Re: Efficient Lookup Table Search for each Field, Combine Results?

FrankVl — Thu, 03 May 2018 13:38:15 GMT

And then you could indeed put the host filter part into a macro for easier maintenance and reuse across searches. Or leave it out altogether if you want to look at all your hosts anyway.

Re: Efficient Lookup Table Search for each Field, Combine Results?

mlorrette — Thu, 03 May 2018 13:48:36 GMT

Worked like a charm!

Re: Efficient Lookup Table Search for each Field, Combine Results?

mlorrette — Thu, 03 May 2018 15:56:04 GMT

@xpac
Upvoted. I've added a lookup table:

index=wineventlog sourcetype="WinEventLog:Security"
[| inputlookup serverList.csv | rename Name as host | fields host]
| dedup host | table host

I now need to figure out how to display servers that are part of serverList.csv but don't appear in the search. Added a second lookup but it didn't work. Separate question though..