topic Re: Using the Results of a Lookuptable in a Search Query in Splunk Search

How to use the Results of a Lookuptable in a Search Query?

XJabs — Wed, 04 May 2022 20:09:14 GMT

Hello,
So I have been working on this for a few days, looking at numerous Splunk responses but have yet to find something that works for my situation.

So I have a large inventory of servers that I search through and currently use a general IN query in my searches but some querys have over 20 or so servers to search through and want to simplify it.

So I am currently using something like this that works but can be exceedingly large depending on what servers I need to look up:

index=myindex hosts IN (server1,server2,server3) <mysearchquery>

So I had a bright idea of creating a lookup table to group the servers together.
The lookup table:
group,server
group1,server1
group1,server2
group1,server3
group2,server4
group2,server5

I can get the desired list of servers by doing the following:
|inputlookup lookuptable.csv | search group=group1 | fields server
This would return:
server1
server2

but applying it to my search has proved a lot more difficult.
I think I was close with this one but have not quite figured it out yet:

index=myindex <Search> [ |inputlookup lookuptable.csv | search group=group1 | fields server ]

Any suggestions would be greatly appreciated, or a link to similar posts for me to review.

Re: Using the Results of a Lookuptable in a Search Query

richgalloway — Wed, 04 May 2022 20:05:31 GMT

Have you tried something like this?

index=myindex <Search> [ |inputlookup lookuptable.csv where group=group1 | fields server | format ]

The format command puts the results into a format for searching, like "server=server1 OR server=server2".

Re: Using the Results of a Lookuptable in a Search Query

XJabs — Wed, 04 May 2022 20:18:32 GMT

Unfortunately still didn't work.
Its interesting how format created it into the OR format as thats how I originally was searching before I discovered the IN command.
Still returned 0 records thought when I ran it as:

index=myindex <Search> [ |inputlookup lookuptable.csv where group=group1 | fields server | format ]

Re: Using the Results of a Lookuptable in a Search Query

richgalloway — Wed, 04 May 2022 20:34:05 GMT

The IN operator is converted to a sequence of ORs by the optimizer.

Can you tell us more about <Search>? I'm thinking the result of format may interacting with <Search> to create an unexpected query that returns no results. If it helps, look in the Job Inspector, click on Search Job Properties, and look at the Normalized Search.

Re: Using the Results of a Lookuptable in a Search Query

XJabs — Wed, 04 May 2022 20:56:17 GMT

Sure thing, its nothing complex, Im keeping it very vague while trouble shooting and just searching for error
This returns results:

index=myindex hosts IN (server1,server2,server3) error

But this is not returning anything:

index=myindex error [ |inputlookup lookuptable.csv where group=group1 | fields server | format ]

Im under the impression its not using the returned list of servers properly in its search?

From the Job inspection:

The following messages were returned by the search subsystem:

info : [subsearch]: Successfully read lookup file ....<Location of lookupfile>

Re: Using the Results of a Lookuptable in a Search Query

richgalloway — Wed, 04 May 2022 21:15:18 GMT

Ah, HA! This is an easy trap to fall into with subsearches and I did it. Vagueness in the question didn't help. 😀

The first search is looking for the host field to have one of several values. However, the second search is looking for the server field to have one of those values. If the index uses host rather than server then no results will be found. The solution is to put a rename command in

index=myindex error [ |inputlookup lookuptable.csv where group=group1 | fields server | rename server as host | format ]

the subsearch.

Re: Using the Results of a Lookuptable in a Search Query

XJabs — Wed, 04 May 2022 21:24:50 GMT

Yup that was my issue!
Sorry for the Vagueness as Im not allowed to share to much.
Really appreciate your help and explanations, learned more than a few things just from the back and forth.