Need Report of all Primary Domain Controllers Sorted by IP Location

[pwalach]

Trying to generate a report that will summarize all of the Primary Domain Controllers in our school district sorted by IP Location. Also need to have the Assetname, Domain, OSname and Model in the report.

Here is what I have so far:

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetUnique,
tblAssets.Domain,
tsysOS.OSname,
tblAssets.Description,
tblComputersystem.Lastchanged,
tsysOS.Image As icon,
tblAssetCustom.PurchaseDate,
tblAssetCustom.Model
From tblComputersystem
Inner Join tblAssets On tblComputersystem.AssetID = tblAssets.AssetID
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
Where tblComputersystem.Domainrole = 5 And tblAssetCustom.State = 1
Order By tblAssets.AssetName

I can't get the IPLocation to link in. Any help would be greatly appreciated, thanks!

[pwalach · Accepted solution]

I managed to get a report that's giving me what I want, here it is:

Select Distinct Top 1000000 tblAssets.AssetID,
tblAssets.AssetUnique,
tblAssets.Domain,
tsysOS.OSname As 'Operating System',
tblAssets.IPAddress,
tsysIPLocations.IPLocation,
tsysOS.Image As icon,
tblAssetCustom.Model
From tblComputersystem
Inner Join tblAssets On tblComputersystem.AssetID = tblAssets.AssetID
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
Left Outer Join tsysIPLocations On tblAssets.IPNumeric >=
tsysIPLocations.StartIP And tblAssets.IPNumeric <= tsysIPLocations.EndIP
Where (tblComputersystem.Domainrole = 5 Or tblComputersystem.Domainrole = 4) And
tblAssetCustom.State = 1
Order By tsysIPLocations.IPLocation