topic Re: Parsing log content JSON in DQL

Parsing log content JSON

susmita_k — Wed, 29 Jan 2025 18:10:05 GMT

I am trying to parse this log content and get the data from the JSON. I need event type, and event_timestamp. Can anyone help with the DQL? Thanks in advance.

2025-01-29 09:37:46,297 app-name [main] DEBUG .util.MessageUtility - Message: {"event_id": "a", "event_source": b, "event_type": "1", "event_taxonomy": "CASE", "event_correlationId": "b967e031", "event_timestamp": 12345678, "event_message": {"agreement_number": "123", "case_type": "A", "previous_status": "000", "new_status": "", "case_id": "123", "schedule_number": null, "additional_info": null}}

Re: Parsing log content JSON

gbaudart — Wed, 29 Jan 2025 19:24:14 GMT

Hi @susmita_k ,

you can try this (after your fetch and yours filters) :

| parse content, "LD 'Message: 'JSON:json"
| fieldsFlatten json, fields:{event_type,event_timestamp}

Let me know if it works.

Re: Parsing log content JSON

susmita_k — Wed, 29 Jan 2025 19:32:12 GMT

yes, it worked, at the same time I am trying to get the log timestamp in EST time, by using below DQL, however, not able to get the result, did I miss anything ?

|PARSE content ,"TIMESTAMP('yyyy-MM-dd HH:mm:ss,SSS'):timestamp_parsed"
|fieldsAdd log_timestamp == formatTimestamp(timestamp_parsed,format:"yyyy-MM-dd HH:mm:ss,SSS", timezone:"US/Eastern")

Re: Parsing log content JSON

gbaudart — Wed, 29 Jan 2025 19:46:39 GMT

Try with only one "="

| FieldsAdd log_timestamp = formatTimestamp(timestamp_parsed,format:"yyyy-MM-dd HH:mm:ss,SSS", timezone:"US/Eastern")

Re: Parsing log content JSON

susmita_k — Wed, 29 Jan 2025 21:24:16 GMT

Thanks so much 🙂

Re: Parsing log content JSON

andre_vdveen — Tue, 11 Feb 2025 18:50:38 GMT

Thanks for posting the DQL, @gbaudart as it helped me with an issue I've been sitting with the entire day! 😅

Re: Parsing log content JSON

imsummii — Tue, 25 Feb 2025 11:53:22 GMT

@gbaudart Could you help me in the similar query for our nested JSON which we parsed from the cloudwatch logs.
This is the content after parsing in it JSON:

{ "AutomationExecutionId": "950a5d72-b10e-4134-8e36-ff040f90460f", "Account_Id": "171739898504", "Region_Name": "us-west-2", "StepExecutions": [ { "StepName": "CreateLogGroup", "Action": "aws:executeScript", "ExecutionStartTime": "2025-02-14 10:30:48.032000+00:00", "ExecutionEndTime": "2025-02-14 10:30:52.097000+00:00", "StepStatus": "Success", "Outputs": { "OutputPayload": [ "{\"Payload\":{\"message\":\"Log group /mcs-ops-patching/950a5d72-b10e-4134-8e36-ff040f90460f created.\\nLog group /mcs-ops-patching/950a5d72-b10e-4134-8e36-ff040f90460f retention policy updated\\n\"}}"

Need to print Key:Value pair for eg.
OutputPayload : "{\"Payload\":{\"message\":\"Log group /mcs-ops-patching/950a5d72-b10e-4134-8e36-ff040f90460f created.\\nLog group /mcs-ops-patching/950a5d72-b10e-4134-8e36-ff040f90460f retention policy updated\\n\"}}"

Attaching the screenshot as well

Re: Parsing log content JSON

gbaudart — Tue, 25 Feb 2025 19:17:02 GMT

Hi @imsummii ,

you can try this (after your fetch and yours filters) :

| parse content, "LD '\"Outputs\": ' JSON:json"
| fieldsFlatten json, fields:{OutputPayload}
| parse OutputPayload, "LD ': [ \"' JSON:json2"
| fieldsFlatten json2, fields:{message}