Lansweeper Integration: View Examples

Overview

Lansweeper runs on a Microsoft SQL Server (MSSQL) database that is used for network inventory and asset management. These are examples of the Views needed to integrate Lansweeper and CSM.

Workstation View Example

IF EXISTS (SELECT Name FROM Sysobjects WHERE Name = ' v_CherwellComputers ' AND Type = 'V' ) DROP VIEW v_CherwellComputers
        GO CREATE VIEW [dbo].[ v_CherwellComputers] AS
        SELECT DISTINCT dbo.tblAssets.AssetName, dbo.tblAssets.AssetUnique, dbo.tblAssets.Domain, dbo.tblOperatingSystem.Caption AS OperatingSystem, dbo.tblOperatingSystem.Version AS OperatingSystemVersion, dbo.tblAssets.FQDN, dbo.tblAssetCustom.Manufacturer, dbo.tblAssetCustom.Model, dbo.tblAssetCustom.Lastchanged, dbo.tblAssets.Firstseen, dbo.tblAssets.Lastseen, dbo.tblAssets.IPAddress, dbo.tblAssets.LastActiveScan, dbo.tblBIOS.Caption AS BiosName, dbo.tblBIOS.Version, dbo.tblBIOS.Manufacturer AS BIOSMake, dbo.tblBIOS.SerialNumber, dbo.tblBIOS.SMBIOSBIOSVersion, dbo.tblPROCESSOR.Caption AS CPUDesc, tblPROCESSOR.Name AS CPUType, tblPROCESSOR.MaxClockSpeed, tblPROCESSOR.DataWidth AS L2Cache, dbo.tblAssetCustom.PurchaseDate, tblAssetCustom.Warrantydate, dbo.tblAssetCustom.Location, tblAssetCustom.Building, tblAssetCustom.Department, dbo.tblAssetCustom.Branchoffice, tblAssetCustom.State, dbo.tblAssetCustom.BarCode, tblAssetCustom.Custom1, Cast(Cast(dbo.tblDiskdrives.Freespace AS bigint) / 1024 / 1024 AS numeric) AS Free, Cast(Cast(dbo.tblDiskdrives.Size AS bigint) / 1024 / 1024 AS numeric) AS Total, dbo.tblAssets.Memory, dbo.tblAssets.NrProcessors FROM dbo.tblAssets INNER JOIN dbo.tblAssetCustom ON dbo.tblAssets.AssetID = dbo.tblAssetCustom.AssetID INNER JOIN dbo.tblComputersystem ON dbo.tblAssets.AssetID = dbo.tblComputersystem.AssetID INNER JOIN dbo.tblADComputers ON dbo.tblAssets.AssetID = dbo.tblADComputers.AssetID INNER JOIN dbo.tblBIOS ON dbo.tblAssets.AssetID = dbo.tblBIOS.AssetID INNER JOIN dbo.tblOperatingsystem ON dbo.tblAssets.AssetID = dbo.tblOperatingsystem.AssetID INNER JOIN dbo.tblPROCESSOR ON dbo.tblAssets.AssetID = dbo.tblPROCESSOR.AssetID INNER JOIN dbo.tblDiskdrives ON dbo.tblAssets.AssetID = dbo.tblDiskdrives.AssetID WHERE dbo.tblDiskdrives.Caption = 'C:'
        GO

Installed Services View Example

IF EXISTS (SELECT Name FROM Sysobjects WHERE Name = 'v_CherwellInstalledServices' AND Type = 'V' ) DROP VIEW v_CherwellInstalledServices
        GO CREATE VIEW [dbo]. [v_CherwellInstalledServices] AS
        SELECT dbo.tblAssets.AssetName, dbo.tblAssets.AssetUnique, dbo.tblAssets.Domain, dbo.tblAssets.Username , dbo.tblAssets.Userdomain, dbo.tblAssets.IPAddress, dbo.tblServices.ServiceID, dbo.tblServicesUni.Caption AS ServiceName, dbo.tblServicesUni.Name AS DisplayName, dbo.tblServices.Started, dbo.tblServiceStartMode.Startmode, dbo.tblServicesUni.Startname, dbo.tblServiceState.State , dbo.tblServices.Lastchanged FROM dbo.tblAssets INNER JOIN dbo.tblServices ON dbo.tblAssets.AssetID = dbo.tblServices.AssetID INNER JOIN dbo.tblServicesUni ON dbo.tblServices.ServiceuniqueID = dbo.tblServicesUni.ServiceuniqueID INNER JOIN dbo.tblServiceStartMode ON dbo.tblServices.StartID = dbo.tblServiceStartMode.StartID INNER JOIN dbo.tblServiceState ON dbo.tblServices.StateID = dbo.tblServiceState.StateID WHERE ISNULL (CAST (dbo.tblServices.ServiceID AS VARCHAR (10 )), '') <> ''
        GO

Installed Software View Example

IF EXISTS (SELECT Name FROM Sysobjects WHERE Name = 'v_CherwellInstalledSoftware' AND Type = 'V' ) DROP VIEW v_CherwellInstalledSoftware
        GO CREATE VIEW [dbo].[v_CherwellInstalledSoftware] AS
        SELECT dbo.tblAssets.AssetName, dbo.tblAssets.AssetUnique, dbo.tblAssets.Domain, dbo.tblAssets.Username, dbo.tblAssets.Userdomain, dbo.tblAssets.IPAddress, dbo.tblSoftwareUni.softwareName, dbo.tblSoftware.SoftwareID, dbo.tblSoftware.Lastchanged, dbo.tblSoftware.softwareVersion, dbo.tblSoftwareUni.SoftwarePublisher, dbo.tblSoftware.Installdate AS SWInstallDate FROM dbo.tblAssets INNER JOIN dbo.tblSoftware ON dbo.tblAssets.AssetID = dbo.tblSoftware.AssetID INNER JOIN dbo.tblSoftwareUni ON dbo.tblSoftware.softID = dbo.tblSoftwareUni.SoftID WHERE ISNULL(CAST(dbo.tblSoftware.SoftwareID AS VARCHAR(10)), '') <> ''
        GO