Find "Duplicate" Assets using AQL
プラットフォームについて: Cloud のみ - この記事は クラウド プラットフォームのアトラシアン製品に適用されます。
要約
Sometimes, when importing Objects into Assets, it can create "duplicate" Objects, which can create a lot more records in your Assets instance, within the Object Schema/Object Type. This could be due to the Objects truly having duplicate records or small unique changes made within the new import that differentiate the new records from the old ones. But how do you go about deleting these "duplicate" records?
ソリューション
You can run AQL queries using either keywords or the created date for the more recently created objects.
For Keywords, you could use LIKE or NOT LIKE:
From AQL documentation:
| いいね！ | クエリ内の入力のサブセットと値を照合します。大文字と小文字は区別されません。 | objecttype=Employees and Office like Stock Returns all objects of Employees type which have an Office attribute value that contains the characters 'Stock' or 'STOCK' |
| not like | Excludes values that match with any subset of input in the query. | objecttype=Employees and Office not like Stock Returns all objects of Employees type which have an Office attribute value that DO NOT contain the characters 'Stock' |
More information on this topic can be found here:
- Use Assets Query Language (AQL) - https://support.atlassian.com/ja/jira-service-management-cloud/docs/use-assets-query-language-aql/
An example query would be similar to the following where it only returns objects that contain the name Google in them:
objecttype = "Android Phones" AND "Name" LIKE GoogleThis would allow you to search for objects with the same Name attribute or any other attribute. This would help identify which objects are the "same" based on objects with the same value.
For Created date, you would use Created in an AQL statement:
You will need to know the Created date of the last import, or the import that created the duplicates. To find this information, please:
- Select the Object Schema related to the Import
- Click on Object Schema in the top right of the UI, which will bring up a drop down
- Click the Configure button that has the cog wheel icon
- Select the Import tab
- Click the View History button
- You will now see the Start and End Times for the Imports history. Use that End Time (or the Start Time) for the Created value in your AQL query.
An example query would be similar to:
"Created" = "Oct 21, 2022, 8:03 AM"
You can use both AQL query examples above to identify the specific records that have the same name, and then note those for reference when using the Created AQL query. That is so that you truly know which Objects have the same name as an existing object but were part of a new import based on a new Created date.
More options for identifying duplicate objects are to:
- anyAttribute lets you search against all Attributes using a LIKE. For example, in a situation where it's IP Address based, odds are that only 1 object should ever have this value in an attribute. That said, if there are some objects that do, they're usually a separate object type, so combining it with object type can resolve that issue specifically.
- Use References, where the same type of object is referenced on an Attribute that can hold multiple references but has 2 that have the same attribute. This functionality is the same as checking for base duplicates, however you are doing it from the perspective of other objects instead of the ones that may be duplicates.
- Visually locate the duplicated Object(s)
- Identify the Duplicate from the Original (the object id of the original will be lower than the duplicate, and reflected in the numeric part of the object Key, e.g. KEY-123 = original while KEY-234 is a duplicate as objectId: 123<234)
Please note that some objects may have been created legitimately and ARE NOT Duplicates, hence you should carefully revise the filtered objects, before deleting any.