topic Re: Help grouping eval results by City in Splunk Search

Help grouping eval results by City

zsizemore — Fri, 01 Jul 2016 17:31:31 GMT

Hi,

My current query 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 by IP_address,term | iplocation IP_address | dedup City | where (isnotnull(Country) AND isnotnull(City) AND NOT Country="United States" AND trim(Country)!="" AND trim(City)!="") | table Country City "Very Short" Short Long

when i run the query without dedup City it lists off every event for each city which is a ton of data and can't be read easily. I'm afraid when I use the dedup, its completely getting rid of the values for some of the City's term results.

is there a way to do this? I was thinking it might be easier to just create a summary index with the first half of my report, and then possibly i could just do some sort of stats cmd to count the City results by term or something like that...i'm relatively new to splunk.

if any of my description is confusing i'd be more than happy to try and clarify further.

Thank you!

Re: Help grouping eval results by City

gfreitas — Fri, 01 Jul 2016 19:05:04 GMT

Instead of using "| table Country CIty "Very Shot" Shot Long" use the command: " | stats values ("Very Short") AS "Very Short", values(Short) AS Short, values(Long) AS Long by Country, City

Hope this helps

Re: Help grouping eval results by City

zsizemore — Fri, 01 Jul 2016 19:24:24 GMT

Thanks, this does help! (I think)

Do i need to leave the dedup City in there?

Re: Help grouping eval results by City

gfreitas — Fri, 01 Jul 2016 20:28:43 GMT

No, you can delete this dedup from the search

Re: Help grouping eval results by City

zsizemore — Tue, 05 Jul 2016 13:14:19 GMT

Now its showing different amounts of the values under each Term?

For example:

Country City Very Short
United Kingdom | Ashford | 11
2
3

Instead i need it to display the total of each term's logins so it'd say 16 instead of those 3 separately.

Re: Help grouping eval results by City

sundareshr — Tue, 05 Jul 2016 13:22:23 GMT

Replace Table command with the following...

... | eval cc=Country."#".City | chart count over cc by term | rex field=cc "(?<County>[^#]+)#(?<City>.*)" | fields - cc

Re: Help grouping eval results by City

gfreitas — Tue, 05 Jul 2016 14:42:39 GMT

Looks what you need is even easier:

| stats count("Very Short") AS "Very Short", count(Short) AS Short, count(Long) AS Long by Country, City

With this search it's going to count ocurrences of "Very Short", "Short" and "Long" by Country and City.
You can also sum the numbers inside the fields of "Very Short", "Short" and "Long" with the command:

| stats sum("Very Short") AS "Very Short", sum(Short) AS Short, sum(Long) AS Long by Country, City

Re: Help grouping eval results by City

zsizemore — Tue, 05 Jul 2016 16:53:20 GMT

I tried it with the count() and it shows the same results for each column (VS/S/L), so I tried it with the sum myself earlier and I think that produced what I was looking for.

Thanks again.