Splunk Search

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

XJabs
Explorer

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.

Labels (1)
0 Karma
1 Solution

richgalloway
SplunkTrust
SplunkTrust

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.

---
If this reply helps you, an upvote would be appreciated.

View solution in original post

richgalloway
SplunkTrust
SplunkTrust

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

---
If this reply helps you, an upvote would be appreciated.

XJabs
Explorer

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 ]
Tags (1)
0 Karma

richgalloway
SplunkTrust
SplunkTrust

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.

---
If this reply helps you, an upvote would be appreciated.

XJabs
Explorer

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>
Tags (1)
0 Karma

richgalloway
SplunkTrust
SplunkTrust

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.

---
If this reply helps you, an upvote would be appreciated.

XJabs
Explorer

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.

Tags (1)
Get Updates on the Splunk Community!

Improve Your Security Posture

Watch NowImprove Your Security PostureCustomers are at the center of everything we do at Splunk and security ...

Maximize the Value from Microsoft Defender with Splunk

 Watch NowJoin Splunk and Sens Consulting for this Security Edition Tech TalkWho should attend:  Security ...

This Week's Community Digest - Splunk Community Happenings [6.27.22]

Get the latest news and updates from the Splunk Community here! News From Splunk Answers ✍️ Splunk Answers is ...