How to query the attribute of an asset object in a JQL filter query

[Alexander Ziegltrum]

Hello,

I need a filter, that lists all issues, where the status is not "done" and where the value of an attribute of a specific asset object is "closed".

The asset object is called "billingaccount", the attribute is called "Status" and one possible value is "closed".

This does not work:

"Status" in iqlFunction ('billingaccount = closed')

Any help is highly appreciated on how to write the JQL query.

Thanks a lot.

Cheers, Alex

[Evgenii]

Hi, @Alexander Ziegltrum 

You can use query like:

status != Done and customfied_12345 in aqlFunction ('Name = "billingaccount" AND Status = closed')

Here is more information in manual:

https://support.atlassian.com/jira-service-management-cloud/docs/assets-jql-functions/

It's important, that you need to use in JQL query field, in which is stored, in issue, assets object. In my example it's customfied_12345, replace it with yours.

Here is my request, to find all issues, where is assets object with atribute Active = true

"Employee Manager" in aqlFunction("objectType=Employees and Active = true")

[Alexander Ziegltrum]

Hey @Evgenii 

thanks a lot for your reply! I got it running basically, but not with the intended results

Queries like that are really tricky. 

I do get results for:

"Billing Account" in aqlFunction ('Status = Offen')

I do not get results for:

 "Billing Account" in aqlFunction ('Status != Offen')

or 

"Billing Account" in aqlFunction ('Status is not Offen') 

My assumption is, that it is related to the customfield "Billing Account", because its Asset object field configuration is like this:

Object schema: ABC

Filter scope (AQL): objectTypeId = 999 AND "Status" = "Offen"

Filter issue scope (AQL): Firma = ${customfield_12345}

Allow search filtering by these attributes: Title, Time

Object attributes to display on issue view: Title, and 9 other attribute(s)

Field can store multiple objects: No

Display a default object when this field appears in a customer portal: No

In the issues of our project, we only want Billing Accounts to be selected, which are in status "Offen".

With the JQL on the other hand, I want to identify issues, that have asset object "Billing Account" with status "Closed".

Is customfield configuration interfering with the JQL now?

[Sean Brewington]

Hi @Alexander Ziegltrum ,

I know this is an old question.

But one thing you may want to try is move the 'Status' clause to the issue scope.

i.e.

Filter scope (AQL): objectTypeId = 999

Filter issue scope (AQL): Firma = ${customfield_12345} AND "Status" = "Offen"

I have found this will still work the same when selecting assets on issues.  But when you write and run a JQL query you can more easily write it with a larger scope to see more issues. The JQL autocomplete will also have the larger scope of the "Filter scope" clause. That may help with the issue you were facing.

And/or this query might also work:

"Billing Account" NOT IN aqlFunction ('Status = Offen')

[Alexander Ziegltrum]

Hey @Sean Brewington 

thanks for picking up this older thread. I just tried the latter query and it seems to work just fine! We still have to prove the reliability of the results yet, but it seem to be very good.

Thanks again :)