Awalker Posted June 13, 2017 Posted June 13, 2017 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 Posted June 13, 2017 Posted June 13, 2017 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 Posted June 14, 2017 Author Posted June 14, 2017 Hi @ArmandoDM, This is really useful many thanks.
samwoo Posted June 14, 2017 Posted June 14, 2017 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 Posted June 14, 2017 Author Posted June 14, 2017 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 Posted June 14, 2017 Posted June 14, 2017 @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 Posted June 14, 2017 Posted June 14, 2017 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 1
ArmandoDM Posted June 14, 2017 Posted June 14, 2017 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
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now