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?
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.
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.
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?
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]
Thanks, that did it. Much appreciated.
Thank you! Exactly what I needed.