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!

March Community Office Hours Security Series Uncovered!

Hello Splunk Community! In March, Splunk Community Office Hours spotlighted our fabulous Splunk Threat ...

Stay Connected: Your Guide to April Tech Talks, Office Hours, and Webinars!

Take a look below to explore our upcoming Community Office Hours, Tech Talks, and Webinars in April. This post ...

Want to Reduce Costs, Mitigate Risk, Improve Performance, or Increase Efficiencies? ...

Splunk Lantern is Splunk’s customer success center that provides advice from Splunk experts on valuable data ...