Hi,
I have been facing issue with f5 APM logs. The device creates multiple events for single session so each line will have individual data but the session id is the same. I could make the table using stats command. Now I would like to find out which users whose account are locked after 7 Invalid user credentials, below is the sample
_time sessionid device
_time sessionid src_ip
_time sessionid user
_time sessionid logon_status (logon_status would be either "account is locked" or "Invalid user credentials"
search : stats values(device) values(src_ip) values(logon_status) values(user) by sessionid
(with this i will get a table, now I would like to find out users whose account is locked after 7 Invalid user credentials.)
your help is very much appreciated
Try like this
your base search
| stats values(device) as device values(src_ip) as src_ip values(user) as user count(eval(logon_status="Invalid user credentials")) as invalid_user_attempt_count latest(logon_status) as last_logon_status by sessionid
| where last_logon_status="account is locked" AND invalid_user_attempt_count>=7
Thanks for your reply @somesoni2
what if I want to find the user locked out multiple times in given time range. your query works for latest user account lock out. and below is the query which I modified as my requirement.
eventtype=apm_log [search eventtype=apm_log balaji| dedup apm_session_id |table apm_session_id | format]
| rex field=_raw "(?:):\sAccess\spolicy\sresult:\s(?<status>.*)"
| rex field=_raw "(?:)New session from client IP (?<src_ip>\d{1,3}\.\d{1,3}\.\d{1,3}\.\d{1,3})\s\(ST=(?<state>[a-zA-Z-\s]{0,15})\/CC=(?<Country>\w+)\/C=(?<continent>\w{0,5}).*Listener\s\/\w{0,10}\/(?<application>[a-zA-Z]{0,10})"
| rex field=_raw "(?:)Received client info - Type: (?<Type>\w+)"
| rex field=_raw "(?:)AD module.*\w+\@\w+\.\w+\.\w+\.\s(?<logon_status>[\w\s]+)"
| rex field=_raw "(?:)Received User-Agent header:(?<device>.+)" | stats earliest(_time) as _time values(user) as user values(status) as status values(src_ip) as src_ip values(state) as state values(Country) as country values(continent) as continent values(application) as applicaiton values(Type) as Type values(logon_status) as logon_status values(device) as device by apm_session_id | search status="Logon_Deny" | sort - _time | eval n=rtrim(logon_status)
| stats first(_time) values(device) as device values(src_ip) as src_ip count(eval(n="Invalid user credentials")) as invalid_user_attempt_count latest(n) as last_logon_status by user | where last_logon_status="User account is locked" AND invalid_user_attempt_count=7
which will work , when we provide one user and would like to find latest account lock out. In above example user name given is balaji. Is there any best way to write above query.
Thanks once again for your quick response.
Are you trying to find all set of events in below format, for a user??
_time sessionid device
_time sessionid src_ip
_time sessionid user
_time sessionid logon_status="Invalid user credentials"
...6 more sets of above with logon_status="Invalid user credentials" ....
_time sessionid device
_time sessionid src_ip
_time sessionid user
_time sessionid logon_status="account is locked"
Yes sir, exactly , I am trying to find that.
Can you provide some samples of data from your table created by stats?
Is it possible to include sum(eventcount) as LoginAttempts or include a field with dummy counter like eval attempt=1 to all events and use sum(attempts) as LoginAttmepts?
Hi,
Here is sample of data after running below search :
eventtype=apm_log [search eventtype=apm_log balaji| dedup apm_session_id |table apm_session_id | format]
| rex field=_raw "(?:):\sAccess\spolicy\sresult:\s(?.*)"
| rex field=_raw "(?:)New session from client IP (?\d{1,3}\.\d{1,3}\.\d{1,3}\.\d{1,3})\s\(ST=(?[a-zA-Z-\s]{0,15})\/CC=(?\w+)\/C=(?\w{0,5}).*Listener\s\/\w{0,10}\/(?[a-zA-Z]{0,10})"
| rex field=_raw "(?:)Received client info - Type: (?\w+)"
| rex field=_raw "(?:)AD module.*\w+\@\w+\.\w+\.\w+\.\s(?[\w\s]+)"
| rex field=_raw "(?:)Received User-Agent header:(?.+)" | stats earliest(_time) as _time values(user) as user values(status) as status values(src_ip) as src_ip values(state) as state values(Country) as country values(continent) as continent values(application) as applicaiton values(Type) as Type values(logon_status) as logon_status values(device) as device by apm_session_id | search status="Logon_Deny" | sort - _time | eval n=rtrim(logon_status)
Below are the results:
apm_session_id _time user status src_ip state country continent applicaiton Type logon_status device n
37dd882f 2017-01-15 15:21:35 balaji Logon_Deny 10.147.0.115 Ash Sharqiyah SA AS RMAIL activesync User account is locked Apple-iPhone7C2%2f1401.456. User account is locked
db5dcc5a 2017-01-15 15:21:32 balaji Logon_Deny 10.147.0.115 Ash Sharqiyah SA AS RMAIL activesync User account is locked Apple-iPhone7C2%2f1401.456. User account is locked
d83f611d 2017-01-15 15:21:01 balaji Logon_Deny 10.147.0.115 Ash Sharqiyah SA AS RMAIL activesync User account is locked Apple-iPhone7C2%2f1401.456. User account is locked
c8335a12 2017-01-15 15:20:59 balaji Logon_Deny 10.147.0.115 Ash Sharqiyah SA AS RMAIL activesync User account is locked Apple-iPhone7C2%2f1401.456. User account is locked
fec983f9 2017-01-15 15:20:52 balaji Logon_Deny 10.147.0.115 Ash Sharqiyah SA AS RMAIL activesync User account is locked Apple-iPhone7C2%2f1401.456. User account is locked
740536a1 2017-01-15 15:20:48 balaji Logon_Deny 10.147.0.115 Ash Sharqiyah SA AS RMAIL activesync Invalid user credentials Apple-iPhone7C2%2f1401.456. Invalid user credentials
7d413a12 2017-01-15 15:20:32 balaji Logon_Deny 10.147.0.115 Ash Sharqiyah SA AS RMAIL activesync Invalid user credentials Apple-iPhone7C2%2f1401.456. Invalid user credentials
86524305 2017-01-15 15:20:27 balaji Logon_Deny 10.147.0.115 Ash Sharqiyah SA AS RMAIL activesync Invalid user credentials Apple-iPhone7C2%2f1401.456. Invalid user credentials
f1956a8c 2017-01-15 15:19:21 balaji Logon_Deny 10.147.0.115 Ash Sharqiyah SA AS RMAIL activesync Invalid user credentials Apple-iPhone7C2%2f1401.456. Invalid user credentials
29448aae 2017-01-15 15:19:18 balaji Logon_Deny 10.147.0.115 Ash Sharqiyah SA AS RMAIL activesync Invalid user credentials Apple-iPhone7C2%2f1401.456. Invalid user credentials
86a099aa 2017-01-15 15:19:02 balaji Logon_Deny 10.147.0.115 Ash Sharqiyah SA AS RMAIL activesync Invalid user credentials Apple-iPhone7C2%2f1401.456. Invalid user credentials
a0cac77b 2017-01-15 15:18:57 balaji Logon_Deny 10.147.0.115 Ash Sharqiyah SA AS RMAIL activesync Invalid user credentials Apple-iPhone7C2%2f1401.456. Invalid user credentials
I would like to see account locked user the number of times he locked like below:
_time user src_ip device country
xxxx user1 xxxxx xxxxxx xxxxx (latest account lockout)
xxxx user1 xxxxx xxxxxx xxxxx (previous account lockout)
thanks,