cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 

DB Query to gather duplicate assets without linked inventory not from the Web Ui but the DB directly

Hello  customer wants  a DB Query to gather duplicate assets without linked inventory not from the Web UI but the DB directly, I did a Query to the dbo.Asset view but the linked inventory field is not in that view, can someone share the tables I need to join to get the linked inventory field?

(2) Replies

In the Compliance Database, the link is actually from an Inventory Device to the Asset, not the other way around.

In the dbo.ComplianceComputer view, there is an AssetID column that links the Inventory Device to a Hardware Asset.

Therefore, if you want to find Hardware Assets that are not linked to Inventory, you need to find rows in the dbo.Asset view where the AssetID does not exist in the dbo.ComplianceComputer view.  

I hope this helps.

 

mfranz
By Level 17 Champion
Level 17 Champion

Hi,

Please keep in mind that FNMS will usually create a Dummy Inventory (ComplianceComputerStatusID = 4) when a computer asset is created without inventory. This is assuming you identify duplicates from the ShortDescription:

USE FNMSCompliance

SELECT
	a.ShortDescription
	,cc.ComputerName
	,cc.ComplianceComputerStatusID
FROM Asset a
LEFT JOIN ComplianceComputer cc
	ON a.AssetID = cc.AssetID
WHERE a.ShortDescription IN (
	SELECT a.ShortDescription
	FROM Asset a
	GROUP BY a.ShortDescription
	HAVING COUNT(*) > 1
	)

This should give you all asset name duplicates, linked computers and their status.

Best regards,

Markward