Comparing data from 2 days by data type?

[Min1025]

Hi

I want to compare the data from 2 days by data type, my expected result is as below, is it possible?

Data Type	Yesterday	Yesterday Count	Today	Today Count	Count Change Rate
A	2023/3/26	15	2023/3/27	18	0.20
B	2023/3/26	20	2023/3/27	19	-0.05
C	2023/3/26	16	2023/3/27	35	1.19
D	2023/3/26	21	2023/3/27	40	0.90
E	2023/3/26	30	2023/3/27	25	-0.17
F	2023/3/26	40	2023/3/27	50	0.25

[Min1025]

Hi @gcusello,

Thank you for the query. I tried this query and encountered 2 issues.

1. The previous date returned both previous day and yesterday.

2. The previous count is same as yesterday count.

Do you have any idea for above issues?

Thanks,

Min

[gcusello]

Hi @Min1025,

please try this:

<your_search> earliest=-2d@d latest=@d
| eval 
   day=strftime(_time,"%Y-%m-%d"),
   yesterday=strftime(now()-86400,"%Y-%m-%d"),
   previous_day=strftime(now()-172800,"%Y-%m-%d")
| stats 
   values(previous_day) AS "Previous day" 
   count(eval(if(day=previous_day,1,0))) AS "Previous day Count"
   values(yesterday) AS "Yesterday" 
   count(eval(if(day=yesterday,1,0))) AS "Yesterday Count"
   BY "Data Type"
| eval "Count Change Rate"=round(1-"Previous day Count"/"Yesterday Count",2)

Ciao.

Giuseppe

[gcusello]

HI @Min1025,

there's a thing that I don't understand: todays count will always be less than yesterdat count because today is in progress, maybe it could be better to count two days ago with yesterday, to have two full days to compare, anyway, please try something like this:

<your_search> earliest=-d@d latest=
| eval day=strftime(_time,"%Y-%m-%d"), today=strftime(now(),"%Y-%m-%d")
| stats 
   values(eval(if(day!=today,day,"") AS "Yesterday" 
   count(eval(if(day!=today,1,0)))  AS "Yesterday Count"
   values(eval(if(day=today,day,"") AS "Today" 
   count(eval(if(day=today,1,0)))  AS "Today Count"
   BY "Data Type"
| eval "Count Change Rate"=round(1-"Yesterday Count"/"Today Count",2)

Ciao.

Giuseppe

[Min1025]

Hi @gcusello,

Thank you for the query. You are right, it's better to count two days ago with yesterday and compare the results for two full days.  Could you please guide the query for this?

I tried the query you provided but I got the same count for "today" and "yesterday", not sure why.

Thanks,

Min

[gcusello]

Hi @Min1025,

ok, in this case, you have only to modify the time period and something else, please try this:

<your_search> earliest=-2d@d latest=@d
| eval 
   day=strftime(_time,"%Y-%m-%d")
   previous_day=if(now()-_time<86400,day,"")
   yesterday=if(now()-_time>86400,day,"")
| stats 
   values(previous_day) AS "Previous day" 
   count(eval(if(day=previous_day,1,0))) AS "Previous day Count"
   max(yesterday) AS "Yesterday" 
   count(eval(if(day=yesterday,1,0))) AS "Yesterday Count"
   BY "Data Type"
| eval "Count Change Rate"=round(1-"Previous day Count"/"Yesterday Count",2)

 Ciao.

Giuseppe