How to solve duplicated assets against CIs?

[Luiz Lucena]

Hello everyone, 

We currently don't have Hardware Asset Mgmt, we only have Asset Management (com.snc.asset_management). 
Few months ago, we imported some computers into the Asset table (alm_hardware) without knowing that one system property should be enabled to sync these assets into the Workstations table (cmdb_ci_computer), the system property is glide.asset.create_ci_with_ire, see this as a reference (https://www.servicenow.com/community/sam-articles/assets-and-cis-understanding-the-difference/tac-p/... ).

After manually import these computers, regional IT across our offices started to roll out these computers to users. We use Microsoft Intune for deployment and we have some scheduled jobs importing those computer information from Intune into ServiceNow, like, who is the user assigned to, warranty information through Dell API, etc. 

When these computers were imported into the Workstation table, it created a new record in Asset (alm_hardware) table as well, instead of updating the original record with the same serial number. 

Now, we have almost 1000 records in the Asset table marked as In Stock, when they actually were already deployed to users. 

Is there a quick way to query and get rid of those duplicated ones?

For example, see the picture below, the duplicated serial number and the original record having the CI field in blank.

Any idea will be very welcome.

[Mohammad Amin]

Hi @Luiz Lucena 

I noted a couple of points from your post to build a query. you said you guys already assigned those assets to users but you have records with the same serial in the " In Stock " state.

see below the code, hopefully, it will help you or give you an idea.

Note: please try this in your Dev instance before applying in PROD

var correctAssetGr = new GlideRecord("alm_hardware");
correctAssetGr.addEncodedQuery("install_status=1"); // Sate = in use
correctAssetGr.query();
while(correctAssetGr.next()) {
   var duplicateAssetGR = new GlideRecord("alm_hardware");
duplicateAssetGR.addEncodedQuery("install_status=6^serial_number="+correctAssetGr.serial_number); // querying assets in "In Stock" state with same Serial of an Authentic/ Correct record. 
duplicateAssetGR.query();
while (duplicateAssetGR.next()) {
   duplicateAssetGR.deleteRecord(); // delete  duplicate records
}

}

Please mark it as correct/helpful if it helped you.
Regards,

Mohammad Amin

[Muralidharan BS · Accepted solution]

HI, @Luiz Lucena Check for the field configuration item in alm_hardware, if the field is empty it means you have created it through import and it was not created by auto sync. You can filter those records and validate and delete those, 

[Luiz Lucena]

Hi Murali, 
Yes, that is what we done. 
Thanks,

[ersureshbe]

Hi, 

Can you check created and created fields? It will differentiate the real and duplicate data.

The workstation is called the cmdb_ci_computer table. You are aware there is a connection between alm_hardware and cmdb_ci_computer table as per OOB.