cancel
Showing results for 
Search instead for 
Did you mean: 
adrian_ritz
Active participant

Asset link to inventory with no serial number

Jump to solution

Hi Community,

I have the following issue.

FNMS 2018 R3 on prem implementation

Asset import from SNOW, via mid servers

The problem is that the quality of data in SNOW are very bad, I have a lot of assets with no serial number, the serial number is blank, or have the value UNKNOWN or do not match the one from inventory.

I'm searching for a solution to link the asset with inventory only after hostname if it's possible, or at least to reduce the number of the unlinked inventory.

Thank you for your hints and I wish you all a nice day.

0 Kudos
1 Solution

Accepted Solutions
Highlighted
mfranz
Analyst

Re: Asset link to inventory with no serial number

Jump to solution

Hi Adrian,

There are mutiple options.

  • You can use a Business Import to link inventories and assets. Here is a little sample SQL statement.
SELECT
	cc.ComputerName Inventory
	,a.ShortDescription Asset
FROM ComplianceComputer cc
JOIN Asset a
	ON cc.AssetID IS NULL
		AND cc.ComputerName = a.ShortDescription
  • Or you could update the database itself. Example:
UPDATE cc
SET cc.AssetID = a.AssetID
FROM ComplianceComputer cc
JOIN Asset a
	ON cc.AssetID IS NULL
		AND cc.ComputerName = a.ShortDescription

Poor data quality, especially duplicates in device names and asset names, can lead to problems. So please be cautious with any of these options. You should review the SQL results before actually changing anything. If possible, test them in a non-production environment first.

Both code examples do exclude inventories which are already linked to an asset.

Best regards,

Markward

 

View solution in original post

2 Replies
Highlighted
mfranz
Analyst

Re: Asset link to inventory with no serial number

Jump to solution

Hi Adrian,

There are mutiple options.

  • You can use a Business Import to link inventories and assets. Here is a little sample SQL statement.
SELECT
	cc.ComputerName Inventory
	,a.ShortDescription Asset
FROM ComplianceComputer cc
JOIN Asset a
	ON cc.AssetID IS NULL
		AND cc.ComputerName = a.ShortDescription
  • Or you could update the database itself. Example:
UPDATE cc
SET cc.AssetID = a.AssetID
FROM ComplianceComputer cc
JOIN Asset a
	ON cc.AssetID IS NULL
		AND cc.ComputerName = a.ShortDescription

Poor data quality, especially duplicates in device names and asset names, can lead to problems. So please be cautious with any of these options. You should review the SQL results before actually changing anything. If possible, test them in a non-production environment first.

Both code examples do exclude inventories which are already linked to an asset.

Best regards,

Markward

 

View solution in original post

adrian_ritz
Active participant

Re: Asset link to inventory with no serial number

Jump to solution
Thank you for your support, I will try to see what can I match, may be enhance the sql command with additional function to be more granular and step by step
0 Kudos