topic Re: Need Help With Simple NOT Search in Splunk Search

Need Help With Simple NOT Search

andrewkenth — Wed, 11 Dec 2013 17:50:13 GMT

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

Re: Need Help With Simple NOT Search

kristian_kolb — Wed, 11 Dec 2013 18:13:19 GMT

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

Re: Need Help With Simple NOT Search

somesoni2 — Wed, 11 Dec 2013 18:15:31 GMT

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

Re: Need Help With Simple NOT Search

andrewkenth — Wed, 11 Dec 2013 19:47:26 GMT

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?

Re: Need Help With Simple NOT Search

andrewkenth — Wed, 11 Dec 2013 20:08:22 GMT

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

Re: Need Help With Simple NOT Search

kristian_kolb — Wed, 11 Dec 2013 20:12:52 GMT

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

Re: Need Help With Simple NOT Search

kristian_kolb — Wed, 11 Dec 2013 20:15:42 GMT

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

Re: Need Help With Simple NOT Search

somesoni2 — Wed, 11 Dec 2013 21:07:16 GMT

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

Re: Need Help With Simple NOT Search

andrewkenth — Wed, 11 Dec 2013 22:00:42 GMT

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.

Re: Need Help With Simple NOT Search

aelliott — Thu, 12 Dec 2013 14:32:57 GMT

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)

Re: Need Help With Simple NOT Search

andrewkenth — Thu, 12 Dec 2013 15:36:32 GMT

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