LanSweeper Inventory Reports

Created On
Last Updated On
byJohn Lambrou
You are here:
< All Topics
Table of Contents

Here are two (2) custom SQL queries that can be imported into your Lansweeper installation.

The first query (#1) is for a basic Inventory report focusing on all physical laptops and desktops that exist in the database. If the device was ever on your network and scanned by Lansweeper, it will come up here.

The second query (#2) is exactly the same as the first query (#1) with the difference here being that the report will only show only laptops and desktops that have been scanned/seen in the last 90 days. This is useful as it basically gives you a quick view into a rolling 3 month window of active endpoints in your environment.

SQL query #1:

Select Top 1000000 Case
When tblComputersystem.Domainrole > 1 Then 'Server'
Else Case
When Coalesce(tblPortableBattery.AssetID, 0) = 0 Then 'Desktop'
Else 'Laptop'
End
End As Type,
tblAssetCustom.Manufacturer As Vendor,
tblAssetCustom.Model,
tblAssets.Memory,
tblAssets.Processor,
tblAssets.AssetUnique,
tblAssetCustom.Serialnumber As [Service Tag Number],
tblADusers.Displayname,
tblState.Statename As [Asset state],
tblOperatingsystem.Caption As Operatingsystem,
tblAssetCustom.AssetID,
tblAssets.Firstseen,
tblAssets.Lastseen,
tblAssetCustom.Comments
From tblAssetCustom
Inner Join tblAssets On tblAssetCustom.AssetID = tblAssets.AssetID
Left Join tblOperatingsystem On tblAssets.AssetID = tblOperatingsystem.AssetID
Inner Join tblAssetGroupLink On tblAssets.AssetID = tblAssetGroupLink.AssetID
Inner Join tblAssetGroups On tblAssetGroups.AssetGroupID =
tblAssetGroupLink.AssetGroupID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Left Join tblADusers On tblADusers.Username = tblAssets.Username And
tblADusers.Userdomain = tblAssets.Userdomain
Left Join tsysIPLocations On tblAssets.IPNumeric >= tsysIPLocations.StartIP
And tblAssets.IPNumeric <= tsysIPLocations.EndIP Inner Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID Left Join tblPortableBattery On tblAssets.AssetID = tblPortableBattery.AssetID Inner Join tblState On tblAssetCustom.State = tblState.State Where Case When tblComputersystem.Domainrole > 1 Then 'Server'
Else Case
When Coalesce(tblPortableBattery.AssetID, 0) = 0 Then 'Desktop'
Else 'Laptop'
End
End Not Like 'VMWare%' And Case
When tblComputersystem.Domainrole > 1 Then 'Server'
Else Case
When Coalesce(tblPortableBattery.AssetID, 0) = 0 Then 'Desktop'
Else 'Laptop'
End
End Not Like 'Server%' And tblAssetCustom.Manufacturer Not Like 'VMWare%' And
tblAssetCustom.Manufacturer Not Like 'ProLiant%' And
tblAssetCustom.Model Not Like 'VMWare%' And tblAssetCustom.Model Not
Like 'Server' And tblAssetCustom.Model Not Like 'ProLiant%' And
tblAssetCustom.Model Not Like 'Virtual Machine%'
Order By tblAssets.Firstseen Desc

SQL query #2:

Select Top 1000000 Case
When tblComputersystem.Domainrole > 1 Then 'Server'
Else Case
When Coalesce(tblPortableBattery.AssetID, 0) = 0 Then 'Desktop'
Else 'Laptop'
End
End As Type,
tblAssetCustom.Manufacturer As Vendor,
tblAssetCustom.Model,
tblAssets.Memory,
tblAssets.Processor,
tblAssets.AssetUnique,
tblAssetCustom.Serialnumber As [Service Tag Number],
tblADusers.Displayname,
tblState.Statename As [Asset state],
tblOperatingsystem.Caption As Operatingsystem,
tblAssetCustom.AssetID,
tblAssets.Firstseen,
tblAssets.Lastseen,
tblAssetCustom.Comments
From tblAssetCustom
Inner Join tblAssets On tblAssetCustom.AssetID = tblAssets.AssetID
Left Join tblOperatingsystem On tblAssets.AssetID = tblOperatingsystem.AssetID
Inner Join tblAssetGroupLink On tblAssets.AssetID = tblAssetGroupLink.AssetID
Inner Join tblAssetGroups On tblAssetGroups.AssetGroupID =
tblAssetGroupLink.AssetGroupID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Left Join tblADusers On tblADusers.Username = tblAssets.Username And
tblADusers.Userdomain = tblAssets.Userdomain
Left Join tsysIPLocations On tblAssets.IPNumeric >= tsysIPLocations.StartIP
And tblAssets.IPNumeric <= tsysIPLocations.EndIP Inner Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID Left Join tblPortableBattery On tblAssets.AssetID = tblPortableBattery.AssetID Inner Join tblState On tblAssetCustom.State = tblState.State Where Case When tblComputersystem.Domainrole > 1 Then 'Server'
Else Case
When Coalesce(tblPortableBattery.AssetID, 0) = 0 Then 'Desktop'
Else 'Laptop'
End
End Not Like 'VMWare%' And Case
When tblComputersystem.Domainrole > 1 Then 'Server'
Else Case
When Coalesce(tblPortableBattery.AssetID, 0) = 0 Then 'Desktop'
Else 'Laptop'
End
End Not Like 'Server%' And tblAssetCustom.Manufacturer Not Like 'VMWare%' And
tblAssetCustom.Manufacturer Not Like 'ProLiant%' And
tblAssetCustom.Model Not Like 'VMWare%' And tblAssetCustom.Model Not
Like 'Server' And tblAssetCustom.Model Not Like 'ProLiant%' And
tblAssetCustom.Model Not Like 'Virtual Machine%' And tblAssets.Lastseen >
GetDate() - 90
Order By tblAssets.Firstseen Desc

If you know SQL then you will know how to build Lansweeper reports as well. (Here’s a good start just in case: w3school)


More information on how to add a report can be found here. (lansweeper)

Close Bitnami banner
Bitnami