→ 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: 
Svante1
Engaged Sweeper

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

2 ACCEPTED SOLUTIONS
rader
Champion Sweeper III

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.

View solution in original post

rader
Champion Sweeper III

Glad to help.

Don't forget to mark the solution as fixed if it resolved the issue. This helps others later looking for answers.

View solution in original post

7 REPLIES 7
rader
Champion Sweeper III

Glad to help.

Don't forget to mark the solution as fixed if it resolved the issue. This helps others later looking for answers.

rader
Champion Sweeper III

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.

Svante1
Engaged Sweeper

Thanks a lot. That really did the trick.

rader
Champion Sweeper III

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.

Svante1
Engaged Sweeper

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

rader
Champion Sweeper III

Would you provide an example of what you're describing? A screenshot perhaps.

Svante1
Engaged Sweeper

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 location is null.pnglocation not is null.png