→ Having trouble accessing our new support portal or creating a ticket? Please notify our team here

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

View solution in original post

1 REPLY 1
Hemoco
Lansweeper Alumni
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.