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