topic Re: Grouping results by count in Splunk Search

Grouping results by count

rchille — Fri, 12 Apr 2013 17:48:44 GMT

I have a search that returns values in a table like this:

USER	TIME	IP	Location
user1	time1	ip1	loc1
user1	time2	ip1	loc1
user2	time2	ip2	loc2
user1	time3	ip3	loc1
user3	time3	ip4	loc4
user1	time4	ip4	loc1

I want to search by grouped User Counts so I tried something like this:

...| stats Count by User values(User) values(Time) values(IP) values (Location)

Which gives me:

USER	TIME	IP	Location
user1	time1	ip1	loc1
	time2	ip3
	time3

user2	time2	ip2	loc2
user3	time3	ip4	loc4
user1	time4	ip4	loc1

What I'm really after is:

USER	TIME	IP	Location
user1	time1	ip1	loc1
	time2	ip3	loc1
	time3	ip3	loc1

user2	time2	ip2	loc2
user3	time3	ip4	loc4
user1	time4	ip4	loc1

It looks like the stats command decouples the fields and reports the TIME IP and LOC based on a column perspective.
I checked around a bit and it looks like evenstats may get me closer, but haven't been able to get it to work either.

Can this be done in Splunk? If so, can someone point me in the right direction?
Thanks!

Re: Grouping results by count

Ayn — Fri, 12 Apr 2013 18:02:24 GMT

Uh, I don't see how your query can result in that output. I don't even see a count field in that table?

Re: Grouping results by count

kristian_kolb — Fri, 12 Apr 2013 18:46:48 GMT

I don't understand either. Your desired output has user1 in two places. Wouldn't you want to have them listed on a per user basis?

Could ...| stats values(TIME) values(IP) values(Location) by USER |...

be what you're after? Bear in mind that the resulting lists will be independently sorted.

Try list() instead of values() if you want all values, not just the distinct.

Re: Grouping results by count

narwhal — Fri, 12 Apr 2013 19:00:24 GMT

I guess I'm missing something too--but wouldn't the useful output be more like:

... | stats count by User,Time,IP,Location | ...

???

-tv

Re: Grouping results by count

rchille — Fri, 12 Apr 2013 19:24:29 GMT

Thanks for the quick replies!
Sorry, I did leave out the 'count' field ... and as for the 2 user1's, copy/paste isn't my friend.

The problem with list or value is exactly that: I get a list of the IPs in one column, a list of Times in the next with but no relationship between the values along the row.

I'm looking to create a list of connections (TIME + IP + LOC) for all of my users. If user1 makes 4 connections during the day, I can look at the display and read off the details of each of the connections.

Thanks again!

Re: Grouping results by count

rchille — Fri, 12 Apr 2013 19:48:57 GMT

Kristian was pointed me the correct direction, I was after:

...| stats Count by User list(User) list(Time) values(IP) list(Location)

Instead of:

...| stats Count by User values(User) values(Time) values(IP) values(Location)

I really thought that I'd tried that, Thanks!