Dashboards & Visualizations

How to combine metadata to get first/last indexed data times for a host with tstats to determine the index(s) the host is in?

wrangler2x
Motivator

I put a search into a dashboard that people who are installing forwarders can use as a quick way to see if logs are coming in from a newly installed forwarder. But recently someone wanted to use it to not only provide that information but also what index the logs are in for that host. metadata does not return index field when looking up a host.

Here was the original search:

| metadata type=hosts index=*
| search host=somehost.uci.edu
| convert ctime(firstTime), ctime(recentTime), ctime(lastTime)
| fields host firstTime recentTime lastTime totalCount

I figured that I should be able to get the indexe(s) from tstats so I modified that search thusly:

| metadata type=hosts index=*
| search host=somehost.uci.edu
| tstats prestats=t append=t count WHERE host=somehost.uci.edu BY index host
| convert ctime(firstTime), ctime(recentTime), ctime(lastTime)
| fields index host firstTime recentTime lastTime totalCount

The problem is that the first line of the results appears to be the output from the metadata search, while the subsequent lines are from tstats, so you have no index on the first line, while on subsequent lines you have index and host, with index and host repeating in many cases.

I'd like to have one line per index and everything I've tried loses the firstTime recentTime lastTime totalCount information. Is there a way to fix that, or is there simply a better kind of search to achieve this?

Tags (1)
0 Karma
1 Solution

adonio
Ultra Champion

try this, or something along those line:

| tstats min(_time) as first_event max(_time) As last_event where index=* host="YOURHOST" by index

use the timepicker as tstats takes it to considerations

View solution in original post

adonio
Ultra Champion

try this, or something along those line:

| tstats min(_time) as first_event max(_time) As last_event where index=* host="YOURHOST" by index

use the timepicker as tstats takes it to considerations

wrangler2x
Motivator

That works just dandy! Thanks.

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...