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
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
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
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)
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.
...and perhaps I should ask, 'unexpected how?'?
what if you use fields + Date, Username
instead of table
at the end of the subsearch
They do appear to be correct. Only when I combine them do I see unexpected results.
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
No, I have missed it. Please include NOT after "search" command in line 5 of search.
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?
What do the results look like when you run the searches separately. Ok?