Skip to content

[BUG] Grouped stats cannot be collected after bin #4340

@Swiddis

Description

@Swiddis

Query Information

PPL Command/Query:

source=big5 | bin `@timestamp` span='1d' | stats count() by `@timestamp`

Original: trying to get active processes over time,

source=big5 | bin `@timestamp` span='1d' | stats count() by `@timestamp`, process.name | sort `@timestamp`, process.name

Expected Result:
Should return the count of records by day

Actual Result:

{
  "error": {
    "reason": "Error occurred in OpenSearch engine: all shards failed",
    "details": "Shard[0]: AggregationExecutionException[Unsupported script value [2023-01-01 00:00:00], expected a number, date, or boolean]\
Shard[1]: AggregationExecutionException[Unsupported script value [2023-01-01 00:00:00], expected a number, date, or boolean]\
Shard[2]: AggregationExecutionException[Unsupported script value [2023-01-01 00:00:00], expected a number, date, or boolean]\
Shard[3]: AggregationExecutionException[Unsupported script value [2023-01-01 00:00:00], expected a number, date, or boolean]\
Shard[4]: AggregationExecutionException[Unsupported script value [2023-01-01 00:00:00], expected a number, date, or boolean]\
\
For more details, please send request for Json format to see the raw response from OpenSearch engine.",
    "type": "SearchPhaseExecutionException"
  },
  "status": 500
}

Dataset Information

Dataset/Schema Type

  • OpenTelemetry (OTEL)
  • Simple Schema for Observability (SS4O)
  • Open Cybersecurity Schema Framework (OCSF)
  • Custom (details below)

Big5 data from OpenSearch benchmark

Index Mapping

{
  "big5": {
    "mappings": {
      "properties": {
        "@timestamp": {
          "type": "date"
        },
        "agent": {
          "properties": {
            "ephemeral_id": {
              "type": "text",
              "fields": {
                "keyword": {
                  "type": "keyword",
                  "ignore_above": 256
                }
              }
            },
            "id": {
              "type": "text",
              "fields": {
                "keyword": {
                  "type": "keyword",
                  "ignore_above": 256
                }
              }
            },
            "name": {
              "type": "text",
              "fields": {
                "keyword": {
                  "type": "keyword",
                  "ignore_above": 256
                }
              }
            },
            "type": {
              "type": "text",
              "fields": {
                "keyword": {
                  "type": "keyword",
                  "ignore_above": 256
                }
              }
            },
            "version": {
              "type": "text",
              "fields": {
                "keyword": {
                  "type": "keyword",
                  "ignore_above": 256
                }
              }
            }
          }
        },
        "aws": {
          "properties": {
            "cloudwatch": {
              "properties": {
                "ingestion_time": {
                  "type": "date"
                },
                "log_group": {
                  "type": "text",
                  "fields": {
                    "keyword": {
                      "type": "keyword",
                      "ignore_above": 256
                    }
                  }
                },
                "log_stream": {
                  "type": "text",
                  "fields": {
                    "keyword": {
                      "type": "keyword",
                      "ignore_above": 256
                    }
                  }
                }
              }
            }
          }
        },
        "cloud": {
          "properties": {
            "region": {
              "type": "text",
              "fields": {
                "keyword": {
                  "type": "keyword",
                  "ignore_above": 256
                }
              }
            }
          }
        },
        "data_stream": {
          "properties": {
            "dataset": {
              "type": "text",
              "fields": {
                "keyword": {
                  "type": "keyword",
                  "ignore_above": 256
                }
              }
            },
            "namespace": {
              "type": "text",
              "fields": {
                "keyword": {
                  "type": "keyword",
                  "ignore_above": 256
                }
              }
            },
            "type": {
              "type": "text",
              "fields": {
                "keyword": {
                  "type": "keyword",
                  "ignore_above": 256
                }
              }
            }
          }
        },
        "event": {
          "properties": {
            "dataset": {
              "type": "text",
              "fields": {
                "keyword": {
                  "type": "keyword",
                  "ignore_above": 256
                }
              }
            },
            "id": {
              "type": "text",
              "fields": {
                "keyword": {
                  "type": "keyword",
                  "ignore_above": 256
                }
              }
            },
            "ingested": {
              "type": "date"
            }
          }
        },
        "host": {
          "properties": {
            "name": {
              "type": "text",
              "fields": {
                "keyword": {
                  "type": "keyword",
                  "ignore_above": 256
                }
              }
            }
          }
        },
        "input": {
          "properties": {
            "type": {
              "type": "text",
              "fields": {
                "keyword": {
                  "type": "keyword",
                  "ignore_above": 256
                }
              }
            }
          }
        },
        "log": {
          "properties": {
            "file": {
              "properties": {
                "path": {
                  "type": "text",
                  "fields": {
                    "keyword": {
                      "type": "keyword",
                      "ignore_above": 256
                    }
                  }
                }
              }
            }
          }
        },
        "message": {
          "type": "text",
          "fields": {
            "keyword": {
              "type": "keyword",
              "ignore_above": 256
            }
          }
        },
        "metrics": {
          "properties": {
            "size": {
              "type": "long"
            },
            "tmin": {
              "type": "long"
            }
          }
        },
        "process": {
          "properties": {
            "name": {
              "type": "text",
              "fields": {
                "keyword": {
                  "type": "keyword",
                  "ignore_above": 256
                }
              }
            }
          }
        },
        "tags": {
          "type": "text",
          "fields": {
            "keyword": {
              "type": "keyword",
              "ignore_above": 256
            }
          }
        }
      }
    }
  }
}

Sample Data

{
  "took": 197,
  "timed_out": false,
  "_shards": {
    "total": 5,
    "successful": 5,
    "skipped": 0,
    "failed": 0
  },
  "hits": {
    "total": {
      "value": 10000,
      "relation": "gte"
    },
    "max_score": 1,
    "hits": [
      {
        "_index": "big5",
        "_id": "3ed8c7059d6506412cb8140c",
        "_score": 1,
        "_source": {
          "@timestamp": "2023-01-01T05:10:44.000Z",
          "aws.cloudwatch": {
            "log_stream": "cherryspeaker",
            "ingestion_time": "2023-09-13T14:47:34.202Z",
            "log_group": "/var/log/messages"
          },
          "cloud": {
            "region": "eu-west-3"
          },
          "log.file.path": "/var/log/messages/cherryspeaker",
          "input": {
            "type": "aws-cloudwatch"
          },
          "data_stream": {
            "namespace": "default",
            "type": "logs",
            "dataset": "generic"
          },
          "process": {
            "name": "sshd"
          },
          "message": "2023-09-13T14:47:34.202Z Sep 13 14:47:34 ip-113-202-89-46 sshd: chill unicorn thunder puma shoulder carver rider stone cloverhoof",
          "event": {
            "id": "fieldrider",
            "ingested": "2023-09-13T13:52:27.202704000Z",
            "dataset": "generic"
          },
          "host": {
            "name": "nimbleraccoon"
          },
          "metrics": {
            "size": 1819,
            "tmin": 1
          },
          "agent": {
            "id": "9d0fd4b2-0cf1-4b9b-9ad1-61e46657134d",
            "name": "nimbleraccoon",
            "type": "filebeat",
            "version": "8.8.0",
            "ephemeral_id": "9d0fd4b2-0cf1-4b9b-9ad1-61e46657134d"
          },
          "tags": [
            "preserve_original_event"
          ]
        }
      },
      {
        "_index": "big5",
        "_id": "96ec04ba2fc9fb4c49e5eef4",
        "_score": 1,
        "_source": {
          "@timestamp": "2023-01-01T07:18:55.000Z",
          "aws.cloudwatch": {
            "log_stream": "ceruleanlady",
            "ingestion_time": "2023-09-13T14:47:34.206Z",
            "log_group": "/var/log/messages"
          },
          "cloud": {
            "region": "ap-northeast-2"
          },
          "log.file.path": "/var/log/messages/ceruleanlady",
          "input": {
            "type": "aws-cloudwatch"
          },
          "data_stream": {
            "namespace": "default",
            "type": "logs",
            "dataset": "generic"
          },
          "process": {
            "name": "cron"
          },
          "message": "2023-09-13T14:47:34.206Z Sep 13 14:47:34 ip-199-242-151-18 cron: binder rat snarl hyena shrieker salmon spur jaguar seeker carp racer fly whimsey duck scorpion muse brow kicker mucksloth",
          "event": {
            "id": "buttercupnose",
            "ingested": "2023-09-13T14:09:45.206840000Z",
            "dataset": "generic"
          },
          "host": {
            "name": "tinyhero"
          },
          "metrics": {
            "size": 1580,
            "tmin": 1
          },
          "agent": {
            "id": "3dbba8f5-1576-4381-8c8c-24c988d20681",
            "name": "tinyhero",
            "type": "filebeat",
            "version": "8.8.0",
            "ephemeral_id": "3dbba8f5-1576-4381-8c8c-24c988d20681"
          },
          "tags": [
            "preserve_original_event"
          ]
        }
      },
      {
        "_index": "big5",
        "_id": "ba239554a59d1d4bbb804418",
        "_score": 1,
        "_source": {
          "@timestamp": "2023-01-01T04:33:24.000Z",
          "aws.cloudwatch": {
            "log_stream": "pewtersight",
            "ingestion_time": "2023-09-13T14:47:34.206Z",
            "log_group": "/var/log/messages"
          },
          "cloud": {
            "region": "eu-west-1"
          },
          "log.file.path": "/var/log/messages/pewtersight",
          "input": {
            "type": "aws-cloudwatch"
          },
          "data_stream": {
            "namespace": "default",
            "type": "logs",
            "dataset": "generic"
          },
          "process": {
            "name": "kernel"
          },
          "message": "2023-09-13T14:47:34.206Z Sep 13 14:47:34 ip-55-217-186-12 kernel: weasel chill crystal dog mustang snapper piper coyote scale yak face ogre swallow dolphin crusher dancer scarer gem mercurykitten",
          "event": {
            "id": "morningsprite",
            "ingested": "2023-09-13T13:56:18.206874000Z",
            "dataset": "generic"
          },
          "host": {
            "name": "viridianstalker"
          },
          "metrics": {
            "size": 1676,
            "tmin": 1
          },
          "agent": {
            "id": "619a4ca8-9cae-4548-a9ea-1b1c8cfda29c",
            "name": "viridianstalker",
            "type": "filebeat",
            "version": "8.8.0",
            "ephemeral_id": "619a4ca8-9cae-4548-a9ea-1b1c8cfda29c"
          },
          "tags": [
            "preserve_original_event"
          ]
        }
      },
      {
        "_index": "big5",
        "_id": "929d9ff3127c63b676c3b8a6",
        "_score": 1,
        "_source": {
          "@timestamp": "2022-12-31T18:49:28.000Z",
          "aws.cloudwatch": {
            "log_stream": "pollenpaw",
            "ingestion_time": "2023-09-13T14:47:34.207Z",
            "log_group": "/var/log/messages"
          },
          "cloud": {
            "region": "eu-north-1"
          },
          "log.file.path": "/var/log/messages/pollenpaw",
          "input": {
            "type": "aws-cloudwatch"
          },
          "data_stream": {
            "namespace": "default",
            "type": "logs",
            "dataset": "generic"
          },
          "process": {
            "name": "cron"
          },
          "message": "2023-09-13T14:47:34.207Z Sep 13 14:47:34 ip-15-41-158-151 cron: butterfly robin master sargent charger shoulder leg mark antelope braid chin devourer wheatpiper",
          "event": {
            "id": "flickerunicorn",
            "ingested": "2023-09-13T13:51:38.207060000Z",
            "dataset": "generic"
          },
          "host": {
            "name": "springwyrm"
          },
          "metrics": {
            "size": 1478,
            "tmin": 1
          },
          "agent": {
            "id": "954bc54b-9454-4971-8c6e-b0968eeeaaed",
            "name": "springwyrm",
            "type": "filebeat",
            "version": "8.8.0",
            "ephemeral_id": "954bc54b-9454-4971-8c6e-b0968eeeaaed"
          },
          "tags": [
            "preserve_original_event"
          ]
        }
      },
      {
        "_index": "big5",
        "_id": "2c2028329b31f9a20a469026",
        "_score": 1,
        "_source": {
          "@timestamp": "2023-01-01T10:23:39.000Z",
          "aws.cloudwatch": {
            "log_stream": "wooltongue",
            "ingestion_time": "2023-09-13T14:47:34.207Z",
            "log_group": "/var/log/messages"
          },
          "cloud": {
            "region": "us-west-2"
          },
          "log.file.path": "/var/log/messages/wooltongue",
          "input": {
            "type": "aws-cloudwatch"
          },
          "data_stream": {
            "namespace": "default",
            "type": "logs",
            "dataset": "generic"
          },
          "process": {
            "name": "cron"
          },
          "message": "2023-09-13T14:47:34.207Z Sep 13 14:47:34 ip-9-161-91-231 cron: lantern lacesloth",
          "event": {
            "id": "helixserpent",
            "ingested": "2023-09-13T13:53:45.207079000Z",
            "dataset": "generic"
          },
          "host": {
            "name": "longprincess"
          },
          "metrics": {
            "size": 1443,
            "tmin": 1
          },
          "agent": {
            "id": "c315dc22-3ea6-44dc-8d56-fd02f675367b",
            "name": "longprincess",
            "type": "filebeat",
            "version": "8.8.0",
            "ephemeral_id": "c315dc22-3ea6-44dc-8d56-fd02f675367b"
          },
          "tags": [
            "preserve_original_event"
          ]
        }
      },
      {
        "_index": "big5",
        "_id": "d4dfafe04040457862d5cd8b",
        "_score": 1,
        "_source": {
          "@timestamp": "2023-01-01T09:50:42.000Z",
          "aws.cloudwatch": {
            "log_stream": "beryldeath",
            "ingestion_time": "2023-09-13T14:47:34.207Z",
            "log_group": "/var/log/messages"
          },
          "cloud": {
            "region": "ap-northeast-2"
          },
          "log.file.path": "/var/log/messages/beryldeath",
          "input": {
            "type": "aws-cloudwatch"
          },
          "data_stream": {
            "namespace": "default",
            "type": "logs",
            "dataset": "generic"
          },
          "process": {
            "name": "journal"
          },
          "message": "2023-09-13T14:47:34.207Z Sep 13 14:47:34 ip-43-87-142-39 journal: deer ridge prince moose mind tiger sight diver otter lasher keeper chanter swallow trader track toe slashboa",
          "event": {
            "id": "coldsword",
            "ingested": "2023-09-13T14:41:14.207111000Z",
            "dataset": "generic"
          },
          "host": {
            "name": "nobleglass"
          },
          "metrics": {
            "size": 1489,
            "tmin": 1
          },
          "agent": {
            "id": "baac7358-a449-4c36-bf0f-befb211f1d38",
            "name": "nobleglass",
            "type": "filebeat",
            "version": "8.8.0",
            "ephemeral_id": "baac7358-a449-4c36-bf0f-befb211f1d38"
          },
          "tags": [
            "preserve_original_event"
          ]
        }
      },
      {
        "_index": "big5",
        "_id": "b03e48f1edc02927b05e1499",
        "_score": 1,
        "_source": {
          "@timestamp": "2023-01-01T05:22:23.000Z",
          "aws.cloudwatch": {
            "log_stream": "atomfin",
            "ingestion_time": "2023-09-13T14:47:34.207Z",
            "log_group": "/var/log/messages"
          },
          "cloud": {
            "region": "us-west-2"
          },
          "log.file.path": "/var/log/messages/atomfin",
          "input": {
            "type": "aws-cloudwatch"
          },
          "data_stream": {
            "namespace": "default",
            "type": "logs",
            "dataset": "generic"
          },
          "process": {
            "name": "systemd"
          },
          "message": "2023-09-13T14:47:34.207Z Sep 13 14:47:34 ip-213-120-11-125 systemd: grin cap gull gecko braid panther prince chiller agatecentaur",
          "event": {
            "id": "leatherflame",
            "ingested": "2023-09-13T14:21:07.207126000Z",
            "dataset": "generic"
          },
          "host": {
            "name": "pondoriole"
          },
          "metrics": {
            "size": 1346,
            "tmin": 1
          },
          "agent": {
            "id": "98a5f50f-4ae2-4bc7-9bfd-3e26e7ac6da7",
            "name": "pondoriole",
            "type": "filebeat",
            "version": "8.8.0",
            "ephemeral_id": "98a5f50f-4ae2-4bc7-9bfd-3e26e7ac6da7"
          },
          "tags": [
            "preserve_original_event"
          ]
        }
      },
      {
        "_index": "big5",
        "_id": "c6af76ca9ec26b716120e0aa",
        "_score": 1,
        "_source": {
          "@timestamp": "2023-01-01T12:35:49.000Z",
          "aws.cloudwatch": {
            "log_stream": "amberscowl",
            "ingestion_time": "2023-09-13T14:47:34.207Z",
            "log_group": "/var/log/messages"
          },
          "cloud": {
            "region": "ap-southeast-2"
          },
          "log.file.path": "/var/log/messages/amberscowl",
          "input": {
            "type": "aws-cloudwatch"
          },
          "data_stream": {
            "namespace": "default",
            "type": "logs",
            "dataset": "generic"
          },
          "process": {
            "name": "systemd"
          },
          "message": "2023-09-13T14:47:34.207Z Sep 13 14:47:34 ip-70-141-237-97 systemd: sequoiastallion",
          "event": {
            "id": "midnightwolverine",
            "ingested": "2023-09-13T13:48:34.207215000Z",
            "dataset": "generic"
          },
          "host": {
            "name": "kiwipuppy"
          },
          "metrics": {
            "size": 1246,
            "tmin": 1
          },
          "agent": {
            "id": "c315dc22-3ea6-44dc-8d56-fd02f675367b",
            "name": "kiwipuppy",
            "type": "filebeat",
            "version": "8.8.0",
            "ephemeral_id": "c315dc22-3ea6-44dc-8d56-fd02f675367b"
          },
          "tags": [
            "preserve_original_event"
          ]
        }
      },
      {
        "_index": "big5",
        "_id": "317bcc200e243e474acae679",
        "_score": 1,
        "_source": {
          "@timestamp": "2022-12-31T20:26:40.000Z",
          "aws.cloudwatch": {
            "log_stream": "carnationtooth",
            "ingestion_time": "2023-09-13T14:47:34.207Z",
            "log_group": "/var/log/messages"
          },
          "cloud": {
            "region": "us-east-2"
          },
          "log.file.path": "/var/log/messages/carnationtooth",
          "input": {
            "type": "aws-cloudwatch"
          },
          "data_stream": {
            "namespace": "default",
            "type": "logs",
            "dataset": "generic"
          },
          "process": {
            "name": "journal"
          },
          "message": "2023-09-13T14:47:34.207Z Sep 13 14:47:34 ip-32-107-26-140 journal: tongue glowsight",
          "event": {
            "id": "pebblewolverine",
            "ingested": "2023-09-13T14:45:17.207276000Z",
            "dataset": "generic"
          },
          "host": {
            "name": "roadcrown"
          },
          "metrics": {
            "size": 1300,
            "tmin": 1
          },
          "agent": {
            "id": "628cdfc8-a97a-4050-8f4f-bf53f2bf983c",
            "name": "roadcrown",
            "type": "filebeat",
            "version": "8.8.0",
            "ephemeral_id": "628cdfc8-a97a-4050-8f4f-bf53f2bf983c"
          },
          "tags": [
            "preserve_original_event"
          ]
        }
      },
      {
        "_index": "big5",
        "_id": "d407fff55e2d2b4a39ef9044",
        "_score": 1,
        "_source": {
          "@timestamp": "2023-01-01T07:27:06.000Z",
          "aws.cloudwatch": {
            "log_stream": "greenlegend",
            "ingestion_time": "2023-09-13T14:47:34.207Z",
            "log_group": "/var/log/messages"
          },
          "cloud": {
            "region": "me-central-1"
          },
          "log.file.path": "/var/log/messages/greenlegend",
          "input": {
            "type": "aws-cloudwatch"
          },
          "data_stream": {
            "namespace": "default",
            "type": "logs",
            "dataset": "generic"
          },
          "process": {
            "name": "journal"
          },
          "message": "2023-09-13T14:47:34.207Z Sep 13 14:47:34 ip-133-90-172-167 journal: tiger weaver drifter cap hair thorn vole palm brassscar",
          "event": {
            "id": "notchburn",
            "ingested": "2023-09-13T14:36:37.207305000Z",
            "dataset": "generic"
          },
          "host": {
            "name": "tiderat"
          },
          "metrics": {
            "size": 1278,
            "tmin": 1
          },
          "agent": {
            "id": "619a4ca8-9cae-4548-a9ea-1b1c8cfda29c",
            "name": "tiderat",
            "type": "filebeat",
            "version": "8.8.0",
            "ephemeral_id": "619a4ca8-9cae-4548-a9ea-1b1c8cfda29c"
          },
          "tags": [
            "preserve_original_event"
          ]
        }
      }
    ]
  }
}

Bug Description

Issue Summary:
The stats command doesn't seem to accept timestamp types as produced by bin.

Steps to Reproduce:

  1. Create index with timestamp data (in this case the index has 7 mil records)
  2. Run a bin timestamp -> stats by timestamp query

Impact:
This is a relevant query for answering questions like "What does our overall request load look like over time?"

Environment Information

OpenSearch Version:
3.1

Additional Details:
N/A

Screenshots

N/A

Metadata

Metadata

Assignees

No one assigned

    Labels

    PPLPiped processing languagebugSomething isn't working

    Type

    No type

    Projects

    Status

    Done

    Milestone

    No milestone

    Relationships

    None yet

    Development

    No branches or pull requests

    Issue actions