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!

Splunk Observability Cloud | Unified Identity - Now Available for Existing Splunk ...

Raise your hand if you’ve already forgotten your username or password when logging into an account. (We can’t ...

Index This | How many sides does a circle have?

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

Registration for Splunk University is Now Open!

Are you ready for an adventure in learning?   Brace yourselves because Splunk University is back, and it's ...