Amended Asset Report

[MrPatrick]

Hi, I'm afraid I suck at using the report builder. I keep trying but I never manage to make any headway. Can you help please. I need a report that will list hte following bits of information:

Computers:

Asset Name
Domain
Model
OS
Serial Number
Last User
Last Seen
First Seen
Location (Based on IP ranges I've already added)
Warranty End Date

Printers
Asset Name
Model
Last Seen
First Seen
Serial Number
Warranty


I've tried playing with the report builder but I have no idea how I work out which table holds which bit of data?

Can you help?

Thanks,
Patrick

[Hemoco · Accepted solution]

Use the reports below for the information you are after.

Computers

Select Top 1000000 tsysOS.Image As icon,
tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
tsysOS.OSname,
tblOperatingsystem.Caption As FullOSname,
tblAssets.SP,
tblAssetCustom.Serialnumber,
tblAssets.Username,
tblAssets.Userdomain,
tblAssets.Lastseen,
tblAssets.Firstseen,
tsysIPLocations.IPLocation,
tblAssetCustom.Warrantydate
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysOS On tsysOS.OScode = tblAssets.OScode
Inner Join tblOperatingsystem
On tblAssets.AssetID = tblOperatingsystem.AssetID
Left Join tsysIPLocations On tblAssets.IPNumeric >= tsysIPLocations.StartIP
And tblAssets.IPNumeric <= tsysIPLocations.EndIP
Where tblAssetCustom.State = 1
Order By tblAssets.Domain,
tblAssets.AssetName

Printers

Select Top 1000000 tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.IPAddress,
tsysAssetTypes.AssetTypename As Assettype,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
tblAssets.Lastseen,
tblAssets.Firstseen,
tblAssetCustom.Serialnumber,
tblAssetCustom.Warrantydate
From tblAssets
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Where tsysAssetTypes.AssetTypename = 'printer' And tblAssetCustom.State = 1
Order By tblAssets.IPNumeric

To use one of the reports above, do the following:
• Open the report builder under Reports/Create New Report.
• Paste the SQL code we provided at the bottom of the page.
• Left-click somewhere in the upper section of the page so the code applies.
• Give the report a Title and hit the Save & Run button to save it. Export options are listed on the left.