4

I have json-formatted CloudWatch logs whose entries resemble this:

{
    "message": "resource_liked",
    "context": {
        "date": {
            "date": "2021-05-07 16:52:11.000000",
            "timezone_type": 3,
            "timezone": "UTC"
        },
    ...

I am trying to write a CloudWatch insights query to make a simple histogram: number of events in the log per hour.

However, I can't use the @timestamp attribute of the log entry. I need to use context.date.date in the entry's message body instead.

Writing this query using @timestamp is simple enough:

stats count(*) by datefloor(@timestamp, 1h)

However, I'm not sure how I can use the message's context.date.date instead.

I assume I need to format the dateimes that look like 2021-05-07 16:52:11.000000 into something that aws understands as a datetime, but I can't find how.

What I've tried so far

stats count(*) by datefloor(context.date.date, 1h) -> "Invalid Date"

stats count(*) by datefloor(toMillis(context.date.date), 1h) -> "Invalid Date"

stats count(*) by datefloor(substr(context.date.date, 0, 19), 1h) -> "Invalid Date"

stats count(*) by datefloor(concat(replace(substr(context.date.date, 0, 23), ' ', 'T'), '-00:00'), 1h) -> Invalid Date. This one makes the field look exactly like how @timestamp is displayed.

amacrobert
  • 2,707
  • 2
  • 28
  • 37

2 Answers2

4
| parse @message '"date": "*:' as hour
| stats count() as cnt by hour

Parsing a timestamp to use with CW Log Insights functions

Jehong Ahn
  • 1,872
  • 1
  • 19
  • 25
3

Might help to convert the string datetime to numerical milliseconds, note that max year is 2100 due to leap year computation.

fields "2021-05-07 16:52:11.000000" as reqDateTime
| parse reqDateTime "*-*-* *:*:*.*" as reqYear, reqM, reqD, reqH, reqMin, reqSec, reqMilliSec
| fields reqYear - 1970 as reqYearDiff, reqYear % 4 == 0 as reqIsLeapYear, reqM/1 as reqMonth, reqD/1 as reqDay, reqH/1 as reqHour, reqMin/1 as reqMinute, reqSec/1 as reqSecond, reqMilliSec/1 as reqMilliSecond
| fields ((reqYearDiff * 365) + ((reqYear % 4 == 1) * 1) + floor(reqYearDiff / 4) # as yearsToDays
         + ((reqMonth == 2) * 31) # 
         + ((reqMonth == 3) * 59) #
         + ((reqMonth == 4) * 90) #
         + ((reqMonth == 5) * 120) #
         + ((reqMonth == 6) * 151) #
         + ((reqMonth == 7) * 181) #
         + ((reqMonth == 8) * 212) #
         + ((reqMonth == 9) * 243) #
         + ((reqMonth == 10) * 273) #
         + ((reqMonth == 11) * 304) #
         + ((reqMonth == 12) * 334) #
         + ((reqMonth > 2) and (reqIsLeapYear == 1)) # as monthsToDays
         + reqDay - 1) * 24 * 60 * 60 * 1000 # as daysToMilliSeconds
         + reqHour * 60 * 60 * 1000 # as hoursToMilliSeconds
         + reqMinute * 60 * 1000 # as minutesToMilliSeconds
         + reqSecond * 1000 # as secondsToMilliSeconds
         + reqMilliSecond  
         as reqMilliSeconds
| display reqMilliSeconds, fromMillis(reqMilliSeconds), reqYear, reqMonth, reqDay, reqHour, reqMinute, reqSecond, reqMilliSecond
| limit 1
pobs
  • 31
  • 2