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, Karma 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, Karma 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, Karma 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, Karma 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!

Introducing the Splunk Community Dashboard Challenge!

Welcome to Splunk Community Dashboard Challenge! This is your chance to showcase your skills in creating ...

Get the T-shirt to Prove You Survived Splunk University Bootcamp

As if Splunk University, in Las Vegas, in-person, with three days of bootcamps and labs weren’t enough, now ...

Wondering How to Build Resiliency in the Cloud?

IT leaders are choosing Splunk Cloud as an ideal cloud transformation platform to drive business resilience,  ...