Splunk Search

Compare value with the value of the previous week

exchanger
Path Finder

Hello,

I want to compare the value of the week before last with the value of my search (last week) and put the percentage growth/decrease in the result.
Currently I can display the value of the previous week to me and in another search the value of the week before last. However, I cannot compare the two values.
My query looks like this:
In search I set last week
"..." | stats count as lastweek | append [search"..." earliest=-14d latest=-7d | stats count as lastweek1

Result:

Lastweek         lastweek1

         XX

                                XX

Desired Result:

Lastweek             lastweek1            Change

XX                             XX                           50%
This gives me two values, but I can't compare them, at least I don't know how. Of course it would be even more elegant if I had the value already in the first query without having to use append. Is there a solution, that I have the values of the previous week in the same query, so that I can compare both values?

 

Labels (1)
0 Karma
1 Solution

gcusello
Legend

Hi @exchanger,

try something like this:

Your_search earliest=-14d latest=now
| eval week=if(now()-_time<604800,"This week","Last week")
| stats 
     count(eval(week="This week")) AS "Count this week" 
     count(eval(week="Last week")) AS "Count last week" 
     avg(eval(if(week="This week",DURATION,"")) AS "AVG this week" 
     avg(eval(if(week="Last week",DURATION,"")) AS "AVG last week"

Ciao.

Giuseppe

View solution in original post

0 Karma

gcusello
Legend

Hi @exchanger,

you have to identify the week in your search using the eval command:

Your_search earliest=-14d latest=now
| eval week=if(_time-now()<604800,"This week","Last week")
| stats count By week

Ciao.

Giuseppe

0 Karma

exchanger
Path Finder

exchanger_0-1615214385258.png

Hello,

So I get then only one result (see screenshot)
The goal would be
Last week             Week before Last week             Change
50                              100                                                    xx%

0 Karma

gcusello
Legend

Hi @exchanger,

which time period did you used in your search?

You need at least 2 weeks.

Ciao.

Giuseppe

0 Karma

exchanger
Path Finder

Hi, there I can set what I want. But maybe I understand your question or answer wrong.

exchanger_0-1615219304231.png

 

0 Karma

gcusello
Legend

Hi @exchanger,

Sorry: my error!

Your_search earliest=-14d latest=now
| eval week=if(now()-_time<604800,"This week","Last week")
| stats count By week

Ciao.

Giuseppe

 

0 Karma

exchanger
Path Finder

@gcusello 

Thank you, so I get both values. However, if I want to put them in a ratio, I don't know how that should work. Because I don't have two variables but the variable "week" which has two values.
Or is there a way with your command to calculate the increase/decrease from the second to the last week?

0 Karma

gcusello
Legend

Hi @exchanger,

use eval inside stats command:

Your_search earliest=-14d latest=now
| eval week=if(now()-_time<604800,"This week","Last week")
| stats count(eval(week="This week")) AS "This week" count(eval(week="Last week")) AS "Last week"

so you have two values on the same row and you can calculate a percentage.

Ciao.

Giuseppe

0 Karma

exchanger
Path Finder

@gcusello 

 

Thank you very much. Now I can also put these in relation. However, I still have one last question. Apart from stats count, I am also asking avg, max etc. So the command looks like this :


stats count(eval(week="This week")) AS "This week" count(eval(week="Last week")) AS "Last week", avg(DURATION) as AVG, max(DURATION) as MAX

I want to have the comparison from the last week to the week before last not only for stats count but also for AVG max etc. I am not sure how to change the command avg(DURATION) to give me the data from last week and how to change it to give me the data from the week before last. Since I have already defined "Last week" and "this week" I would probably have to put them in the brackets "(DURATION"), I just wonder how?

 

0 Karma

gcusello
Legend

Hi @exchanger,

try something like this:

Your_search earliest=-14d latest=now
| eval week=if(now()-_time<604800,"This week","Last week")
| stats 
     count(eval(week="This week")) AS "Count this week" 
     count(eval(week="Last week")) AS "Count last week" 
     avg(eval(if(week="This week",DURATION,"")) AS "AVG this week" 
     avg(eval(if(week="Last week",DURATION,"")) AS "AVG last week"

Ciao.

Giuseppe

0 Karma

gcusello
Legend

hI @exchanger

good for you!

Ciao and happy splunking.

Giuseppe

P.S.: Karma Points are appreciated 😉

0 Karma
Get Updates on the Splunk Community!

Routing Data to Different Splunk Indexes in the OpenTelemetry Collector

This blog post is part of an ongoing series on OpenTelemetry. The OpenTelemetry project is the second largest ...

Getting Started with AIOps: Event Correlation Basics and Alert Storm Detection in ...

Getting Started with AIOps:Event Correlation Basics and Alert Storm Detection in Splunk IT Service ...

Register to Attend BSides SPL 2022 - It's all Happening October 18!

Join like-minded individuals for technical sessions on everything Splunk!  This is a community-led and run ...