→ The Lansweeper Customer Excellence Awards 2024 - Submit Your Project Now! Learn More & Enter Here

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

View solution in original post

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