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 snmp trap HP 1910 switch
by  info  
Go to last post Go to first unread
Last post: Today at 11:52:17 AM(UTC)
Lansweeper Lsagent cloud relay changes the scanserver value
by  ghelpdesk   Go to last post Go to first unread
Last post: Today at 2:45:11 AM(UTC)
Lansweeper Is there a chance to get the firewall off via Lansweeper?
by  RedWood  
Go to last post Go to first unread
Last post: Yesterday at 11:23:31 PM(UTC)
Lansweeper Not working Wake on Lan
by  RedWood   Go to last post Go to first unread
Last post: Yesterday at 11:17:33 PM(UTC)
Lansweeper Wake on Lan Issues
by  RedWood  
Go to last post Go to first unread
Last post: Yesterday at 11:06:12 PM(UTC)
Lansweeper New Web Interface
by  anpatterson03   Go to last post Go to first unread
Last post: Yesterday at 10:05:19 PM(UTC)
Lansweeper LsAgent failing - Lansweeper SSL Expired
by  nlertn-PRE  
Go to last post Go to first unread
Last post: Yesterday at 9:34:12 PM(UTC)