Splunk Search
Highlighted

How to merge eventcount output of indexes with stats call to max(_indextime)?

Engager

I want to query splunk so that it can find all index names that do not have _ at the beginning and query for the max(_indextime) for each of them in an efficient way. However, I have been running into problems with structuring the query correctly. I wrote two queries that I need to combine:

| eventcount summarize=false index=* | fields index | dedup index which gives me all of the indexes that I want

AND
| search index=* | fields + _indextime | stats max(_indextime) as max_time | convert ctime(max_time) which gives me the max time that I want

After a lot of searches, I tried to combine them in the following way"
| eventcount summarize=false index=* | fields index | dedup index | stats max(_indextime) by index, which gives me a table of the correct indexes and an empty column of max(_indextime):

-----index-----|-----max(_indextime)-----
index1
index2
index3

What I would want is:

-----index-----|-----max(_indextime) (should be ctime(max(_indextime)))-----
index1..........|...07/10/2018 11:00:00
index2..........|...07/10/2018 11:04:00
index3..........|...07/09/2018 05:00:00

How do I go about connecting these two queries? I have a feeling that I'm making some wrong assumptions about how to feed only the index name into the second query, but I'm not sure how to rectify them. If I can feed the indexes to search index={index} that would be fantastic, otherwise, if there's a way to make it more efficient with a single query to give me what I want, that would be even more great.

Thanks,
-EV

0 Karma
Highlighted

Re: How to merge eventcount output of indexes with stats call to max(_indextime)?

SplunkTrust
SplunkTrust

Hi @evuk,

Try this,

    |tstats max(_indextime) as max_time where index=* by index|convert ctime(max_time)

View solution in original post

Highlighted

Re: How to merge eventcount output of indexes with stats call to max(_indextime)?

Engager

This seems to work fine (but it gives me less indexes than exist as compared to the search in the original posting), but it is rather slow. Is there any way to speed it up and not have it look through all events? I can narrow down the time, but is there something that can optimize it so that it can be faster and start doing a backwards search for the latest time instead of looking through all indexes' times?

0 Karma
Highlighted

Re: How to merge eventcount output of indexes with stats call to max(_indextime)?

SplunkTrust
SplunkTrust

Are you running it for all times time range? That would be slow in anyways.

0 Karma
Highlighted

Re: How to merge eventcount output of indexes with stats call to max(_indextime)?

Engager

yes because some indexes are current and others are not. I'm both trying to monitor and export the latest data. The goal is to show only the latest data since the last time that the user has looked at the data, so I may have to search for the latest data within a day or a month or anything else.

0 Karma
Highlighted

Re: How to merge eventcount output of indexes with stats call to max(_indextime)?

SplunkTrust
SplunkTrust

Hi @evuk,
Try tunning the tstats with admin privs. Can you compare what's missing in tstats and in your original search ? Mostly it should be some summary indexes

0 Karma
Highlighted

Re: How to merge eventcount output of indexes with stats call to max(_indextime)?

Engager

Some of our indexes appear to collect data sporadically, so that's what's causing it. Thanks for your answer! 😄

0 Karma
Highlighted

Re: How to merge eventcount output of indexes with stats call to max(_indextime)?

Esteemed Legend

Like this:

| tstats max(_indextime) AS max_time WHERE NOT index="_*" BY index
| convert ctime(max_time)
0 Karma
Highlighted

Re: How to merge eventcount output of indexes with stats call to max(_indextime)?

Engager

I'm not sure how this is possible, but the above is only giving me indexnames; it's somehow not registering the NOT part. I also tried parentheses around the NOT index="*" part, which gave me the same thing as the above.

0 Karma