SQL Query Map location

[Svante1]

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

[rader · Accepted solution]

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.

[rader · Accepted solution]

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 · Accepted solution]

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]

Thanks a lot. That really did the trick.

[rader]

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]

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]

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

[Svante1]

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