Efficient Lookup Table Search for each Field, Combine Results?

[mlorrette]

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

[xpac]

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. 🙂

[FrankVl]

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.

[mlorrette]

@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..

[xpac]

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

[mlorrette]

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

[pradeepkumarg · Accepted solution]

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

[mlorrette]

Worked like a charm!