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
... View more