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"

[Mikael Sandberg · Accepted solution]

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.

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

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?

[Mikael Sandberg]

No, unfortunately you cannot move objects between different object types/schemes at the moment, so once you started with a specific structure you cannot change it without starting over. You can vote and keep an eye on this suggestion, JSDCLOUD-10000, since this can be done on DC.  

[LGU]

@Mikael Sandberg ok, thank you very much, this was very helpful explanation!

[Janis Plume _eazyBI_]

Hi,

Such reporting comes strightforward from eazyBI app for Jira. I have the Assets level "abstract" object type with common attributes for all asset types

eazyBI offers out-of-the-box filtering of assets by the Owner:

Kindly,

Janis, eazyBI support