Flattening details field in Security Hub findings

Hello,

Thanks for reaching out,

I was able to find the sample reference table/view DDL in the below workshop for the security hub findings dashboard.

https://catalog.us-east-1.prod.workshops.aws/workshops/b1cdc52b-eb11-44ed-8dc8-9dfe5fb254f5/en-US/build/athenadb

you can also check the " 2-Create-Athena-table.yaml “ file in the above blog which contains the template with all the details.

CREATE OR REPLACE VIEW SecurityHub.securityhubfindingsview AS SELECT id , detail.findings[1].awsaccountid awsaccountid , detail.findings[1].CreatedAt CreatedAt , detail.findings[1].UpdatedAt UpdatedAt , detail.findings[1].ProductArn ProductArn , detail.findings[1].GeneratorId CheckId , detail.findings[1].Region Region , detail.findings[1].Workflow.status WorflowStatus , detail.findings[1].Compliance.status ComplianceStatus , detail.findings[1].Types FindingType , detail.findings[1].Title FindingTitle , detail.findings[1].Description FindingDescription , detail.findings[1].Severity.Label Severity , detail.findings[1].Resources[1].Type ResourceType , detail.findings[1].Resources[1].Id ResourceId , detail.findings[1].Note.Text Notes FROM SecurityHub.securityhubfindings WHERE (detail.findings[1].awsaccountid IS NOT NULL)

Please note that some of the fields might have been updated or modified over the time so please update the DDL as per your use case. Further you can also refer to the docs- https://docs.aws.amazon.com/athena/latest/ug/flattening-arrays.html for idea on flattening arrays

Thank you!