Grafana Elasticsearch table aggregate by time and then terms for multiple metrics

Grafana Elasticsearch
1

  • What Grafana version and what operating system are you using?
    Grafana v10.2.2

  • What are you trying to achieve?
    In Table visualization, I want to aggregate by both time and terms, then show multiple metrics in the table.

In SQL terms, it would be like this:

SELECT
    date
    ,order_status
    ,COUNT(1)
    ,SUM(amount)
FROM
    orders
GROUP BY
    date
    ,order_status
  • How are you trying to achieve it?
    Firstly, I tried to group by Terms and then by Date Histogram, it shows multiple frames.

image
image1666Ă—1650 185 KB

image
image1670Ă—802 41.5 KB

  • What happened?
    Then, I tried Reduce to rows, but SUM and COUNT go to rows as well.

image
image1662Ă—918 59.3 KB

Moreover, I tried Join by Field, and it spreads out in columns.

image
image1654Ă—1138 72.9 KB

  • What did you expect to happen?
    What I expect is I can “group by” time and terms, and then show multiple metrics like SUM and COUNT in a table, something like this:

image
image1936Ă—526 44.4 KB

2

Welcome @blue86321

I highly recommend you look into using infinity grafana plugin and hit the 9200 endpoint and using UQL query language you have a whole log more flexibility.

Can you share a sample ES document you are working with minus sensitive data, just obfuscate it

3

Thank you for your advice, I’ll go check out that plugin later.

Here’s my ES doc, it’s demo data so I don’t mind sharing it.

{
  "_source": {
      "@timestamp": "2023-12-07T01:11:23.684Z",
      "log_name": "myapp-order",
      "order": {
        "order_id": "f5ca4d4f-11ec-4a1f-b057-87baefb02fec",
        "status": "PENDING",
        "amount": 5700,
        "order_time": "2023-12-07T01:10:15.685583",
        "pay_time": "2023-12-07T01:11:23.684537"
      },
      "user": {
        "uid": "4",
        "anonymous": false
      },
      "time": "2023-12-07T01:11:23.684537"
    }
}
1 Like
4

to get you started take a look at this jsonata query using sample es doc, which is a feature of infinity which gives you a lot of flexibility

https://try.jsonata.org/iNIgL-5Sm

Here is jsonata docu

5

Hi, @yosiasz
I tried the Infinity plugin, but I don’t think it solves my problem.
In Elasticsearch, if we write a query via API, we need to use request body for a GET request (see the following code block), which Infinity doesn’t support.

GET /_search
{
  "query": {
    "query_string": {
      "query": "(new york city) OR (big apple)",
      "default_field": "content"
    }
  }
}

Therefore, I still suffer from my original problem :frowning: but thank you for your help!

6

Might want to read up on the documentation and poke around the plugin options

image

7

Yes, I did. That’s why I said it seems like it doesn’t support request body for a GET request.

As you can see from the screenshot, there are only Headers and Query Params options, but no body option.
body option will pop up only when we select a POST method.

8

I do not see a body in your GET request

If you see specs of GET

Not sure if es does so something different with use of body in GET

9

In Elasticsearch, we do need body to query data, as I mentioned earlier in GET /_search.

Here is an example of how we query data in index myapp-order-2023.12 and look up all docs with its order.status equal to PENDING

curl -L -X GET 'http://localhost:9200/myapp-order-2023.12/_search' \
-H 'Content-Type: application/json' \
-d '{
  "query": {
    "term": {
      "order.status.keyword": {
        "value": "PENDING"
      }
    }
  }
}'

I know it didn’t follow the spec, but in reality, there are some scenarios in which we need something that is not standard according to the spec.

So, Infinity plugin cannot solve my current problem anyway. It seems like for Elasticsearch in Grafana, there is no way we can get the result that I mentioned at the very beginning of my problem.

1 Like