nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

How to search all JSM Assets using AQL and export to Excel?

LGU

Hello,

I'm trying to figure out how to search assets using some query and exporting it to Excel.

It seems like there is no such option using GUI in Cloud version. Only thing I found is that I can run AQL on specific asset object types, but I need to run query on all of them like in case of looking for all assets that are in country/location X & Y.

It seems that only option is to use API.

Using the object API : https://developer.atlassian.com/cloud/assets/rest/api-group-object/#api-object-aql-post I can get the results I want, but what I get is in very non-friendly format. It's objects in objects with attributes.

I tried to export the result in JSON from powershell (where I run the API call) and then import it to Excel, but it needs many column expansions and in the end it's a mess and on top of that the attributes that I certainly do need in the final excel are linked to assets on multiple lines so I end up having LAPTOP1 in 10 rows, each row having 1 attribute for that asset... someone can probably do some magic and transform/clean this up, but.... there has to be some simple way to do a basic query like: "Location = Prague" and get all the assets (laptops, workstations, switches, etc..) that have their location set to Prague.

Any tips on this would be greatly appreciated, I'm sure I'm just missing something and the answer is not "you have to be power query wizard to get this in Excel in proper format of objects and corresponding attributes"

Accepted answers

Mikael Sandberg

If you go into Assets and the object schema that holds your objects. From here you can use filter to get the objects you are looking for and then use the Bulk actions to export the filtered list to a csv file.

Screenshot 2024-11-01 at 9.50.43 AM.png

All comments

Mikael Sandberg

Screenshot 2024-11-01 at 9.50.43 AM.png

LGU

Once again thank you for the quick reply @Mikael Sandberg , but using the AQL this way does not search thru all the object types (not even thru the child object types), it's only searching the selected object type. Unless I'm missing something.

Here's the structure we have:

Also we do not have any objects in the Assets category/object type, so the AQL search is not even available there, so I tried creating one dummy asset/object and then searching for Laptops and it doesn't work.

Mikael Sandberg

Okay, so you have not set up your structure so the parent object type is an abstract object type. In that case you are correct, you would not be able to search on the top level and get objects from the child object types.

If you had configured you Assets object type as an abstract object type it would looks something like this and in that case you would be able to search across all child object types.

Screenshot 2024-11-01 at 10.47.04 AM.png

In your case the only way would be to use an app that can generate a report from Assets like EazyBI or via Analytics if you are on an enterprise plan. The other option would to go into each object type and search/export from there.

LGU

Oh I see, and there's probably no simple way to fix this right? To transform the structure we have to the "correct" abstract object type one?

Welcome!

It looks like you're new here. Sign in or register to get started.

Quick Links

Find more posts tagged with

jira

cloud

jira-service-management-cloud

jira-service-management

assets

aql