Sum/filter on the buckets

[wzhang]

Hi Elastic forum,

 I am evaluating ELK for system monitoring purpose. our system will generate data like:

{"Date": "2017-01-31T09:00:14.596168", "VehicleName": "Honda CRV", "Status": "On", "Group": "groupA", "Company": "companyA"}

{"Date": "2017-01-31T08:00:14.596168", "VehicleName": "Honda CRV", "Status": "Off", "Group": "groupA", "Company": "companyA"}

{"Date": "2017-01-31T09:01:14.596168", "VehicleName": "Honda Civic", "Status": "On", "Group": "groupB", "Company": "companyA"}

{"Date": "2017-01-31T09:02:14.596168", "VehicleName": "Honda FIT", "Status": "On", "Group": "groupC", "Company": "companyB"}

I need to create dashboard to find the number of vehicle whose latest status is "On" for each group/company.
so the dashboard should display:
companyA: 2
CompanyB: 1

In visualize, I could use aggregation to get the latest status for each vehicle. but could not figure out how to only return vehicles with latest statue "On" and get the sum for each company/group.

is it possible to create a dashboard like that in Kibi/kibana?

thanks for any idea/help!

William

[LeeDr (Lee Drengenberg)]

Hi William,
For this kind of report I think you would need to get the data into an Elasticsearch index in a slightly different way.

If each of these cars, or car/group/company sets is unique, you could create another index where each change of status is an update to that car, instead of an insert (index). This would be a small index which only contains 1 doc per car. It could still have the timestamp so that you could see the time of the last status change.

Then the visualization or data table would be very simple.

How is the data currently being loaded into Elasticsearch ?

Regards,
Lee