PowerShell

Using Lansweeper with PowerShell to your advantage

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.

Lansweeper - Why should I care?

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.AssetName

Someone 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.

Lansweeper - Accessing reports with PowerShell

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:

  • “$Env:USERPROFILE\Evotec\PSLansweeper” – (in my case C:\Users\przemyslaw.klys\Evotec\PSLansweeper)
  • “$Env:ALLUSERSPROFILE\Evotec\PSLansweeper” – (in my case C:\ProgramData\Evotec\PSLansweeper)
  • Built-in PSLansweeper module folder Resources\Reports where all my reports reside

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.

Lansweeper - Currently built-in reports

You can check for available reports (including your own) using the following command

Get-LansweeperReport -ListReports -SqlInstance "LANSWEEPER.AD.EVOTEC.XYZ"
  • AdobeJulySecurityUpdateVulnerabilityAudit
  • AllInstalledWindowsSoftware
  • AllServersTypes
  • AllWorkstationsWithoutAntivirusSoftwareAudit
  • AssetUptimeSinceLastRebootAudit
  • BitLockerDriveEncryptionAudit
  • ComputerProcessorInformation
  • DellSupportAssistDLLHijackingVulnerabilityAudit
  • Firefox68SecurityUpdateAudit
  • InstalledWebBrowsers
  • InstalledWindowsFeatures
  • InstalledWindowsUpdates
  • IntelProcessorDiagnosticToolVulnerabilityAudit
  • LocalPrinters
  • MicrosoftBlueKeepVulnerabilityAudit
  • MicrosoftPatchTuesdayAuditJuly2019
  • MicrosoftPatchTuesdayAuditJune2019
  • MonitorInformation
  • MultipleChrome75VulnerabilitiesAudit
  • NonOEMWindowsLicense
  • OEMWindowsLicense PrinterStatus
  • PrinterTonerLevel
  • ServerswithLessThan10%FreeDiskSpaceAudit
  • SharedPrinters
  • SoftwareLicenseKeyOverview
  • UptimeAudit
  • WindowsAutomaticUpdateSettingsAudit
  • WindowsDiskSpaceAudit
  • WindowsServerOverview
  • WorkstationswithLessThan10%FreeDiskSpaceAudit
Lansweeper - Requirements to run

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.

This post was last modified on July 14, 2019 16:27

Przemyslaw Klys

System Architect with over 14 years of experience in the IT field. Skilled, among others, in Active Directory, Microsoft Exchange and Office 365. Profoundly interested in PowerShell. Software geek.

Share
Published by
Przemyslaw Klys
Tags: dbatoolslansweeperpowershellreportingsqluser reports
5 years ago

Recent Posts

Upgrade Azure Active Directory Connect fails with unexpected error

Today, I made the decision to upgrade my test environment and update the version of…

5 days ago

Mastering Active Directory Hygiene: Automating Stale Computer Cleanup with CleanupMonster

Have you ever looked at your Active Directory and wondered, "Why do I still have…

4 months ago

Active Directory Replication Summary to your Email or Microsoft Teams

Active Directory replication is a critical process that ensures the consistent and up-to-date state of…

8 months ago

Syncing Global Address List (GAL) to personal contacts and between Office 365 tenants with PowerShell

Hey there! Today, I wanted to introduce you to one of the small but excellent…

1 year ago

Active Directory Health Check using Microsoft Entra Connect Health Service

Active Directory (AD) is crucial in managing identities and resources within an organization. Ensuring its…

1 year ago

Seamless HTML Report Creation: Harness the Power of Markdown with PSWriteHTML PowerShell Module

In today's digital age, the ability to create compelling and informative HTML reports and documents…

1 year ago