Splunk Search

Find the least and most occurrence in a field -- populating in the same row

sureshchinta
Explorer

I have three log transactions containing following extracted fields - all joined together by a common transaction id field:

  1. User Id, transaction Id
  2. Token Id, Transaction Id
  3. Action, Transaction Id

In some cases, a token id and user id combination are not unique - means, multiple user's may've used the same token id. I have extracted the combination of such occurrences.

Question is, how to find the most used user Id (max occurrences of User Id) and flag it as owner and min used user id (as violation) in the same row ?

This lists all the transactions by both user's sharing the same token:

host=myhost* earliest=1521260803 latest=1521262603 (UserId=abc OR UserId=xyz OR Token=1521260803791) | selfjoin TxnId | where len(UserId) > 0 AND len(Token) > 0 | table UserId Token TxnId | join TxnId [ search host=myhost* earliest=1521260803 latest=1521262603 client:* ] | table UserId Action Token TxnId

Thanks

Tags (2)
0 Karma
1 Solution

elliotproebstel
Champion

I think you're saying you want statistics about tokens - in particular, to know the user ID that used the token the most, the user ID that used the token the least, the action least taken with that token, and maybe the transaction IDs that were associated with the token. If that's right, I think this approach should work for you:

a base search that gathers all relevant events
| stats values(UserID) AS UserID values(Token) AS Token values(Action) AS Action BY TxnId
| eventstats count AS user_count BY Token, UserID
| eventstats count AS action_count BY Token, Action
| eventstats max(user_count) AS max_user_count min(user_count) AS min_user_count min(action_count) AS min_action_count BY Token
| eval max_user_id=if(user_count=max_user_count, UserID, NULL)
| eval min_user_id=if(user_count=min_user_count, UserID, NULL)
| eval min_action=if(action_count=min_action_count, Action, NULL)
| stats values(max_user_id) AS Most_Occurrences_User values(min_user_id) AS Least_Occurrences_User values(min_action) AS Least_Occurrences_Action values(TxnId) AS Transaction_IDs BY Token

View solution in original post

0 Karma

elliotproebstel
Champion

I think you're saying you want statistics about tokens - in particular, to know the user ID that used the token the most, the user ID that used the token the least, the action least taken with that token, and maybe the transaction IDs that were associated with the token. If that's right, I think this approach should work for you:

a base search that gathers all relevant events
| stats values(UserID) AS UserID values(Token) AS Token values(Action) AS Action BY TxnId
| eventstats count AS user_count BY Token, UserID
| eventstats count AS action_count BY Token, Action
| eventstats max(user_count) AS max_user_count min(user_count) AS min_user_count min(action_count) AS min_action_count BY Token
| eval max_user_id=if(user_count=max_user_count, UserID, NULL)
| eval min_user_id=if(user_count=min_user_count, UserID, NULL)
| eval min_action=if(action_count=min_action_count, Action, NULL)
| stats values(max_user_id) AS Most_Occurrences_User values(min_user_id) AS Least_Occurrences_User values(min_action) AS Least_Occurrences_Action values(TxnId) AS Transaction_IDs BY Token
0 Karma

sureshchinta
Explorer

Wow! this is great. I need more time to fully understand but the query almost got me there ! Thank you so much.

0 Karma

sureshchinta
Explorer

Should clarify on what I'm looking for with output:

Table with following fields:
Token
Most Occurrences user ID
Least Occurrences user ID
Least Occurrences user Action
Transaction ID (optional to have)

BTW, I've tried with stats min, max or eventstats with no luck so far.

0 Karma
Get Updates on the Splunk Community!

Index This | How many sides does a circle have?

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

Registration for Splunk University is Now Open!

Are you ready for an adventure in learning?   Brace yourselves because Splunk University is back, and it's ...

Splunkbase | Splunk Dashboard Examples App for SimpleXML End of Life

The Splunk Dashboard Examples App for SimpleXML will reach end of support on Dec 19, 2024, after which no new ...