‎10-06-2022 07:53 AM - last edited on ‎04-01-2024 03:51 PM by Mercedes_O
Hi. I found this query to find out in what map location assets are in.
Is there a way to find out which assets that don't have map location?
Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tsysAssetTypes.AssetTypename As Type,
tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.IPAddress,
tsysIPLocations.IPLocation,
tblAssets1.AssetName As AssetLocation,
tblAssets.Domain,
Case tsysAssetTypes.AssetTypename
When 'Windows' Then tsysOS.OSname
When 'Linux' Then tblLinuxSystem.OSRelease
When 'Apple Mac' Then tblMacOSInfo.KernelVersion
Else ''
End As OS,
tblState.Statename,
tblAssetCustom.Location,
tblAssetCustom.Building,
tblAssetCustom.Department,
tblAssetCustom.Contact,
tblAssetCustom.Custom1,
tblAssetCustom.Custom8,
tblAssets.Firstseen,
tblAssets.Lastseen,
tblAssets.Lasttried
From tblAssets
Left Join tblAssetRelations On tblAssetRelations.ChildAssetID =
tblAssets.AssetID
Left Join tsysAssetRelationTypes On tsysAssetRelationTypes.RelationTypeID =
tblAssetRelations.Type
Left Join tblAssets tblAssets1 On
tblAssets1.AssetID = tblAssetRelations.ParentAssetID
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Left Join tblOperatingsystem On tblAssets.AssetID = tblOperatingsystem.AssetID
Inner Join tsysIPLocations On tsysIPLocations.LocationID =
tblAssets.LocationID
Left Join tblADComputers On tblAssets.AssetID = tblADComputers.AssetID
Inner Join tblState On tblState.State = tblAssetCustom.State
Left Join tblLinuxSystem On tblAssets.AssetID = tblLinuxSystem.AssetID
Left Join tsysOS On tblAssets.OScode = tsysOS.OScode
Left Join tblMacOSInfo On tblAssets.AssetID = tblMacOSInfo.AssetID
Where tsysAssetRelationTypes.Name = 'is located in'
Order By tblAssets.AssetName
Solved! Go to Solution.
‎10-11-2022 09:31 PM
You're looking for the Mapped Locations, not the Edit Asset Locations then. I found a great write-up by a user @RCorbeil about this and have adapted it to your code.
See these articles to get a better explanation than I can give.
First to report on Maps and their Assets by @Susan_A : https://community.lansweeper.com/t5/forum/location-maps-and-their-assets/m-p/52717#post40676
Second to see why the Mapped location report is more difficult explained by @Esben_D : https://community.lansweeper.com/t5/forum/report-of-asset-location/m-p/30542
Third is where I found the info that may help your report from user @RCorbeil : https://community.lansweeper.com/t5/forum/report-showing-asset-locations/m-p/19136
That said, here's the report that I modified that may help you.
Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName As Name,
tsysAssetTypes.AssetTypename As Type,
tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.IPAddress,
tsysIPLocations.IPLocation,
tblAssets.AssetName As AssetLocation,
tblAssets.Domain,
Case tsysAssetTypes.AssetTypename
When 'Windows' Then tsysOS.OSname
When 'Linux' Then tblLinuxSystem.OSRelease
When 'Apple Mac' Then tblMacOSInfo.KernelVersion
Else ''
End As OS,
tblState.Statename,
tblAssetCustom.Location,
tblAssetCustom.Building,
tblAssetCustom.Department,
tblAssetCustom.Contact,
tblAssetCustom.Custom1,
tblAssetCustom.Custom8,
tblAssets.Firstseen,
tblAssets.Lastseen,
tblAssets.Lasttried,
al.AssetName As [On Map]
From tblAssets
Left Join tblOperatingsystem On tblAssets.AssetID = tblOperatingsystem.AssetID
Inner Join tsysIPLocations On tsysIPLocations.LocationID =
tblAssets.LocationID
Left Join tblADComputers On tblAssets.AssetID = tblADComputers.AssetID
Left Join tblLinuxSystem On tblAssets.AssetID = tblLinuxSystem.AssetID
Left Join tsysOS On tblAssets.OScode = tsysOS.OScode
Left Join tblMacOSInfo On tblAssets.AssetID = tblMacOSInfo.AssetID
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Left Join tblAssetRelations As ar On ar.ChildAssetID = tblAssets.AssetID
Left Join tblAssets As al On ar.ParentAssetID = al.AssetID And
al.Assettype = 66
Inner Join tblState On tblState.State = tblAssetCustom.State
Order By [On Map]
This report will show all assets without filtering. You should see in the last column "On Map" a list of your current maps and blank entries. If you apply the "Is Null" to the "On Map" field criteria, it should only show the blank entries now.
Your last two lines should now be:
Where al.AssetName Is Null
Order By [On Map]
Good luck.
‎10-12-2022 06:37 PM
Glad to help.
Don't forget to mark the solution as fixed if it resolved the issue. This helps others later looking for answers.
‎10-12-2022 06:37 PM
Glad to help.
Don't forget to mark the solution as fixed if it resolved the issue. This helps others later looking for answers.
‎10-11-2022 09:31 PM
You're looking for the Mapped Locations, not the Edit Asset Locations then. I found a great write-up by a user @RCorbeil about this and have adapted it to your code.
See these articles to get a better explanation than I can give.
First to report on Maps and their Assets by @Susan_A : https://community.lansweeper.com/t5/forum/location-maps-and-their-assets/m-p/52717#post40676
Second to see why the Mapped location report is more difficult explained by @Esben_D : https://community.lansweeper.com/t5/forum/report-of-asset-location/m-p/30542
Third is where I found the info that may help your report from user @RCorbeil : https://community.lansweeper.com/t5/forum/report-showing-asset-locations/m-p/19136
That said, here's the report that I modified that may help you.
Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName As Name,
tsysAssetTypes.AssetTypename As Type,
tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.IPAddress,
tsysIPLocations.IPLocation,
tblAssets.AssetName As AssetLocation,
tblAssets.Domain,
Case tsysAssetTypes.AssetTypename
When 'Windows' Then tsysOS.OSname
When 'Linux' Then tblLinuxSystem.OSRelease
When 'Apple Mac' Then tblMacOSInfo.KernelVersion
Else ''
End As OS,
tblState.Statename,
tblAssetCustom.Location,
tblAssetCustom.Building,
tblAssetCustom.Department,
tblAssetCustom.Contact,
tblAssetCustom.Custom1,
tblAssetCustom.Custom8,
tblAssets.Firstseen,
tblAssets.Lastseen,
tblAssets.Lasttried,
al.AssetName As [On Map]
From tblAssets
Left Join tblOperatingsystem On tblAssets.AssetID = tblOperatingsystem.AssetID
Inner Join tsysIPLocations On tsysIPLocations.LocationID =
tblAssets.LocationID
Left Join tblADComputers On tblAssets.AssetID = tblADComputers.AssetID
Left Join tblLinuxSystem On tblAssets.AssetID = tblLinuxSystem.AssetID
Left Join tsysOS On tblAssets.OScode = tsysOS.OScode
Left Join tblMacOSInfo On tblAssets.AssetID = tblMacOSInfo.AssetID
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Left Join tblAssetRelations As ar On ar.ChildAssetID = tblAssets.AssetID
Left Join tblAssets As al On ar.ParentAssetID = al.AssetID And
al.Assettype = 66
Inner Join tblState On tblState.State = tblAssetCustom.State
Order By [On Map]
This report will show all assets without filtering. You should see in the last column "On Map" a list of your current maps and blank entries. If you apply the "Is Null" to the "On Map" field criteria, it should only show the blank entries now.
Your last two lines should now be:
Where al.AssetName Is Null
Order By [On Map]
Good luck.
‎10-12-2022 08:30 AM
Thanks a lot. That really did the trick.
‎10-08-2022 03:21 AM
Add "Is Null" without the quotes in your Location Criteria field.
This makes your last two lines:
Where tblAssetCustom.Location Is Null And tsysAssetRelationTypes.Name =
'is located in'
Order By tblAssets.AssetName
This should work for you.
‎10-10-2022 07:42 AM
Hi rader.
That alternative results in less assets than the original. Only Narrowed i down from 160 to 38 There are still ( only ) assets there that has mapping in the result
‎10-10-2022 05:12 PM
Would you provide an example of what you're describing? A screenshot perhaps.
‎10-11-2022 09:05 AM
Hi again.
I think there is a confusion of what I really want.
Your suggested solution only reports assets without any manual typed locations. ( Called Location )
I need the ones that are not placed on a map. ( Called AssetLocation )
Attaching two screenshots
Experience Lansweeper with your own data. Sign up now for a 14-day free trial.
Try Now