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

View solution in original post

3 REPLIES 3
Hemoco
Lansweeper Alumni
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
Engaged Sweeper
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
Honored Sweeper II
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