I have the following fields:
User HostName Access
User A machine A SSH
User A machine A VPN
User A machine B SSH
User B machine B SSH
User B machine B SMB
User C machine C SSH
and so on....
How do I create a table that will list the user showing the unique values of either HostName or Access? I want to be able to search for users who are coming from multiple machines and/or using multiple access methods.
Thx
| stats values(HostName), values(Access) by User
will give you a table of User, HostName, and Access where the HostName and Access cells have the distinct values listed in lexicographical order.
Ref:
Stats Functions
| stats values(HostName), values(Access) by User
will give you a table of User, HostName, and Access where the HostName and Access cells have the distinct values listed in lexicographical order.
Ref:
Stats Functions
Actually doing just | stats dc(HostName) as "HostCount", dc(Access) as "AccessCount" by User gives me a nice summary table for the user's host count and access count.
Looking at the example you sent me (and perhaps I'm not stating with enough clarity so apologizes there) for example for acurry@buttercup games.com, how do you get the unique/distinct count for each values(usage) so it would look like the following:
values(usage) - count
Borderline - 58
Business - 42
Personal - 46
Unknown - 50
Violation - 50
(count 246 is broken down in each category) for acurry (and every other user as well)
Thx
Hi alijohson Great Answer, wish we could make unique values of business as Columns , which would give nice table look
Kind of below example
User Name Business Borderline Personal Violation Other
abc@xyz.com 44 32 23 43 333
Ah I see.
sourcetype=cisco_wsa_squid | stats count by usage, cs_username | stats list(count), values(usage) by cs_username
The difference being list(count)
That's it! Can't hank you enough for your time and help - greatly appreciated!
Happy Splunking ^__^
Al,
Thx a million for the reply and info.
How would I now add counts to the data (for either filed) and then sort by the count?
| stats values(HostName), values(Access), count(HostName) as "HostCount", count(Access) as "AccessCount" by User
| sort -UsageCount
OR
| stats values(HostName), values(Access), count(HostName) as "HostCount", count(Access) as "AccessCount" by User
| sort -AccessCount
depending on which one you want to sort by
Thx again as that worked perfectly, What I'm trying to do now is to get a breakdown in counts for either HostName or Access as such:
User Values(Access) Values(HostName) HostCount AccessCount
UserA VPN MachineA 4 1
UserA SSH MachineB 2 4
ETC
Isn't that what the above does ? Breakdown in a different sense?
It does break it down, but I'm not seeing counts per Hostname or per Access - seeing a total count of both Hostname and Access.
Thx
So If you just do | stats dc(HostName) as "HostCount", dc(Access) as "AccessCount" by User
you're getting two columns of identical values? I'm guessing its because you might just be using count, which is counting the events with that value present rather than occurrences of different values, which I think is what you're looking for.
See this screenshot for an example search and the difference: http://i.imgur.com/KLVbW9N.png
perhaps dc
is better than c
for Hostname and Access
Kristian,
Great suggestion on dc as that worked great as well
Definitely, great point - especially since its paired with values (which are unique too)