Elasticsearch raport problem

adam.lipski_rhenus · January 25, 2024, 11:22am

Infos:

Used Zammad version: 6.2.0-1706169043.76bdc8b0.jammy
Used Zammad installation type: package
Operating system: Ubuntu 22.04.3 LTS
Browser + version: all

i create raport in raport profiles and when i try run in report i see this error

status code 500

Unable to process GET request to elasticsearch URL 'http://localhost:9200/zammad_production_ticket/_search'. Check the response and payload for detailed information:

Response:
#<UserAgent::Result:0x00007fb1b9bee570 @success=false, @body="{\"error\":{\"root_cause\":[{\"type\":\"parsing_exception\",\"reason\":\"[terms] query does not support [organization_id]\",\"line\":1,\"col\":232}],\"type\":\"x_content_parse_exception\",\"reason\":\"[1:232] [bool] failed to parse field [must]\",\"caused_by\":{\"type\":\"x_content_parse_exception\",\"reason\":\"[1:232] [bool] failed to parse field [must]\",\"caused_by\":{\"type\":\"x_content_parse_exception\",\"reason\":\"[1:232] [bool] failed to parse field [must]\",\"caused_by\":{\"type\":\"parsing_exception\",\"reason\":\"[terms] query does not support [organization_id]\",\"line\":1,\"col\":232}}}},\"status\":400}", @data=nil, @code="400", @content_type=nil, @error="Client Error: #<Net::HTTPBadRequest 400 Bad Request readbody=true>!", @header={"x-elastic-product"=>"Elasticsearch", "warning"=>"299 Elasticsearch-7.17.17-aba4da413a368e296dfc64fb20897334d0340aa1 \"Elasticsearch built-in security features are not enabled. Without authentication, your cluster could be accessible to anyone. See https://www.elastic.co/guide/en/elasticsearch/reference/7.17/security-minimal-setup.html to enable security.\"", "content-type"=>"application/json; charset=UTF-8", "content-length"=>"565"}>

Payload:
{"size":0,"query":{"bool":{"must":[{"range":{"created_at":{"from":"2023-12-31T23:00:00Z","to":"2024-12-31T22:59:59Z"}}},{"bool":{"must":[{"bool":{"must":[{"terms":{"state_id":["2"]}}]}},{"bool":{"must":[{"terms":{"organization_id":2}}]}},{"bool":{"must_not":[{"term":{"state.name.keyword":"merged"}}]}}]}}]}},"aggs":{"time_buckets":{"date_histogram":{"field":"created_at","calendar_interval":"month","time_zone":"Europe/Warsaw"}}},"sort":[{"updated_at":{"order":"desc"}},"_score"]}

Payload size: 0M

adam.lipski_rhenus · January 25, 2024, 11:37am

but this qery work normal when i edit him
and this problem exist only in case when i use organization of the current user

MrGeneration · May 16, 2024, 2:29pm

I’m afraid you’re encountering a bug here. Using specific organizations does work.
I did create a bug report for you already:

github.com/zammad/zammad

Report-Profile non-functional with organization is current users organization

opened 02:28PM - 16 May 24 UTC

MrGeneration

needs verification

### Used Zammad Version 6.3.1 ### Environment - Installation method: 6.3.x.70…297fbb6.saas - Operating system (if you're unsure: `cat /etc/os-release` ): any - Database + version: any - Elasticsearch version: any - Browser + version: verified on Vivaldi 6.7.3329.29 - This issue has been found by our community: https://community.zammad.org/t/elasticsearch-raport-problem/13216 ### Actual behaviour You cannot create a dynamic report profile that looks for the users (that looks at the reporting) organization. It is working fine to provide a specific organization though. ### Expected behaviour As Zammad provides me the option, I am able to have dynamic reportings based on my current organization depending on who looks at the same report. Response: ``` Unable to process GET request to elasticsearch URL 'http://127.0.0.1:9200/zp_00011060_production_ticket/_search'. Check the response and payload for detailed information: Response: #<UserAgent::Result:0x00007f9cc8f93c08 @success=false, @body="{\"error\":{\"root_cause\":[{\"type\":\"parsing_exception\",\"reason\":\"[terms] query does not support [organization_id]\",\"line\":1,\"col\":183}],\"type\":\"x_content_parse_exception\",\"reason\":\"[1:183] [bool] failed to parse field [must]\",\"caused_by\":{\"type\":\"x_content_parse_exception\",\"reason\":\"[1:183] [bool] failed to parse field [must]\",\"caused_by\":{\"type\":\"x_content_parse_exception\",\"reason\":\"[1:183] [bool] failed to parse field [must]\",\"caused_by\":{\"type\":\"parsing_exception\",\"reason\":\"[terms] query does not support [organization_id]\",\"line\":1,\"col\":183}}}},\"status\":400}", @data=nil, @code="400", @content_type=nil, @error="Client Error: #<Net::HTTPBadRequest 400 Bad Request readbody=true>!", @header={"x-elastic-product"=>"Elasticsearch", "content-type"=>"application/json; charset=UTF-8", "content-length"=>"565"}> Payload: {"size":0,"query":{"bool":{"must":[{"range":{"created_at":{"from":"2024-01-01T00:00:00Z","to":"2024-12-31T23:59:59Z"}}},{"bool":{"must":[{"bool":{"must":[{"terms":{"organization_id":2}}]}},{"bool":{"must_not":[{"term":{"state.name.keyword":"merged"}}]}}]}}]}},"aggs":{"time_buckets":{"date_histogram":{"field":"created_at","calendar_interval":"month","time_zone":"UTC"}}},"sort":[{"updated_at":{"order":"desc"}},"_score"]} Payload size: 0M ``` ### Steps to reproduce the behaviour * be a user that is part of an organization * create a report profile with just "ticket.organization is current users organization", save * go to reporting and try to use the profile. ### Support Ticket _No response_ ### I'm sure this is a bug and no feature request or a general question. yes

Just to be safe and just in case:
Should you plan to provide a report profile to your customers, be advised that this is strongly not encouraged as customers could gain access to others data.