Need help with simple agregation

[marjue]

Hello
I need help in a simple case but I’m too stupid.
There is an index with simple monitoring data. The main fields are:
service_name (text)
service_status (text)
service_time (date)

Every 5 minutes a new state for every service_name is put to the index.

What I need is a query wich gives me the most current state of every existing service. I want to use it in a kibana dashboard.

Thanks for your help
Marcus

[DineshNaik (Dinesh Kumar Naik)]

Hi @marjue
Welcome to the community.

Try something like this:

GET index_name/_search
{
"size": 0,
"aggs": {
"by_service": {
"terms": {
"field": "service_name.keyword",
"size": 1000
},
"aggs": {
"latest_state": {
"top_hits": {
"sort": [
{
"service_time": {
"order": "desc"
}
}
],
"size": 1,
"_source": {
"includes": [ "service_name", "service_status", "service_time" ]
}
}
}
}
}
}
}

You can tweak this as per your need on size . Let me know if this helps.

[marjue]

@DineshNaik
Thankk you
Is it possible to use this in Kibana? I thought only ES|QL is possible.
I’m very new to ES and Kibana.

[DineshNaik (Dinesh Kumar Naik)]

You can try this in kibana dev tool directly

[Tortoise]

Hello @marjue

As you want to create a dashboard to only show the recent information below are the high level steps :

1. Create a dataview say services with service_time as the Timestamp

service_name (text)
service_status (text)
service_time (date)

2. Create a dashboard > Add panel > Lens > Select the above dataview

you can use metrics as per below screenshot

rows => service_name (100)

Metrics =>

service_status (Last value)
service_time (Last value)

And if it is ES|QL

FROM services*
| STATS latest_time = MAX(service_time) BY service_name

Thanks!!