Counting in a Metric View the Lasted Update of a Status

[Augusto_Calado_Bueno (Augusto)]

Hello everyone.

Could you help me with a question? any help is welcome.

Just to contextualize my case, I have a programm that receives several services orders which belongs to clients. Any client can have several services orders.

The orders have different status as "Approved", "Reproved", "Finished" and "In Progress".

I am trying to create a Kibana Metric View that displayes the number of services orders reproved
taking into account a specific criteria.

criteria: I need to count the total of reproved service orders made by clients, taking into account the latest service order of each client and considering it in my count only who has reproved status.

The result that I would like to achieve is the same result as below SQL query:

SELECT COUNT() FROM SERVICE_ORDER WHERE STATUS = 'REPROVED" AND UPDATE_DATE IN*

• (SELECT MAX(UPDATE_DATE) FROM SERVICE_ORDER X GROUP BY ID_CLIENT)*

I have tried, but without success, to use the aggregation "Unique Count" with field "id_client" and add some filters

I imagine I need to use the buckets aggregations filters with some criteria, but I don't know if it's the right approach .... Also, being honest with you, I haven't had a chance to learn about using this aggregation (filter), so I have some doubts.

Could you guide me in this problem?

Any help will be appreciated!!

Thank you very much!

PS: Sorry for my english, if you have any questions about what I wrote, please let me know.
PS: My elasticSearch and kibana are from AWS and I figured out that using native sql don't work because of some limitations/restrictions of aws version.

[flash1293 (Joe Reuter)]

Hi, thanks for reaching out.

Allow me to rephrase the data you want to present - and please correct me when I'm wrong about this: You want to get the count of all clients that have at least one reproved order.

And your documents represent individual orders and look like this:
{ order_date: ..., status: 'REPROVED', id_client: '...' }

It sounds like the approach you tried was already correct - doing a unique count on id_client and only take into account orders with a REPROVED status. You can create a filter pill in the filter bar above the visualization. These will be saved along with the visualization and will also be applied if you embed the visualization on a dashboard:

You wrote you tried this approach without success, what was the problem with the result?

[Augusto_Calado_Bueno (Augusto)]

@flash1293 thank you very much for your anwser!! and sorry for my delay.

You almost undertood all my case . You missed just one small detail. So let me clarify more.

My problem here is that I would like to consider just the lastet service order of a client and count that if it has the reproved as its status. So, just to explain what I mean. let's supose that I have these entries in elasticsearch

{ order_date: 2019-10-15 ,status: 'REPROVED', id_client: 1 }
{ order_date: 2019-10-15 ,status: 'APPROVED', id_client: 2 }
{ order_date: 2019-10-14 ,status: 'REPROVED', id_client: 1 }
{ order_date: 2019-10-14 ,status: 'REPROVED', id_client: 2 }

The view that I'd like to create is a metric visualization

and it will display 1 as its result. But why 1? Well, since we want to count the lastest order service for each client, but just if it has status reproved, then the only client that has this kind of value is id_client = 1, because the latest order of client 2 is approved.

I hope I have clarified a little more

Thank you so much for trying to help me with this issue. I really appreciate every help.

PS: Sorry for my english, if you have any questions about what I wrote, please let me know

[flash1293 (Joe Reuter)]

I understand, that makes sense. Unfortunately I don't see a way currently to do this in Kibana.

You could change your ingest logic by maintaining a separate index that only contains the last order for each client. On this separate index you could simply filter down to all orders with the desired status.

Of course that moves to problem into your own code - instead of simply ingesting a new document, you would have to look up the current "last order" of the client and overwrite the current document. It depends on your setup how easy this is.