How to create a fast report showing hosts, their indexes and lasttime using the metadata command and a lookup against a csv list of hosts?

[hartfoml]

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?

[somesoni2]

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

[somesoni2]

What version of Splunk do you use? Does this lookup file configured to be updated daily?

[hartfoml]

version 6.2.3

[hartfoml]

This is have

| inputlookup hosts_lookup.csv | join type=left host [metadata type=hosts]