Using JQL to Query AQL

[Nick Jones]

Background

We work in education and have an environment with multiple JSM projects (40+), covering around 5,000 staff members as customers.  Each staff member has a supervisor.  Normal team size is around 10-20 people which can change based on where they are located.

We have an asset database that is updated daily from our employee management system so that changes are automated. All of that is working great and as expected.

Problem

I want to be able to utilize this asset field in JQL.  Specifically, I want to be able to search for all tickets assigned to subordinates of a supervisor.

Using JQL I can return the results if I hard code the names/emails.  What I would like to be able to do is use the aqlFunction to return the result set back.

assignee in aqlFunction("Person.Supervisor = \"theSuperVisorsEmail@example.org\"")

Is this possible?

[Mikael Sandberg · Accepted solution]

Hi @Nick Jones,

Welcome to Atlassian Community!

The aqlFunction() can only be used on Asset object custom fields and that is why you get an error when trying to use it with assignee. One way around this would be to have an asset object field that is set when the assignee is set so that you can search on the object.

[Nick Jones]

Hi @Mikael Sandberg we were thinking the same thing.  That on creation or change of assignee to have it populate an asset field with automation.  Thanks for the quick response.

Sad that the functionality isn't part of the product. 

[Bryan Spangler]

First a warning I am using Data center and it appears in your screen shots that you are using cloud. 

We have a similar asset setup with a supervisor attribute for our employee schema and a jira ticket that is created when a device is Checked out to the user. I have created a script using scriptrunner HAPI (i assume it would not take took much work to adapt it if you dont have scriptrunner) that returns all the usernames of the provided manager. I have also been working on a custom JQL function to use this in our queues and customer portal. I am VERY new to groovy and scriptrunner so the JQL query is slow going as I do not have the fundamental understanding of groovy that it seems to require. I'm not sure of your level of comfortability with groovy and/or scriptrunner but would be happy to share those scripts if you are interested. Just let me know an I will add them to my next comment. I would welcome any input on getting to JQL function to work as well.