file name comparison between today vs yesterday

[selvam_sekar]

Hi,

I want to create the panel (table) to monitor the todays data vs yesterdays log data as below. 

Please could you help ? how to get the missed data

Current SPL:

basesearch
| stats count as Count_Today by User
| appendcols
[ basesearch
| stats count as Count_Yesterday by User]
| eval Missing=abs(round(VOLUMELASTWEEK-VOLUMETODAY))
| table User Count_Today Count_Yesterday Missing

Expected Result:

User	Count_Today	Count_Yesterday	Missing	Missed File Name
ABC	5	4	1	abc*

[ITWhisperer · Accepted solution]

appendcols is not often the way to go, as is probably the case here too.

The reason for that is the the events which are appended are not correlated with the first set of results, e.g. by user.

You could try using chart

basesearch (including both days)
| bin _time span=1d
| chart count by user _time

This will at least give you the counts so you can subtract one day's count from the other.

However, find out which file or files are missing, is more tricky.

[selvam_sekar]

is there a way to get the difference between today's volume difference vs yesterdays volume difference in percentage ?

Current SPL:

base search earliest=-1d@d latest=now
| eval Day=if(_time<relative_time(now(),"@d"),"Yesterday","Today")
| chart count by User_Id, Day.

Expected Result:

User_Id	Today	Yesterday	Percentage_Difference
abc	5	10	100%
xyz	2	4	100%

[ITWhisperer]

I have no idea what that means, can you give an example of your expected results and how you think they should be calculated?

[selvam_sekar]

sure. for example, user called abc uploaded two files today with name as abc.1 , abc.2.

the same user abc uploaded four files yesterday abc.1, abc.2, abc.3, abc.4.

I want to create the table, with  user name and uploaded files count today and yesterday.. what is missing file count from previous day.

in this scenario,

User	Today	Yesterday	Missing File from previous Day
abc	2	4	2 ( in Percentage) 100%

[ITWhisperer]

How is 2 missing 100%? 100% of what?

[selvam_sekar]

sorry my bad, it should be 50% variance. Today =2, yesterday 4

(Yesterday count - Today count / Yesterday  count )* 100

(4-2 /4)* 100 = >2/4 *100 ==> 50%

[ITWhisperer]

base search earliest=-1d@d latest=now
| eval Day=if(_time<relative_time(now(),"@d"),"Yesterday","Today")
| chart count by User_Id, Day
| eval Percentage_Difference = ((Yesterday - Today) / Yesterday) * 100

[selvam_sekar]

Many thanks for your time and insights @ITWhisperer  🙂 it works as expected.

[selvam_sekar]

Sure, thanks for the note.

is it possible for finding the missing file ? any reference

[ITWhisperer]

| bin _time span=1d
| stats count by user file _time
| eval days_ago = ((relative_time(now(), "@d") - _time) / 84600) + 1
| stats sum(days_ago) as day_flag by user file
| where day_flag < 3

This will give you day_flag = 1 if the file was missing yesterday and day_flag = 2 if the file was missing today

[selvam_sekar]

 I tried this and it seems to returns no results. What I am trying is to compare the file received previous day and whether that's is there in today. and return the actual file name.

for example,

file name in the log say abc.1, abc.2 received previous day and today it will be expected that the same file names and counts are received. Due to some reason, if  abc.1 is not received and we want to display, the abc.1

Current SPL:

basesearch

| bin _time span=1d
| eval days_ago = ((relative_time(now(), "@d") - _time) / 84600) + 1
| stats sum(days_ago) as day_flag by User_Id file
| where day_flag < 3

[ITWhisperer]

There was a typo in my solution - try this

basesearch

| bin _time span=1d
| eval days_ago = ((relative_time(now(), "@d") - _time) / 86400) + 1
| stats sum(days_ago) as day_flag by User_Id file
| where day_flag < 3