Aggregation of latest values

[hakan-kucuk]

"Hello,

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 (
    SELECT 
        order_id, 
        order_status, 
        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!"

[Joel_Duffield]

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

[hakan-kucuk]

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

[Joel_Duffield]

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”)

[hakan-kucuk]

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?

[Joel_Duffield]

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.