Group results by eval syntax

[zsizemore]

Hi, i'm trying to group my results from these eval commands

| 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 count dc(User) as usercount values(term) as term by IP_address
| stats sum(usercount) as TotalUsers by term
| iplocation IP_address

this gives me the terms (Very short, short, long) and the Total users for the respective terms.

what i need to do is show the TotalUsers for the three terms -- for their locations around the world..

So for example,

China: Very short - 10 users
Short - 5 users
Long - 1 user

I'm pretty new to Splunk so i'm not completely sure if this is possible, i've been googling and messing around with this the past few days and can't really make any headway.

Any help is appreciated 🙂

Thanks

[woodcock]

Add this:

... | stats count AS numUsers BY Country term

[somesoni2 · Accepted solution]

Try this

your base search| 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 City "Very Short" Short Long

[zsizemore]

Thanks for the quick response -- this seems to pretty much do what I was looking for.

I'm having trouble sorting this table, I tried sort 0 term and sort 0 usercount .

Also would it be possible then to display this data on a map with geostats to be able to hover over a country/region and see each term's usercount for it?

[somesoni2]

The column names have been changes to "Very Short", Short and Long (see the last table command), so to sort it based on any of those terms, use | sort 0 "Very Short and so on.

Try this for geostats

 your base search| 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 "Very Short" Short Long lat lon | geostats latfield=lat longfield=lon values(*) as *

[zsizemore]

with that, hovering over the country, the fields displayed are "gs_cntlat, gs_cntlong, gs_sumlat, gs_sumlong"

[somesoni2]

How about this (I'm probably not the best person for geostats)

your base search| 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 City "Very Short" Short Long lat lon | geostats latfield=lat longfield=lon values('Very Short') as "Very Short" values(Short) as Short values(Long) as Long by City

[zsizemore]

i've got an error in 'geostats' command: the argument 'Short' is invalid. It doesn't give an error for 'Very Short' which comes before it, so i'm not sure why its giving an error for that..

That's fine if you're not the best person for geostats haha all of this is very much appreciated.

[somesoni2]

Mind trying this

your base search| 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  | geostats latfield=lat longfield=lon values(usercount) by term

[zsizemore]

This seems to be working! Thanks for all of your help, I should be able to play around with this myself.