https://community.splunk.com/t5/Splunk-Search/How-to-merge-eventcount-output-of-indexes-with-stats-call-to-max/m-p/434206#M123944 <P>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:</P> <P><CODE>| eventcount summarize=false index=* | fields index | dedup index</CODE> which gives me all of the indexes that I want</P> <P>AND<BR /> <CODE>| search index=* | fields + _indextime | stats max(_indextime) as max_time | convert ctime(max_time)</CODE> which gives me the max time that I want</P> <P>After a lot of searches, I tried to combine them in the following way"<BR /> <CODE>| eventcount summarize=false index=* | fields index | dedup index | stats max(_indextime) by index</CODE>, which gives me a table of the correct indexes and an empty column of max(_indextime):</P> <PRE><CODE>-----index-----|-----max(_indextime)----- index1 index2 index3 </CODE></PRE> <P>What I would want is:</P> <PRE><CODE>-----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 </CODE></PRE> <P>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.</P> <P>Thanks,<BR /> -EV</P> Tue, 10 Jul 2018 15:49:06 GMT evuk 2018-07-10T15:49:06Z https://community.splunk.com/t5/Splunk-Search/How-to-merge-eventcount-output-of-indexes-with-stats-call-to-max/m-p/434206#M123944 <P>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:</P> <P><CODE>| eventcount summarize=false index=* | fields index | dedup index</CODE> which gives me all of the indexes that I want</P> <P>AND<BR /> <CODE>| search index=* | fields + _indextime | stats max(_indextime) as max_time | convert ctime(max_time)</CODE> which gives me the max time that I want</P> <P>After a lot of searches, I tried to combine them in the following way"<BR /> <CODE>| eventcount summarize=false index=* | fields index | dedup index | stats max(_indextime) by index</CODE>, which gives me a table of the correct indexes and an empty column of max(_indextime):</P> <PRE><CODE>-----index-----|-----max(_indextime)----- index1 index2 index3 </CODE></PRE> <P>What I would want is:</P> <PRE><CODE>-----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 </CODE></PRE> <P>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.</P> <P>Thanks,<BR /> -EV</P> Tue, 10 Jul 2018 15:49:06 GMT https://community.splunk.com/t5/Splunk-Search/How-to-merge-eventcount-output-of-indexes-with-stats-call-to-max/m-p/434206#M123944 evuk 2018-07-10T15:49:06Z https://community.splunk.com/t5/Splunk-Search/How-to-merge-eventcount-output-of-indexes-with-stats-call-to-max/m-p/434207#M123945 <P>Hi @evuk,</P> <P>Try this,</P> <PRE><CODE> |tstats max(_indextime) as max_time where index=* by index|convert ctime(max_time) </CODE></PRE> Tue, 10 Jul 2018 16:12:55 GMT https://community.splunk.com/t5/Splunk-Search/How-to-merge-eventcount-output-of-indexes-with-stats-call-to-max/m-p/434207#M123945 renjith_nair 2018-07-10T16:12:55Z https://community.splunk.com/t5/Splunk-Search/How-to-merge-eventcount-output-of-indexes-with-stats-call-to-max/m-p/434208#M123946 <P>Like this:</P> <PRE><CODE>| tstats max(_indextime) AS max_time WHERE NOT index="_*" BY index | convert ctime(max_time) </CODE></PRE> Tue, 10 Jul 2018 16:39:13 GMT https://community.splunk.com/t5/Splunk-Search/How-to-merge-eventcount-output-of-indexes-with-stats-call-to-max/m-p/434208#M123946 woodcock 2018-07-10T16:39:13Z https://community.splunk.com/t5/Splunk-Search/How-to-merge-eventcount-output-of-indexes-with-stats-call-to-max/m-p/434209#M123947 <P>I'm not sure how this is possible, but the above is only giving me <EM>indexnames; it's somehow not registering the NOT part. I also tried parentheses around the NOT index="</EM>*" part, which gave me the same thing as the above.</P> Tue, 10 Jul 2018 17:23:06 GMT https://community.splunk.com/t5/Splunk-Search/How-to-merge-eventcount-output-of-indexes-with-stats-call-to-max/m-p/434209#M123947 evuk 2018-07-10T17:23:06Z https://community.splunk.com/t5/Splunk-Search/How-to-merge-eventcount-output-of-indexes-with-stats-call-to-max/m-p/434210#M123948 <P>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?</P> Tue, 10 Jul 2018 17:24:23 GMT https://community.splunk.com/t5/Splunk-Search/How-to-merge-eventcount-output-of-indexes-with-stats-call-to-max/m-p/434210#M123948 evuk 2018-07-10T17:24:23Z https://community.splunk.com/t5/Splunk-Search/How-to-merge-eventcount-output-of-indexes-with-stats-call-to-max/m-p/434211#M123949 <P>Are you running it for all times time range? That would be slow in anyways.</P> Tue, 10 Jul 2018 17:33:51 GMT https://community.splunk.com/t5/Splunk-Search/How-to-merge-eventcount-output-of-indexes-with-stats-call-to-max/m-p/434211#M123949 somesoni2 2018-07-10T17:33:51Z https://community.splunk.com/t5/Splunk-Search/How-to-merge-eventcount-output-of-indexes-with-stats-call-to-max/m-p/434212#M123950 <P>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.</P> Tue, 10 Jul 2018 17:38:48 GMT https://community.splunk.com/t5/Splunk-Search/How-to-merge-eventcount-output-of-indexes-with-stats-call-to-max/m-p/434212#M123950 evuk 2018-07-10T17:38:48Z https://community.splunk.com/t5/Splunk-Search/How-to-merge-eventcount-output-of-indexes-with-stats-call-to-max/m-p/434213#M123951 <P>Hi @evuk,<BR /> 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</P> Wed, 11 Jul 2018 02:45:45 GMT https://community.splunk.com/t5/Splunk-Search/How-to-merge-eventcount-output-of-indexes-with-stats-call-to-max/m-p/434213#M123951 renjith_nair 2018-07-11T02:45:45Z https://community.splunk.com/t5/Splunk-Search/How-to-merge-eventcount-output-of-indexes-with-stats-call-to-max/m-p/434214#M123952 <P>Some of our indexes appear to collect data sporadically, so that's what's causing it. Thanks for your answer! <span class="lia-unicode-emoji" title=":grinning_face_with_smiling_eyes:">😄</span></P> Wed, 11 Jul 2018 14:02:47 GMT https://community.splunk.com/t5/Splunk-Search/How-to-merge-eventcount-output-of-indexes-with-stats-call-to-max/m-p/434214#M123952 evuk 2018-07-11T14:02:47Z