Getting latest record of each day

[veerabhadraiah]

Hi ,

In mysql i had query like

select id, eventIdx, sitename,machine, nid, nocstatus, reportDate,priority from (select id, eventIdx, sitename,machine, nid, nocstatus, reportDate,priority from event.tempGraphSummary where reportDate between '2018-04-04' and '2018-04-19' and priority=1 and sitename = 'notifyconsole__2018000743' order by id desc) as t group by sitename,machine,priority,reportDate,nid

Which gives me data of all latest records of each date and with machine list.

In ElasticSearch i tried like

{
  "size": 1000,
  "query": {
    "bool": {
      "must": [
        {"term": {"sitename": "notifyconsole__2018000743"}},
        {"term": {"priority": "1"}}
      ]
    }
  },"sort":[{"id":{"order":"asc"}}],
  "aggs": {
    "id1_count": {"terms": { "field": "nid"},
	"aggs": {
        "id2_count": {"terms": { "field": "sitename"},
          "aggs": {
              "id3_count": {"terms": { "field": "priority"},
               "aggs": {"id4_count": {"terms": { "field": "reportDate"},
                   "aggs": {"id5_count": {"terms": { "field": "machine"} }
                    }
                 }
                }
             }

          }
       }
       
    }
  }
}
}

But am not getting expected result please someone help me.

[jpountz (Adrien Grand)]

You probably need to run a date_histogram aggregation with a sub top hits aggregation that has a size equal to 1 and sorts by decreasing id. See https://www.elastic.co/guide/en/elasticsearch/reference/current/search-aggregations-metrics-top-hits-aggregation.html.