topic Re: How can I sort results for previous week and current week in two seperate columns? in Splunk Search

How can I sort results for previous week and current week in two seperate columns?

wanda619 — Fri, 09 Sep 2022 14:55:34 GMT

Hi Folks,

How can i display the results for 2022-09-02 in Result_Prev column and 2022-09-09 in Result column and keeping the other columns same. Calculating Percent difference from a this week to the previous week results.

any help is appreciated. Thank you !

Re: How can I sort results for previous week and current week in two seperate columns?

yuanliu — Sun, 11 Sep 2022 01:42:39 GMT

You didn't specify how frequently data is collected, so I'll assume that they can be aggregated into as a weekly stats by something like earliest=-2w@w latest=0w@w. With this assumption, you can do

* earliest=-2w@w latest=0w@w | bin span=1w@w _time | stats count by CustomerName Error _time | stats earliest(count) as ResultPrev latest(count) as Result max(_time) as _time by CustomerName Error | eventstats sum(Result) as "Percent of Total" | eval "Percent Difference" = round((Result - ResultPrev)/ResultPrev * 100, 2), "Percent of Total" = round(Result / 'Percent of Total' * 100, 2)

Of course, replace "*" with your real search.

Re: How can I sort results for previous week and current week in two seperate columns?

wanda619 — Tue, 13 Sep 2022 19:07:40 GMT

@yuanliu The data is collected daliy as there are daily transactions and the error are shown daily

Re: How can I sort results for previous week and current week in two seperate columns?

wanda619 — Wed, 14 Sep 2022 16:58:56 GMT

@yuanliu I am having this error when I am trying to use the above code:

Re: How can I sort results for previous week and current week in two seperate columns?

yuanliu — Sat, 17 Sep 2022 18:11:25 GMT

@wanda619 My mistake. Should be -0w@w.

* earliest=-2w@w latest=-0w@w | bin span=1w@w _time | stats count by CustomerName Error _time | stats earliest(count) as ResultPrev latest(count) as Result max(_time) as _time by CustomerName Error | eventstats sum(Result) as "Percent of Total" | eval "Percent Difference" = round((Result - ResultPrev)/ResultPrev * 100, 2), "Percent of Total" = round(Result / 'Percent of Total' * 100, 2)

Re: How can I sort results for previous week and current week in two seperate columns?

wanda619 — Tue, 20 Sep 2022 15:12:17 GMT

@yuanliu when i try to search itby the above code you mentioned, I did not get the errors i needed as it is blanking and giving 0 outputs on results column.

Re: How can I sort results for previous week and current week in two seperate columns?

yuanliu — Tue, 20 Sep 2022 17:24:20 GMT

If "Results" column is 0, that means count in the first stats command is also 0, i.e., if you run

* earliest=-2w@w latest=-0w@w | bin span=1w@w _time | stats count by CustomerName Error _time

count should all be zero. Can you confirm that? Maybe there's some spelling error? Does the main search return anything?