I was wondering whether it is possible to create a scripted field that can query the top hit (last value) for each unique value of product and then sum that so it can be used for graphing in Kibana?
Basically I have a range of products and some companies that spend X amount of money on various products. The total amount spent on each product is delivered in .csv format every day, hence I need to find the latest value first.
The idea is that if I can sum up the last values for each product in a single value, I can then apply a term (company name) and data histrogram (per month) to get the total cost for each company per month and plot that out to easily spot trends.
I don't believe you can run any aggregation on the source of a top hit. Fetching the top hits happens after the aggregation phase has finished so there's no chance to do additional work on them other than sorting and displaying.
Often, you can work around this. In your case, you can aggregate companies -> products, then sort each product by it's timestamp descending. This will put the largest (most recent) timestamp at the top. If the products terms size is set to 1, you will only have the most recent product in that bucket, and can use that for further analytics.
It's a little less convenient and takes some fiddling but is often doable
This question sounds very similar to one I recently answered - the answer there about how to solve this using scripted aggregations may be useful to you, although it sounds like you may have to add a date histogram aggregation into the mix as well.
Apache, Apache Lucene, Apache Hadoop, Hadoop, HDFS and the yellow elephant
logo are trademarks of the
Apache Software Foundation
in the United States and/or other countries.