{"action":"created","issue":{"url":"https://api.github.com/repos/logstash-plugins/logstash-filter-aggregate/issues/50","repository_url":"https://api.github.com/repos/logstash-plugins/logstash-filter-aggregate","labels_url":"https://api.github.com/repos/logstash-plugins/logstash-filter-aggregate/issues/50/labels{/name}","comments_url":"https://api.github.com/repos/logstash-plugins/logstash-filter-aggregate/issues/50/comments","events_url":"https://api.github.com/repos/logstash-plugins/logstash-filter-aggregate/issues/50/events","html_url":"https://github.com/logstash-plugins/logstash-filter-aggregate/issues/50","id":193913386,"number":50,"title":"Last event does not aggregate properly","user":{"login":"iti888","id":24396452,"avatar_url":"https://avatars.githubusercontent.com/u/24396452?v=3","gravatar_id":"","url":"https://api.github.com/users/iti888","html_url":"https://github.com/iti888","followers_url":"https://api.github.com/users/iti888/followers","following_url":"https://api.github.com/users/iti888/following{/other_user}","gists_url":"https://api.github.com/users/iti888/gists{/gist_id}","starred_url":"https://api.github.com/users/iti888/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/iti888/subscriptions","organizations_url":"https://api.github.com/users/iti888/orgs","repos_url":"https://api.github.com/users/iti888/repos","events_url":"https://api.github.com/users/iti888/events{/privacy}","received_events_url":"https://api.github.com/users/iti888/received_events","type":"User","site_admin":false},"labels":[{"id":286668390,"url":"https://api.github.com/repos/logstash-plugins/logstash-filter-aggregate/labels/discuss","name":"discuss","color":"cc317c","default":false}],"state":"open","locked":false,"assignee":null,"assignees":[],"milestone":null,"comments":30,"created_at":"2016-12-06T23:15:04Z","updated_at":"2016-12-28T22:52:00Z","closed_at":null,"body":"- Version: ES 5.02 with elasticsearch-jdbc-2.3.4.1, openjdk version \"1.8.0_111\"\r\n- Operating System: ubuntu16.04.1, Oracle 12c on linux\r\n- Config File (if you have sensitive info, please remove it):\r\ninput {\r\n    jdbc {\r\n        # Oracle jdbc connection string to Oracle database\r\n        jdbc_connection_string => \"jdbc:oracle:thin:@//host:port/sid\"\r\n        # The user we wish to execute our statement as\r\n        jdbc_user => \"xxx\"\r\n        jdbc_password=> \"xxx\"\r\n        # The path to our downloaded jdbc driver\r\n        jdbc_driver_library => \"PATH/ojdbc7.jar\"\r\n        # The name of the driver class for Oracle\r\n        jdbc_driver_class => \"Java::oracle.jdbc.driver.OracleDriver\"\r\n        # our query\r\n        statement => \"select t1.id,t1.name,t1.description, t2.date_of_birth from t1 inner join t2 on t1.ID=t2.T1_ID\"\r\n    }\r\n}\r\nfilter {\r\n aggregate {\r\n             task_id => \"%{id}\"\r\n             code => \"\r\n                map['id'] ||= event.get('id')\r\n\t\tmap['name'] ||= event.get('name')\r\n\t\tmap['description'] ||= event.get('description')\r\n\t\t\r\n                map['date_of_birth'] ||= []\r\n                map['date_of_birth'] <<= event.get('date_of_birth')\r\n             \"\r\n             push_previous_map_as_event => true\r\n             timeout => 5\r\n\t}\r\n}\r\noutput {\r\n    elasticsearch {\r\n        index => \"test\"\r\n        document_type => \"aggregate\"\r\n        document_id => \"%{id}\"\r\n        hosts => \"host\"\r\n\t}\r\n}\r\n\r\n- Sample Data:\r\nDROP TABLE t1;\r\n\r\nCREATE TABLE t1 (id NUMBER (10) NOT NULL, name VARCHAR2 (30 BYTE) NOT NULL, description VARCHAR2 (200 BYTE));\r\n\r\nALTER TABLE t1 ADD (\r\n  CONSTRAINT t1_pk\r\n  PRIMARY KEY\r\n  (id)\r\n  ENABLE VALIDATE);\r\nCOMMIT;\r\n\r\nINSERT INTO t1\r\nVALUES (1, 'name1', '1st name');\r\n\r\nINSERT INTO t1\r\nVALUES (2, 'name2', '2nd name');\r\n\r\nINSERT INTO t1\r\nVALUES (3, 'name3', '3rd name');\r\n\r\nCOMMIT;\r\n\r\ndrop table t2;\r\nCREATE TABLE t2 (id NUMBER (10) NOT NULL, t1_id NUMBER (10) NOT NULL, date_of_birth DATE NOT NULL);\r\n\r\n\r\nALTER TABLE t2 ADD (\r\n  CONSTRAINT t2_pk\r\n  PRIMARY KEY\r\n  (id)\r\n  ENABLE VALIDATE);\r\nCOMMIT;\r\n\r\nINSERT INTO t2\r\nVALUES (1, 1, to_date('1/1/2000','mm/dd/yyyy'));\r\nINSERT INTO t2\r\nVALUES (2, 1, to_date('2/1/2000','mm/dd/yyyy'));\r\nINSERT INTO t2\r\nVALUES (3, 1, to_date('3/1/2000','mm/dd/yyyy'));\r\nINSERT INTO t2\r\nVALUES (4, 2, to_date('4/1/2000','mm/dd/yyyy'));\r\nINSERT INTO t2\r\nVALUES (5, 3, to_date('5/1/2000','mm/dd/yyyy'));\r\nINSERT INTO t2\r\nVALUES (6, 3, to_date('6/1/2000','mm/dd/yyyy'));\r\ncommit;\r\n- Steps to Reproduce:\r\nCreate the table in Oracle 12C as script provide above\r\nCreate the config file as provided above as oracle-out_test.conf\r\nrun:\r\nsudo ./bin/logstash -f oracle-out_test.conf --path.settings=/etc/logstash\r\n\r\nResults are as follows:\r\ncurl -XGET localhost:9200/test/_search?pretty\r\n{\r\n  \"took\" : 3,\r\n  \"timed_out\" : false,\r\n  \"_shards\" : {\r\n    \"total\" : 5,\r\n    \"successful\" : 5,\r\n    \"failed\" : 0\r\n  },\r\n  \"hits\" : {\r\n    \"total\" : 3,\r\n    \"max_score\" : 1.0,\r\n    \"hits\" : [\r\n      {\r\n        \"_index\" : \"test\",\r\n        \"_type\" : \"aggregate\",\r\n        \"_id\" : \"2\",\r\n        \"_score\" : 1.0,\r\n        \"_source\" : {\r\n          \"@timestamp\" : \"2016-12-06T22:54:35.107Z\",\r\n          \"date_of_birth\" : [\r\n            \"2000-04-01T08:00:00.000Z\"\r\n          ],\r\n          \"name\" : \"name2\",\r\n          \"@version\" : \"1\",\r\n          \"description\" : \"2nd name\",\r\n          \"id\" : 2,\r\n          \"tags\" : [ ]\r\n        }\r\n      },\r\n      {\r\n        \"_index\" : \"test\",\r\n        \"_type\" : \"aggregate\",\r\n        \"_id\" : \"1\",\r\n        \"_score\" : 1.0,\r\n        \"_source\" : {\r\n          \"@timestamp\" : \"2016-12-06T22:54:35.107Z\",\r\n          \"date_of_birth\" : [\r\n            \"2000-01-01T08:00:00.000Z\",\r\n            \"2000-02-01T08:00:00.000Z\",\r\n            \"2000-03-01T08:00:00.000Z\"\r\n          ],\r\n          \"name\" : \"name1\",\r\n          \"@version\" : \"1\",\r\n          \"description\" : \"1st name\",\r\n          \"id\" : 1,\r\n          \"tags\" : [ ]\r\n        }\r\n      },\r\n      {\r\n        \"_index\" : \"test\",\r\n        \"_type\" : \"aggregate\",\r\n        \"_id\" : \"3\",\r\n        \"_score\" : 1.0,\r\n        \"_source\" : {\r\n          \"@timestamp\" : \"2016-12-06T22:54:34.993Z\",\r\n          \"date_of_birth\" : \"2000-06-01T07:00:00.000Z\",\r\n          \"name\" : \"name3\",\r\n          \"@version\" : \"1\",\r\n          \"description\" : \"3rd name\",\r\n          \"id\" : 3,\r\n          \"tags\" : [ ]\r\n        }\r\n      }\r\n    ]\r\n  }\r\n}\r\n\r\nDid you see that the last doc date_of_birth is:           \"date_of_birth\" : \"2000-06-01T07:00:00.000Z\",\r\nwhile the first doc is: \"date_of_birth\" : [\r\n            \"2000-04-01T08:00:00.000Z\"\r\n          ],\r\n\r\nLast doc does not aggregate.\r\n\r\nAlso, why is the mapping like this:\r\ncurl -XGET localhost:9200/test/_mapping?pretty\r\n{\r\n  \"test\" : {\r\n    \"mappings\" : {\r\n      \"aggregate\" : {\r\n        \"properties\" : {\r\n          \"@timestamp\" : {\r\n            \"type\" : \"date\"\r\n          },\r\n          \"@version\" : {\r\n            \"type\" : \"text\",\r\n            \"fields\" : {\r\n              \"keyword\" : {\r\n                \"type\" : \"keyword\",\r\n                \"ignore_above\" : 256\r\n              }\r\n            }\r\n          },\r\n          \"date_of_birth\" : {\r\n            \"type\" : \"date\"\r\n          },\r\n          \"description\" : {\r\n            \"type\" : \"text\",\r\n            \"fields\" : {\r\n              \"keyword\" : {\r\n                \"type\" : \"keyword\",\r\n                \"ignore_above\" : 256\r\n              }\r\n            }\r\n          },\r\n          \"id\" : {\r\n            \"type\" : \"long\"\r\n          },\r\n          \"name\" : {\r\n            \"type\" : \"text\",\r\n            \"fields\" : {\r\n              \"keyword\" : {\r\n                \"type\" : \"keyword\",\r\n                \"ignore_above\" : 256\r\n              }\r\n            }\r\n          }\r\n        }\r\n      }\r\n    }\r\n  }\r\n}\r\n\r\nI would expect to have \"type\":\"nested\" somewhere. Did I do anything wrong?\r\n\r\nThanks"},"comment":{"url":"https://api.github.com/repos/logstash-plugins/logstash-filter-aggregate/issues/comments/269554193","html_url":"https://github.com/logstash-plugins/logstash-filter-aggregate/issues/50#issuecomment-269554193","issue_url":"https://api.github.com/repos/logstash-plugins/logstash-filter-aggregate/issues/50","id":269554193,"user":{"login":"fbaligand","id":3750300,"avatar_url":"https://avatars.githubusercontent.com/u/3750300?v=3","gravatar_id":"","url":"https://api.github.com/users/fbaligand","html_url":"https://github.com/fbaligand","followers_url":"https://api.github.com/users/fbaligand/followers","following_url":"https://api.github.com/users/fbaligand/following{/other_user}","gists_url":"https://api.github.com/users/fbaligand/gists{/gist_id}","starred_url":"https://api.github.com/users/fbaligand/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/fbaligand/subscriptions","organizations_url":"https://api.github.com/users/fbaligand/orgs","repos_url":"https://api.github.com/users/fbaligand/repos","events_url":"https://api.github.com/users/fbaligand/events{/privacy}","received_events_url":"https://api.github.com/users/fbaligand/received_events","type":"User","site_admin":false},"created_at":"2016-12-28T22:52:00Z","updated_at":"2016-12-28T22:52:00Z","body":"I just tested @iti888 use case described in this issue (with tables t1 and t2).\r\n\r\nSo :\r\n- The good news is that I reproduce your problem : the last 'aggregated' event (id=3) is not produced.\r\n- But the explanation is quite simple : when you don't set a jdbc input `schedule` option, as soon as all generated events are processed, Logstash is stopped. So aggregate plugin's timeout mechanism doesn't have time to happen.\r\n- If you set tcp input plugin, then logstash is not stopped after all result rows are processed, so aggregate timeout happens, and the \"aggregated\" event with id=3 is produced.\r\n\r\nCompared to your initial comment, things I have changed in my test configuration :\r\n- add `-w 1 ` flag at logstash startup\r\n- add ` order by t1.id` at end of jdbc input `statement` option\r\n- replace `map['date_of_birth'] <<= event.get('date_of_birth')` by `map['date_of_birth'] << event.get('date_of_birth')` \r\n- set `stdout` as output plugin (instead of elasticsearch)\r\n"}}
{"actor":{"display_login":"fbaligand"}}