Adding IP Locations to a certain vendor / name PC report.

[rathm]

I am trying to build a report of certain vendor and name which i have but I want it to pull the location from the Web40AllIPLocations, it gives me the correct location but the PC is listed multiple times? Anybody know why? Here is what I am using currently,

Select Top 1000000 tblComputers.Computer, tblComputerSystemProduct.Vendor,
tblComputerSystemProduct.Name, tblComputers.Username,
web40AllIPLocations.IPLocation
From tblComputerSystemProduct Inner Join
tblComputers On tblComputers.Computername =
tblComputerSystemProduct.Computername Inner Join
web40AllIPLocations On tblComputerSystemProduct.Computername =
web40AllIPLocations.Computername
Group By tblComputers.Computer, tblComputerSystemProduct.Vendor,
tblComputerSystemProduct.Name, tblComputers.Username,
web40AllIPLocations.IPLocation, tblComputers.LastknownIP
Having (tblComputerSystemProduct.Vendor = 'Dell Inc.' And
tblComputerSystemProduct.Name = 'Latitude E6400') Or
(tblComputerSystemProduct.Vendor = 'Hewlett-Packard' And
tblComputerSystemProduct.Name = 'Latitude E4200') Or
(tblComputerSystemProduct.Name = 'Latitude E4300') Or
(tblComputerSystemProduct.Name = 'Latitude E6500') Or
(tblComputerSystemProduct.Name = 'Latitude E6410') Or
(tblComputerSystemProduct.Name = 'HP Compaq dc5750 Small Form Factor')

Thanks in advance for any advice\help.

[Hemoco]

This is the 5.0 version of the report:

Select Distinct Top 1000000 tblAssets.AssetID,
tblAssets.AssetUnique,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
tblAssets.Username,
tsysIPLocations.IPLocation
From tblAssets
Left Join tsysIPLocations On tblAssets.IPNumeric >= tsysIPLocations.StartIP
And tblAssets.IPNumeric <= tsysIPLocations.EndIP
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Where (tblAssetCustom.Manufacturer = 'Dell Inc.' And tblAssetCustom.Model =
'Latitude E6400') Or
(tblAssetCustom.Manufacturer = 'Hewlett-Packard' And tblAssetCustom.Model =
'Latitude E4200') Or
(tblAssetCustom.Model = 'Latitude E4300') Or
(tblAssetCustom.Model = 'Latitude E6500') Or
(tblAssetCustom.Model = 'Latitude E6410') Or
(tblAssetCustom.Model = 'HP Compaq dc5750 Small Form Factor')

[murpmic]

Do you have an updated example of this for version 5.0?

[rathm]

That is exactly what I needed. Thanks!

[Hemoco]

Could you try running the report as shown below instead.

Select Distinct Top 1000000 tblComputers.Computer,
tblComputerSystemProduct.Vendor, tblComputerSystemProduct.Name,
tblComputers.Username, web40AllIPLocations.IPLocation
From tblComputerSystemProduct Inner Join
tblComputers On tblComputers.Computername =
tblComputerSystemProduct.Computername Left Join
web40AllIPLocations On web40AllIPLocations.Computername =
tblComputers.Computername
Where (tblComputerSystemProduct.Vendor = 'Dell Inc.' And
tblComputerSystemProduct.Name = 'Latitude E6400') Or
(tblComputerSystemProduct.Vendor = 'Hewlett-Packard' And
tblComputerSystemProduct.Name = 'Latitude E4200') Or
(tblComputerSystemProduct.Name = 'Latitude E4300') Or
(tblComputerSystemProduct.Name = 'Latitude E6500') Or
(tblComputerSystemProduct.Name = 'Latitude E6410') Or
(tblComputerSystemProduct.Name = 'HP Compaq dc5750 Small Form Factor')
Order By tblComputers.Computer