Splunk Search

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

evuk
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
1 Solution

renjith_nair
SplunkTrust
SplunkTrust

Hi @evuk,

Try this,

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

View solution in original post

woodcock
Esteemed Legend

Like this:

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

evuk
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

renjith_nair
SplunkTrust
SplunkTrust

Hi @evuk,

Try this,

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

evuk
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

somesoni2
SplunkTrust
SplunkTrust

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

0 Karma

evuk
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

renjith_nair
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

Happy Splunking!
0 Karma

evuk
Engager

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

0 Karma
Get Updates on the Splunk Community!

.conf24 | Registration Open!

Hello, hello! I come bearing good news: Registration for .conf24 is now open!   conf is Splunk’s rad annual ...

Splunk is officially part of Cisco

Revolutionizing how our customers build resilience across their entire digital footprint.   Splunk ...

Splunk APM & RUM | Planned Maintenance March 26 - March 28, 2024

There will be planned maintenance for Splunk APM and RUM between March 26, 2024 and March 28, 2024 as ...