Splunk Search

Help grouping eval results by City

Path Finder

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!

0 Karma
1 Solution

Builder

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

View solution in original post

0 Karma

Legend

Replace Table command with the following...

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

Builder

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

View solution in original post

0 Karma

Path Finder

Thanks, this does help! (I think)

Do i need to leave the dedup City in there?

0 Karma

Builder

No, you can delete this dedup from the search

0 Karma

Path Finder

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.

0 Karma

Builder

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

0 Karma

Path Finder

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.

0 Karma