Create
cancel
Showing results for 
Search instead for 
Did you mean: 
Sign up Log in

It's not the same without you

Join the community to find out what other Atlassian users are discussing, debating and creating.

Atlassian Community Hero Image Collage

equivalent sql for Advanced searching - functions reference

Dear support,

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

and also

 

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.

regards,

Xavier

 

1 answer

1 accepted

0 votes
Answer accepted

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)

Dear Nic,

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 ?

 

regards,
Xavier

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.

Suggest an answer

Log in or Sign up to answer
TAGS
Community showcase
Published in Jira Service Desk

The Complete Guide to Atlassian for ITSM

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...

2,151 views 15 22
Read article

Community Events

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

Events near you