how we can queries database to get ticket with approvals or pending approach, as explained on Advanced searching options.
My needs is to provide some simple report with equivalent of JQL:
project = "IT Service Desk" AND issuetype in ("xxx") AND Approvals = myPending() ORDER BY created DESC
project = "IT Service Desk" AND issuetype in ("xxx") AND Approvals = approvals() ORDER BY created DESC (or myApprovals)
as dashboard is not available for customer users (those users have an account in jira, but are not member of service desk application..)
I would like to "rewrite" those JQL by querying sql database.
is it possible ?
Thanks in advance.
It is possible, but it is painful, and can easily cause performance issues.
Jira's database is absolutely not built or intended for any direct reporting. The JQL you've given us as an example requires 8 joins, just for a single issue type, and that's probably an underestimate as I've only looked at the basics of the function you've used.
I would very strongly recommend using a reporting tool that understands Jira data, or better, the REST API (which can run the JQL you want to "translate" without hitting performance any more than a dashboard would)
thanks for your quick answer. I understand using database is not real recommended (but it really help me if you can provide such SQL query, by PM if possible).
But how I can retrieve the result of JQL via REST on Confluence ? Is it possible ? And what about connected "customer" users that didn't have access to Jira, only Jira SD ? are they able to see the Confluence page where REST get data ?
Anything can run JQL - see https://developer.atlassian.com/server/jira/platform/jira-rest-api-examples/#searching-for-issues-examples - you can almost paste your JQL into there for a working call.
To get it into Confluence, you will need to find something that can make the call, and more importantly, format it usefully. You will need to look for something that can obscure what you are doing as well, as it will need to connect as a Jira user who can see all the returned issues, and you don't want your customers seeing the details of that.
Hi Atlassian Community! This is Teresa from the Atlassian team. My colleague Paul Buffington @Buff and I are excited to share a brand new ITSM resource we’ve created – "The Complete Guide to At...
Connect with like-minded Atlassian users at free events near you!Find an event
Connect with like-minded Atlassian users at free events near you!
Unfortunately there are no Community Events near you at the moment.Host an event
You're one step closer to meeting fellow Atlassian users at your local event. Learn more about Community Events