NOT IN Search

[andrewkenth]

My apologies if this is a very basic question. I am seeking to run 2 searches and find events in one that have no related events in the other. I have managed to narrow down my 2 searches and all I have left to complete is combining the 2 for 1 result set.

I am looking for all USER's in search 1 that do not match a USER_NAME in search 2. I think I'm missing how to set USER=USER_NAME

I've played around with the NOT filter but that doesn't seem to return any results (when in fact it should return all results from search 1 as there are no matches due to some bad data, which actually works to help me prove my search works in this case!).

Search 1:
index=charlesriver sourcetype=CrdApp_Events EVENT=LOGIN | bucket span=1d _time | stats count first(_time) as Date values(sourcetype) as st_vals values(Message_Type) as msg_type by USER

Search 2:
index=charlesriver sourcetype=CrdApp_Users | bucket span=1d _time | stats count first(_time) as Date by USER_NAME

NOT IN Search:
index=charlesriver sourcetype=CrdApp_Events EVENT=LOGIN | bucket span=1d _time | stats count first(_time) as Date values(sourcetype) as st_vals values(Message_Type) as msg_type by USER NOT [search index=charlesriver sourcetype=CrdApp_Users | bucket span=1d _time | stats count first(_time) as Date by USER_NAME]

[somesoni2]

There are many ways you can do this.

index=charlesriver sourcetype=CrdApp_Events EVENT=LOGIN | bucket span=1d _time | stats count first(_time) as Date values(sourcetype) as st_vals values(Message_Type) as msg_type 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]


 index=charlesriver sourcetype=CrdApp_Events EVENT=LOGIN  [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] | bucket span=1d _time | stats count first(_time) as Date values(sourcetype) as st_vals values(Message_Type) as msg_type by USER

index=charlesriver sourcetype=CrdApp_Events EVENT=LOGIN | bucket span=1d _time | stats count first(_time) as Date values(sourcetype) as st_vals values(Message_Type) as msg_type by USER
|join type=outer USER [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 |eval exclude="Y"] |WHERE NOT exclude="Y"