Splunk Search

Editing current finding variance search

bryhoffman
Explorer

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.

bryhoffman_0-1689801500388.png

 

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

Labels (1)
0 Karma
1 Solution

yuanliu
SplunkTrust
SplunkTrust

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.

View solution in original post

yuanliu
SplunkTrust
SplunkTrust

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.
Get Updates on the Splunk Community!

Accelerate Service Onboarding, Decomposition, Troubleshooting - and more with ITSI’s ...

Accelerate Service Onboarding, Decomposition, Troubleshooting - and more! Faster Time to ValueManaging and ...

New Release | Splunk Enterprise 9.3

Admins and Analyst can benefit from:  Seamlessly route data to your local file system to save on storage ...

2024 Splunk Career Impact Survey | Earn a $20 gift card for participating!

Hear ye, hear ye! The time has come again for Splunk's annual Career Impact Survey!  We need your help by ...