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

[DEAD_BEEF]

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

[jplumsdaine22 · Accepted solution]

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]

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.

[jplumsdaine22]

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?