topic Re: Using Stats List to get multiple UserIDs into a single field by IP. Need to sort by UserID in Splunk Search

Using Stats List to get multiple UserIDs into a single field by IP. Need to sort by UserID

GenericSplunkUs — Tue, 14 Nov 2017 22:05:08 GMT

Might have trouble explaining this in an understandable way, might be why I was unable to google my answer.

I'm using | stats list(userID) by IP | on a search for UserID logins. I get the results of the IP and what UserIDs have logged into it. I need to sort this list I have now by the list(UserID) field with the most UserIDs in it.

Since these values are getting put into a single field list(userID) i'm not sure I can sort them by count like I want to.

Any advice? I eventually want to set an alert when an IP has more than a certain number of Users logging into it. But I'm just trying to get this search into my dashboard first.

Re: Using Stats List to get multiple UserIDs into a single field by IP. Need to sort by UserID

maciep — Tue, 14 Nov 2017 22:19:24 GMT

FYI, list() will list the users in the same order as the events, including duplicates. If you want a "list" of unique users, use values().

To answer the question, you'll just want to get a count of the users in stats as well and then you can sort by that.

So with the way you have it

...  | stats list(userID) as users count by IP | sort 0 - count

OR with the way you have it, but using a distinct count

... | stats list(userID) as users dc(userID) as count by IP | sort 0 - count

OR with a list of distinct users and a distinct count

.... | stats values(userID) as users dc(userID) as count by IP| sort 0 - count

Re: Using Stats List to get multiple UserIDs into a single field by IP. Need to sort by UserID

niketn — Tue, 14 Nov 2017 22:23:39 GMT

@GenericSplunkUser, you should read about statistical and charting functions to understand what could be useful when you are trying to use a transforming command like stats.

The list() function will give your all userIDs including the duplicates in the same order as they occurred (i.e. reverse chronological order as per data). However, list() can show maximum 100 results. Which implies when your IP has more than 100 times user login (can be coming from same user as it accounts for duplicates as well), it will just display 100.

The values() function on the other hand will only show unique userIDs however, the results will be sorted in alphabetical order, since the function sorts and find unique values. This can account for more than 100 users (unique) but not all logins.

You would need to use count() and distinct_count() ( also used as abbreviated function dc() ) to know total user logins including duplicates and unique respectively. Following is the search you can try:

  <YourBaseSearch>
 | stats dc(usertID) as distinct_user_count count(usertID) user_count values(userID) as distinct_userIDs by IP
 | sort - user_count

Refer to documentation of Statistical Functions: http://docs.splunk.com/Documentation/Splunk/latest/SearchReference/CommonStatsFunctions#Types_of_statistical_and_charting_functions

Re: Using Stats List to get multiple UserIDs into a single field by IP. Need to sort by UserID

niketn — Tue, 14 Nov 2017 22:24:38 GMT

@maciep, you beat me to it 🙂

Re: Using Stats List to get multiple UserIDs into a single field by IP. Need to sort by UserID

GenericSplunkUs — Tue, 14 Nov 2017 22:30:14 GMT

index=Events EventType="Logon" | dedup userID | stats list(user) by ip

I dedup the userID before I did the stats command so it would only pull a single UserID. Did I just go about that weird and created my issue?

I think I just answered my own question here with your first suggestion.

I did the "Index=Events EventType="Logon" | dedup userID | | stats list(userID) as users count by IP | sort 0 - count" And this is working as I wanted. Giving unique users per IP with a count I can sort on.

Thanks!

Re: Using Stats List to get multiple UserIDs into a single field by IP. Need to sort by UserID

GenericSplunkUs — Tue, 14 Nov 2017 22:32:45 GMT

Thanks for pointing me in a direction for research. I got an answer that is working for me above but I'm going to take your advice and see how well that does.

Re: Using Stats List to get multiple UserIDs into a single field by IP. Need to sort by UserID

maciep — Tue, 14 Nov 2017 22:36:36 GMT

be careful with dedup. In your search you'll remove dup ids, but what if one user has logged into multiple IPs? Only one IP will get that count.

If you're going to dedup, do it by both: ... | dedup userID, IP