Splunk Search

How to create a table listing users and unique values for other associated fields as HostName or Access?

Motivator

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

1 Solution

Splunk Employee
Splunk Employee

| 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

View solution in original post

Splunk Employee
Splunk Employee

| 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

View solution in original post

Motivator

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

0 Karma

New Member

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

0 Karma

Splunk Employee
Splunk Employee

Ah I see.

sourcetype=cisco_wsa_squid | stats count by usage, cs_username | stats list(count), values(usage) by cs_username

alt text

Splunk Employee
Splunk Employee

The difference being list(count)

Motivator

That's it! Can't hank you enough for your time and help - greatly appreciated!

0 Karma

Splunk Employee
Splunk Employee

Happy Splunking ^__^

0 Karma

Motivator

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?

0 Karma

Splunk Employee
Splunk Employee

| 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

Motivator

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

0 Karma

Splunk Employee
Splunk Employee

Isn't that what the above does ? Breakdown in a different sense?

0 Karma

Motivator

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

0 Karma

Splunk Employee
Splunk Employee

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

Ultra Champion

perhaps dc is better than c for Hostname and Access

Motivator

Kristian,

Great suggestion on dc as that worked great as well

0 Karma

Splunk Employee
Splunk Employee

Definitely, great point - especially since its paired with values (which are unique too)