All around report

[ITninja]

Hello!

I have been having a hard time getting a report to show everything that I need.

I am looking to have it show,

type(monitor, computer, VOIP phone), make, model, serial number, and IP location

Can anyone help out with this?

Thanks!

[Hemoco · Accepted solution]

Use the report below

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tsysAssetTypes.AssetTypename,
tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.IPAddress,
tblAssets.Lastseen,
tblAssets.Lasttried,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
tblAssetCustom.Serialnumber,
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
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Where tblAssetCustom.State = 1
Order By tblAssets.AssetName,
tsysAssetTypes.AssetTypename,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
tblAssetCustom.Serialnumber

[ITninja]

Thanks! The only problem here is that there is no IP Location ( I have my separate offices split up by IP ranges), and when I add in IP Location it puts each asset 4 times listing it in each IP Range.

Any idea why its doing that? And how I can get it to show its proper location only and not duplicate or quadruplicate itself?

Thanks again!

[RCorbeil]

Those are all fields available from the tables in the report builder's default query.

SELECT Top 1000000
  tblAssets.AssetID,
  tblAssets.AssetName,
  tsysAssetTypes.AssetTypename,
  tsysAssetTypes.AssetTypeIcon10 As icon,
  tblAssets.IPAddress,
  tblAssets.Lastseen,
  tblAssets.Lasttried,
  tblAssetCustom.Manufacturer,
  tblAssetCustom.Model,
  tblAssetCustom.Serialnumber
FROM
  tblAssets
  INNER JOIN tblAssetCustom ON tblAssets.AssetID = tblAssetCustom.AssetID
  INNER JOIN tsysAssetTypes ON tsysAssetTypes.AssetType = tblAssets.Assettype
WHERE
  tblAssetCustom.State = 1
ORDER BY
  tsysAssetTypes.AssetTypename,
  tblAssetCustom.Manufacturer,
  tblAssetCustom.Model,
  tblAssetCustom.Serialnumber