https://community.splunk.com/t5/Splunk-Search/How-to-create-a-table-listing-users-and-unique-values-for-other/m-p/115986#M30782 <P>That's it! Can't hank you enough for your time and help - greatly appreciated!</P> Wed, 12 Nov 2014 20:38:44 GMT jwalzerpitt 2014-11-12T20:38:44Z https://community.splunk.com/t5/Splunk-Search/How-to-create-a-table-listing-users-and-unique-values-for-other/m-p/115972#M30768 <P>I have the following fields:</P> <P>User HostName Access<BR /> User A machine A SSH<BR /> User A machine A VPN<BR /> User A machine B SSH<BR /> User B machine B SSH<BR /> User B machine B SMB<BR /> User C machine C SSH</P> <P>and so on....</P> <P>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.</P> <P>Thx</P> Tue, 11 Nov 2014 20:30:38 GMT https://community.splunk.com/t5/Splunk-Search/How-to-create-a-table-listing-users-and-unique-values-for-other/m-p/115972#M30768 jwalzerpitt 2014-11-11T20:30:38Z https://community.splunk.com/t5/Splunk-Search/How-to-create-a-table-listing-users-and-unique-values-for-other/m-p/115973#M30769 <P><CODE> | stats values(HostName), values(Access) by User </CODE> will give you a table of User, HostName, and Access where the HostName and Access cells have the distinct values listed in lexicographical order.</P> <P>Ref:<BR /> <A href="http://docs.splunk.com/Documentation/Splunk/latest/SearchReference/CommonStatsFunctions">Stats Functions</A> </P> Tue, 11 Nov 2014 21:20:43 GMT https://community.splunk.com/t5/Splunk-Search/How-to-create-a-table-listing-users-and-unique-values-for-other/m-p/115973#M30769 aljohnson_splun 2014-11-11T21:20:43Z https://community.splunk.com/t5/Splunk-Search/How-to-create-a-table-listing-users-and-unique-values-for-other/m-p/115974#M30770 <P>Al,</P> <P>Thx a million for the reply and info.</P> <P>How would I now add counts to the data (for either filed) and then sort by the count?</P> Tue, 11 Nov 2014 21:36:41 GMT https://community.splunk.com/t5/Splunk-Search/How-to-create-a-table-listing-users-and-unique-values-for-other/m-p/115974#M30770 jwalzerpitt 2014-11-11T21:36:41Z https://community.splunk.com/t5/Splunk-Search/How-to-create-a-table-listing-users-and-unique-values-for-other/m-p/115975#M30771 <P><CODE>| stats values(HostName), values(Access), count(HostName) as "HostCount", count(Access) as "AccessCount" by User<BR /> | sort -UsageCount</CODE></P> <P>OR</P> <P><CODE>| stats values(HostName), values(Access), count(HostName) as "HostCount", count(Access) as "AccessCount" by User<BR /> | sort -AccessCount</CODE></P> <P>depending on which one you want to sort by</P> Tue, 11 Nov 2014 22:19:41 GMT https://community.splunk.com/t5/Splunk-Search/How-to-create-a-table-listing-users-and-unique-values-for-other/m-p/115975#M30771 aljohnson_splun 2014-11-11T22:19:41Z https://community.splunk.com/t5/Splunk-Search/How-to-create-a-table-listing-users-and-unique-values-for-other/m-p/115976#M30772 <P>perhaps <CODE>dc</CODE> is better than <CODE>c</CODE> for Hostname and Access</P> Wed, 12 Nov 2014 07:36:43 GMT https://community.splunk.com/t5/Splunk-Search/How-to-create-a-table-listing-users-and-unique-values-for-other/m-p/115976#M30772 kristian_kolb 2014-11-12T07:36:43Z https://community.splunk.com/t5/Splunk-Search/How-to-create-a-table-listing-users-and-unique-values-for-other/m-p/115977#M30773 <P>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:</P> <P>User Values(Access) Values(HostName) HostCount AccessCount<BR /> UserA VPN MachineA 4 1<BR /> UserA SSH MachineB 2 4<BR /> ETC </P> Wed, 12 Nov 2014 12:46:50 GMT https://community.splunk.com/t5/Splunk-Search/How-to-create-a-table-listing-users-and-unique-values-for-other/m-p/115977#M30773 jwalzerpitt 2014-11-12T12:46:50Z https://community.splunk.com/t5/Splunk-Search/How-to-create-a-table-listing-users-and-unique-values-for-other/m-p/115978#M30774 <P>Kristian,</P> <P>Great suggestion on <STRONG>dc</STRONG> as that worked great as well</P> Wed, 12 Nov 2014 12:47:36 GMT https://community.splunk.com/t5/Splunk-Search/How-to-create-a-table-listing-users-and-unique-values-for-other/m-p/115978#M30774 jwalzerpitt 2014-11-12T12:47:36Z https://community.splunk.com/t5/Splunk-Search/How-to-create-a-table-listing-users-and-unique-values-for-other/m-p/115979#M30775 <P>Definitely, great point - especially since its paired with values (which are unique too)</P> Wed, 12 Nov 2014 16:24:27 GMT https://community.splunk.com/t5/Splunk-Search/How-to-create-a-table-listing-users-and-unique-values-for-other/m-p/115979#M30775 aljohnson_splun 2014-11-12T16:24:27Z https://community.splunk.com/t5/Splunk-Search/How-to-create-a-table-listing-users-and-unique-values-for-other/m-p/115980#M30776 <P>Isn't that what the above does ? Breakdown in a different sense?</P> Wed, 12 Nov 2014 17:05:16 GMT https://community.splunk.com/t5/Splunk-Search/How-to-create-a-table-listing-users-and-unique-values-for-other/m-p/115980#M30776 aljohnson_splun 2014-11-12T17:05:16Z https://community.splunk.com/t5/Splunk-Search/How-to-create-a-table-listing-users-and-unique-values-for-other/m-p/115981#M30777 <P>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.</P> <P>Thx</P> Wed, 12 Nov 2014 17:09:43 GMT https://community.splunk.com/t5/Splunk-Search/How-to-create-a-table-listing-users-and-unique-values-for-other/m-p/115981#M30777 jwalzerpitt 2014-11-12T17:09:43Z https://community.splunk.com/t5/Splunk-Search/How-to-create-a-table-listing-users-and-unique-values-for-other/m-p/115982#M30778 <P>So If you just do <CODE> | stats dc(HostName) as "HostCount", dc(Access) as "AccessCount" by User</CODE> 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.</P> <P>See this screenshot for an example search and the difference: <A href="http://i.imgur.com/KLVbW9N.png">http://i.imgur.com/KLVbW9N.png</A></P> Wed, 12 Nov 2014 18:01:43 GMT https://community.splunk.com/t5/Splunk-Search/How-to-create-a-table-listing-users-and-unique-values-for-other/m-p/115982#M30778 aljohnson_splun 2014-11-12T18:01:43Z https://community.splunk.com/t5/Splunk-Search/How-to-create-a-table-listing-users-and-unique-values-for-other/m-p/115983#M30779 <P>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.</P> <P>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:</P> <P>values(usage) - count<BR /> Borderline - 58<BR /> Business - 42<BR /> Personal - 46<BR /> Unknown - 50<BR /> Violation - 50</P> <P>(count 246 is broken down in each category) for acurry (and every other user as well)</P> <P>Thx</P> Wed, 12 Nov 2014 18:36:44 GMT https://community.splunk.com/t5/Splunk-Search/How-to-create-a-table-listing-users-and-unique-values-for-other/m-p/115983#M30779 jwalzerpitt 2014-11-12T18:36:44Z https://community.splunk.com/t5/Splunk-Search/How-to-create-a-table-listing-users-and-unique-values-for-other/m-p/115984#M30780 <P>Ah I see. </P> <P><CODE> sourcetype=cisco_wsa_squid | stats count by usage, cs_username | stats list(count), values(usage) by cs_username </CODE></P> <P><IMG src="http://i.imgur.com/py44vwB.png" alt="alt text" /></P> Mon, 28 Sep 2020 18:09:22 GMT https://community.splunk.com/t5/Splunk-Search/How-to-create-a-table-listing-users-and-unique-values-for-other/m-p/115984#M30780 aljohnson_splun 2020-09-28T18:09:22Z https://community.splunk.com/t5/Splunk-Search/How-to-create-a-table-listing-users-and-unique-values-for-other/m-p/115985#M30781 <P>The difference being <CODE>list(count)</CODE></P> Wed, 12 Nov 2014 18:48:11 GMT https://community.splunk.com/t5/Splunk-Search/How-to-create-a-table-listing-users-and-unique-values-for-other/m-p/115985#M30781 aljohnson_splun 2014-11-12T18:48:11Z https://community.splunk.com/t5/Splunk-Search/How-to-create-a-table-listing-users-and-unique-values-for-other/m-p/115986#M30782 <P>That's it! Can't hank you enough for your time and help - greatly appreciated!</P> Wed, 12 Nov 2014 20:38:44 GMT https://community.splunk.com/t5/Splunk-Search/How-to-create-a-table-listing-users-and-unique-values-for-other/m-p/115986#M30782 jwalzerpitt 2014-11-12T20:38:44Z https://community.splunk.com/t5/Splunk-Search/How-to-create-a-table-listing-users-and-unique-values-for-other/m-p/115987#M30783 <P>Happy Splunking ^__^ </P> Wed, 12 Nov 2014 20:59:46 GMT https://community.splunk.com/t5/Splunk-Search/How-to-create-a-table-listing-users-and-unique-values-for-other/m-p/115987#M30783 aljohnson_splun 2014-11-12T20:59:46Z https://community.splunk.com/t5/Splunk-Search/How-to-create-a-table-listing-users-and-unique-values-for-other/m-p/115988#M30784 <P>Hi alijohson Great Answer, wish we could make unique values of business as Columns , which would give nice table look </P> <P>Kind of below example </P> <P>User Name Business Borderline Personal Violation Other<BR /> <A href="mailto:abc@xyz.com">abc@xyz.com</A> 44 32 23 43 333</P> Thu, 20 Jul 2017 15:46:22 GMT https://community.splunk.com/t5/Splunk-Search/How-to-create-a-table-listing-users-and-unique-values-for-other/m-p/115988#M30784 cvrkishore 2017-07-20T15:46:22Z