Assets without Asset Location

[JLangthaler]

I am currently trying to create a report which should find all 'Printer' Assets, which aren't mapped to any asset location. The report underneath finds all printers, which already have a location set, though I can't seem to find the right criteria for when the asset location is undefined.

Select Top 1000000 tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.AssetName As Asset,
tblAssets.Domain,
tsysAssetTypes.AssetTypename As AssetType,
tblAssets.Username,
tblAssets.Userdomain,
tblAssets.IPAddress,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
tblAssetCustom.Serialnumber,
tblAssets.Firstseen,
tblAssets.Lastseen,
tblAssets.Lasttried,
tsysAssetRelationTypes.Name As Relation,
tblAssets1.AssetID,
tblAssets1.AssetName,
tblAssets1.Description
From tblAssets
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tblAssetRelations On tblAssetRelations.ChildAssetID =
tblAssets.AssetID
Inner Join tsysAssetRelationTypes On tsysAssetRelationTypes.RelationTypeID =
tblAssetRelations.Type
Inner Join tblAssets tblAssets1 On
tblAssets1.AssetID = tblAssetRelations.ParentAssetID
Inner Join tblAssetCustom On tblAssetCustom.AssetID = tblAssets.AssetID
Where tsysAssetTypes.AssetTypename = 'Printer' And tsysAssetRelationTypes.Name =
'is located in'
Order By tblAssets1.AssetName,
tblAssets.Domain,
Asset

[RobiA · Accepted solution]

try this:

SELECT TOP 1000000 assType.AssetTypeIcon10 As icon,
ass.AssetName As Asset,
ass.Domain,
assType.AssetTypename As AssetType,
ass.IPAddress,
assCust.Manufacturer,
assCust.Model,
assCust.Serialnumber,
ass.Firstseen,
ass.Lastseen,
ass.Lasttried,
COALESCE(assRelDetails.Relation,'-') AS [Relation],
COALESCE(assRelDetails.ParentAssetName,'-') AS [ParentAssetName],
COALESCE(assRelDetails.ParentDescr,'-') AS [.ParentDescr]

FROM tblAssets as ass
INNER JOIN tsysAssetTypes as assType On assType.AssetType = ass.Assettype
INNER JOIN tblAssetCustom as assCust ON assCust.AssetID = ass.AssetID
LEFT JOIN tblAssetRelations as assRel ON assRel.ChildAssetID = ass.AssetID
LEFT JOIN (SELECT assRel.RelationID,

assRelTyp.Name as [Relation],
assParent.AssetName as [ParentAssetName] ,
assParent.Description as [ParentDescr]
FROM tblAssetRelations as assRel
LEFT JOIN tsysAssetRelationTypes as assRelTyp ON assRelTyp.RelationTypeID = assRel.Type
LEFT JOIN tblAssets as assParent ON assParent.AssetID = assRel.ParentAssetID ) as assRelDetails ON
assRelDetails.RelationID = assRel.RelationID

WHERE assType.AssetTypename = 'Printer' AND ( assRel.Type is Null OR assRelDetails.Relation not like 'is located in')

1. I did it with "LEFT JOIN"s
2. and the embeded SELECT for more details about existing relations

...a Lansweeper fan

[JLangthaler]

Hi,

that did it, thanks a lot!

RobiA wrote:
try this:

SELECT TOP 1000000 assType.AssetTypeIcon10 As icon,
ass.AssetName As Asset,
ass.Domain,
assType.AssetTypename As AssetType,
ass.IPAddress,
assCust.Manufacturer,
assCust.Model,
assCust.Serialnumber,
ass.Firstseen,
ass.Lastseen,
ass.Lasttried,
COALESCE(assRelDetails.Relation,'-') AS [Relation],
COALESCE(assRelDetails.ParentAssetName,'-') AS [ParentAssetName],
COALESCE(assRelDetails.ParentDescr,'-') AS [.ParentDescr]

FROM tblAssets as ass
INNER JOIN tsysAssetTypes as assType On assType.AssetType = ass.Assettype
INNER JOIN tblAssetCustom as assCust ON assCust.AssetID = ass.AssetID
LEFT JOIN tblAssetRelations as assRel ON assRel.ChildAssetID = ass.AssetID
LEFT JOIN (SELECT assRel.RelationID,

assRelTyp.Name as [Relation],
assParent.AssetName as [ParentAssetName] ,
assParent.Description as [ParentDescr]
FROM tblAssetRelations as assRel
LEFT JOIN tsysAssetRelationTypes as assRelTyp ON assRelTyp.RelationTypeID = assRel.Type
LEFT JOIN tblAssets as assParent ON assParent.AssetID = assRel.ParentAssetID ) as assRelDetails ON
assRelDetails.RelationID = assRel.RelationID

WHERE assType.AssetTypename = 'Printer' AND ( assRel.Type is Null OR assRelDetails.Relation not like 'is located in')

1. I did it with "LEFT JOIN"s
2. and the embeded SELECT for more details about existing relations

...a Lansweeper fan