custom inventory report sql

[KateZilla]

What is the SQL syntax to create a report with the following fields?

Computer (Windows or MAC)
Computer Name
Date purchased
Cost (Custom 3 Field)
Last Logged In
IP Location

[Susan_A · Accepted solution]

I moved this topic from the Report Center section of the forum to Report Requests & Questions. Please note that the Report Center is for posting ready-to-use reports, not questions.

The report below lists the information you are after. If you are interested in building or modifying reports, we would recommend:

• Reviewing some SQL tutorials, as the Lansweeper report builder is a standard SQL editor. If you know SQL, you know how to build Lansweeper reports as well. This seems like a good tutorial.
  
• Making use of our database dictionary, more information on which can be found here. The dictionary explains in great detail what each table and field stores.

Select Top 1000000 tblAssets.AssetID,
  tblAssets.AssetName,
  tblAssets.Domain,
  tsysAssetTypes.AssetTypename,
  tsysAssetTypes.AssetTypeIcon10 As icon,
  tblAssets.Username,
  tblAssets.Userdomain,
  tblAssets.IPAddress,
  tsysIPLocations.IPLocation,
  tblAssetCustom.PurchaseDate,
  tblAssetCustom.Custom3 As Cost,
  tblAssets.Firstseen,
  tblAssets.Lastseen,
  tblAssets.Lasttried
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
  Left Join tsysIPLocations On tsysIPLocations.StartIP <= tblAssets.IPNumeric
    And tsysIPLocations.EndIP >= tblAssets.IPNumeric
Where tsysAssetTypes.AssetTypename In ('windows', 'apple mac') And
  tblAssetCustom.State = 1
Order By tblAssets.IPNumeric