So i am trying to compare bar graphs for event count for our indexes for two separate days. We are upgrading our environment, and I was wanting this query to show us the event count before and after we upgrade. I am have tried using the earliest=-<int>d and latest=-<int>d, but the query keeps using the time picker. I am using dbinspect, so i wasn't sure if that had something to do with it. Below is the working query that outputs the same results for both EventCount and EventCount_1
|dbinspect index=* | search index!=_*
| fields bucketId eventCount index _time
| stats sum(eventCount) as EventCount values(max(_time)) as Time by index
| table index EventCount,
| join type=outer index [| dbinspect index=*
| search index!=_*
| fields bucketId eventCount index
| stats sum(eventCount) as EventCount_1 by index
| table index EventCount_1]
| table index EventCount EventCount_1
I have tried putting the the time periods in a few places, after the first index, in which the query runs, but returns the same results using the time from the time picker. If i place it after the search, I dont get any results.
|dbinspect index=* earliest=-4d latest=-3d | search index!=_*
| fields bucketId eventCount index _time
| stats sum(eventCount) as EventCount values(max(_time)) as Time by index
| table index EventCount,
| join type=outer index [| dbinspect index=* | search index!=_* earliest=2023-05-30T00:00:00 latest=2023-06-01T23:59:59
| fields bucketId eventCount index
| stats sum(eventCount) as EventCount_1 by index
| table index EventCount_1]
| table index EventCount EventCount_1
^ this is also a working query, but it still uses the time from time picker instead of the stated one in query ^
Am I supposed to be using a different type of time selection with the dbinspect? If i don't use dbinspect, I don't get the same results. Is there any other way to get these results? I'm just trying to get event count by index. Thank you for any help.
Is there a reason why you need to count by bucket? I notice that the final table doesn't include bucketId. dbinspect doesn't support earliest or latest. Meanwhile, tstats is almost as quick. But tstats doesn't output info about bucket.
If you only need to count by index, below is one way to do it. Here, I assume that your cutover time is 2023-05-26 0000, and you want to search between one day before (2023-05-25 0000) and one day after (2023-05-27 0000).
| tstats count where earliest="05/25/2023:00:00:00" latest="05/27/2023:00:00:00" by index _time
| eval beforeUpgrade = if(_time < strptime("2023-05-26", "%F"), count, null())
| eval afterUpgrade = if(_time > strptime("2023-05-26", "%F"), count, null())
| stats sum(*Upgrade) as *Upgrade by index
Here, index=* AND index!=_* are not used because this combination is the default.