topic Group results by eval syntax in Splunk Search

Group results by eval syntax

zsizemore — Thu, 23 Jun 2016 18:46:06 GMT

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

Re: Group results by eval syntax

somesoni2 — Thu, 23 Jun 2016 18:51:35 GMT

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

Re: Group results by eval syntax

woodcock — Thu, 23 Jun 2016 18:52:07 GMT

Add this:

... | stats count AS numUsers BY Country term

Re: Group results by eval syntax

zsizemore — Thu, 23 Jun 2016 19:15:12 GMT

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?

Re: Group results by eval syntax

somesoni2 — Thu, 23 Jun 2016 19:21:29 GMT

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 *

Re: Group results by eval syntax

zsizemore — Tue, 29 Sep 2020 10:02:24 GMT

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

Re: Group results by eval syntax

somesoni2 — Thu, 23 Jun 2016 19:34:00 GMT

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

Re: Group results by eval syntax

zsizemore — Thu, 23 Jun 2016 19:40:28 GMT

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.

Re: Group results by eval syntax

somesoni2 — Thu, 23 Jun 2016 20:14:13 GMT

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

Re: Group results by eval syntax

zsizemore — Thu, 23 Jun 2016 20:28:48 GMT

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