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 Count of each browser installed
by  AllSeeingEye   Go to last post Go to first unread
Last post: Yesterday at 2:44:48 PM(UTC)
Lansweeper Custom Reports Email Question
by  muffintopman  
Go to last post Go to first unread
Last post: Yesterday at 1:57:58 PM(UTC)
Lansweeper Windows Update Setting Report
by  Brandon   Go to last post Go to first unread
Last post: 8/11/2020 5:11:45 PM(UTC)
Lansweeper Customized Printer Toner Report
by  Cripple.Zero  
Go to last post Go to first unread
Last post: 8/10/2020 9:08:00 PM(UTC)
Lansweeper Pages printed in the last 30 days
by  RC62N   Go to last post Go to first unread
Last post: 8/10/2020 5:18:05 PM(UTC)
Lansweeper Report for a percentage of calls closed within SLA
by  mouaad  
Go to last post Go to first unread
Last post: 8/10/2020 3:45:58 PM(UTC)
Lansweeper Report for tickets closed per agent last week
by  MLeman   Go to last post Go to first unread
Last post: 8/10/2020 9:55:39 AM(UTC)
Lansweeper Software List by Computer/Server Name
by  nnewton  
Go to last post Go to first unread
Last post: 8/7/2020 6:56:47 AM(UTC)