Find index for a given host

[jmo1]

I have a query to detect missing forwarders (hosts)

| metadata type=hosts | eval age = now() - lastTime | search host=* | search age > 10 | sort age d | convert ctime(lastTime) | fields age,host,lastTime

This works and, obviously, reveals the age, host, and last time they were seen.  I need to also include the index where the host is sending its data.  Since this query is using a metadata, that information doesn't appear to be available.  How can I modify this search to also include the actual index to which a host is reporting?

[manjunathmeti · Accepted solution]

hi @jmo1 ,

You can use tstats command to get host and index data.

| tstats count where index="*" by host, index 
| stats values(index) as indexes by host

| metadata type=hosts 
| eval age = now() - lastTime 
| search host=* 
| search age > 10 
| sort age d 
| convert ctime(lastTime) 
| fields age,host,lastTime 
| appendcols 
    [| tstats count where index="*" by host, index 
    | stats values(index) as indexes by host]

If this reply helps you, an upvote/like would be appreciated. 

[jmo1]

I am afraid that I spoke too soon.  I do get a list of indexes now, but the host doesn't align with the index.  

1	90619	host.splunkcloud.com	01/27/2021 15:00:00	aa101
2	85961	SQL01	01/27/2021 16:17:38	aa101
3	23253	SQL01	01/28/2021 09:42:46	aa101
4	527	host.splunkcloud.com	01/28/2021 16:01:32	aa101
5	255	kf1	01/28/2021 16:06:04	aa101
6	252	PROXY01	01/28/2021 16:06:07	aa101

The indexes that is returned is just a listing of the indexes in alphabetical order.  The index listed does not contain the host.

  Can you verify that what you provided would match the host to the index containing the host?

[manjunathmeti]

You can join the data instead.  See if this works. 

| metadata type=hosts 
| eval age = now() - lastTime 
| search host=* 
| search age > 10 
| sort age d 
| convert ctime(lastTime) 
| fields age,host,lastTime 
| join type=left host 
    [| tstats count where index="*" by host, index 
    | stats values(index) as indexes by host]

[jmo1]

Thanks, that did it.  Much appreciated.

[jmo1]

Thank you!  Exactly what I needed.