Add a median line to hour over hour time chart

[jgbricker]

How would I add a third trend line into the timechart to show the median value of a 30 day sample? I was thinking of a flat dashed line that is faded. Below is the day over day trend.

`my_alerts` earliest=-0d@d latest=now * |eval ReportKey="Today" |append [search `my_alerts` earliest=-1d@d latest=-0d@d |eval ReportKey="Yesterday" |eval _time=_time+86400 ] |timechart span=1h count by ReportKey

[somesoni2 · Accepted solution]

Give this a try

 `my_alerts` earliest=-30d@d latest=now | eval Today=if(_time>=relative_time(now(),"@d"),1,0) | eval Yesterday=if(_time<relative_time(now(),"@d") AND _time>=relative_time(now(),"-1d@d"),1,0) | eval Last30day=if(_time<relative_time(now(),"@d"),1,0) | bucket span=1h _time | stats sum(*day) as *day by _time
| eventstats sum(Last30day) as tLast30day count(eval(Last30day>0)) as cLast30day | eval Last30dayAvg=tLast30day/cLast30day 
| table _time Today Yesterday Last30dayAvg | where _time>=relative_time(now(),"-1d@d") | eval _time=if(Yesterday>0,_time+86400,_time) | stats max(*) as * by _time

[jgbricker]

I would love to get this search broken down with an explanation, I'm trying to modify it to use it for a last 7 day vs previous 7 day comparison and I'm not quite getting there. I believe it has to do with not understanding the time manipulation quite right.

[somesoni2]

1) Select data for last 30 day + today
2) For each event, we compare the _time field value, if it's falls under today's date, we set Today=1 for that event and 0 otherwise. Doing of sum(Total) will give count of events that fall under today's date. Similar calculation will happen for Yesterday and last 30 day.
3) Bucket data day-wise and calculate sum of each *day field (Today, Yesterday, Last30day)
4) Now, for today the Last30day sum will be 0 and it will affect the average, so using eventstats, we calculate sum of Last30day (count of event of each day of last 30 day period) and count how many days it had non-zero count. The average is calculated based on that.
5) Now the chart is giving a row for each day for last 30 day+ today, so we filter to keep only today and yesterday.
6) Then adjust _time for yesterday to match today by adding 86400 (1 day) and then last stats will get count by today and yesterday in same row.

[jgbricker]

playing with this and trying to get it correct-

`my_alerts` earliest=-30d@d latest=now | eval Last7Days=if(_time<relative_time(now(),"@d") AND _time>=relative_time(now(),"-7@d"),1,0) | eval Previous7Days=if(_time<relative_time(now(),"@d") AND _time>=relative_time(now(),"-14d@d"),1,0) | eval Last30day=if(_time<relative_time(now(),"@d"),1,0) | bucket span=1h _time | stats sum(*day) as *day by _time
 | eventstats sum(Last30day) as tLast30day count(eval(Last30day>0)) as cLast30day | eval Last30dayAvg=tLast30day/cLast30day 
 | table _time Last7Days Previous7Days Last30dayAvg | where _time>=relative_time(now(),"-7d@d") | eval _time=if(Yesterday>0,_time+604800,_time) | stats max(*) as * by _time

[somesoni2]

If last & days include today as well, use condition if( _time>=relative_time(now(),"-7d@d"),1,0) else it's correct (except you're missing a d after 7). If the previous 7 day is -14d to -7d (7 days prior to last 7 day period), use condition if(_time<relative_time(now(),"-7d@d") AND _time>=relative_time(now(),"-14d@d"),1,0). Also, the where condition should be adjusted to keep last 14 days events ( last 7 day + 7 prior days), so change it to _time>=relative_time(now(),"-14d@d").

[jgbricker]

I appreciate all the help and guidance!

This appears to be working -

`my_alerts` earliest=-30d@d latest=now | eval Last7day=if(_time<relative_time(now(),"@d") AND _time>=relative_time(now(),"-7d@d"),1,0)
| eval Previous7day=if(_time<relative_time(now(),"-7d@d") AND _time>=relative_time(now(),"-14d@d"),1,0) 
| eval Last30day=if(_time<relative_time(now(),"@d"),1,0) | bucket span=1h _time | stats sum(*day) as *day by _time
| eventstats sum(Last30day) as tLast30day count(eval(Last30day>0)) as cLast30day | eval Last30dayAvg=tLast30day/cLast30day
| table _time Last7day Previous7day Last30dayAvg  | where _time>=relative_time(now(),"-14d@d")  
| eval _time=if(_time<relative_time(now(),"-7d@d") AND _time>=relative_time(now(),"-14d@d"),_time+604800,_time) | stats max(*) as * by _time