How to get and use Lansweeper data for Microsoft Deployment Manager

This document explains how to extract data from Lansweeper and upload it into Licenseware’s Microsoft Deployment Manager (MDM) app.

Video Overview

Data Extraction

Lansweeper

Lansweeper has a SQL DB component that stores the data it collects. In order to query the DB, you’ll need to satisfy the following requirements:

• Install Lansweeper
• Make sure that you have access to the Lansweeper installation

Once the above requirements are satisfied, you can execute the Licenseware Queries for Lansweeper. In order to do that, you’ll need to follow these simple steps:

• Open Lansweeper
• Login
• Go to Reports
• Click on Reports and select Create New Report
• Add the name (Title) as per the naming convention below depending on the query you are going to run;

When saving and exporting the reports, please make sure that you use this naming convention:

• “lansweeper_software_inventory” for the software inventory query;
• “lansweeper_installed_software” for the installed software query;
• “lansweeper_hardware_inventory” for the hardware query;

Licenseware Queries for Lansweeper

Use the following queries to extract the hardware and software data required for analysis.

Lansweeper Hardware Inventory

Copy/Paste the following script in Lansweeper to extract the relevant hardware data.

SELECT
    DISTINCT tblassets.AssetID AS SoftwareAssetID,
    tblassets.AssetName AS AssetName,
    COALESCE(
        tblOperatingsystem.Caption,
        tblLinuxSystem.OperatingSystem
    ) AS OperatingSystem,
    tblassetcustom.Manufacturer AS Manufacturer,
    tblComputersystem.Model AS Model,
    tblassetcustom.SystemSKU AS SystemSKU,
    tblassets.NrProcessors AS NrProcessors,
    tblassetcustom.Serialnumber AS Serialnumber,
    COALESCE(
        tblProcessor.Manufacturer,
        tblLinuxProcessors.Manufacturer
    ) AS ProcessorManufacturer,
    COALESCE(tblProcessor.Caption, tblLinuxProcessors.Family) AS ProcessorModel,
    tblassets.Domain AS Domain,
    tblProcessor.NumberOfCores AS NumberOfCores,
    CASE
        WHEN tblProcessor.NumberOfLogicalProcessors / tblProcessor.NumberOfCores = 2
        THEN 1
        ELSE 0
    END AS HyperThreading,
    tblComputersystem.SystemType AS SystemType,
    CASE
        WHEN tblassets.AssetID NOT IN (
            SELECT
                tblHyperVGuest.AssetID
            FROM
                tblHyperVGuest
        ) AND
        tblassets.AssetID NOT IN (
            SELECT
                tblVmwareGuest.AssetID
            FROM
                tblVmwareGuest
        ) AND
        tblassets.AssetID NOT IN (
            SELECT
                tblCitrixGuest.AssetID
            FROM
                tblCitrixGuest
        )
        THEN 0
        ELSE 1
    END AS VirtualFlag
FROM
    tblassets
    LEFT JOIN tblassetcustom
    ON tblassets.AssetID = tblassetcustom.AssetID
    LEFT JOIN tsysassettypes
    ON tsysassettypes.AssetType = tblassets.Assettype
    LEFT JOIN tblProcessor
    ON tblassets.AssetID = tblProcessor.AssetID
    LEFT JOIN tblLinuxProcessors
    ON tblassets.AssetID = tblLinuxProcessors.AssetID
    LEFT JOIN tblOperatingsystem
    ON tblassets.AssetID = tblOperatingsystem.AssetID
    LEFT JOIN tblComputersystem
    ON tblassets.AssetID = tblComputersystem.AssetID
    LEFT JOIN tblHyperVGuest
    ON tblassets.AssetID = tblHyperVGuest.AssetID
    LEFT JOIN tblVmwareGuest
    ON tblassets.AssetID = tblVmwareGuest.AssetID
    LEFT JOIN tblCitrixGuest
    ON tblassets.AssetID = tblCitrixGuest.AssetID
    LEFT JOIN tblLinuxSystem
    ON tblassets.AssetID = tblLinuxSystem.AssetID
    LEFT JOIN tblLinuxBios
    ON tblassets.AssetID = tblLinuxBios.AssetID
WHERE
    tblassetcustom.State = 1 AND
    tblassets.Assettype = -1

Export the table data as is, and do not alter the column headers.

When exporting the report, rename the file “lansweeper_hardware_inventory”

Lansweeper Software Inventory

Copy/Paste the following script in Lansweeper to extract the relevant software inventory data.

Select Distinct Concat(tblSoftwareUni.SoftwarePublisher, '_', tblSoftwareUni.softwareName, '_', tblSoftware.softwareVersion) As SoftwareKey,
    tblSoftwareUni.SoftwarePublisher As SoftwarePublisher,
    tblSoftwareUni.softwareName As softwareName,
    tblSoftware.softwareVersion As softwareVersion
From tblSoftware
    Left Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID

Export the table data as is, and do not alter the column headers.

When exporting the report, rename the file “lansweeper_software_inventory”

Lansweeper Installed Software

Copy/Paste the following script in Lansweeper to extract a mapping of installed software per device. 

Select Distinct tblSoftware.AssetID As SoftwareAssetID,
    Concat(tblSoftwareUni.SoftwarePublisher, '_', tblSoftwareUni.softwareName,
  '_', tblSoftware.softwareVersion) As SoftwareKey
  ,tblAssets.AssetID As AssetID
From tblSoftware
    Left Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
    Left Join tblAssets On tblAssets.AssetID = tblSoftware.AssetID
Where tblAssets.Assettype = -1

Export the table data as is, and do not alter the column headers.

When exporting the report, rename the file “lansweeper_installed_software”