Subquery Most Common API call by Account ID
For context, I am looking at CloudTrail logs in order to audit 3rd-party AWS account activity in our VPC. I have a couple fields: role, accountID, and eventName. "eventName" in CloudTrail logs refers to the API call made by a role.
What I would like to do is get the most commonly called API value for each accountID. The closest I have come is the below:
count by eventName,accountID | max(_count) group by accountID
The extra step I can't figure out is how to show the value of the max count (the API event) as opposed to the count number.
-
Hi Nikita,
For this we need to have MAX for each AccountID and for that we either need to leverage the SUBQUERY or JOIN
Below is one sample query I have written
(_collector="HTTP")
| parse "*,*" as A1,B1
| count as C1 by A1,B1
| where [subquery: _collector="HTTP"
| parse "*,*" as A1,B1
| count as C1 by A1,B1
| max(c1) as C1 by B1
| compose C1,B1]See the screenshots for more details.
Regards
Please sign in to leave a comment.
Comments
1 comment