Convert string to number in AWS log insight

Question

I'm attempting to get the value of subtracting two numbers from a log messages that looks like this.

"[Statistics] | 1697041097288531000 | 1697041097406589000 | 1697041097458528733"

I attempted the following query via aws log insights, with no luck. Is it even possible to do this in aws log insights?

fields @timestamp, @message
| filter @message like /[Statistics]/
| parse MessageTemplate "*|*|*|*" as header, pub1, pub2, pub3
| stats abs(pub2) - abs(pub1) as elaspedTime by @timestamp
| limit 20

please note my logs are JSON logs with a field called MessageTemplate.

The result I get is as follows, as you can see the elaspedTime column is empty I was hoping to see the result of subtracting pub2 from pub1. So if we use the example message I provided I above, I would expect to see the result of 1697041097406589000 - 1697041097288531000 as the elaspedTime value for one of the rows.

If I swap out the pub1 and pub2 fields for hard coded numbers it works, but of course I'm trying to use the string values and have them cast to numbers from the log message.

Answer 1

stats accepts constant values, grouping values or aggregation functions as its parameters.

When you replace your fields with hardcoded numbers, they become constant values. But you're neither grouping by pub1 and pub2 nor aggregating them, so abs(pub2) - abs(pub1) cannot be evaluated within stats context. Most database engines would throw an error at this point, but Log Insights just swallows it and returns an empty value instead.

It looks like you don't really need stats here. Replace your query with:

fields @timestamp, @message
| filter @message like /[Statistics]/
| parse MessageTemplate "*|*|*|*" as header, pub1, pub2, pub3
| fields abs(pub2) - abs(pub1) as elapsedTime
| limit 20