→ 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: 
oocio
Engaged Sweeper
I'm trying to develop a report that is like the "All Assets" under the Asset link, but I just want to display the Asset Name, Type, OS, Model and IP address.

If I use this query I get all asset types:

Select tblComputers.Computername, tblComputers.ComputerUnique,
tblComputers.Domain, RTrim(tblOperatingsystem.Caption + ' ' +
tblOperatingsystem.OtherTypeDescription) as OS, tblOperatingsystem.OSType
From tblComputers Inner Join
tblOperatingsystem On tblComputers.Computername =
tblOperatingsystem.Computername



But if I try to add the OS to the query like this, I only get Windows Asset Types:

Select Top 1000000 tblAssets.IPAddress,
tblAssets.AssetID,
tblAssets.AssetName,
tsysAssetTypes.AssetTypename As [Asset Type],
tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssetCustom.Model,
tsysOS.OSname
From tblAssets
Inner Join tsysAssetTypes On tblAssets.Assettype = tsysAssetTypes.AssetType
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
Where tblAssetCustom.State = 1
Order By tblAssets.IPAddress,
tblAssets.AssetName Desc



How can I get the report to show all asset types and leave the OS blank if it's not in tsysOS like it does on the All Assets screen?
1 ACCEPTED SOLUTION
Susan_A
Lansweeper Alumni
Right-click on the link between tsysOS and tblAssets and tick "select all rows from tblAssets". If you join two tables with an Inner Join as you are doing now, a result will only be displayed in the report output if a record exists for both sides of the table relation. This is standard SQL behavior, not specific to Lansweeper.

View solution in original post

2 REPLIES 2
oocio
Engaged Sweeper
I knew this was question was due to my ignorance and not a shortcoming of Lansweeper.

This worked.

Thanks for your help.

Susan_A
Lansweeper Alumni
Right-click on the link between tsysOS and tblAssets and tick "select all rows from tblAssets". If you join two tables with an Inner Join as you are doing now, a result will only be displayed in the report output if a record exists for both sides of the table relation. This is standard SQL behavior, not specific to Lansweeper.