Looking at yesterdays data but need to filter the data to only show the time used in the time picker

[nelesama]

I've got a data set which collects data everyday but for my graph I'd like to compare the time selected to the same duration 24 hours before.

I can get the query to do the comparison but I want to be able to show only the timeframe selected in the timepicker i.e. last 30 mins rather then the fill -48hours etc.

Below is the query I've used:

index=naming version=2.2.* metric="playing" earliest=-36h latest=now
| dedup _time, _raw
| timechart span=1h sum(value) as value
| timewrap 1d
| rename value_latest_day as "Current 24 Hours", value_1day_before as "Previous 24 Hours"
| foreach * [eval <<FIELD>>=round(<<FIELD>>, 0)]

This is the base query I've used.

For a different version I have done a join however that takes a bit too long to join. Ideally I want to be able to filter the above data (as it's quite quick to load) but only for the time picked in the time picker.

Thanks,

[nelesama]

@ITWhisperer what would I need to do if I wanted to look at a bigger window? 

My max would be to pick 7 days in my time picker, how would i edit the above to look at that?

Thank you in advance

[ITWhisperer]

Your requirement is unclear - do you want your 30 minutes for the last 7 days, or 30 minutes and 30 minutes 7 days ago, or 7 days and a different 7 days from some other point in the past?

[nelesama]

Sorry for the unclear message, I'd like to select whatever duration in the time picker i.e. last 30 mins / last 7 days and be able to look at the past data for the time period.

So for the 30 mins today, I'd look at today's 30 mins and then compare yesterdays 30 mins. Your query actually helps me do that however seems like there's a limit of 48 hours.

In the time picker, I'd like to use the above to select (max) 7 days worth of data and look at the previous 7 days worth of data for that.

If I wanted to do that would that be a different query or could I do that by editing the above query.

Please do let me know if that was unclear

Thanks,

[ITWhisperer]

Essentially, the mvrange and mvexpand gives you two events one with row equal to zero and one with row equal to one. If you can use these to calculate how far back you want the send event to be based on the difference between the info_min_time and info_max_time (which are returned by addinfo), you can modify the calculation for earliest and latest appropriately. Hopefully that makes sense.

[nelesama]

thank you @ITWhisperer that's perfect and hasn't slowed down my query!

[ITWhisperer · Accepted solution]

Try starting with something like this

index=naming version=2.2.* metric="playing" [| makeresults
| fields - _time
| addinfo
| eval day=mvrange(0,2)
| mvexpand day
| eval earliest=relative_time(info_min_time,"-".day."d")
| eval latest=relative_time(info_max_time,"-".day."d")
| fields earliest latest]