How can I join these two queries ? AWS_Compliance_By_Account +: AWS_Account_Owners_Query

Follow

David Balducci

• February 03, 2023 15:18
• Edited

Query1: AWS_Compliance_By_Account

_sourceCategory=AWS/Security/SecurityHub 
| json  "AwsAccountId","Compliance.Status" as aws_account_id, compliance_status nodrop
| where aws_account_id matches "*" 
| where compliance_status matches "*"
| if(compliance_status matches "FAILED",1,0) as Controls_Failed
| if(compliance_status matches "PASSED",1,0) as Controls_Passed
| if(compliance_status matches "NOT_AVAILABLE",1,0) as Controls_NotAvailable
| if(compliance_status matches "WARNING",1,0) as Controls_Warning
| if(compliance_status matches "*",1,0) as Controls_Item
| sum(Controls_Passed) as Passed_Total, sum(Controls_Failed) as Failed_Total, sum(Controls_NotAvailable) as NotAvailable_Total, sum(Controls_Warning) as Warning_Total, sum(Controls_Item) as Controls_Total group by aws_Account_id
| Passed_Total / Controls_Total * 100  as Compliance_Percent
| format("%.2f %s", Compliance_Percent,"%") as Compliance_Percent
| fields aws_account_id, Compliance_Percent, Failed_Total, Passed_Total, NotAvailable_Total,Warning_Total, Controls_Total | order by aws_Account_id asc

 

Query2: AWS_Account_Owners_Query (note I would se the parm to *)

_source="Accounts JSON" and _collector="AWS Accounts"
|where accountOwnerUni matches {{accountOwnerUniParm}}
|where awsAccountStatus="ACTIVE"
|count by awsAccountId, accountOwnerUni,accountOwner,ownerEmail, adminAccounts 
|sort by accountOwner asc, awsAccountId asc

The join would be where  Query1. aws_account_id = Query2. awsAccountId

I would like to see the owners info on the same line in query1, any help would be appreciated as I am new to SUMO Logic query language. 

 

0

• 

  Andrzej Stefański

  • February 06, 2023 10:21

  Hi David, 

  Query 2 from your case looks like a good candidate for a lookup table (see https://help.sumologic.com/docs/search/lookup-tables/create-lookup-table/). 

  Using save operator (see https://help.sumologic.com/docs/search/search-query-language/search-operators/save/ ) you can update lookup periodically via scheduled search or create complex query having such update performed in internal query. 

  Matching query data with the data stored in lookup is performed by 'lookup' operator (see https://help.sumologic.com/docs/search/search-query-language/search-operators/lookup/ ). 

  In your case simplest way would be to: 
  1. Create lookup table AWS_Account_Owners_Lookup

  2. Save results from second query to the lookup by adding query below and executing it in search: 

  | save path://"/Library/Users/myusername@mydomain.com/AWS_Account_Owners_Lookup"
  

  one can check saved content using cat operator in search:
  

  cat path://"/Library/Users/myusername@mydomain.com/AWS_Account_Owners_Lookup"
  

  3. Use lookup operator in first query to match the data: 

  | lookup * from path://"/Library/Users/myusername@mydomain.com/AWS_Account_Owners_Lookup" on aws_account_id=awsAccountId
  

  4. (optional) create Scheduled Search to periodically update lookup table

   

  If you have quite dynamic environment and you need to have lookup updated on each query execution you can consider including lookup update in subquery. I would consider it as a next step (see: https://help.sumologic.com/docs/search/subqueries/#reference-data-from-child-query-using-save-and-lookup ). 

   

  Hope it helps you in your project. 

   

   

  0

  Comment actions Permalink

Please sign in to leave a comment.