How to count eval outputs and display them by City/Country?

[zsizemore]

My ultimate goal is to have a table that displays the "Term" describing the login span, # of users that fall under the respective category, and showing the Country, City.

So basically it groups the amount of user logins by a certain term, and then groups them again by the Country/City.

What I have so far is:

| stats earliest(_time) as first_login latest(_time) as last_login by IP_address User   
| eval term=last_login-first_login   
| eval term=case(term<86400, "Very Short", term>86400 AND term<(86400*7), "Short", term>(86400*7), "Long") 
| chart dc(User) as usercount over IP_address by term
| iplocation IP_address
| table Country City "Very Short" Short Long
| where NOT Country="United States"

I'm pretty new to Splunk and not sure where to go from this to get the results I'm looking for.

Any guidance would be much appreciated.

[woodcock]

Like this:

| stats range(_time) AS term BY IP_address User   
| eval term=case(term<86400,                    "Very Short",
                 term>86400 AND term<(86400*7), "Short",
                 term>(86400*7),                "Long",
                 true(),                        "IMPOSSIBLE!") 
| iplocation IP_address
| where NOT Country="United States"
| stats dc(User) AS usercount BY Country City term
| table Country City term usercount

If there is not a value for both Country and City, then the event will not be counted. If this is undesirable, then you can use fillnull to force values of Unknown.

[woodcock]

This should be considerably more efficient than the accepted solution.

[somesoni2 · Accepted solution]

Are you looking for output like this

Country City Term #Users

If yes then try this

 | stats earliest(_time) as first_login latest(_time) as last_login by IP_address User   
 | eval term=last_login-first_login   
 | eval term=case(term<86400, "Very Short", term>86400 AND term<(86400*7), "Short", term>(86400*7), "Long") 
 | stats dc(User) as usercount by IP_address term
 | iplocation IP_address  | where NOT Country="United States"
 | table Country City term usercount | rename term as Term usercount as "#Users"

[zsizemore]

That is what i'm looking for! (I think)

Is there a way to completely filter out the Country and City when theres no value for one or both of them? I could probably figure out a way to just put "Unknown" for when it can't locate a login as a last resort.

[somesoni2]

Give this a try

| stats earliest(_time) as first_login latest(_time) as last_login by IP_address User   
  | eval term=last_login-first_login   
  | eval term=case(term<86400, "Very Short", term>86400 AND term<(86400*7), "Short", term>(86400*7), "Long") 
  | stats dc(User) as usercount by IP_address term
  | iplocation IP_address  | where (isnotnull(Country) AND isnotnull(City) AND NOT Country="United States")
  | table Country City term usercount | rename term as Term usercount as "#Users"

[zsizemore]

Still getting a lot of rows with no Country or City values, and some only missing City values. I think its okay to leave the unknown City values if there is a Country, but if theres neither, I'd like to just not show those results.

I tried using OR instead of AND and played around with that command line a little bit and still can't seem to exclude those unknown locs.

[somesoni2]

Give this a try

 | stats earliest(_time) as first_login latest(_time) as last_login by IP_address User   
   | eval term=last_login-first_login   
   | eval term=case(term<86400, "Very Short", term>86400 AND term<(86400*7), "Short", term>(86400*7), "Long") 
   | stats dc(User) as usercount by IP_address term
   | iplocation IP_address  | where (isnotnull(Country) AND isnotnull(City) AND NOT Country="United States" AND trim(Country)!="" AND trim(City)!="")
   | table Country City term usercount | rename term as Term usercount as "#Users"

[zsizemore]

That worked!

Seems like it'd be such an easy thing to fix but it was a bit more tricky I guess haha.

Thanks a ton for your help. Now to learn visualization 🙂