This article describes how to generate correct Microsoft SQL Server inventory in a Deployment Manager environment, and how to import the generated inventory to Enterprise Compliance Manager.
Installer evidence returned for various editions and components of Microsoft SQL Server is not adequate to differentiate those systems. In a ManageSoft environment, we can provide specialized inventory to correctly identify SQL Server.
This enhanced inventory is generated via an inventory agent plugin called cmtrack.dll. If this file is present on the managed device, additional inventory is generated for Microsoft SQL Server that is more accurate than the installer evidence or file evidence.
Importing the correct inventory data into Enterprise Compliance Manager will give an accurate overview of Microsoft SQL Server installations in your organization.
To set up inventory generation, follow these steps:
select * from SoftwareRecognitionImport
If this procedure is followed correctly, you should see the new software titles in ManageSoft Compliance Manager with correct install counts.
To confirm cmtrack.dll is working, ensure the managed device's inventory log contains an entry with the text "Compliance Manager tracker plugin started". This message appears once each time inventory is gathered. The inventory log by default is saved in C:\Windows\Temp\ManageSoft\tracker.log (when the inventory is run by the schedule agent).
If you receive no additional inventory, but are expecting additional inventory to appear, check your managed device version. This tracker plugin has been tested only on managed devices of release 7.9 and later.
Next, perform the validation check (see above) by inspecting the log files on relevant managed devices to ensure that the plug-in has been installed correctly and is operational.
The inventory agent plugin generates additional inventory for:
Microsoft SQL Server 2005 and 2008
Database instances, Analysis Services, Reporting Services, and Client Tools, are detected and returned (as WMI evidence).
Inventory is generated for 32-bit (x86) and 64-bit (x64) editions when the plugin runs in x64 operating systems. However, some x64 editions are reported as 32-bit (x86). This occurs because these editions run inside a 64-bit operating system as 32-bit applications. The following editions report in this manner:
More information about the affected editions is available at the Microsoft SQL Server features comparison site.
More information on 64-bit computing is available at the Microsoft 64-bit computing overview site and the Microsoft SQL Server (64-bit) site.
Microsoft SQL Server 2000
Database instances and Client Tools are detected and returned (as WMI evidence).
Analysis Services 2000 and Reporting Services 2000 are returned via installer evidence as they are separate products.
From release #205, the ARL contains three sets of application titles (and associated evidence) across the Microsoft SQL Server product range.
Compliance Manager releases prior to 7.8.6 are not configured to import the evidence generated by the inventory agent plugin. As a result, a customization of SourceProcedures.xml file is required.
NOTE: Before performing this customization, ensure you have read and understood the information in the ManageSoft Compliance Manager manual, under the chapter titled "Customization".
DECLARE @SuperClassName nvarchar(256), @PropertyName nvarchar(20), @ClassName nvarchar(256) SET @SuperClassName = 'CIM_OperatingSystem' SET @ClassName = 'MGS_Application' SET @PropertyName = 'Name' DECLARE @SuperClassID int, @ClassID int, @PropertyID int SET @SuperClassID = (SELECT hc.HardwareClassID FROM dbo.HardwareClass AS hc WHERE hc.Class = @SuperClassName) SET @ClassID = (SELECT hc.HardwareClassID FROM dbo.HardwareClass AS hc WHERE hc.Class = @ClassName) SET @PropertyID = (SELECT hp.HardwarePropertyID FROM dbo.HardwareProperty AS hp WHERE hp.Property = @PropertyName) SELECT DISTINCT rtrim(ltrim(hc.Class)) AS ClassName, rtrim(ltrim(hp.[Property])) AS PropertyName, rtrim(ltrim(hv.[Value])) AS PropertyValue FROM dbo.HardwareValue AS hv JOIN dbo.HardwareProperty hp ON hp.HardwarePropertyID = hv.HardwarePropertyID JOIN dbo.HardwareObject ho ON ho.HardwareObjectID = hv.HardwareObjectID JOIN dbo.HardwareClass hc ON hc.HardwareClassID = ho.HardwareClassID JOIN #ManageSoftComputers ON #ManageSoftComputers.ExternalID = ho.ComputerID WHERE ( (hc.HardwareClassID = @SuperClassID OR hc.SuperClassID = @SuperClassID) AND hp.HardwarePropertyID = @PropertyID ) OR (hc.HardwareClassID = @ClassID) OPTION (MAXDOP 1)
DECLARE @SuperClassName nvarchar(256), @PropertyName nvarchar(20), @ClassName nvarchar(256) SET @SuperClassName = 'CIM_OperatingSystem' SET @ClassName = 'MGS_Application' SET @PropertyName = 'Name' DECLARE @SuperClassID int, @ClassID int, @PropertyID int SET @SuperClassID = (SELECT hc.HardwareClassID FROM dbo.HardwareClass AS hc WHERE hc.Class = @SuperClassName) SET @ClassID = (SELECT hc.HardwareClassID FROM dbo.HardwareClass AS hc WHERE hc.Class = @ClassName) SET @PropertyID = (SELECT hp.HardwarePropertyID FROM dbo.HardwareProperty AS hp WHERE hp.Property = @PropertyName) SELECT DISTINCT rtrim(ltrim(hc.Class)) AS ClassName, rtrim(ltrim(hp.[Property])) AS PropertyName, rtrim(ltrim(hv.[Value])) AS PropertyValue, ho.ComputerID AS ExternalID FROM dbo.HardwareValue AS hv JOIN dbo.HardwareProperty hp ON hp.HardwarePropertyID = hv.HardwarePropertyID JOIN dbo.HardwareObject ho ON ho.HardwareObjectID = hv.HardwareObjectID JOIN dbo.HardwareClass hc ON hc.HardwareClassID = ho.HardwareClassID JOIN #ManageSoftComputers ON #ManageSoftComputers.ExternalID = ho.ComputerID WHERE ( (hc.HardwareClassID = @SuperClassID OR hc.SuperClassID = @SuperClassID) AND hp.HardwarePropertyID = @PropertyID ) OR(hc.HardwareClassID = @ClassID) OPTION (MAXDOP 1)
When these additional modifications are in place, you may resume the Procedure above for setting up your revised inventory generation.
May 05, 2011 05:47 PM