Need Help With Simple NOT Search

[andrewkenth]

I can't beleive I'm coming to Answers to ask this as I've done it many times before but I must be missing something that I'm hoping you can help me find.

I have a list of events by user and date and I want to show any of those user/date combiation not in the list of user date combinations below:

List of Events with Users (and Date)

index=charlesriver (sourcetype=SQLAPP_Events OR sourcetype=SQLRPT_Events) "Login succeeded for user "  | bucket span=1d _time | stats count first(_time) as Date by _time, SqlServerLogon | rename SqlServerLogon as UserName

List of Allowed Users (and Date)

index=charlesriver (sourcetype=SqlServer_AppDB_Users OR sourcetype=SqlServer_RptDB_Users) | bucket span=1d _time | stats count first(_time) as Date by _time, UserName | table Date, UserName 


index=charlesriver (sourcetype=SQLAPP_Events OR sourcetype=SQLRPT_Events) "Login succeeded for user "  | bucket span=1d _time | stats count first(_time) as Date by _time, SqlServerLogon | rename SqlServerLogon as UserName
| search NOT [ search 
index=charlesriver (sourcetype=SqlServer_AppDB_Users OR sourcetype=SqlServer_RptDB_Users) | bucket span=1d _time | stats count first(_time) as Date by _time, UserName | table Date, UserName 
] 
| eval Date=strftime(Date,"%m/%d/%Y") | table Date, UserName, count | sort -Date

My raw data and props.conf look like this:

"TimeStamp","ServerName","HostName","DBName","UserName","LoginType","AssociatedRole"
"Dec 10 2013 12:36AM","426420-SQLCLUS1","426099-WELLDB1","ConfigLogging","dbo","WINDOWS_USER","db_owner"

NO_BINARY_CHECK = 1
pulldown_type = 1
HEADER_MODE = firstline
FIELD_DELIMITER=,
FIELD_QUOTE="
TIME_FORMAT=%b %d %Y %H:%M%p
TIMESTAMP_FIELDS=TimeStamp

[andrewkenth · Accepted solution]

Something seemed to have been wrong with my search string as this works:

index=charlesriver (sourcetype=SQLAPP_Events OR sourcetype=SQLRPT_Events) "Login succeeded for user "
|  bucket span=1d _time | stats count first(_time) as Date by _time, SqlServerLogon | rename SqlServerLogon as UserName | table Date UserName count 
| search NOT [ search 
index=charlesriver sourcetype=SqlServer_AppDB_Users OR sourcetype=SqlServer_RptDB_Users | bucket span=1d _time | stats count first(_time) as Date by _time, UserName | table Date UserName | sort -Date
] | eval Date=strftime(Date,"%m/%d/%Y") | table Date, UserName, count | sort -Date

[aelliott]

try including the trim search command in the usernames to see if there are spaces after them.

Eval UserName=trim(SqlServerLogon)

Eval UserName=trim(UserName)

[andrewkenth]

Unexpected meaning I almost always have a match and this search shows that there are never matches. I can manually find the same Date/UserName combination value in each list.

[kristian_kolb]

...and perhaps I should ask, 'unexpected how?'?

[kristian_kolb]

what if you use fields + Date, Username instead of table at the end of the subsearch

[andrewkenth]

They do appear to be correct. Only when I combine them do I see unexpected results.

[somesoni2]

Try following

index=charlesriver (sourcetype=SQLAPP_Events OR sourcetype=SQLRPT_Events) "Login succeeded for user "  
| bucket span=1d _time | stats count first(_time) as Date by SqlServerLogon | rename SqlServerLogon as UserName
|search [index=charlesriver (sourcetype=SqlServer_AppDB_Users OR sourcetype=SqlServer_RptDB_Users) 
| bucket span=1d _time| stats first(_time) as Date by UserName]
| eval Date=strftime(Date,"%m/%d/%Y") | table Date, UserName, count | sort -Date

[somesoni2]

No, I have missed it. Please include NOT after "search" command in line 5 of search.

[andrewkenth]

Am I missing something though? I want everything in the 1st list that is NOT in the 2nd list. Shouldn't I be using the NOT search?

[kristian_kolb]

What do the results look like when you run the searches separately. Ok?