How to edit my search to find users whose accounts have been locked after X number of invalid credential entries?

[thambisetty_bal]

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

[somesoni2]

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

[thambisetty_bal]

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.

[somesoni2]

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"

[thambisetty_bal]

Yes sir, exactly , I am trying to find that.

[niketn]

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?

[thambisetty_bal]

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,