I am querying CloudTrail logs from S3 bucket via Amazon Athena.
My goal is to extract start/stop time for given instance id.
I am using query as:
SELECT
eventName,
eventTime,
if(responseelements like '%i-0000000000000%' ,'i-0000000000000') as instanceId
FROM cloudtrail_logs_pp
WHERE (responseelements like '%i-0000000000000%' )
AND (eventName = 'StopInstances' OR eventName = 'StartInstances')
AND "timestamp" BETWEEN '2022/01/01'
AND '2022/08/01'
ORDER BY eventTime
The issue I am facing is of duplicated entries via API call.
The structure of json file is:
{
"eventVersion": "1.08",
"eventTime": "2022-06-22T05:15:33Z",
"eventName": "StartInstances",
"requestParameters": {
"instancesSet": {
"items": [{
"instanceId": "i-00000"
}]
}
},
"responseElements": {
"requestId": "e95d270a",
"instancesSet": {
"items": [{
"instanceId": "i-00000",
"currentState": {
"code": 0,
"name": "pending"
},
"previousState": {
"code": 80,
"name": "stopped"
}
}]
}
},
"sessionCredentialFromConsole": "true"
},
However there are few entries where current and previous state are same.
How can I enhance my query to remove those entries?
Also there are cases when multiple instances were stopped / started - so can't use index in the query.