topic Re: Stats/Chart count distinct users by Country and eval field? in Splunk Search

Stats/Chart count distinct users by Country and eval field?

zsizemore — Wed, 06 Jul 2016 14:27:46 GMT

Hi,

I have a query showing the amount of distinct logins by IP address based on the "term" i've created in the query.

| 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 by IP_address,term
| iplocation IP_address 
| where (isnotnull(Country) AND isnotnull(City) AND NOT Country="United States" AND trim(Country)!="" AND trim(City)!="")
| stats sum("Very Short") AS "Very Short", sum(Short) AS Short, sum(Long) AS Long by Country, City

After doing some cross-checking, I realized these results are counting multiple values for users if that makes sense. So if "xxx99" had three "Very Short" logins and "xxx90" had 2 "Very Short" logins, its counting it as 5 "Very Short", when in fact I need it to count as 2 (the amount of users that were categorized as Very Short at least once).

I've tried getting around this myself with 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") 
| iplocation IP_address 
| stats dc(User) AS usercount by Country City term
| where (isnotnull(Country) AND isnotnull(City) AND NOT Country="United States" AND trim(Country)!="" AND trim(City)!="")
| search Country=Azerbaijan
| chart sum(usercount) over Country by term

but with those results, I can't seem to get it to display the Country AND City, not just the City.

Any thoughts?

Thanks!

Re: Stats/Chart count distinct users by Country and eval field?

somesoni2 — Wed, 06 Jul 2016 17:51:38 GMT

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") 
 | iplocation IP_address 
 | where (isnotnull(Country) AND isnotnull(City) AND NOT Country="United States" AND trim(Country)!="" AND trim(City)!="")
 | stats dc(User) AS usercount by Country City term
 | search Country=Azerbaijan | eval temp=Country."##".City
 | chart sum(usercount) over temp by term | rex field=temp "(?<Country>.+)##(?<City>.+)" | table Country, City "Very Short", Short, Long

Re: Stats/Chart count distinct users by Country and eval field?

zsizemore — Thu, 07 Jul 2016 13:48:13 GMT

Thanks this seems to work. I changed the order of the first eval to eval term=case(term>(86400*7), "Long", term>86400 AND term<(86400*7), "Short", term<86400, "Very Short") so that if a user is identified as Long, they can't also be counted for short/very short.

I also added a dedup User after the stats dc(User) cmd line which looks to do what I've been looking for!

Re: Stats/Chart count distinct users by Country and eval field?

zsizemore — Thu, 07 Jul 2016 13:50:40 GMT

could I ask what exactly the eval temp= line is doing? I can't seem to find much info about that or the rex cmd how it works.

Re: Stats/Chart count distinct users by Country and eval field?

somesoni2 — Thu, 07 Jul 2016 15:07:38 GMT

The temp= line is combining two fields Country and City ( your can use any name for that matter, I used temp as it's a temporary field). So this way we ran the chart with 3 field grouping (using temp). Once the data is charted, we're using rex command to separate out those combined fields.

Re: Stats/Chart count distinct users by Country and eval field?

zsizemore — Thu, 07 Jul 2016 19:56:29 GMT

Okay very interesting, i'm learning a lot still. Is there a way to display this with geostats?