topic Re: Compare value with the value of the previous week in Splunk Search

Compare value with the value of the previous week

exchanger — Mon, 08 Mar 2021 14:57:56 GMT

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

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?

Re: Compare value with the value of the previous week

gcusello — Mon, 08 Mar 2021 14:34:26 GMT

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

Re: Compare value with the value of the previous week

exchanger — Mon, 08 Mar 2021 14:41:12 GMT

Hello,

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

Re: Compare value with the value of the previous week

gcusello — Mon, 08 Mar 2021 15:48:45 GMT

Hi @exchanger,

which time period did you used in your search?

You need at least 2 weeks.

Ciao.

Giuseppe

Re: Compare value with the value of the previous week

exchanger — Mon, 08 Mar 2021 16:01:56 GMT

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

Re: Compare value with the value of the previous week

gcusello — Mon, 08 Mar 2021 16:10:30 GMT

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

Re: Compare value with the value of the previous week

exchanger — Mon, 08 Mar 2021 16:55:23 GMT

@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?

Re: Compare value with the value of the previous week

gcusello — Mon, 08 Mar 2021 17:09:15 GMT

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

Re: Compare value with the value of the previous week

exchanger — Mon, 08 Mar 2021 18:15:34 GMT

@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?

Re: Compare value with the value of the previous week

gcusello — Tue, 09 Mar 2021 08:13:19 GMT

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

Re: Compare value with the value of the previous week

gcusello — Tue, 09 Mar 2021 11:34:38 GMT

hI @exchanger

good for you!

Ciao and happy splunking.

Giuseppe

P.S.: Karma Points are appreciated 😉