IP Location Report to also include undefined

[Notonyourradar]

I have a report to list the local admins to each machine. I have it grouped by IP Location but noticed that it doesn't include any of the undefined location entries. We're a large organization so it's simply impossible to keep up with locations, so something is always bound to be undefined, but is it possible to include undefined as a location?

This is what I am working with:

Select Top 1000000 tblAssets.AssetUnique,
tblUsersInGroup.AssetID,
tblUsersInGroup.Lastchanged,
tblAssets.Domain,
tsysOS.OSname,
tblAssets.Description,
tblUsersInGroup.Domainname,
tblUsersInGroup.Username,
tsysOS.Image As icon,
tsysIPLocations.IPLocation,
tblAssets.Lastseen
From tblUsersInGroup
Inner Join tblAssets On tblUsersInGroup.AssetID = tblAssets.AssetID
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
Inner Join tsysIPLocations On tblAssets.IPNumeric >= tsysIPLocations.StartIP
And tblAssets.IPNumeric <= tsysIPLocations.EndIP
Where Not Exists(Select Distinct * From (Select tblAssets.AssetName As Domain,
tblUsers.Name As Username
From tblAssets
Inner Join tblUsers On tblAssets.AssetID = tblUsers.AssetID
Where tblUsers.BuildInAdmin = 1 And tblUsersInGroup.Domainname Like
tblAssets.AssetName And tblUsersInGroup.Username Like tblUsers.Name
Union
Select tsysadmins.Domain,
tsysadmins.AdminName As username
From tsysadmins
Where tblUsersInGroup.Domainname Like tsysadmins.Domain And
tblUsersInGroup.Username Like tsysadmins.AdminName) DERIVEDTBL) And
tblUsersInGroup.Admingroup = 1 And tblAssetCustom.State = 1
Order By tsysIPLocations.IPLocation

[Notonyourradar]

Thanks for your help!

[Nick_VDB · Accepted solution]

Assets that show heir IP location as 'Undefined' do not have any information stored in the tsysIPLocations table. In your report there is an inner join created to tsysIPLocations. As a result there are only assets that have an IP locations name stored in the table that are given back in the report.

We modified the report by doing a 'Left Join' to the tsysIPLocations. This way even assets that do not have an IP Location will be given back in the report. These assets will have nothing for the 'IP Location' column.

Select Top 1000000 tblAssets.AssetUnique,
  tblUsersInGroup.AssetID,
  tblUsersInGroup.Lastchanged,
  tblAssets.Domain,
  tsysOS.OSname,
  tblAssets.Description,
  tblUsersInGroup.Domainname,
  tblUsersInGroup.Username,
  tsysOS.Image As icon,
  tsysIPLocations.IPLocation,
  tblAssets.Lastseen
From tblUsersInGroup
  Inner Join tblAssets On tblUsersInGroup.AssetID = tblAssets.AssetID
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
  Left Join tsysIPLocations On tblAssets.IPNumeric >= tsysIPLocations.StartIP
    And tblAssets.IPNumeric <= tsysIPLocations.EndIP
Where Not Exists(Select Distinct * From (Select tblAssets.AssetName As Domain,
      tblUsers.Name As Username
    From tblAssets
      Inner Join tblUsers On tblAssets.AssetID = tblUsers.AssetID
    Where tblUsers.BuildInAdmin = 1 And tblUsersInGroup.Domainname Like
      tblAssets.AssetName And tblUsersInGroup.Username Like tblUsers.Name
    Union
    Select tsysadmins.Domain,
      tsysadmins.AdminName As username
    From tsysadmins
    Where tblUsersInGroup.Domainname Like tsysadmins.Domain And
      tblUsersInGroup.Username Like tsysadmins.AdminName) DERIVEDTBL) And
  tblUsersInGroup.Admingroup = 1 And tblAssetCustom.State = 1
Order By tsysIPLocations.IPLocation