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