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.
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
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.
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?