Previous Topic

Next Topic

Book Contents

Book Index

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 you need 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.tblComputers.Computername, 
		dbo.tblComputers.ComputerUnique, 
		dbo.tblComputers.Computer, 
		dbo.tblComputers.Domain,
		dbo.tblADComputers.OperatingSystem, 
		dbo.tblADComputers.OperatingSystemServicePack, 
		dbo.tblADComputers.OperatingSystemVersion,
		dbo.tblADComputers.DNSHostname, 
		dbo.tblComputersystem.Manufacturer, 
		dbo.tblComputersystem.Model,
		dbo.tblCompCustom.Lastchanged, 
		dbo.tblCompCustom.Firstseen, 
		dbo.tblCompCustom.Lastseen, 
		dbo.tblComputers.LastknownIP,
		LastActiveScan,
		dbo.tblBIOS.Caption AS BiosName, 
		dbo.tblBIOS.BIOSVersion, 
		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.tblVideoController.Caption AS Video,
		dbo.tblCompCustom.PurchaseDate, tblCompCustom.Warrantydate,
		dbo.tblCompCustom.Location, tblCompCustom.Building, tblCompCustom.Department,
		dbo.tblCompCustom.Branchoffice, tblCompCustom.State,
		dbo.tblCompCustom.BarCode, tblCompCustom.Custom1, tblCompCustom.Lastsaved,
		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.web40CorrectMemory.Memory, 
		dbo.web40ProcessorCapacity.NrOfProcessors
FROM dbo.tblComputers INNER JOIN dbo.tblCompCustom ON dbo.tblComputers.Computername = dbo.tblCompCustom.Computername
			INNER JOIN dbo.tblComputersystem ON dbo.tblComputers.Computername = dbo.tblComputersystem.Computername
		INNER JOIN dbo.tblADComputers ON dbo.tblComputers.Computername = dbo.tblADComputers.Computername
		INNER JOIN dbo.tblBIOS ON dbo.tblComputers.Computername = dbo.tblBIOS.Computername
		INNER JOIN dbo.tblPROCESSOR ON dbo.tblComputers.Computername = dbo.tblPROCESSOR.Computername
		-- INNER JOIN dbo.tblVideoController ON dbo.tblComputers.Computername = dbo.tblVideoController.Computername
		INNER JOIN dbo.tblDiskdrives ON dbo.tblComputers.Computername = dbo.tblDiskdrives.Computername
		INNER JOIN dbo.web40CorrectMemory ON dbo.tblComputers.Computername = dbo.web40CorrectMemory.Computername
		INNER JOIN dbo.web40ProcessorCapacity ON dbo.tblComputers.Computername = dbo.web40ProcessorCapacity.Computername
--ORDER BY dbo.tblComputers.ComputerUnique
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.tblComputers.Computername, 
	dbo.tblComputers.ComputerUnique, 
	dbo.tblComputers.Domain,
	dbo.tblComputers.Username , 
	dbo.tblComputers.Userdomain, 
	dbo.tblComputers.LastknownIP,
	dbo.tblServices.ServiceID,
	dbo.tblServices.Caption AS ServiecName, 
	dbo.tblServices.Name AS DisplayName,
	dbo.tblServices.Started,
	dbo.tblServices.Startmode,
	dbo.tblServices.Startname, 
	dbo.tblServices.State , 
	dbo.tblServices.Lastchanged
FROM dbo.dbo.tblComputers LEFT JOIN dbo.tblServices ON dbo.tblComputers. Computername = dbo.tblServices. ComputerName
WHERE ISNULL (CAST (dbo.tblServices .ServiceID AS VARCHAR (10 )), '') <> ''

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.tblComputers.Computername, 
	dbo.tblComputers.ComputerUnique, 
	dbo.tblComputers.Domain,
	dbo.tblComputers.Username, 
	dbo.tblComputers.Userdomain, 
	dbo.tblComputers.LastknownIP,
	dbo.tblSoftware.softwareName, 
	dbo.tblSoftware.SoftwareID, 
	dbo.tblSoftware.Lastchanged,
	dbo.tblSoftware.softwareVersion, 
	dbo.tblSoftware.SoftwarePublisher, 
	dbo.tblSoftware.Installdate AS SWInstallDate
FROM dbo.tblComputers LEFT JOIN dbo.tblSoftware ON dbo.tblComputers.Computername = dbo.tblSoftware.ComputerName
WHERE ISNULL(CAST(dbo.tblSoftware.SoftwareID AS VARCHAR(10)), '') <> ''
GO

See Also

About CSM Integrations

Out-Of-The-Box Connector Integrations

Avaya Integration

Atrium Discovery and Dependency Mapping (ADDM) Integration

Barcode Scanner Integration

Bomgar Integration

Cherwell Discovery and Inventory (CDI) Integration

Cisco Call Manager Integration

Computer Telephony Integration (CTI) Integration

Dimension Data Xigo Integration

eDOCs Integration

External Database Integration

Footprints Integration

Four Rivers TMS Integration

HP OpenView Integration

inContact ACD/IVR Integration

Intelligent Notification Integrations

Ipswitch What’s Up Gold Integration

JAMF Casper Suite Integration

JIRA Integration

KACE Integration

Knowledge Base Integration

Knowledge Brokers Integration

Lansweeper Integration

Lawson Integration

Lightweight Directory Access Protocol (LDAP) Integration

Microsoft Active Directory® Integration

Microsoft® Dynamics CRM Integration

Microsoft® Dynamics GP Integration

Microsoft® Outlook® Integration

Microsoft® System Center Configuration Manager (SCCM) Integration

Microsoft® System Center Operations Manager (SCOM) Integration

Microsoft® Windows PowerShell Integration

Nagios Integration

NetIQ eDirectory Integration

Oracle Access Manager Integration

Oracle Enterprise Resource Planning (ERP) Integration

Oracle Identity Manager (OIM) Integration

Oracle PeopleSoft Integration

Salesforce Chatter Integration

SAP Integration

Snow Inventory Integration

SolarWinds Inventory Integration

SpiceWorks Integrations

Splunk Integration

Symantec Altiris Integration

Tripwire Enterprise Integration