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

[sureshchinta]

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

[elliotproebstel · Accepted solution]

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

[sureshchinta]

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

[sureshchinta]

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.