Splunk Search

How to do an open text search using a lookup table?

DEAD_BEEF
Builder

I have an inventory csv file and want to do an open text search for all the hostnames in my lookup table. The reason I say "open text search" is because the hostname field is named differently across various indexes so I can't point my LUT column to a specific log field.

I am basically trying to avoid the below query as I don't want to type out (copy+paste) all the hostnames

index=web OR index=main OR index=os (host1 OR host2 OR ... host n)

My current query is:

index=*
    [| inputlookup hosts.csv 
    | fields name
    ]
| stats count by index sourcetype
0 Karma
1 Solution

jplumsdaine22
Influencer

To return strings instead of KV pairs from a subsearch, use the format command (after renaming the field to literally search)

 index=*
 [| inputlookup hosts.csv 
 | fields name
 | rename name as search
 | format
 ]
 | stats count by index sourcetype

For event faster performance, (but if and only if your hostnames contain no segments (dashes etc)) use |tstats instead

 |tstats count WHERE index=*
     [| inputlookup hosts.csv 
     | fields name
     | rename name as search
     | format
     ]
 by index sourcetype

View solution in original post

jplumsdaine22
Influencer

To return strings instead of KV pairs from a subsearch, use the format command (after renaming the field to literally search)

 index=*
 [| inputlookup hosts.csv 
 | fields name
 | rename name as search
 | format
 ]
 | stats count by index sourcetype

For event faster performance, (but if and only if your hostnames contain no segments (dashes etc)) use |tstats instead

 |tstats count WHERE index=*
     [| inputlookup hosts.csv 
     | fields name
     | rename name as search
     | format
     ]
 by index sourcetype

DEAD_BEEF
Builder

The first query works perfectly! The tstats doesn't seem to work as I get the error, Error in 'TsidxStats': WHERE clause is not an exact query. The hostnames have "." in some of them, not sure if that is the problem.

0 Karma

jplumsdaine22
Influencer

Yep any special characters will cause the stats search to fail. For a deeper explanation into why that is so, have a read through this: https://conf.splunk.com/files/2017/slides/fields-indexed-tokens-and-you.pdf.

If the answer worked for you would you mind accepting it?

0 Karma
Get Updates on the Splunk Community!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...