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