Help grouping eval results by City

[zsizemore]

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!

[sundareshr]

Replace Table command with the following...

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

[gfreitas · Accepted solution]

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

[zsizemore]

Thanks, this does help! (I think)

Do i need to leave the dedup City in there?

[gfreitas]

No, you can delete this dedup from the search

[zsizemore]

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.

[gfreitas]

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

[zsizemore]

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.