Aggregation of latest values


I’m new to Graylog and struggling to create a report for my specific scenario. I have a dataset of orders with the following structure:

order_id order_status timestamp
1 started 01.01.2023
1 in_progress 02.01.2023
2 started 02.01.2023
1 complete 03.01.2023
2 in_progress 04.01.2023

What I need is the latest status of each order along with the count of those statuses. In SQL terms, the query would look like this:

sqlCopy code

WITH ranked AS (
        ROW_NUMBER() OVER (PARTITION BY order_id ORDER BY timestamp DESC) AS r
    FROM orders
SELECT order_status, COUNT(*) 
FROM ranked
WHERE r = 1
GROUP BY order_status;

I appreciate any guidance on how to achieve this in Graylog. Thank you!"

My SQL is getting rusty, can you post an example of what the aggregation should look like based on the values in your example?

I want to have

  1. I want to get latest status for each order id
  2. Then i want to count the number of orders for each latest status.
    something like
    status, count(*)
    in_progress, 2
    completed, 12

So for the first part, you can do an aggregation like this:

But I think you will have to run the second one as its own widget. That would be a group by the field status, and then the metric could be count, of some field that all of them have (you can even use “timestamp”)

I now how to do them separately as widgets. but I don’t know how to use the first one’s result as an input to the second widget.
Can you guide me how to do it?

That I don’t think you can really do, widgets and the queries that run in them, are separate objects and don’t have any kind of relationship to each other. Graylog also doesn’t really have the idea of a query that feeds a query.

This topic was automatically closed 14 days after the last reply. New replies are no longer allowed.