I would like to find out failed login attempts with Event Code (4625) here the condition is failed login attempts with in one hour and if the failed attempts less than that what ever the event code present in the index will be displayed.?

[90509]

Hi All,

I would like to find out failed login attempts with Event Code (4625) , here the condition is failed login attempts happen with in one hour which are >6 should represent with 4625 and if the failed login attempts less than 6< then what ever the event code which is presented in that index will be displayed.I have got first part of the condition but how could I display how many times a event code happens in that index if the user tried to access?

the first part of the query:
index="..........." AND sourcetype=".........." AND tag=pci AND NOT ((user=null) OR (tag=..........)) AND EventCode=4625
| fillnull value="N/A"
| bin _time span=1hr as HourDay
| eval hourDay=strftime(HourDay,"%m-%d-%y %H:%M:%S")
| stats count last(hourDay) as timestamp by user, HourDay, EventCode, Account_Domain, Account_Name, dvc_owner,dvc_bunit,dvc_ip, subject, dvc
| where count>6
| fields - HourDay
| search NOT
[| inputlookup PCI_service_accounts_list.csv
| rename service_accounts as user]

Second part of the Query:

searchindex="..........." AND sourcetype=".........." AND tag=pci AND NOT ((user=null) OR (tag=..........)) AND EventCode=4720 OR EventCode=4722 OR EventCode=4738
| fillnull value="N/A"
| bin _time span=1hr as HourDay
| eval hourDay=strftime(HourDay,"%m-%d-%y %H:%M:%S")
| stats count last(hourDay) as timestamp by user, HourDay, EventCode, Account_Domain, Account_Name, dvc_owner,dvc_bunit,dvc_ip, subject, dvc
| where count>1
| fields - HourDay
| search NOT
[| inputlookup PCI_service_accounts_list.csv
| rename service_accounts as user]

please let me know how do i perform these two search in a single search bar with defined conditions. if it won't work please let me know best search method.

[sandeepmakkena]

searchindex="..........." AND sourcetype=".........." AND tag=pci AND NOT ((user=null) OR (tag=..........)) AND EventCode=4720 OR EventCode=4722 OR EventCode=4738 OR EventCode=4625 NOT [| inputlookup PCI_service_accounts_list.csv | rename service_accounts as user]
| fillnull value="N/A"
| bin _time span=1hr as HourDay
| eval hourDay=strftime(HourDay,"%m-%d-%y %H:%M:%S")
| eval failed_login_count=if(searchmatch("EventCode=4625"),1,0)
| eventstats sum(failed_login_count) as total_failed_login_count
| eval other_event_count=if(searchmatch("EventCode!=4625"),1,0)
| eventstats sum(other_event_count) as total_other_event_count
| stats count last(hourDay) as timestamp by user, HourDay, EventCode, Account_Domain, Account_Name, dvc_owner,dvc_bunit,dvc_ip, subject, dvc
| where total_failed_login_count>6 OR total_other_event_count>1 AND total_other_event_count<6
| fields - HourDay

Try this and let me know if you have any problem with it.
Thanks!

[90509]

Thanks for your assistance but the code only showing <6 failed attempts and which is only showing 4625 I am not able to see remaining event codes, so how could we overcome this.

[90509]

one more thing if keep
| stats count last(hourDay) as timestamp by user, HourDay, EventCode, Account_Domain, Account_Name, dvc_owner,dvc_bunit,dvc_ip, subject, dvc

I am not able to see any statistics if I keep |fields -HourDay. if I remove |fields -HourDay and keep |stats as it is I am not able to see any statistics as well.

If I move |stats to last line and remove |filelds -HourDay then I am getting statistics.so please suggest me how to do this.

[sandeepmakkena]

I don't think you need this |filelds -HourDay.

| where total_failed_login_count>6 OR total_other_event_count>1 can you just this condition. I am sorry, I don't have relevant data to test it.

Thanks.

[wenthold]

1. If you're filtering out anything, it's best to do it as early as possible. By filtering out users at the end instead of the base search you're spending computation time on results you're going to then remove. I moved the last ("search NOT [| inputlookup ...." to your base search to avoid this.
2. I also changed the "AND NOT ((user=null) ..." , the NOT operator is less efficient and since the user field should exist in all the events it can be used here. Use the "NOT" operator where you want to include events where a field doesn't exist. I left the NOT operator in for the tags, since tags may not always exist in some events.
3. The search command "user!=null" or "NOT user=null" will filter out any events where the user is literally named "null". If you have an account named null and you want to exclude it, this will work. If you want to only include fields where "user" is a field that exists, you can just search with "user=*" instead.

You can combine the searches and just use the search command with the OR operator to account for both conditions:

index="..........." AND sourcetype=".........." AND tag=pci AND user!=null NOT (tag=..........) (EventCode=4720 OR EventCode=4722 OR EventCode=4738 OR EventCode=4625)  NOT [| inputlookup PCI_service_accounts_list.csv | rename service_accounts as user]
| fillnull value="N/A" user, dvc_owner, dvc_bunit, dvc_ip, subject, dvc
| bin _time span=1hr as HourDay
| eval hourDay=strftime(HourDay,"%m-%d-%y %H:%M:%S") 
| stats count last(hourDay) as timestamp by user, HourDay, EventCode, Account_Domain, Account_Name, dvc_owner,dvc_bunit,dvc_ip, subject, dvc 
| search (EventCode=4625 count>6) OR (EventCode!=4625 count>1)

[90509]

Thanks for your assistance but the code only showing <6 failed attempts and which is only showing 4625 I am not able to see remaining event codes, so how could we overcome this.

[90509]

| stats count last(hourDay) as timestamp by user, HourDay, EventCode, Account_Domain, Account_Name, dvc_owner,dvc_bunit,dvc_ip, subject, dvc

if I move |stats to last line then I am getting statistics.so please suggest me best way.

[wenthold]

I had set the last search command criteria wrong.

I had set it to "| search (EventCode=4625 count>6) OR (EventCode!=4625 count>2)" but looking back at your original post, your second search was filtering with "| where count>1".

I've updated my response to change it to " "| search (EventCode=4625 count>6) OR (EventCode!=4625 count>1)"

If you still don't get results, simplify the search to look for what isn't there to see if you can find out why. To test, remove the 4625 event criteria from the base search, and remove the filter looking for count>1 and see if you get anything:

index="..........." AND sourcetype=".........." AND tag=pci AND user!=null NOT (tag=..........)  (EventCode=4720 OR EventCode=4722 OR EventCode=4738)  NOT  [| inputlookup PCI_service_accounts_list.csv | rename service_accounts as user]
 | fillnull value="N/A" user, dvc_owner, dvc_bunit, dvc_ip, subject, dvc
 | bin _time span=1hr as HourDay
 | eval hourDay=strftime(HourDay,"%m-%d-%y %H:%M:%S") 
 | stats count last(hourDay) as timestamp by user, HourDay, EventCode, Account_Domain, Account_Name, dvc_owner,dvc_bunit,dvc_ip, subject, dvc 

If you do see the kind of results you expect then add the count>1 back in:

 index="..........." AND sourcetype=".........." AND tag=pci AND user!=null NOT (tag=..........)  (EventCode=4720 OR EventCode=4722 OR EventCode=4738)  NOT [| inputlookup PCI_service_accounts_list.csv | rename service_accounts as user]
 | fillnull value="N/A" user, dvc_owner, dvc_bunit, dvc_ip, subject, dvc
 | bin _time span=1hr as HourDay
 | eval hourDay=strftime(HourDay,"%m-%d-%y %H:%M:%S") 
 | stats count last(hourDay) as timestamp by user, HourDay, EventCode, Account_Domain, Account_Name, dvc_owner,dvc_bunit,dvc_ip, subject, dvc 
 | search count>1

and if that works, then the (updated) full search I provided should work.