Splunk Search

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

Motivator

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

0 Karma
1 Solution

Esteemed Legend

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

View solution in original post

Esteemed Legend

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

View solution in original post

Motivator

TYVM - worked perfectly

0 Karma

SplunkTrust
SplunkTrust

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

Ciao.
Giuseppe

0 Karma

Motivator

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

Contributor

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
0 Karma

Motivator

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
0 Karma
State of Splunk Careers

Access the Splunk Careers Report to see real data that shows how Splunk mastery increases your value and job satisfaction.

Find out what your skills are worth!