Asset Relationship and Harddrive Information Report

[GoHawks5]

Trying to automate our surplus system by using Lansweeper information, however not all of our devices surplus will have been on network to be scanned by Lansweeper before being sent to surplus. For each pallet we have a unique Asset name created manually as a custom "Shipment Pallet" asset type. We require to track the harddrive serial number, size and model when available, but when I add the harddrive fields to the report, I lose any manually entered assets that LS hasn't scanned.

Can anyone help me figure out how to display valid HDD information as well as the devices which have none? Here is what I have?

I apologize in advanced if its not pretty, I'm new to SQL.



Select Top 1000000 tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssetRelations.StartDate As [DOD Date],
tblAssets.AssetName As [Tag Number],
tblState.Statename,
tblAssets_1.AssetName As [Pallet ID],
Cast(Cast(tblDiskdrives.Size As bigint) / 1024 / 1024 / 1024 As numeric)
As [HDD Size (GB)],
tblDiskdrives.Volumeserialnumber As [HDD Serial],
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
tblAssets.Lastseen
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tblAssetRelations
On tblAssets.AssetID = tblAssetRelations.ChildAssetID
Inner Join tblAssets tblAssets_1 On tblAssetRelations.ParentAssetID =
tblAssets_1.AssetID
Inner Join tblState On tblState.State = tblAssetCustom.State
Inner Join tsysAssetRelationTypes On tblAssetRelations.Type =
tsysAssetRelationTypes.RelationTypeID
Inner Join tblDiskdrives On tblAssets.AssetID = tblDiskdrives.AssetID
Where tblAssetRelations.Type = 202

[GoHawks5]

Thank you! Worked perfect!

[Susan_A · Accepted solution]

In the visual representation of the report in the upper section of the report builder, right-click on the link between tblAssets and tblDiskdrives and tick "select all rows from tblAssets". By default, SQL will only display a line in the report output if there's a record in both connected tables. This is standard SQL behavior, not specific to Lansweeper. As tblDiskdrives only stores records for scanned (Windows) assets, using a standard join to connect this table to tblAssets will remove anything that isn't Windows from the report.