Identify and remove Duplicate Assets

[Awalker]

Afternoon all, after some assistance in regards to the asset manager side of things.

We use the asset manager and currently do this manually via the spreadsheet templates, it has been noticed that as your only able to perform a validation on certain asset class types e.g. computers but not others e.g. telecoms we have unfortunately added duplicate records into Hornbill. I want to know if anyone has an easy way to identify duplicates (A DD query perhaps) and how best to delete them other than going through them one. 

All help appreciated and would be good for my company if we could have the validation option for all asset types against the asset number.

Regards

Alex

 

[ArmandoDM]

Hi @Awalker,

a query to identify the duplicates is a good approach.

As far the deletion of the duplicated, once you identify the duplicates, the best way is selecting the checkbox for the duplicated in the asset list page and then delete them in one go.

In the screenshot is an example on how you may identify the duplicated with a query. I am assuming that there are assets with the same name in the same class, so with the second query you may get a comma separated list of IDs which correspond to the same asset. 

Hope this helps.

 

Regards,

Armando

 

 

 

[Awalker]

Hi @ArmandoDM,

 

This is really useful many thanks.

[samwoo]

15 hours ago, ArmandoDM said:

Hi @Awalker,

a query to identify the duplicates is a good approach.

As far the deletion of the duplicated, once you identify the duplicates, the best way is selecting the checkbox for the duplicated in the asset list page and then delete them in one go.

In the screenshot is an example on how you may identify the duplicated with a query. I am assuming that there are assets with the same name in the same class, so with the second query you may get a comma separated list of IDs which correspond to the same asset. 

Hope this helps.

 

Regards,

Armando

 

 

 

Hi @ArmandoDM (hope it's ok that I jump in with a query @Awalker?),

Is there a way to pull out the asset's original name, in case it was renamed by someone, to have this shown next to the duplicate assets? If it's the original asset leave it blank, if not then include the original asset tag.

It's been tough but we've identified users who renames asset tags when a new user gets a new laptop. I've been trying to crack down on it and because some assets were delete due to being duplicates... i'm actually worried that these assets were renamed from old ones... and now the old ones are not in Hornbill any more.

I also +1 to the option to be able to validate against an asset tag.

Thanks,

Samuel

[Awalker]

Thank you to both, @samwoo & @ArmandoDM many thanks for the help here.

 

I have taken both and used the below query to bring back a count of asset tag numbers if more than 1 entry: -

SELECT h_asset_tag, count(*) FROM h_cmdb_assets group by h_asset_tag having count(*) > 1

 

 

[ArmandoDM]

@Awalker, @samwoo,

the only way to pull the original name out of an asset is through a query on the h_sys_audit_trial table, which tracks the changes done to an asset. I will do my best to write a query that may help you

 

Regards,

 

Armando

 

[ArmandoDM]

Hi @samwoo,

as an example I wrote a query for you which tells which assets have been renamed (and then cancelled):

select t.h_id, a.h_pk_asset_id, a.h_class, a.h_name, t.h_action_type,t.h_old_value, t.h_new_value from h_sys_audit_trail t left join h_cmdb_assets a on a.h_pk_asset_id = t.h_id where t.h_table = 'h_cmdb_assets' and t.h_column = 'h_name' and t.h_action_type = 'Update' order by a.h_class, t.h_action_type;

I attached a screenshot with a use case.

Hope this helps

Regards,

 

Armando

 

[ArmandoDM]

Hi @samwoo,

 

if you need to know which Asset Tag has been renamed, you may just replace  t.h_column = 'h_name' with t.h_column = 'h_asset_tag' , and you will have the list of assets whose tag was renamed.

 

Regards,

 

Armando