Compare values between yesterday and today

[imheejin]

Hi All,

I am a newbie in Splunk world and looking for some help in structuring my query.

I have an index with data like this -

index=sec_sso sourcetype=sso_insa earlist=-1d@d latest=now
| eval Day=if(_time<relative_time(now(), "@d"), "Yesterday", "Today")
| eventstats count by EMINO DPRTM_NAME
| search count=1 
| table EMINO DPRTM_NAME Day
 

If there is only today's value (new registration), I want to change this query to exclude that value. 

[javiergn]

Hi @imheejin ,

If you want to exclude all of those values that only exist today, then you can do something like this:

index=sec_sso sourcetype=sso_insa earliest=-1d@d
| bin span=1d _time
| eval Day=if(_time<relative_time(now(), "@d"), "Yesterday", "Today")
| eventstats dc(_time) as count by EMINO DPRTM_NAME
| search NOT (count=1 Day="Today")
| table EMINO DPRTM_NAME Day

[imheejin]

Thanks for the comment. I'll give you more information.

The query I want to make is -

1. Compare the DPRTM_NAME yesterday and today to find someone with a different value. (Display both the person's yesterday and today DPRTM_NAME)

2. In the result of number 1, exclude the information of the person who was newly registered today (the person who does not have a yesterday value).

Unfortunately, the query you constructed excludes all values ​​of Today.

[javiergn]

Hi, the query above excludes all values of Today where the distinct count (dc) of the adjusted _time is 1, meaning they did not exist Yesterday.

bin will basically round _time to the day so that only two values are used in your example: yesterday and today.

Then evenstats will calculate the dc (distinct count) of _time grouped by EMINO and DPRTM_NAME.  If the combined values of EMINO and DPRTM_NAME are different between Yesterday and Today the count will be 2, or if those values only existed yesterday then the count will be 1 but Day will be "Yesterday". If that makes sense.

What you are describing below is slightly different to what you posted initially and it's still not 100% clear to me.

Can you paste an example in tabular format with the fields involved and what the desired output would be?