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

View solution in original post

1 REPLY 1
pwalach
Engaged Sweeper III
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