# Using aggregate filter to count number of events and sum field's values **URL:** \ **Category:** Logstash\ **Created:** [August 10, 2020, 6:29pm UTC](https://discuss.elastic.co/t/using-aggregate-filter-to-count-number-of-events-and-sum-fields-values/244448 "2020-08-10T18:29:58Z")\ **Posts on this page:** 20\ **Page:** 1
**Author:** ![sahere37](https://avatars.discourse-cdn.com/v4/letter/s/b2d939/32.png) [@sahere37](https://discuss.elastic.co/u/sahere37)\ **Post date:** [August 10, 2020, 6:29pm UTC](https://discuss.elastic.co/t/using-aggregate-filter-to-count-number-of-events-and-sum-fields-values/244448/1 "2020-08-10T18:29:58Z")
Hi all, I have an index in elasticsearch name as "myindex" so that have fields like"date\_eng","company\_name","service\_name", "amount". I want to use aggregate filter so that counts the times which an services has been used by a company in yesterday and also sum the value of amount for each services of company; according this i used company\_name, service\_name and date as aggregate task\_id. for example, data is as following: ``` {"date_eng":2020-08-09, "company_name": "cm1","service_name":"ser1","amount":100} {"date_eng":2020-08-09, "company_name": "cm1","service_name":"ser1","amount":0} {"date_eng":2020-08-09, "company_name": "cm1","service_name":"ser1","amount":10} {"date_eng":2020-08-09, "company_name": "cm1","service_name":"ser2","amount":5} {"date_eng":2020-08-09, "company_name": "cm1","service_name":"ser2","amount":10} {"date_eng":2020-08-09, "company_name": "cm2","service_name":"ser1","amount":0} {"date_eng":2020-08-09, "company_name": "cm2","service_name":"ser2","amount":50} ``` output should be as following: ``` cm1-ser1-2020-08-09-3-110 cm1-ser2-2020-08-09-2-15 cm2-ser1-2020-08-09-1-0 cm2-ser2-2020-08-09-1-50 ``` for this purpose, i used following script in logstash: ``` input { elasticsearch { hosts => ["http://10.0.1.1:9200/"] index => "myindex*" query => '{ "query": { "bool" : { "filter" : { "range" : { "mytimestamp" : { "gte": "now-1d/d", "lte": "now-1d/d"}} } } }, "sort": ["_doc"] }' } } filter { mutate { split => ["date_eng", "-"] add_field => { "year" => "%{date_eng[0]}" } add_field => { "mounth" => "%{date_eng[1]}" } add_field => { "day" => "%{date_eng[2]}" } } mutate { add_field => {"aggregate_id" => "%{year}_%{mounth}_%{day}_%{company_name}_%{service_name}"} } aggregate { task_id => "%{aggregate_id}" code => " map['value'] ||= 0 map['count'] ||= 0 map['value'] += event.get('amount') map['count'] +=1 event.set('company_value', map['value']) event.set('company_count', map['count']) " } mutate { add_field => { "My_Data" => "%{company_name}-%{service_name}-%{year}-%{mounth}-%{day}-%{company_count}-%{company_value}" } } } output { csv { fields => ["My_Data"] path => "D:\my data\CS_%{year}_%{mounth}_%{day}.txt" } stdout { codec => rubydebug } } ``` but when i run the script the result is not expected and is as following: ``` cm1-ser1-2020-08-09-2-100 cm1-ser1-2020-08-09-3-110 cm1-ser2-2020-08-09-1-10 cm1-ser2-2020-08-09-2-15 cm2-ser1-2020-08-09-1-0 cm2-ser2-2020-08-09-1-50 ``` Any advise will be so appreciated. Many thanks ---
**Author:** ![Badger](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/badger/32/25190_2.png) [@Badger](https://discuss.elastic.co/u/Badger)\ **Post date:** [August 10, 2020, 7:22pm UTC](https://discuss.elastic.co/t/using-aggregate-filter-to-count-number-of-events-and-sum-fields-values/244448/2 "2020-08-10T19:22:17Z")
I would suggest doing the aggregation in elasticsearch. ---
**Author:** ![sahere37](https://avatars.discourse-cdn.com/v4/letter/s/b2d939/32.png) [@sahere37](https://discuss.elastic.co/u/sahere37)\ **Post date:** [August 11, 2020, 3:18pm UTC](https://discuss.elastic.co/t/using-aggregate-filter-to-count-number-of-events-and-sum-fields-values/244448/3 "2020-08-11T15:18:05Z")
many thanks for your reply. actually my ELK license is basic and i want to schedule the reporting procedure which cannot do it in the case of basic license. I will be so appreciated if you can advise me to handle this case using logstash. Many thanks. ---
**Author:** ![Badger](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/badger/32/25190_2.png) [@Badger](https://discuss.elastic.co/u/Badger)\ **Post date:** [August 11, 2020, 3:20pm UTC](https://discuss.elastic.co/t/using-aggregate-filter-to-count-number-of-events-and-sum-fields-values/244448/4 "2020-08-11T15:20:58Z")
It is not a licence issue. You are running an elasticsearch query to fetch data that you want to aggregate. I am suggesting you try to do the aggregation as part of the query. ---
**Author:** ![sahere37](https://avatars.discourse-cdn.com/v4/letter/s/b2d939/32.png) [@sahere37](https://discuss.elastic.co/u/sahere37)\ **Post date:** [August 11, 2020, 3:22pm UTC](https://discuss.elastic.co/t/using-aggregate-filter-to-count-number-of-events-and-sum-fields-values/244448/5 "2020-08-11T15:22:35Z")
Do you mean i use aggregation in elasticsearch input query which used in logstash? ---
**Author:** ![Badger](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/badger/32/25190_2.png) [@Badger](https://discuss.elastic.co/u/Badger)\ **Post date:** [August 11, 2020, 3:38pm UTC](https://discuss.elastic.co/t/using-aggregate-filter-to-count-number-of-events-and-sum-fields-values/244448/6 "2020-08-11T15:38:13Z")
Yes. ---
**Author:** ![sahere37](https://avatars.discourse-cdn.com/v4/letter/s/b2d939/32.png) [@sahere37](https://discuss.elastic.co/u/sahere37)\ **Post date:** [August 12, 2020, 6:06am UTC](https://discuss.elastic.co/t/using-aggregate-filter-to-count-number-of-events-and-sum-fields-values/244448/7 "2020-08-12T06:06:12Z")
many thanks for your reply. actually one of my field's type is text and i cannot use sum aggregation on it. I am changing field type in logstash side (change from string to integer). can you please advise me how can i handle the issue mentioned in the first comment, actually for each input events with same task\_id, aggregate filter has an output event whereas it is expected to release all events with same task\_id as an output event. ---
**Author:** ![Badger](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/badger/32/25190_2.png) [@Badger](https://discuss.elastic.co/u/Badger)\ **Post date:** [August 12, 2020, 3:52pm UTC](https://discuss.elastic.co/t/using-aggregate-filter-to-count-number-of-events-and-sum-fields-values/244448/8 "2020-08-12T15:52:02Z")
OK, assuming your data is sorted you can do something similar to [example 4](https://www.elastic.co/guide/en/logstash/current/plugins-filters-aggregate.html#plugins-filters-aggregate-example4). Use event.cancel to delete the individual data rows, and push\_previous\_map\_as\_event to create an event once all of the data for a given id has been seen. Note that only the data in the map is pushed as part of the event, so make sure you add everything you want in the final event to the map. You _must_ disable java\_execution for this to work. ---
**Author:** ![sahere37](https://avatars.discourse-cdn.com/v4/letter/s/b2d939/32.png) [@sahere37](https://discuss.elastic.co/u/sahere37)\ **Post date:** [August 12, 2020, 4:28pm UTC](https://discuss.elastic.co/t/using-aggregate-filter-to-count-number-of-events-and-sum-fields-values/244448/9 "2020-08-12T16:28:32Z")
it is so so weird. when i add "event.cancel()" and "push\_previous\_map\_as\_event =\> true" to aggregate filter, logstash output is as following which means no one of fields has value!!!!!!!!! %{company\_name}-%{service\_name}-%{year}-%{mounth}-%{day}-1 %{company\_name}-%{service\_name}-%{year}-%{mounth}-%{day}-2 ... and when i delete these two commands the output is same as previous ---
**Author:** ![Badger](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/badger/32/25190_2.png) [@Badger](https://discuss.elastic.co/u/Badger)\ **Post date:** [August 12, 2020, 4:41pm UTC](https://discuss.elastic.co/t/using-aggregate-filter-to-count-number-of-events-and-sum-fields-values/244448/10 "2020-08-12T16:41:56Z")
If you do event.cancel then the original data rows are deleted. The aggregate generates new events as it processes each group of rows. When those events get to ``` mutate { add_field => { "My_Data" => "%{company_name}-%{service_name}-%{year}-%{mounth}-%{day}-%{company_count}-%{company_value}" } } ``` None of those fields will exist unless you added them to the map when processing the original rows. ``` map['value'] ||= 0 map['count'] ||= 0 ``` The events will have fields called [value] and [count] since those exist in the map. ---
**Author:** ![sahere37](https://avatars.discourse-cdn.com/v4/letter/s/b2d939/32.png) [@sahere37](https://discuss.elastic.co/u/sahere37)\ **Post date:** [August 12, 2020, 4:54pm UTC](https://discuss.elastic.co/t/using-aggregate-filter-to-count-number-of-events-and-sum-fields-values/244448/11 "2020-08-12T16:54:33Z")
Many thanks for your reply. I add fields to map as following: ``` map['Company_name'] ||= event.get('Company_name') map['Service_name'] ||= event.get('Service_name') map['year'] ||= event.get('year') map['mounth'] ||= event.get('mounth') map['day'] ||= event.get('day') ``` the output changes and has less events but still is not correct ---
**Author:** ![sahere37](https://avatars.discourse-cdn.com/v4/letter/s/b2d939/32.png) [@sahere37](https://discuss.elastic.co/u/sahere37)\ **Post date:** [August 12, 2020, 5:02pm UTC](https://discuss.elastic.co/t/using-aggregate-filter-to-count-number-of-events-and-sum-fields-values/244448/12 "2020-08-12T17:02:45Z")
when i used "year" or "month" or "day" as task\_id it work correct and count number of all events (it is noted that value of these three field is same in all events). but when i used "Company\_name" or "service\_name" , the output is not expected. ---
**Author:** ![Badger](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/badger/32/25190_2.png) [@Badger](https://discuss.elastic.co/u/Badger)\ **Post date:** [August 12, 2020, 5:28pm UTC](https://discuss.elastic.co/t/using-aggregate-filter-to-count-number-of-events-and-sum-fields-values/244448/13 "2020-08-12T17:28:33Z")
Field names are case sensitive. Company\_name and company\_name are different fields. Similarly for service\_name. ---
**Author:** ![sahere37](https://avatars.discourse-cdn.com/v4/letter/s/b2d939/32.png) [@sahere37](https://discuss.elastic.co/u/sahere37)\ **Post date:** [August 12, 2020, 5:35pm UTC](https://discuss.elastic.co/t/using-aggregate-filter-to-count-number-of-events-and-sum-fields-values/244448/14 "2020-08-12T17:35:02Z")
No it is my mistake in writing and are correct in script ---
**Author:** ![Badger](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/badger/32/25190_2.png) [@Badger](https://discuss.elastic.co/u/Badger)\ **Post date:** [August 12, 2020, 5:50pm UTC](https://discuss.elastic.co/t/using-aggregate-filter-to-count-number-of-events-and-sum-fields-values/244448/15 "2020-08-12T17:50:10Z")
OK, so what does the filter section look like now? ---
**Author:** ![sahere37](https://avatars.discourse-cdn.com/v4/letter/s/b2d939/32.png) [@sahere37](https://discuss.elastic.co/u/sahere37)\ **Post date:** [August 12, 2020, 5:57pm UTC](https://discuss.elastic.co/t/using-aggregate-filter-to-count-number-of-events-and-sum-fields-values/244448/16 "2020-08-12T17:57:05Z")
my aggregate filter is as following: ``` mutate { add_field => {"aggregate_id" => "%{year}_%{mounth}_%{day}_%{Company_name}_%{Service_name}"} } aggregate { task_id => "%{Company_name}" code => " map['company_count'] ||= 0 map['company_count'] +=1 map['Service_name'] ||= event.get('Service_name') map['Company_name'] ||= event.get('Company_name') map['year'] ||= event.get('year') map['mounth'] ||= event.get('mounth') map['day'] ||= event.get('day') event.cancel() " push_previous_map_as_event => true timeout => 3600 } mutate { add_field => { "My_Data" => "%{Company_name}-%{Service_name}-%{year}-%{mounth}-%{day}-%{company_count}" } ``` which My\_Data is output field writing on a .txt file. ---
**Author:** ![sahere37](https://avatars.discourse-cdn.com/v4/letter/s/b2d939/32.png) [@sahere37](https://discuss.elastic.co/u/sahere37)\ **Post date:** [August 12, 2020, 6:07pm UTC](https://discuss.elastic.co/t/using-aggregate-filter-to-count-number-of-events-and-sum-fields-values/244448/17 "2020-08-12T18:07:27Z")
I have 68 events, which my correct output should be as following: ``` cm1-ser1-2020-08-09-52 cm1-ser2-2020-08-09-4 cm2-ser1-2020-08-09-12 ``` when I used "year" field as task\_id, output is as following which used last event comany\_name and service\_name: ``` cm1-ser1-2020-08-09-68 ``` when I used "aggregate\_id" as task\_id, output is as following: ``` cm1-ser1-2020-08-09-6 cm2-ser1-2020-08-09-4 cm1-ser1-2020-08-09-36 cm2-ser1-2020-08-09-2 cm1-ser1-2020-08-09-2 cm2-ser1-2020-08-09-2 cm1-ser2-2020-08-09-2 cm2-ser1-2020-08-09-2 cm1-ser2-2020-08-09-2 cm2-ser1-2020-08-09-2 cm1-ser1-2020-08-09-8 ``` using "Company\_name" and "Service\_name" as "task\_id" leads to same result like "aggregate\_id" ---
**Author:** ![Badger](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/badger/32/25190_2.png) [@Badger](https://discuss.elastic.co/u/Badger)\ **Post date:** [August 12, 2020, 6:11pm UTC](https://discuss.elastic.co/t/using-aggregate-filter-to-count-number-of-events-and-sum-fields-values/244448/18 "2020-08-12T18:11:33Z")
> [@Badger](#): > > OK, assuming your data is sorted Your data is not sorted, so you need to something like [example 3](https://www.elastic.co/guide/en/logstash/current/plugins-filters-aggregate.html#plugins-filters-aggregate-example3), and use push\_map\_as\_event\_on\_timeout instead of push\_previous\_map\_as\_event. ---
**Author:** ![sahere37](https://avatars.discourse-cdn.com/v4/letter/s/b2d939/32.png) [@sahere37](https://discuss.elastic.co/u/sahere37)\ **Post date:** [August 12, 2020, 6:13pm UTC](https://discuss.elastic.co/t/using-aggregate-filter-to-count-number-of-events-and-sum-fields-values/244448/19 "2020-08-12T18:13:40Z")
my input data is not sorted ---
**Author:** ![sahere37](https://avatars.discourse-cdn.com/v4/letter/s/b2d939/32.png) [@sahere37](https://discuss.elastic.co/u/sahere37)\ **Post date:** [August 12, 2020, 6:25pm UTC](https://discuss.elastic.co/t/using-aggregate-filter-to-count-number-of-events-and-sum-fields-values/244448/20 "2020-08-12T18:25:20Z")
I change aggregate filter as following: ``` aggregate { task_id => "%{aggregate_id}" code => " map['company_count'] ||= 0 map['company_count'] +=1 event.set('company_count', map['company_count']) " push_map_as_event_on_timeout => true timeout => 3600 } ``` and back to first problem 🙂 My output is as following: ``` cm1-ser1-2020-08-09-1 cm1-ser1-2020-08-09-2 cm1-ser1-2020-08-09-3 cm1-ser1-2020-08-09-4 cm1-ser1-2020-08-09-5 cm1-ser1-2020-08-09-6 cm2-ser1-2020-08-09-1 cm2-ser1-2020-08-09-2 cm2-ser1-2020-08-09-3 cm2-ser1-2020-08-09-4 cm1-ser1-2020-08-09-7 cm1-ser1-2020-08-09-8 cm1-ser1-2020-08-09-9 cm1-ser1-2020-08-09-10 cm1-ser1-2020-08-09-11 cm1-ser1-2020-08-09-12 cm1-ser1-2020-08-09-13 cm1-ser1-2020-08-09-14 cm1-ser1-2020-08-09-15 cm1-ser1-2020-08-09-16 cm1-ser1-2020-08-09-17 cm1-ser1-2020-08-09-18 cm1-ser1-2020-08-09-19 cm1-ser1-2020-08-09-20 cm1-ser1-2020-08-09-21 cm1-ser1-2020-08-09-22 cm1-ser1-2020-08-09-23 cm1-ser1-2020-08-09-24 cm1-ser1-2020-08-09-25 cm1-ser1-2020-08-09-26 cm1-ser1-2020-08-09-27 cm1-ser1-2020-08-09-28 cm1-ser1-2020-08-09-29 cm1-ser1-2020-08-09-30 cm1-ser1-2020-08-09-31 cm1-ser1-2020-08-09-32 cm1-ser1-2020-08-09-33 cm1-ser1-2020-08-09-34 cm1-ser1-2020-08-09-35 cm1-ser1-2020-08-09-36 cm1-ser1-2020-08-09-37 cm1-ser1-2020-08-09-38 cm1-ser1-2020-08-09-39 cm1-ser1-2020-08-09-40 cm1-ser1-2020-08-09-41 cm1-ser1-2020-08-09-42 cm2-ser1-2020-08-09-5 cm2-ser1-2020-08-09-6 cm1-ser1-2020-08-09-43 cm1-ser1-2020-08-09-44 cm2-ser1-2020-08-09-7 cm2-ser1-2020-08-09-8 cm1-ser2-2020-08-09-1 cm1-ser2-2020-08-09-2 cm2-ser1-2020-08-09-9 cm2-ser1-2020-08-09-10 cm1-ser2-2020-08-09-3 cm1-ser2-2020-08-09-4 cm2-ser1-2020-08-09-11 cm2-ser1-2020-08-09-12 cm1-ser1-2020-08-09-45 cm1-ser1-2020-08-09-46 cm1-ser1-2020-08-09-47 cm1-ser1-2020-08-09-48 cm1-ser1-2020-08-09-49 cm1-ser1-2020-08-09-50 cm1-ser1-2020-08-09-51 cm1-ser1-2020-08-09-52 ``` [Next page](https://discuss.elastic.co/t/using-aggregate-filter-to-count-number-of-events-and-sum-fields-values/244448.md?page=2)