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.