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.
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:
Of course, rename the columns to whatever more aesthetically pleasing as needed.
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:
Of course, rename the columns to whatever more aesthetically pleasing as needed.
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.
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.
Thank you so much! That works great.
@SplunkLunk the search above provided by @to4kawa will get what you need.