How to get only newest records by name?

Elastic Stack Kibana
1

Hello,
In I'm using kibana 4.4.1 and in elasticsearch I store the status of PC, only when PC status is changed (open, closed, warings, etc)

My data looks like:
{ "status_id":1 , "pc":"lpt001" , "date":"2016-10-25T17:49:00Z" }
{ "status_id":3 , "pc":"lpt001" , "date":"2016-10-25T15:48:00Z" }
{ "status_id":4 , "pc":"lpt002" , "date":"2016-10-25T15:46:00Z" }
{ "status_id":1 , "pc":"lpt002" , "date":"2016-10-25T12:48:00Z" }

And I what to get the newest record in order to have at any time how many PC's are opened, closed or have some issues.

I have tried wit aggs and max, but it doesn.t work:

{
  "aggs": {
    "max_date": {
      "max": {
        "field": "date"
      }
    }
  }
}

There is any way to do this in kibana?

Thank you,
Ovidiu

2

well kibana by default sorts by time and the range is your timepicker.

you probably just want to go make a data table .

As for the concept of "Newest record" this is not a concept that can be used. you can only say here are the entries with in this time frame'

3

Hello Ed,

Thank you for your answer! I already made some Visualizations in kibana with some very simple filters, but this is first time when I need this kind of filter
Let me understand, in Kibana I cannot create a simple filter who in sql looks like that:

select MAX([date]) max_date
    ,pc_id
from hist
group by pc_id

And in order to do that I should export this data into a relational database? Or there is any other option?

Thank you,
Ovidiu

4

it is dangerous to think of a sql statement and ELK Query language in the same way. While you can do similar things the concepts are very different. Especially on things like subqueries which "MAX" sort of is doing in your example Go find the MAX date in the table, then query the rest.

So the groupby you might consider it as the "Aggregation" either "Count" or Math like (SUM , MAX, MIN) of a number

Think of it this way, (Sorry my sql is rusty)

select between(now-10, NOW) from INDEX where verb is "GET" groupby URL
or
select between(now-10, NOW) from INDEX where verb is "GET" groupby (@TIMESTAMP/1m) and URL

This would be closer on how Kibana works with aggregations,

{
query: {
"This of this as the where clause"
}
filter: {
"Think of this as the select statement between being the timepicker"
}
aggro{
This of this as the groupby
}
}

I am sure you get very close to the results you want, it just takes a bit to get used to the interface.
You may want to start a new thread with details on your data (right from Elasticsearch) and a DSL that you have come up with and someone who is more advanced in the query language can help you out.

1 Like
5

BTW, since I am not employed by elasticsearch, I can recommend open source tools you might like

This open source project simulats SQL and might help you transition, don't know how well it works but has been around for a while

This is a nice alternative to Kibana but needs some maturity
https://zeppelin.apache.org/

6

I cannot change the tool. the report should be done in kibana :smiley:

7

After @ywelsch helps me with a query for elasticsearch which looks like:

{
  "query": {
    "match_all": { }
  },
  "aggs" : {
        "pcstatus" : {
            "terms" : {
                "field" : "pc"
            },
            "aggs": {
                "top_date_hit": {
                    "top_hits": {
                        "sort": [
                            {
                                "date": {
                                    "order": "desc"
                                }
                            }
                        ],
                        "size" : 1
                    }
                }
            }
        }
    }
}

Even if at this moment I don't understand 100% how this queries works, I was able to convert it into Visualize query for kibana which looks like:

{
  "title": "New Visualization",
  "type": "table",
  "params": {
    "perPage": 10,
    "showPartialRows": false,
    "showMeticsAtAllLevels": true
  },
  "aggs": [
    {
      "id": "1",
      "type": "max",
      "schema": "metric",
      "params": {
        "field": "date"
      }
    },
    {
      "id": "5",
      "type": "terms",
      "schema": "bucket",
      "params": {
        "field": "pc",
        "size": 0,
        "order": "desc",
        "orderBy": "1"
      }
    },
    {
      "id": "6",
      "type": "terms",
      "schema": "bucket",
      "params": {
        "field": "status_id",
        "size": 1,
        "order": "desc",
        "orderBy": "_term"
      }
    }
  ],
  "listeners": {}
}

I hope this will help the others. :slight_smile: