Basic Inventory Report needs help

[davidc]

I have a basic inventory report that somehow is ignoring non-Windows devices. I would like it to show all devices (like the built-in location inventory reports), and simply ignore missing columns.

Here is my report. Thanks in advance for any assistance.

SELECT Top 1000000 	tblAssets.AssetID,
	tblAssets.AssetName AS Name,
	tsysAssetTypes.AssetTypeIcon10 AS icon,
	CASE tblAssets.LocationID
	WHEN '1' THEN 'Location1'
	WHEN '2' THEN 'Location2'
	WHEN '3' THEN 'Location3'
	ELSE 'Uknown'
	END AS Location,
	tsysAssetTypes.AssetTypename AS Type,
	tblAssets.IPAddress AS [IP Addr],
	tblAssetCustom.Manufacturer + ' ' + tblAssetCustom.Model AS [Make / Model],
	tblAssets.Processor AS CPU,
	CAST(ROUND(CAST(tblAssets.Memory AS FLOAT)/1024, 0) AS NVARCHAR) + ' GB' AS Memory,
	CASE tblComputerSystem.SystemType 
	WHEN 'x64-based PC' THEN '64-bit'
	WHEN 'X86-based PC' THEN '32-bit'
	END	AS Arch,
	CONVERT (nvarchar(10), tblAssets.FirstSeen, 120) AS [First Seen]
FROM tblAssets
	INNER JOIN tblComputersystem ON tblAssets.AssetID = tblComputersystem.AssetID
	INNER JOIN tblAssetCustom ON tblAssets.AssetID = tblAssetCustom.AssetID
	INNER JOIN tsysAssetTypes ON tsysAssetTypes.AssetType = tblAssets.Assettype
WHERE tblAssetCustom.State = 1
ORDER BY Location, Type, Name

[Tom_P · Accepted solution]

As mentioned by RC62N, the table 'tblComputerSystem' only stores Windows data. Changing the 'INNER JOIN' to 'LEFT OUTER JOIN' adds the non-windows based assets to the result list.

SELECT Top 1000000  tblAssets.AssetID,
    tblAssets.AssetName AS Name,
    tsysAssetTypes.AssetTypeIcon10 AS icon,
    CASE tblAssets.LocationID
    WHEN '1' THEN 'Location1'
    WHEN '2' THEN 'Location2'
    WHEN '3' THEN 'Location3'
    ELSE 'Uknown'
    END AS Location,
    tsysAssetTypes.AssetTypename AS Type,
    tblAssets.IPAddress AS [IP Addr],
    tblAssetCustom.Manufacturer + ' ' + tblAssetCustom.Model AS [Make / Model],
    tblAssets.Processor AS CPU,
    CAST(ROUND(CAST(tblAssets.Memory AS FLOAT)/1024, 0) AS NVARCHAR) + ' GB' AS Memory,
    CASE tblComputerSystem.SystemType 
    WHEN 'x64-based PC' THEN '64-bit'
    WHEN 'X86-based PC' THEN '32-bit'
    END AS Arch,
    CONVERT (nvarchar(10), tblAssets.FirstSeen, 120) AS [First Seen]
FROM tblAssets
    INNER JOIN tblAssetCustom ON tblAssets.AssetID = tblAssetCustom.AssetID
    INNER JOIN tsysAssetTypes ON tsysAssetTypes.AssetType = tblAssets.Assettype
	LEFT OUTER JOIN tblComputersystem ON tblAssets.AssetID = tblComputersystem.AssetID
WHERE tblAssetCustom.State = 1
ORDER BY Location, Type, Name

[RCorbeil]

I'll bet tblComputerSystem and/or tblAssetCustom contains data about only your Windows systems. By using the default INNER JOIN against those tables, you're effectively telling the query to filter out any non-Windows assets. Try changing the INNER joins to LEFT joins and see whether that fixes things.