Numeric fields returned by CW Logs query are displayed in scientific notation

[crh]

• What Grafana version and what operating system are you using?
  Grafana v11.3, Ubuntu 22.04

• What are you trying to achieve?
  I’m trying to create a time series data of the number of requests coming to my API, but grouped by a value of a custom field in logs (“authorizerCallerIdentity.id”, let’s call it “ID”), which is a 9-digit number (like 123456789). The end goal would be to have time-series data with datapoints as 5 min averages, with separate plots for each value of “ID”.

• How are you trying to achieve it?
  I’m using a CW Logs query:

fields @timestamp, authorizerCallerIdentity.id
| stats count() as request_count by authorizerCallerIdentity.id, bin(5m)

• What happened?
  The time-series data is showing up without issues. However, the IDs in the graph’s legend are converted to scientific notation format without a way to turn them back to original values.

• What did you expect to happen?

1. That the labels generated by query would be displayed properly, OR
2. That there would be a way to dynamically convert them to regular numbers - Standard Unit options or Overrides only apply to values, not displayed fields.

• Can you copy/paste the configuration(s) that you are having problems with?
  Query pasted above, nothing else changed in configs.

• Did you receive any errors in the Grafana UI or in related logs? If so, please tell us exactly what they were.
  No errors

• Did you follow any online instructions? If so, what is the URL?
  N/A

• Additional information:
  If I try to present the data in table format, the IDs are displayed properly as 9-digit numbers:

fields @timestamp, authorizerCallerIdentity.id
| stats count() as request_count by authorizerCallerIdentity.id

As I mentioned before, field overrides don’t impact labels (fields?) in the legend, only the Y-axis values.

A potential workarounds I can see are:

1. Do nothing - if label is “1.23456789e+08 request_count”, then the ID is 123456789, simple as that, but looks messy
2. Create overrides for ALL IDs - this would be possible as there is a finite list of IDs that I have, however this option is time-consuming and, frankly speaking, not elegant.

I hope I’ve managed to describe the issue properly. I can provide additional context and/or screenshots if necessary. Any help would be greatly appreciated!

[jangaraj]

Try to use transformation Convert field type and format that ID field as string.

Transform data | Grafana documentation

[crh]

Thank you for your reply jangaraj, if you mean transforming the authorizerCallerIdentity.id (base field name) as string, then unfortunately it doesn’t change anything. Neither does setting individual generated fields as strings.

[jangaraj]

Enable table view and provide screenshot, pls. That id field must be recognised as string, not number.

[crh]

Here you go, as you can see field type is string in the column, but it’s still converted:

[jangaraj]

It looks good. I guess it is already converted before it reachs transformation. Use query inspector and check how it is returned from CW API.
Could you provide screenshot of the query part, pls.

[crh]

Example response from query inspector for 1 object:

response:Object
results:Object
A:Object
status:200
frames:Array[39]
0:Object
schema:Object
name:"4.35501115e+08"
refId:"A"
meta:Object
typeVersion:Array[0,0]
custom:Object
stats:Array[3]
fields:Array[3]
0:Object
name:"authorizerCallerIdentity.id"
type:"string"
typeInfo:Object
frame:"string"
nullable:true
labels:Object
1:Object
name:"bin(5m)"
type:"time"
typeInfo:Object
frame:"time.Time"
nullable:true
labels:Object
2:Object
name:"request_count"
type:"number"
typeInfo:Object
frame:"float64"
nullable:true
labels:Object

Running the exact same query in Logs Insights returns the IDs as they should be displayed:

[jangaraj]

You have arrays there - open everything there and check values - don’t just high level - go deep into frames and data.

[crh]

Sorry for that, here’s a fully expanded array of a single object:

          {
            "schema": {
              "name": "7.70202767e+08",
              "refId": "A",
              "meta": {
                "typeVersion": [
                  0,
                  0
                ],
                "custom": {
                  "Status": "Complete"
                },
                "stats": [
                  {
                    "displayName": "Bytes scanned",
                    "value": 797236762
                  },
                  {
                    "displayName": "Records scanned",
                    "value": 1330724
                  },
                  {
                    "displayName": "Records matched",
                    "value": 1319190
                  }
                ]
              },
              "fields": [
                {
                  "name": "authorizerCallerIdentity.id",
                  "type": "string",
                  "typeInfo": {
                    "frame": "string",
                    "nullable": true
                  },
                  "labels": {}
                },
                {
                  "name": "bin(5m)",
                  "type": "time",
                  "typeInfo": {
                    "frame": "time.Time",
                    "nullable": true
                  },
                  "labels": {}
                },
                {
                  "name": "request_count",
                  "type": "number",
                  "typeInfo": {
                    "frame": "float64",
                    "nullable": true
                  },
                  "labels": {}
                }
              ]
            },
            "data": {
              "values": [
                [
                  "7.70202767e+08",
                  "7.70202767e+08",
                  "7.70202767e+08",
                  "7.70202767e+08",
                  "7.70202767e+08",
                  "7.70202767e+08",
                  "7.70202767e+08",
                  "7.70202767e+08",
                  "7.70202767e+08",
                  "7.70202767e+08",
                  "7.70202767e+08",
                  "7.70202767e+08",
                  "7.70202767e+08",
                  "7.70202767e+08",
                  "7.70202767e+08",
                  "7.70202767e+08",
                  "7.70202767e+08",
                  "7.70202767e+08",
                  "7.70202767e+08",
                  "7.70202767e+08",
                  "7.70202767e+08",
                  "7.70202767e+08",
                  "7.70202767e+08",
                  "7.70202767e+08",
                  "7.70202767e+08",
                  "7.70202767e+08",
                  "7.70202767e+08",
                  "7.70202767e+08",
                  "7.70202767e+08",
                  "7.70202767e+08",
                  "7.70202767e+08",
                  "7.70202767e+08",
                  "7.70202767e+08",
                  "7.70202767e+08",
                  "7.70202767e+08",
                  "7.70202767e+08",
                  "7.70202767e+08"
                ],
                [
                  1732083600000,
                  1732083900000,
                  1732084200000,
                  1732084500000,
                  1732084800000,
                  1732085100000,
                  1732085400000,
                  1732085700000,
                  1732086000000,
                  1732086300000,
                  1732086600000,
                  1732086900000,
                  1732087200000,
                  1732087500000,
                  1732087800000,
                  1732088100000,
                  1732088400000,
                  1732088700000,
                  1732089000000,
                  1732089300000,
                  1732089600000,
                  1732089900000,
                  1732090200000,
                  1732090500000,
                  1732090800000,
                  1732091100000,
                  1732091400000,
                  1732091700000,
                  1732092000000,
                  1732092300000,
                  1732092600000,
                  1732092900000,
                  1732093200000,
                  1732093500000,
                  1732093800000,
                  1732094100000,
                  1732094400000
                ],
                [
                  47,
                  134,
                  107,
                  135,
                  117,
                  128,
                  172,
                  143,
                  178,
                  141,
                  130,
                  176,
                  167,
                  162,
                  200,
                  139,
                  130,
                  153,
                  150,
                  118,
                  156,
                  101,
                  154,
                  145,
                  178,
                  173,
                  168,
                  104,
                  178,
                  130,
                  124,
                  169,
                  166,
                  125,
                  131,
                  135,
                  110
                ]
              ]
            }
          },

[jangaraj]

So it looks like that scientific notation in string is returned from CW API and Grafana just shows it:

You may try:

• Grafana transformations - transform ID field to number, then back to string - 2 transformations
• Use Insights QL and achieve non scientific notation result - be creative - bling guess - contact with space string, …

[crh]

Thank you for the hints. I tried the two-way transformation but it doesn’t affect the fields in legend, only values.
I’ve also tried concat() to add an empty string like concat('', authorizerCallerIdentity.id) as idAlias, but this completely breaks the data returned by the query.

Is there a possibility that this is a bug with the cloudwatch plugin? It seems that although the IDs are interpreted as a string, grafana receives them in a different format for some unknown reason.

[jangaraj]

@crh

this is a bug with the cloudwatch plugin

How do you know that’s a bug in the CloudWatch plugin? Could you prove that AWS didn’t return that format? Did you try to ask your AWS support?

[crh]

I have not discussed it with AWS support yet. I’m not sure if this is a legit bug, just thinking out loud if that could be a possibility. It’s basing on the fact that the same query shows different field formats on AWS side and Grafana side.

[jangaraj]

And I give you a fact that it looks like AWS API returns that when you check result in the query inspector. So you need to prove, e.g. use aws cli/curl and simulate that query from AWS console and prove that AWS returns data in non scientific notation. That you can say that it’s a Grafana’s issue.

[yosiasz]

Make that same API call outside of grafana using insomnia or postman and postback the results

[crh]

Query ran via AWS CLI:

aws logs start-query \
    --log-group-name "/my/log/group" \
    --start-time $(date -d "-15 minutes" +%s) \
    --end-time $(date +%s) \
    --query-string "fields @timestamp, authorizerCallerIdentity.id | stats count() as request_count by authorizerCallerIdentity.id, bin(5m)" \
    --region "eu-west-1"

Excerpt from result:

aws logs get-query-results \
    --query-id "015fa472-1039-44af-96f2-db0d0739c871" \
    --region "eu-west-1"
{
    "results": [
        [
            {
                "field": "authorizerCallerIdentity.id",
                "value": "955512908"
            },
            {
                "field": "bin(5m)",
                "value": "2024-11-20 15:45:00.000"
            },
            {
                "field": "request_count",
                "value": "6"
            }
        ],
        [
            {
                "field": "authorizerCallerIdentity.id",
                "value": "955512908"
            },
            {
                "field": "bin(5m)",
                "value": "2024-11-20 15:40:00.000"
            },
            {
                "field": "request_count",
                "value": "1071"
            }
        ],
        [
            {
                "field": "authorizerCallerIdentity.id",
                "value": "955512908"
            },
            {
                "field": "bin(5m)",
                "value": "2024-11-20 15:35:00.000"
            },
            {
                "field": "request_count",
                "value": "1260"
            }
        ],
        [
            {
                "field": "authorizerCallerIdentity.id",
                "value": "955512908"
            },
            {
                "field": "bin(5m)",
                "value": "2024-11-20 15:30:00.000"
            },
            {
                "field": "request_count",
                "value": "1239"
            }
        ],
        [
            {
                "field": "authorizerCallerIdentity.id",
                "value": "946112716"
            },
            {
                "field": "bin(5m)",
                "value": "2024-11-20 15:40:00.000"
            },
            {
                "field": "request_count",
                "value": "372"
            }
        ],
        [
            {
                "field": "authorizerCallerIdentity.id",
                "value": "946112716"
            },
            {
                "field": "bin(5m)",
                "value": "2024-11-20 15:35:00.000"
            },
            {
                "field": "request_count",
                "value": "429"
            }
        ],
(...)
    "statistics": {
        "recordsMatched": 222896.0,
        "recordsScanned": 242366.0,
        "bytesScanned": 145653219.0
    },
    "status": "Complete"
}

No scientific notation visible here.

[jangaraj]

Correct, it’s visible that id is a field, not label, so it’s a metric value, not label/dimension from the Grafana point of view.
I guess Grafana loads it into float64 type, which has that scientific notation. It’s a Grafana issue, because AWS data model in the response is not vey suitable for Grafana.

[crh]

Thank you for confirming my suspicions, I guess I will open an issue on github then, it would be nice to have this show up properly without any manual labor involved.

[crh]

Cloudwatch plugin: Numeric fields returned by CW Logs query are displayed in scientific notation

Here’s the link to the github issue if anyone’s interested.