Everyday I bring in events (with a logon id, USER below) and a list of approved users. I want to compare the 2 lists together and show unmatched USER fields, but only for the same date between the event list and the approved users list comes in. Currently I am matching but I seem to be joining across all dates. How do I limit the join to only the same date (and USER)?
index=charlesriver sourcetype=CrdApp_Events EVENT=LOGIN
| bucket span=1d _time
| stats count first(_time) as Date by USER
| search NOT
[search index=charlesriver sourcetype=CrdApp_Users
| bucket span=1d _time
| stats count first(_time) as Date by USER_NAME
| table USER_NAME|rename USER_NAME as USER]
One option can be to use join
index=charlesriver sourcetype=CrdApp_Events EVENT=LOGIN
| bucket span=1d _time
| stats count first(_time) as Date by USER
| join Date,USER [search index=charlesriver sourcetype=CrdApp_Users
| bucket span=1d _time
| stats count first(_time) as Date by USER_NAME
| table USER_NAME,Date|rename USER_NAME as USER |eval filter=1]
|where NOT filter=1 | fields - filter
Can you try like this (added Date field in subsearch)
index=charlesriver sourcetype=CrdApp_Events EVENT=LOGIN | bucket span=1d _time | stats count first(_time) as Date by USER |search NOT [search index=charlesriver sourcetype=CrdApp_Users | bucket span=1d _time | stats count first(_time) as Date by USER_NAME| table Date,USER_NAME|rename USER_NAME as USER]