Extract info from JSON string

Viewed 40

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.

0 Answers
Related