→ Having trouble accessing our new support portal or creating a ticket? Please notify our team here

cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
davidc
Engaged Sweeper
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
1 ACCEPTED SOLUTION
Tom_P
Lansweeper Employee
Lansweeper Employee
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

View solution in original post

2 REPLIES 2
Tom_P
Lansweeper Employee
Lansweeper Employee
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
Honored Sweeper II
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.