Subquery Most Common API call by Account ID

Follow

Nikita Mokhov

• February 09, 2021 17:10

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. 

0

• 

  Shobhit Garg

  • February 12, 2021 06:33

  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

  0

  Comment actions Permalink

Please sign in to leave a comment.