→ 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: 
JLangthaler
Engaged Sweeper
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
1 ACCEPTED SOLUTION
RobiA
Engaged Sweeper
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

View solution in original post

2 REPLIES 2
RobiA
Engaged Sweeper
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
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