chart count for comparison

[selvam_sekar]

Hi,

I have the below SPL and I am not able to get the expected results. Please could you help?

if i use stats count by - then i'm not getting the expected result as below.

SPL:

basesearch earliest=@d latest=now
| append
[ search earliest=-1d@d latest=-1d]
| eval Consumer = case(match(File_Name,"^ABC"), "Down", match(File_Name,"^csd"),"UP", match(File_Name,"^CSD"),"UP",1==1,"Others")
| eval Day=if(_time<relative_time(now(),"@d"),"Yesterday","Today")
| eval percentage_variance=abs(round(((Yesterday-Today)/Yesterday)*100,2))
| table Name Consumer Today Yesterday percentage_variance

Expected Result:

Name	Consumer	Today	Yesterday	percentage_variance
TEN	UP	10	10	0.0%

[ITWhisperer · Accepted solution]

Assuming these are counts, you need to get values for Today and Yesterday into the same event in the pipeline. Try something like this

basesearch earliest=@d latest=now
| append
[ search earliest=-1d@d latest=-1d]
| eval Consumer = case(match(File_Name,"^ABC"), "Down", match(File_Name,"^csd"),"UP", match(File_Name,"^CSD"),"UP",1==1,"Others")
| eval Day=if(_time<relative_time(now(),"@d"),"Yesterday","Today")
| stats count by Name Consumer Day
| eval {Day}=count
| fields - Day
| stats values(Today) as Today values(Yesterday) as Yesterday by Name Consumer
| eval percentage_variance=abs(round(((Yesterday-Today)/Yesterday)*100,2))
| table Name Consumer Today Yesterday percentage_variance

[selvam_sekar]

Many Thanks @ITWhisperer . In this SPL Logic how do we ignore the weekend dataand bring only the last working day count for yesterday ? is it possible ?

[ITWhisperer]

Change this line so that it takes into account what the previous day is

[ search earliest=-1d@d latest=-1d]