Getting Data In

Find index for a given host

jmo1
Path Finder

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?

 

Labels (2)
0 Karma
1 Solution

manjunathmeti
Champion

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. 

View solution in original post

manjunathmeti
Champion

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. 

View solution in original post

jmo1
Path Finder

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.  

 

  

 
190619host.splunkcloud.com01/27/2021 15:00:00aa101
285961SQL0101/27/2021 16:17:38aa101
323253SQL0101/28/2021 09:42:46aa101
4527host.splunkcloud.com01/28/2021 16:01:32aa101
5255kf101/28/2021 16:06:04aa101
6252PROXY0101/28/2021 16:06:07aa101

 

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?

Tags (1)
0 Karma

manjunathmeti
Champion

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
Path Finder

Thanks, that did it.  Much appreciated.

0 Karma

jmo1
Path Finder

Thank you!  Exactly what I needed.

0 Karma
.conf21 CFS Extended through 5/20!

Don't miss your chance
to share your Splunk
wisdom in-person or
virtually at .conf21!

Call for Speakers has
been extended through
Thursday, 5/20!