I'm on a tight deadline for one of the chapters for PowerShell Conference Book vol. 2. That means my brain wants me to do a lot of different things but writing that chapter. I've decided to write this simple PowerShell command that allows me to use Lansweeper in PowerShell. If you never heard of Lansweeper, it's a great inventory tool that can scan Windows, Linux, Network, Printers, and other types of assets gathering it all in SQL Database. Usually, you would use their friendly, fast interface to access data it stores because it offers a lot of flexibility, export options, and many many features.
What is unique about Lansweeper is that it's free up to 100 assets, and for 1000 or more assets it has low pricing point. So whether you have small Clients having 10-20-50 computers and a couple of printers or more prominent Client with 500 assets, it's excellent inventory software that works in the same way. If you have more than 100 assets, it won't ruin your budget – and you will always know what is where. Also, they have a pretty great community that tends to create helpful SQL based reports that allow you to find critical stuff for your organization with almost zero effort on your side. Below list shows five resources of different report types that users or Lansweeper vendor provide for you to utilize.
Those reports are provided as an MS SQL Query, in a way you can quickly implement it via Create New Report in Lansweeper. Those are some handy reports – You want to know Bios Versions on all your computers – here you go. The best thing, new reports tend to show monthly.
Select Top 1000000 tblAssets_1.AssetID,
tblAssets_1.AssetName,
tblAssets_1.Domain,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
tblBIOS.SMBIOSBIOSVersion As CurrentBios,
Bios.biosMax As LatestBios,
Case When tblBIOS.SMBIOSBIOSVersion = Bios.biosMax Then 'black' Else 'red'
End As foregroundcolor,
tsysOS.Image As icon
From tblAssets As tblAssets_1
Inner Join tblBIOS On tblAssets_1.AssetID = tblBIOS.AssetID
Inner Join tblAssetCustom On tblAssets_1.AssetID = tblAssetCustom.AssetID
Inner Join (Select tblAssetCustom_1.Manufacturer,
tblAssetCustom_1.Model,
Max(tblBIOS_1.SMBIOSBIOSVersion) As biosMax
From tblAssets
Inner Join tblAssetCustom As tblAssetCustom_1 On tblAssets.AssetID =
tblAssetCustom_1.AssetID
Inner Join tblBIOS As tblBIOS_1 On tblAssets.AssetID = tblBIOS_1.AssetID
Where tblAssetCustom_1.Model Not Like 'VirtualBox' And
tblAssetCustom_1.Model Not Like 'VMware Virtual Platform' And
tblAssetCustom_1.Model Not Like 'Virtual Machine'
Group By tblAssetCustom_1.Manufacturer,
tblAssetCustom_1.Model) As Bios On tblAssetCustom.Model = Bios.Model And
tblAssetCustom.Manufacturer = Bios.Manufacturer And
tblBIOS.SMBIOSBIOSVersion <> Bios.biosMax
Inner Join tsysOS On tsysOS.OScode = tblAssets_1.OScode
Where tblBIOS.SMBIOSBIOSVersion <> Bios.biosMax And tblAssetCustom.State = 1
Order By tblAssets_1.AssetNameSomeone already did the hard work of creating excellent SQL query for you to utilize. There are hundreds of reports to get almost anything you can imagine — problem with finding unpatched Firefox or Chrome, no problem.
I can hear you asking – If Lansweeper is so great, why do you need to access it with PowerShell? Well, Lansweeper gathers a lot of data, lots of data that you usually have to spend time preparing in PowerShell thru different means, and that's not always the fastest way. Think of a situation where the manager asks you what version of Microsoft Office is on all computers or as in the example above BIOS version. Can you get that with PowerShell? Sure. But to get that you will have to scan every PC in your domain and that's not trivial because not all computers are online, at all times. But if you have Lansweeper, it already has that information. I can hear you saying again – But if you already have that report in Lansweeper, why need for PowerShell? Well, based on that Lansweeper report, I can do any action I want in PowerShell. Send an email, install new software, restart the computer, any activity that you can think of at your fingertips. Well sort of, because we have to get that SQL connection up and running first right? Since I had two „free” hours, I wrote a quick PowerShell Module called PSLansweeper which you can install straight from PowerShellGallery
Install-Module PSLansweeper # Update-Module PSLansweeper
This module has one command called Get-LansweeperReport working in two modes. First mode with three parameters – SQLInstance, Database (default: LansweeperDB) and Report allows you to get report content (you can ask multiple reports at the same time). Second mode is to list available reports with ListReports parameter.
You can query multiple reports at the same time and when you do that Hashtable of Arrays returns just like on the screenshot below. You can then pick the report output as you need.
You can choose from 20 or so Lansweeper reports that I have copied over from Lansweper website for testing purposes.
But since I didn't want to limit this module to only reports, I've picked, and reports that I will add when I will need something, you can add more reports yourself. The way it works is that the Reports parameter is dynamic. This means the list is built based on SQL files in three folders:
All you have to do is put SQL code in one of the folders, name file any way you want to with SQL extension.
The Report parameter will remove all spaces, remove all hyphens, and show you all reports together from all three folders. So whether you want to have BIOS information, or something entirely different, custom one, create new SQL file in the proper folder, add SQL Query in there, save and be ready to go.
You can check for available reports (including your own) using the following command
Get-LansweeperReport -ListReports -SqlInstance "LANSWEEPER.AD.EVOTEC.XYZ"
As it stands PSLansweeper interacts with Lansweeper SQL Database using current PowerShell credentials. So make sure you have permissions to access the SQL Database of Lansweeper for a module to work. Make sure PowerShell account you're using has proper access. All sources are available on GitHub.
