Asset link to inventory with no serial number

adrian_ritz asked a question.

May 22, 2019 at 1:31 PM

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.

FlexNet Manager Suite On-Premises

Top Rated Answers

mfranz
7 years ago
Hi Adrian,
There are mutiple options.
You can use a Business Import to link inventories and assets. Here is a little sample SQL statement.
SELECTcc.ComputerName Inventory,a.ShortDescription AssetFROM ComplianceComputer ccJOIN Asset aON cc.AssetID IS NULLAND cc.ComputerName = a.ShortDescription
Or you could update the database itself. Example:
UPDATE ccSET cc.AssetID = a.AssetIDFROM ComplianceComputer ccJOIN Asset aON cc.AssetID IS NULLAND 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

Expand Post
Selected as Best

All Answers

mfranz
7 years ago
Hi Adrian,
There are mutiple options.
You can use a Business Import to link inventories and assets. Here is a little sample SQL statement.
SELECTcc.ComputerName Inventory,a.ShortDescription AssetFROM ComplianceComputer ccJOIN Asset aON cc.AssetID IS NULLAND cc.ComputerName = a.ShortDescription
Or you could update the database itself. Example:
UPDATE ccSET cc.AssetID = a.AssetIDFROM ComplianceComputer ccJOIN Asset aON cc.AssetID IS NULLAND 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

Expand Post
Selected as Best
- adrian_ritz
  7 years ago
  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
  Expand Post
- ChrisG (Flexera Software)
  4 years ago
  Just to add a note about the idea of using an "UPDATE ComplianceComputer [...]" SQL statement as suggested above to link inventory device records to assets:
  If you do this, you would also need to clean up existing records in the ComplianceComputer view that may be linked to Asset records: either delete them (if they have a status of "awaiting inventory"), or unlink them (otherwise). If that is not done then you would likely end up with multiple ComplianceComputer records having the same AssetID, which may have unexpected consequences.
  Expand Post