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

[krystel_campos]

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?

[kclausen]

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]

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