
Options
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎07-14-2014 04:29 PM
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!
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!
Solved! Go to Solution.
Labels:
- Labels:
-
Report Center
1 ACCEPTED SOLUTION

Options
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎07-14-2014 05:49 PM
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
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
1 REPLY 1

Options
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎07-14-2014 05:49 PM
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
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
