Subsearch with multiple field matching

[andrewkenth]

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]

[somesoni2]

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

[somesoni2]

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]