topic Re: Comparing data from 2 days by data type in Splunk Search

Comparing data from 2 days by data type?

Min1025 — Mon, 27 Mar 2023 14:17:09 GMT

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

Re: Comparing data from 2 days by data type

gcusello — Mon, 27 Mar 2023 06:54:58 GMT

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

Re: Comparing data from 2 days by data type

Min1025 — Tue, 28 Mar 2023 08:17:16 GMT

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

Re: Comparing data from 2 days by data type

gcusello — Tue, 28 Mar 2023 08:36:52 GMT

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

Re: Comparing data from 2 days by data type?

Min1025 — Thu, 30 Mar 2023 10:31:52 GMT

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

Re: Comparing data from 2 days by data type?

gcusello — Thu, 30 Mar 2023 11:13:21 GMT

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