How can I convert bytes to MB or GB in Cloudwatch Log Insight queries from a VPC flow log group?

Question

I am building the below VPC flow log message and a syntax error is seen.

```
fields @timestamp, @logStream, @entity.KeyAttributes.Identifier, region, accountId, action, type, srcAddr, srcPort, pktSrcAddr, pktDstAddr, flowDirection, dstAddr, dstPort, tcpFlags, subnetId, interfaceId, bytes
| sort @timestamp desc
| limit 10000
| parse bytes as int
| stats sum(bytes) as total_bytes
| calculate total_mb = total_bytes/1024/1024 // Calculate total megabytes
```

Error: token recognition error at: '|' at line 5 and position 0

What is the correct syntax for defining stats?

SpendGhost · Accepted Answer

Correct answer is:

```
parse @message "* * * * * * * * * * * * * * * * * * * * * * * * * * *" as accountId, action, azId, bytes, dstAddr, dstPort, end, flowDirection, instanceId, interfaceId, packets, pktDstAwsService, pktDstAddr, pktSrcAwsService, pktSrcAddr, protocol, region, rejectReason, srcAddr, srcPort, start, subnetId, tcpFlags, trafficPath, type, version, vpcId
| sort @timestamp desc
| limit 100
| stats 
sum(bytes) as total_bytes, 
sum(bytes)/1024/1024 as total_mb,
sum(packets) as total_packets
by accountId, 
action, 
flowDirection,
interfaceId,
srcAddr,
srcPort,
dstAddr,
dstPort,
protocol,
region,
type,
version,
vpcId,
azId,
subnetId
```

SHAJAM · Answer

Give this a try. Comments within the code

```
fields @timestamp, @logStream, @entity.KeyAttributes.Identifier, region, accountId, action, type, srcAddr, srcPort, pktSrcAddr, pktDstAddr, flowDirection, dstAddr, dstPort, tcpFlags, subnetId, interfaceId, bytes
| sort @timestamp desc
| limit 10
| parse 'bytes' as bytes_int # note 'bytes' 
| stats sum(bytes_int) / (1024 * 1024) as total_mb # calculate while using sum
```

SpendGhost · Answer

The AI bot answer did not fully resolve my issue.

Used suggested query and now the query only displayed the bytes converted. I want the see the "bytes" field converted to MB or GB.

![Enter image description here](/media/postImages/original/IMCZoPh1cTQKWPu7IjiRnGHw)

re:Post Agent · Answer

To convert bytes to MB or GB in CloudWatch Logs Insights queries for VPC flow logs, you need to use the correct syntax for mathematical operations. The error in your query is due to the incorrect use of the `calculate` command, which isn't part of CloudWatch Logs Insights query syntax.

Instead, you can perform the conversion directly within the `stats` command using metric math expressions. Here's the corrected query:

```
    fields @timestamp, @logStream, @entity.KeyAttributes.Identifier, region, accountId, action, type, srcAddr, srcPort, pktSrcAddr, pktDstAddr, flowDirection, dstAddr, dstPort, tcpFlags, subnetId, interfaceId, bytes
    | sort @timestamp desc
    | limit 10000
    | stats sum(bytes) as total_bytes, sum(bytes)/1024/1024 as total_mb
    ```

This query:
    1. Selects the fields you're interested in
    2. Sorts by timestamp in descending order
    3. Limits to 10,000 results
    4. Calculates both the sum of bytes and converts it to megabytes in a single `stats` command

If you want to convert to gigabytes, you can use:
    ```
    stats sum(bytes) as total_bytes, sum(bytes)/1024/1024/1024 as total_gb
    ```

Note that you don't need to explicitly parse bytes as an integer since CloudWatch Logs Insights automatically recognizes the bytes field in VPC flow logs as a numeric field that can be used in calculations.
      
**Sources**  
[Supported logs and discovered fields - Amazon CloudWatch Logs](https://docs.aws.amazon.com/AmazonCloudWatch/latest/logs/CWL_AnalyzeLogData-discoverable-fields.html)  
[stats - Amazon CloudWatch Logs](https://docs.aws.amazon.com/AmazonCloudWatch/latest/logs/CWL_QuerySyntax-Stats.html)  
[Community | Monitor VPC interface endpoints with CloudWatch](https://community.aws/content/2hDuaJGRF6qRvnpdljQBG2WyYQk/monitor-vpc-interface-endpoints-with-cloudwatch)

How can I convert bytes to MB or GB in Cloudwatch Log Insight queries from a VPC flow log group?

Add your answer

Relevant content