How to run stats for just user and return values for other fields?

[jwalzerpitt]

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

[woodcock · Accepted solution]

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

[jwalzerpitt]

TYVM - worked perfectly

[gcusello]

Hi jwalzerpitt,
did you tried to ad the values(City) AS City etc... options to the stats command?

Ciao.
Giuseppe

[jwalzerpitt]

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")

[sandeepmakkena]

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

[jwalzerpitt]

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