I am trying to query cloudtrail logs using Athena. My goal is to find specific instances and extract them with their Tags.
The query I am using is: SELECT eventTime, awsRegion , json_extract(responseelements, '$.instancesSet.items[0].instanceId') AS instanceId, json_extract(responseelements, '$.instancesSet.items[0].tagSet.items') AS TAGS FROM cloudtrail_logs_PP WHERE (eventName = 'RunInstances' OR eventName = 'StartInstances' ) AND requestparameters LIKE '%mytest1%' AND "timestamp" BETWEEN '2021/09/01' AND '2021/10/01' ORDER BY eventTime;
Using this query - I am able to get all Tags under one column.
I want to extract only specific Tags and need help in the same. How cam I extract the only specific Tag?
I tried enhancing my query as json_extract(responseelements, '$.instancesSet.items[0].tagSet.items[0]' but the order of Tags is diff in diff logs - so cant pass the index location.
My json file in S3 is something like below:
{ "eventVersion": "1", "eventTime": "2022-05-27T18:44:29Z", "eventName": "RunInstances", "awsRegion": "us-east-1", "requestParameters": { "instancesSet": { "items": [{ "imageId": "ami-1234545", "keyName": "DDKJKD" }] }, "instanceType": "m5.2xlarge", "monitoring": { "enabled": false }, "hibernationOptions": { "configured": false } }, "responseElements": { "instancesSet": { "items": [{ "tagSet": { "items": [ { "key": "11", "value": "DS" }, { "key": "1", "value": "A" }] }] } } }