Notification

Icon
Error

Assets without Asset Location

Posted: Wednesday, December 4, 2019 3:48:01 PM(UTC)
JLangthaler

JLangthaler

Member Original PosterPosts: 2
1
Like
This issue has been solved! Click here to view the solution
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
#1RobiA Member Posts: 1  
posted: 12/5/2019 11:19:36 AM(UTC)
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
#2JLangthaler Member Original PosterPosts: 2  
posted: 12/5/2019 12:44:19 PM(UTC)
Hi,

that did it, thanks a lot!

Originally Posted by: RobiA Go to Quoted Post
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


Active Discussions

Lansweeper Lansweeper Webserver Error
by  Bruce.B   Go to last post Go to first unread
Last post: Today at 3:19:01 AM(UTC)
Lansweeper LsAgent install issue
by  Susan.A  
Go to last post Go to first unread
Last post: Yesterday at 8:46:19 PM(UTC)
Lansweeper Is a Common Table Expression possible?
by  PatrickWolf   Go to last post Go to first unread
Last post: Yesterday at 6:52:24 PM(UTC)
Lansweeper Tracking assets like docking station, mouse and keyboards
by  cycleheat  
Go to last post Go to first unread
Last post: Yesterday at 5:00:39 PM(UTC)
Lansweeper Update to V. 7.2.107.4
by  cycleheat   Go to last post Go to first unread
Last post: Yesterday at 4:57:38 PM(UTC)
Lansweeper Windows Defender AV
by  Rob B  
Go to last post Go to first unread
Last post: Yesterday at 4:55:19 PM(UTC)
Lansweeper Scan Server Scanning wrong IP range
by  wayneRex   Go to last post Go to first unread
Last post: Yesterday at 3:49:36 PM(UTC)
Lansweeper Lansweeper support levels
by  RobertB  
Go to last post Go to first unread
Last post: 1/23/2020 10:39:05 PM(UTC)