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. 

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
Got questions? Get answers!

Join the Splunk Community Slack to learn, troubleshoot, and make connections with fellow Splunk practitioners in real time!

Meet up IRL or virtually!

Join Splunk User Groups to connect and learn in-person by region or remotely by topic or industry.

Get Updates on the Splunk Community!

Accelerate Splunk App Development with AI

  Learn how to automate the repetitive parts of your build cycle.   Watch On-Demand   Replay the Accelerate ...

Splunk Agent Observability: Measuring AI Value with Splunk Tokenomics on AWS

The AI landscape is moving faster than ever, and our latest 2026 State of Observability report provides the ...

Enterprise Security Watch Party: What’s New in 8.8 - Recap

Splunk Community HQ hosted a Splunk Enterprise Security Watch Party: What’s New In ES 8.8 last Thursday. I had ...