cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
Notonyourradar
Engaged Sweeper III
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
1 ACCEPTED SOLUTION
Nick_VDB
Champion Sweeper III
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

View solution in original post

2 REPLIES 2
Notonyourradar
Engaged Sweeper III
Thanks for your help!
Nick_VDB
Champion Sweeper III
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