/*
|
| Query showing information summarized by job id, category, and type
| Replace table_name before running
|
| UNION statement exists to account for how findings related to custom identifiers are reported.
| Running the query as is, will not impact the results, if you don't use custom identifiers.
| If you do not use custom identifiers to find sensitive data in Macie you may remove everything between the UNION and group by.
*/

select classificationdetails.jobId, 
       sensitive_data.category, 
       detections_data.type, 
       sum(cast(detections_data.count as INT)) total_detections
from <table_name>, 
     unnest(classificationdetails.result.sensitiveData) as t(sensitive_data),
     unnest(sensitive_data.detections) as t(detections_data)
where  resourcesaffected.s3object.embeddedfiledetails is null
group by classificationdetails.jobId, 
         sensitive_data.category, 
         detections_data.type
UNION
select classificationdetails.jobId,
       'CustomIdentifier',
       custom_detections_data.name,
       sum(cast(custom_detections_data.count as INT)) total_detections
from <table_name>,
    unnest(classificationdetails.result.customDataIdentifiers.detections) as t(custom_detections_data)
where resourcesaffected.s3object.embeddedfiledetails is null
group by classificationdetails.jobId,
         'CustomIdentifier',
         custom_detections_data.name
order by total_detections desc