Splunk Search

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

jwalzerpitt
Influencer

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

aljohnson_splun
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

aljohnson_splun
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

jwalzerpitt
Influencer

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

cvrkishore
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

aljohnson_splun
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

aljohnson_splun
Splunk Employee
Splunk Employee

The difference being list(count)

jwalzerpitt
Influencer

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

0 Karma

aljohnson_splun
Splunk Employee
Splunk Employee

Happy Splunking ^__^

0 Karma

jwalzerpitt
Influencer

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

aljohnson_splun
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

jwalzerpitt
Influencer

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

aljohnson_splun
Splunk Employee
Splunk Employee

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

0 Karma

jwalzerpitt
Influencer

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

aljohnson_splun
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

kristian_kolb
Ultra Champion

perhaps dc is better than c for Hostname and Access

jwalzerpitt
Influencer

Kristian,

Great suggestion on dc as that worked great as well

0 Karma

aljohnson_splun
Splunk Employee
Splunk Employee

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

Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...