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!
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!
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!
I guess I'm missing something too--but wouldn't the useful output be more like:
... | stats count by User,Time,IP,Location | ...
???
-tv
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.
/K
Uh, I don't see how your query can result in that output. I don't even see a count field in that table?