If you're using Lansweeper Classic to manage your IT environment, you're sitting on a goldmine of data. But raw data isn't enough—you need clear, actionable insights. That’s where SQL comes in. With just a few basic SQL skills, you can build custom reports that reveal everything from asset inventories to software installations and network health.
Whether you're a seasoned IT pro or just getting started, learning how to write SQL queries in Lansweeper will help you make smarter decisions, faster.
The Challenge: Why SQL Can Feel Overwhelming
Many Lansweeper users open the SSMS hoping to create tailored views of their IT assets—only to hit a wall. They’re faced with a blank SQL editor, unsure where to begin. The problem? Lansweeper stores data across dozens of tables. Without knowing how to join those tables or filter the results, you end up with incomplete or overwhelming reports.
Here’s the good news: You don’t need to be a database expert to write effective SQL. You just need a few key concepts.
Understanding the Basics: How Lansweeper Stores Data
Lansweeper organizes data into tables—each with a specific purpose:
- tblAssets: Contains core information about each device.
- tblSoftware: Lists all installed software on those devices.
- tblOperatingsystem, tblNetwork, tblProcessor, and many more: Each stores a different slice of your IT environment.
- Check the built-in database dictionary for a detailed view what each database table and field stores. Full details to access the dictionary can be found here: Access Lansweeper database documentation
To build a useful report, you need to:
- Select the right columns.
- Join related tables.
- Filter the data to focus on what matters.
- Sort the results for clarity.
Let’s walk through the process step-by-step.
Step-by-Step: Writing Your First SQL Report in Lansweeper
Open the SSMS studio
- In the Lansweeper db menu
- Right-click on the Lansweeper database
- Click new SQL
Start with SELECT
This tells Lansweeper which columns to display in your report.
Example:
SELECT
tblAssets.AssetID,
tblAssets.AssetName,
tblSoftware.SoftwareName
Use FROM and JOIN to Link Tables
- FROM specifies your main table (usually tblAssets).
- JOIN connects related tables using a common key (like AssetID).
Example:
FROM tblAssets
LEFT JOIN tblSoftware ON tblAssets.AssetID = tblSoftware.AssetID
Tip: Use LEFT JOIN if you want to include all assets, even if they don’t have software installed. Use INNER JOIN if you only want assets with matching software records.
Filter with WHERE
Use WHERE to narrow down your results.
Example:
WHERE tblAssets.Status = 'Active'
You can also combine conditions:
WHERE tblAssets.Status = 'Active' AND tblSoftware.SoftwareName LIKE '%Chrome%'
Sort with ORDER BY
Make your report easier to read by sorting the results.
Example:
ORDER BY tblAssets.AssetName ASC
Test and Save Your Report
- Click Run Query to preview the results.
- Tweak your SQL if needed.
- Once satisfied, click Save and give your report a descriptive name.
Bonus Tip: Use Aliases for Cleaner Code
Aliases let you shorten table names, making your queries easier to read.
Example:
SELECT a.AssetName, s.SoftwareName
FROM tblAssets a
LEFT JOIN tblSoftware s ON a.AssetID = s.AssetID
This is functionally identical to the longer version—but much easier to scan and maintain.
Final Thoughts: Start Simple, Grow with Confidence
You don’t need to master complex SQL to start getting value from Lansweeper reports. Start with a few key tables, build small queries, and test often. As you grow more comfortable, you’ll be able to create powerful, customized reports that give you deep visibility into your IT environment.
Remember: Every great report starts with a single SELECT.
Happy querying!
