I segregate my data using indexes for each group. I have a csv with a list of hosts that cross several indexes.
I can find the hosts like this:
| metadata type=hosts | convert timeformat="%Y/%m/%d %T" ctime(*Time) | join host [| inputlookup hosts_lookup.csv ]
I can find the ones in individual indexes like this:
| metadata type=hosts index=foo | convert timeformat="%Y/%m/%d %T" ctime(*Time) | join host [| inputlookup hosts_lookup.csv ]
I want to create a report of systems and which index they are in, but I can't do this
| metadata type=hosts | convert timeformat="%Y/%m/%d %T" ctime(*Time) | join host [| inputlookup hosts_lookup.csv ] | table host index lastTime
This search works but takes too long:
index=* | join host [| metadata type=hosts | convert timeformat="%Y/%m/%d %T" ctime(*Time) | join host [| inputlookup hosts_lookup.csv ] ] | dedup host | table host index lastTime
How can i create a report that will run fast that can show the host
, index
, lastTime
fields using the lookup table?
For start try this (using tstats, will be much faster than regular query )
| tstats count WHERE index=* by index,host | join host [| metadata type=hosts | convert timeformat="%Y/%m/%d %T" ctime(*Time) | join host [| inputlookup hosts_lookup.csv ] ] | dedup host | table host index lastTime
What version of Splunk do you use? Does this lookup file configured to be updated daily?
version 6.2.3
This is have
| inputlookup hosts_lookup.csv | join type=left host [metadata type=hosts]