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]

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?

[adonio · Accepted solution]

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]

That works just dandy! Thanks.