select * from jira_backlog_issue

jeanpauldevooght commented 2 years ago

Describe the bug Whenever I run a query against ira_backlog_issue, I get the error message Warning: executeQueries: query 1 of 1 failed: ERROR: rpc error: code = Unknown desc = request failed. Please analyze the request body for more details. Status code: 400 (SQLSTATE HV000) I cannot find any information from the underlying go-jira lib request object despite setting STEAMPIPE_LOG_LEVEL to trace. The equivalent curl command works from the CLI curl --request GET --user email:token --url "https://domain.atlassian.net/rest/agile/1.0/board/70/backlog?startAt=140&maxResults=1&expand=names" --header "Accept: application/json"

Steampipe version (steampipe -v) v0.15.1

Plugin version (steampipe plugin list) v0.5.0

To reproduce I had some help from the Slack group but I don't see why the default queries would fail for me. I have noticed 400 or 403 status codes with the following tables

jira_backlog_issue: 400
jira_advanced_setting: 403
jira_group: 403
jira_project_role: 403
jira_sprint: 400
jira_workflow: 403

Expected behavior I would expect to run the default queries against my Atlassian domain using my username and token.

Additional context The underying library's message about checking the request body is frustrating if there is nothing to check

misraved commented 2 years ago

@jeanpauldevooght Sorry to hear that you are running into issues with the jira plugin.

I was able to run the above query without running into any errors.

Could you please share how many backlog issues are you trying to run the query against?
Has the plugin been configured correctly as per https://hub.steampipe.io/plugins/turbot/jira#configuration
Are you only getting this error for the default queries? Are you able to run other queries mentioned in the table docs - https://hub.steampipe.io/plugins/turbot/jira/tables/jira_backlog_issue

cbruno10 commented 2 years ago

@jeanpauldevooght From our Slack discussion, I recall you were having issues around compiling the Jira plugin locally from the add-debug-backlog-issues branch. Were you ever able to successfully build that plugin version and run against it?

Also, can you please run the following queries and share the results here:

select * from jira_backlog_issue limit 1
select * from jira_backlog_issue where board_id = 70

Slack thread with additional context: https://steampipe.slack.com/archives/C01UECB59A7/p1656408548963219

jeanpauldevooght commented 2 years ago

@misraved

I saw 141 issues and with that curl command, I pull the 141th one
Plugin must be configured right as I can query all other tables. I run this on Windows via a Docker images which maps the jira.spc file stored on the host. I checked I see the right data from within the container
I am not succeeding with any of these or other queries

jeanpauldevooght commented 2 years ago

@cbruno10 I was unfortunately not able to build that version and had also tried a local jira (also via Docker) but dropped that as there are perhaps differences in the project data which cause these issues.

Running any query against that table or the other tables I listed returns an error. For jira_backlog_issue, it's always 400

Thanks both - I was also wondering if my Jira rights are not sufficient but I am not able to spot this through their Web UI. What else could there be? The project and boards themselves?

misraved commented 2 years ago

@jeanpauldevooght I believe the issue could be with the permissions. I found this link online which might be helpful in figuring out the permissions - https://support.atlassian.com/jira-cloud-administration/docs/manage-project-permissions/

Alternatively, you could have used the following query -

select
  id,
  name,
  actor_id,
  actor.display_name,
  actor.account_type,
  actor.active as actor_status
from
  jira_project_role as role,
  jsonb_array_elements_text(actor_account_ids) as actor_id,
  jira_user as actor
where
  actor_id = actor.account_id;

but I realised that you are getting a 403 error for the table (https://hub.steampipe.io/plugins/turbot/jira/tables/jira_project_role#table-jira_project_role).

jeanpauldevooght commented 2 years ago

Thank you @misraved - I have tried to find the possible roles with curl --request GET --url "https://domain.atlassian.net/rest/api/3/role" but that requires an ADMIN privilege. Is there a permission requirement I can somehow formulate for using all tables or is this specific to my company's set up on Atlassian cloud you think?

misraved commented 2 years ago

Thanks @jeanpauldevooght for the input. I looked into different articles pertaining to Jira permission model and I think it could be specific to the company's set up on Atlassian cloud.

Relevant thread - https://community.atlassian.com/t5/Jira-Software-questions/How-can-I-give-access-to-a-project-board-backlog-to-an-external/qaq-p/1193944

jeanpauldevooght commented 2 years ago

Thank you @misraved - I looked a the corresponding Board Settings section and see a 'Saved Filter', a locked project under 'Shares', a 'Filter Query' What puzzles me though is that I can successfully pull data via curl, so why not via the plugin? I use "https://domain.atlassian.net/rest/agile/1.0/board/70/backlog?startAt=140&maxResults=1&expand=names" which is configured in my jira.spc which works for other tables...

misraved commented 2 years ago

Thanks @jeanpauldevooght , that is definitely confusing. The results should not vary since the same token is being used in both the cases (curl command and steampipe query). We don't tamper with the permission layer essentially so that seems out of question.

I will try to dig more around this issue and provide you with an update shortly.

jeanpauldevooght commented 2 years ago

I finally managed to run Cody's modified plugin from branch add-debug-backlog-issues I used a Docker Buster go-1.18.4 container where I built steampipe 0.16.0-rc.2 and the plugin. Then I set my original jira.spc configuration and ran a simple "select * from jira_backlog_issues" which failed. The output below is the result.

buster@a2e700be6b10:~/.steampipe/logs$ cat plugin-2022-07-25.log
2022-07-25 16:42:05.197 UTC [WARN]  steampipe-plugin-jira.plugin: [WARN]  1658767324118: jira_backlog_issue.listBacklogIssues: res_body=[123, 34, 101, 114, 114, 111, 114, 77, 101, 115, 115, 97, 103, 101, 115, 34, 58, 91, 34, 66, 97, 99, 107, 108, 111, 103, 115, 32, 97, 114, 101, 32, 110, 111, 116, 32, 115, 117, 112, 112, 111, 114, 116, 101, 100, 32, 111, 110, 32, 116, 104, 105, 115, 32, 98, 111, 97, 114, 100, 34, 93, 44, 34, 101, 114, 114, 111, 114, 115, 34, 58, 123, 125, 125]
2022-07-25 16:42:05.197 UTC [ERROR] steampipe-plugin-jira.plugin: [ERROR] 1658767324118: jira_backlog_issue.listBacklogIssues: api_error="request failed. Please analyze the request body for more details. Status code: 400"
2022-07-25 16:42:05.197 UTC [ERROR] steampipe-plugin-jira.plugin: [ERROR] 1658767324118: streamRows error chan select: request failed. Please analyze the request body for more details. Status code: 400
2022-07-25 16:42:05.197 UTC [WARN]  steampipe-plugin-jira.plugin: [WARN]  1658767324118: Execute call failed - cancelling pending item in cache
2022-07-25 16:42:05.198 UTC [WARN]  steampipe-plugin-jira.plugin: [WARN]  1658767324118: jira_backlog_issue.listBacklogIssues: res_body=[123, 34, 101, 114, 114, 111, 114, 77, 101, 115, 115, 97, 103, 101, 115, 34, 58, 91, 34, 66, 97, 99, 107, 108, 111, 103, 115, 32, 97, 114, 101, 32, 110, 111, 116, 32, 115, 117, 112, 112, 111, 114, 116, 101, 100, 32, 111, 110, 32, 116, 104, 105, 115, 32, 98, 111, 97, 114, 100, 34, 93, 44, 34, 101, 114, 114, 111, 114, 115, 34, 58, 123, 125, 125]
2022-07-25 16:42:05.198 UTC [ERROR] steampipe-plugin-jira.plugin: [ERROR] 1658767324118: jira_backlog_issue.listBacklogIssues: api_error="request failed. Please analyze the request body for more details. Status code: 400"
2022-07-25 16:42:05.198 UTC [ERROR] steampipe-plugin-jira.plugin: [ERROR] 1658767324118: error chan select: request failed. Please analyze the request body for more details. Status code: 400
2022-07-25 16:42:05.208 UTC [WARN]  steampipe-plugin-jira.plugin: [WARN]  1658767324118: jira_backlog_issue.listBacklogIssues: res_body=[123, 34, 101, 114, 114, 111, 114, 77, 101, 115, 115, 97, 103, 101, 115, 34, 58, 91, 34, 66, 97, 99, 107, 108, 111, 103, 115, 32, 97, 114, 101, 32, 110, 111, 116, 32, 115, 117, 112, 112, 111, 114, 116, 101, 100, 32, 111, 110, 32, 116, 104, 105, 115, 32, 98, 111, 97, 114, 100, 34, 93, 44, 34, 101, 114, 114, 111, 114, 115, 34, 58, 123, 125, 125]
2022-07-25 16:42:05.208 UTC [ERROR] steampipe-plugin-jira.plugin: [ERROR] 1658767324118: jira_backlog_issue.listBacklogIssues: api_error="request failed. Please analyze the request body for more details. Status code: 400"
2022-07-25 16:42:05.211 UTC [WARN]  steampipe-plugin-jira.plugin: [WARN]  1658767324118: jira_backlog_issue.listBacklogIssues: res_body=[123, 34, 101, 114, 114, 111, 114, 77, 101, 115, 115, 97, 103, 101, 115, 34, 58, 91, 34, 66, 97, 99, 107, 108, 111, 103, 115, 32, 97, 114, 101, 32, 110, 111, 116, 32, 115, 117, 112, 112, 111, 114, 116, 101, 100, 32, 111, 110, 32, 116, 104, 105, 115, 32, 98, 111, 97, 114, 100, 34, 93, 44, 34, 101, 114, 114, 111, 114, 115, 34, 58, 123, 125, 125]
2022-07-25 16:42:05.211 UTC [ERROR] steampipe-plugin-jira.plugin: [ERROR] 1658767324118: jira_backlog_issue.listBacklogIssues: api_error="request failed. Please analyze the request body for more details. Status code: 400"
2022-07-25 16:42:05.232 UTC [WARN]  steampipe-plugin-jira.plugin: [WARN]  1658767324118: jira_backlog_issue.listBacklogIssues: res_body=[123, 34, 101, 114, 114, 111, 114, 77, 101, 115, 115, 97, 103, 101, 115, 34, 58, 91, 34, 66, 97, 99, 107, 108, 111, 103, 115, 32, 97, 114, 101, 32, 110, 111, 116, 32, 115, 117, 112, 112, 111, 114, 116, 101, 100, 32, 111, 110, 32, 116, 104, 105, 115, 32, 98, 111, 97, 114, 100, 34, 93, 44, 34, 101, 114, 114, 111, 114, 115, 34, 58, 123, 125, 125]
2022-07-25 16:42:05.232 UTC [ERROR] steampipe-plugin-jira.plugin: [ERROR] 1658767324118: jira_backlog_issue.listBacklogIssues: api_error="request failed. Please analyze the request body for more details. Status code: 400"

cbruno10 commented 2 years ago

@jeanpauldevooght Sorry about the bad log message format, I've fixed that in a commit I just pushed to the add-debug-backlog-issues branch. Can you please try again? Thanks!

jeanpauldevooght commented 2 years ago

Thank you Cory! I re-ran the query and obtain the following output which definitely helps. Would be really great to get this type of message back. Jira is somewhat confusing - the response body above seems to correspond to the return of the curl command but the output below indicates something different which shows how poorly I understand Jira... what do you see?

2022-07-26 07:46:09.180 UTC [WARN]  steampipe-plugin-jira.plugin: [WARN]  1658821568328: jira_backlog_issue.listBacklogIssues: res_body="{"errorMessages":["Backlogs are not supported on this board"],"errors":{}}"
2022-07-26 07:46:09.181 UTC [ERROR] steampipe-plugin-jira.plugin: [ERROR] 1658821568328: jira_backlog_issue.listBacklogIssues: api_error="request failed. Please analyze the request body for more details. Status code: 400"
2022-07-26 07:46:09.181 UTC [ERROR] steampipe-plugin-jira.plugin: [ERROR] 1658821568328: streamRows error chan select: request failed. Please analyze the request body for more details. Status code: 400
2022-07-26 07:46:09.181 UTC [WARN]  steampipe-plugin-jira.plugin: [WARN]  1658821568328: Execute call failed - cancelling pending item in cache
2022-07-26 07:46:09.185 UTC [WARN]  steampipe-plugin-jira.plugin: [WARN]  1658821568328: jira_backlog_issue.listBacklogIssues: res_body="{"errorMessages":["Backlogs are not supported on this board"],"errors":{}}"
2022-07-26 07:46:09.185 UTC [ERROR] steampipe-plugin-jira.plugin: [ERROR] 1658821568328: jira_backlog_issue.listBacklogIssues: api_error="request failed. Please analyze the request body for more details. Status code: 400"
2022-07-26 07:46:09.185 UTC [ERROR] steampipe-plugin-jira.plugin: [ERROR] 1658821568328: error chan select: request failed. Please analyze the request body for more details. Status code: 400
2022-07-26 07:46:09.188 UTC [WARN]  steampipe-plugin-jira.plugin: [WARN]  1658821568328: jira_backlog_issue.listBacklogIssues: res_body="{"errorMessages":["Backlogs are not supported on this board"],"errors":{}}"
2022-07-26 07:46:09.188 UTC [ERROR] steampipe-plugin-jira.plugin: [ERROR] 1658821568328: jira_backlog_issue.listBacklogIssues: api_error="request failed. Please analyze the request body for more details. Status code: 400"
2022-07-26 07:46:09.202 UTC [WARN]  steampipe-plugin-jira.plugin: [WARN]  1658821568328: jira_backlog_issue.listBacklogIssues: res_body="{"errorMessages":["Backlogs are not supported on this board"],"errors":{}}"
2022-07-26 07:46:09.202 UTC [ERROR] steampipe-plugin-jira.plugin: [ERROR] 1658821568328: jira_backlog_issue.listBacklogIssues: api_error="request failed. Please analyze the request body for more details. Status code: 400"
2022-07-26 07:46:09.230 UTC [WARN]  steampipe-plugin-jira.plugin: [WARN]  1658821568328: jira_backlog_issue.listBacklogIssues: res_body="{"errorMessages":["Backlogs are not supported on this board"],"errors":{}}"
2022-07-26 07:46:09.230 UTC [ERROR] steampipe-plugin-jira.plugin: [ERROR] 1658821568328: jira_backlog_issue.listBacklogIssues: api_error="request failed. Please analyze the request body for more details. Status code: 400"

jeanpauldevooght commented 2 years ago

Just to add that there are two boards related to this project as per select * from jira_board which includes board_ids 70 and 72 and are of type scrm and kanban respectively. The table jira_board has a total of 6 rows. The kanban board is configured with a backlog containing one item as per config via browser. When I run the curl command against board ID 72 (kanban) I get {"errorMessages":["Backlogs are not supported on this board"],"errors":{}} and when I run against the scrum type board ID 70, I get from curl a number of records back. When I run the select * from jira_backlog_issues where board_id=70 I get an output as in my previous post.

jdevoo commented 2 years ago

Just wanted to close saying this issue can be fixed for both jira_backlog_issue and jira_sprint by moving / expanding the following statement after client.Do instead of client.NewRequest

if isNotFoundError(err) || strings.Contains(err.Error(), "400") {
  return nil, nil
}

I arrived at this by focusing on the two problematic select * from and comparing with jira_epic which would not return a 400 error code. Without this test, the plugin processes a collection of boards most of which lead to a 400 error and never returned records for those boards that were OK.

I am not sure how to fix the TODO in table_jira.sprint about not getting board id details. I had noticed that queries with where board_id would not help me skip the undesirable boards.

cbruno10 commented 1 year ago

@jeanpauldevooght Thank you very much for testing the fix and identifying some areas where we had inconsistent error handling! I've just pushed https://github.com/turbot/steampipe-plugin-jira/commit/6c907ec65461c61defde4dc33168b7f255dd7f57, which cleans up some inconsistencies in error handling in all tables, and I've moved around some of the error handling code that was in the incorrect places, like you had identified, in several tables.

turbot / steampipe-plugin-jira

select * from jira_backlog_issue #61