I have the following search looking for > three login attempts with > 0 successes and two or > failures by user, src, Country, Region, and City which limits me to searching for all five fields.
index="foo" sourcetype="foo:bar" tag=authentication "Primary authentication"
| dedup _time
| iplocation src
| stats count(action) as Attempts, count(eval(match(action,"failure"))) as Failed,
count(eval(match(action,"success"))) as Success
earliest(_time) as FirstAttempt latest(_time) as LatestAttempt by user src Country Region City
| where Attempts>=3 AND Success>0 AND Failed>=2
| eval FirstAttempt=strftime(FirstAttempt,"%x %X")
| eval LatestAttempt=strftime(LatestAttempt,"%x %X")
How would I modify this search to include the values from the src, Country, Region, and City fields but be based on just the user so that I would see events in which a user tried to login from New York and Panama, and China, etc.?
Thx
Like this (including other optimizations)
index="foo" sourcetype="foo:bar" tag=authentication "Primary authentication"
| stats count(action) AS Attempts, count(eval(action="failure")) AS Failed,
min(_time) AS FirstAttempt max(_time) AS LatestAttempt BY user src
| eval Success = Attempts - Failed
| iplocation src
| rename COMMENT AS "This creates: Country Region City"
| where Attempts>=3 AND Success>0 AND Failed>=2
| eval FirstAttempt=strftime(FirstAttempt,"%x %X")
| eval LatestAttempt=strftime(LatestAttempt,"%x %X")
| sort 0 - Attempts
| stats list(*) AS * BY src
Like this (including other optimizations)
index="foo" sourcetype="foo:bar" tag=authentication "Primary authentication"
| stats count(action) AS Attempts, count(eval(action="failure")) AS Failed,
min(_time) AS FirstAttempt max(_time) AS LatestAttempt BY user src
| eval Success = Attempts - Failed
| iplocation src
| rename COMMENT AS "This creates: Country Region City"
| where Attempts>=3 AND Success>0 AND Failed>=2
| eval FirstAttempt=strftime(FirstAttempt,"%x %X")
| eval LatestAttempt=strftime(LatestAttempt,"%x %X")
| sort 0 - Attempts
| stats list(*) AS * BY src
TYVM - worked perfectly
Hi jwalzerpitt,
did you tried to ad the values(City) AS City
etc... options to the stats command?
Ciao.
Giuseppe
One other thing I noticed about this search if I just run the following over the past 24 hours limiting to 'by user' is the inconsistency of the search in that I get zero events and then re-run it a few times and I get one user listed and then re-run a few more times and get a different user, but usually zero events are returned and I can't figure out why both users don't show up every times I run the search as they fall within the 24 hour time period. If I filter for each user I get results for each one, but this search is general never returns the same correct results
index="foo" sourcetype="foo:bar" tag=authentication "Primary authentication"
| dedup _time
| iplocation src
| stats count(action) as Attempts, count(eval(match(action,"failure"))) as Failed,
count(eval(match(action,"success"))) as Success
earliest(_time) as FirstAttempt latest(_time) as LatestAttempt by user
| where Attempts>=3 AND Success>0 AND Failed>=2
| eval FirstAttempt=strftime(FirstAttempt,"%x %X")
| eval LatestAttempt=strftime(LatestAttempt,"%x %X")
Did you try using chart
| chart count(action) as Attempts, count(eval(match(action,"failure"))) as Failed,
count(eval(match(action,"success"))) as Success
earliest(_time) as FirstAttempt latest(_time) as LatestAttempt by user Country
I modified the search as follows, using chart and putting where at the bottom, and know everytime I runt he search I no longer get zero results, but it bounces randomly between the two users, but never listing them together
| dedup _time
| iplocation src
| chart count(action) as Attempts, count(eval(match(action,"failure"))) as Failed,
count(eval(match(action,"success"))) as Success
earliest(_time) as FirstAttempt latest(_time) as LatestAttempt by user
| eval FirstAttempt=strftime(FirstAttempt,"%x %X")
| eval LatestAttempt=strftime(LatestAttempt,"%x %X")
| where Attempts>=3 AND Success>0 AND Failed>=2