Splunk Search

How To Get User Login ID and Login Attempts by Application

SplunkLunk
Path Finder

So I have a list of 11 applications and I want all the user IDs and number of logins attempts for each user over a specified period of time. But I only want the application name to show once. So the report would look something like:

AppNameTitle       User ID      Login Attempts

AppName1            User ID1     10
                    User ID 2    19
                    User ID 3    25

Right now my report looks like:

AppNameTitle       User ID      Login Attempts

AppName1            User ID1     10
AppName1            User ID 2    19
AppName1            User ID 3    25

My main search terms are:

| stats count(USER_ID) as count, values(USER_ID) as USER_ID by APPLICATION_NAME

Any advice? Thanks.

Tags (2)
0 Karma
1 Solution

tauliang
Communicator

Do something like this instead:

 | stats count as Count by APPLICATION_NAME USER_ID
 | stats list(USER_ID) as USER_ID list(Count) as Count by APPLICATION_NAME

A full example including the lines generating data:

| makeresults count=40
| streamstats count 
| eval count = count %4
| eval APPLICATION_NAME = case(count=1 OR count=3, "AppName2", count=0 OR count=2, "AppName1")
| eval USER_ID = case(count=1, "User ID 1", count=0, "User Id 2", count=2, "User Id 3", count=3, "User Id 4")
| stats count as Count by APPLICATION_NAME USER_ID
| stats list(USER_ID) as USER_ID list(Count) as Count by APPLICATION_NAME

You should get what you wanted:

Results

Of course, rename the columns to whatever more aesthetically pleasing as needed.

View solution in original post

tauliang
Communicator

Do something like this instead:

 | stats count as Count by APPLICATION_NAME USER_ID
 | stats list(USER_ID) as USER_ID list(Count) as Count by APPLICATION_NAME

A full example including the lines generating data:

| makeresults count=40
| streamstats count 
| eval count = count %4
| eval APPLICATION_NAME = case(count=1 OR count=3, "AppName2", count=0 OR count=2, "AppName1")
| eval USER_ID = case(count=1, "User ID 1", count=0, "User Id 2", count=2, "User Id 3", count=3, "User Id 4")
| stats count as Count by APPLICATION_NAME USER_ID
| stats list(USER_ID) as USER_ID list(Count) as Count by APPLICATION_NAME

You should get what you wanted:

Results

Of course, rename the columns to whatever more aesthetically pleasing as needed.

SplunkLunk
Path Finder

So your first part got me almost where I wanted. This is my search with your second line added:

index=[my_index] sourcetype=[my_sourcetype] source=[my_source]
| stats count as Count by APPLICATION_NAME USER_ID
| stats list(USER_ID) as USER_ID list(Count) as Count by APPLICATION_NAME
| search [|inputlookup app_file.csv|fields APPLICATION_NAME]
| lookup app_file.csv APPLICATION_NAME OUTPUT App_Name
| rename App_Name as "Application Name", USER_ID as "User ID"
| table "Application Name", "User ID", Count
| sort "Application Name", -Count

Now my search results show three columns. Application Name, User ID, and Count. It displays beautifully. One section for each application name with the second column being all the user IDs with events sorted alphabetically by user name for each application. Is there a way to sort by highest number of events first vs. alphabetically by user ID? I want the app owners to see who the heaviest users are without having to scan the list, sort an export, etc. I thought that's what the last line would do but no luck.

0 Karma

to4kawa
Ultra Champion
index=[my_index] sourcetype=[my_sourcetype] source=[my_source] 
| stats count as Count by APPLICATION_NAME USER_ID
| sort APPLICATION_NAME, - Count
| stats list( .....

stats list() gets these from the top.

SplunkLunk
Path Finder

Thank you so much! That works great.

0 Karma

tauliang
Communicator

@SplunkLunk the search above provided by @to4kawa will get what you need.

0 Karma
Get Updates on the Splunk Community!

Introducing the 2024 SplunkTrust!

Hello, Splunk Community! We are beyond thrilled to announce our newest group of SplunkTrust members!  The ...

Introducing the 2024 Splunk MVPs!

We are excited to announce the 2024 cohort of the Splunk MVP program. Splunk MVPs are passionate members of ...

Splunk Custom Visualizations App End of Life

The Splunk Custom Visualizations apps End of Life for SimpleXML will reach end of support on Dec 21, 2024, ...