Editing current finding variance search

[bryhoffman]

Hello,

I have an search that is used on a dashboard that I would like tweaked.

Currently this search/panel displays the variance of current hour over the same hour the week before. for example: The value at hour 10 on Wed 7/19/23 will be compared to the value at hour 10 on Wed 7/12/23 and give variance.

Instead, I would like to compare current hour value to the value of the AVG of that same hour over the last 2 weeks (instead of compared to 1 day). For example I would like hour 10 on Wed 7/19/23 to be compared to the avg of hour 10 each day from Tues 7/18/23 to Wed 7/5/23.

Current search:

| tstats count where index=msexchange host=SMEXCH13* earliest=-14d@d latest=-13d@d by _time span=1h
| eval hour=strftime(_time,"%H")
| eval ReportKey="2weekprior"
| stats values(count) as count by hour, ReportKey
| append
[| tstats count where index=msexchange host=SMEXCH13* earliest=-7d@d latest=-6d@d by _time span=1h
| eval hour=strftime(_time,"%H")
| eval ReportKey="1weekprior"
| stats values(count) as count by hour, ReportKey ]
| append
[| tstats count where index=msexchange host=SMEXCH13* earliest=-0d@d latest=-0h@h by _time span=1h
| eval hour=strftime(_time,"%H")
| eval ReportKey="currentweek"
| stats values(count) as count by hour, ReportKey ]
| eval currenthour=strftime(_time,"%H")
| xyseries hour, ReportKey, count
| eval nowhour = strftime(now(),"%H")
| eval comparehour = nowhour-1
|where hour<=comparehour
|sort by -hour
| table hour,nowhour,comparehour, currentweek,1weekprior,2weekprior
|eval 1weekvar = currentweek/'1weekprior'
|eval 2weekvar = currentweek/'2weekprior'
|eval variance=round(((('1weekvar'+'2weekvar')/2)*100)-100,2)
|table hour,variance
|head 5

[yuanliu · Accepted solution]

The search is a lot simpler if we just follow your description of the problem:

| tstats count where index=msexchange host=SMEXCH13* earliest=-2w@d latest=-0h@h by _time span=1h
| eval hour = strftime(_time,"%H")
| eval interval = if(_time > relative_time(now(), "-0d@d"), "today", "past2weeks")
| stats avg(count) as count by hour interval
| xyseries hour interval count
| where isnotnull(today)
| eval variance = round((today / past2weeks - 1) * 100, 2)
| fields - count interval

Note:

1. The past2weeks calculation does not include today's.  In most cases, this is acceptable but today's can be included if necessary.
2. The "variance" calculation is based on your description and the sample code you provided, namely, the percentage difference between today's value and the mean value in the past two weeks.  This is not the common mathematical definition of variance; and the value can be negative.