Summary

Synopsis

Summary

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.

A specialized solution

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.

Pre-requisites

• ManageSoft Deployment Manager for administration servers, release 7.6.4 or later version upto 8.5. (Releases up to and including 7.9.0 must be patched for compatibility with ManageSoft Compliance Manager. This patch is available from the ManageSoft\Patches folder on the Compliance Manager product DVD.)
• ManageSoft Deployment Manager for distribution servers, release 7.9 to 8.5
• ManageSoft for managed devices, release 7.9 or to 8.5.
• ManageSoft Compliance Manager (release 7.8.6 to 8.5 is recommended; releases prior to 7.8.6 require a customization. Please see below for more information.)
• ManageSoft Application Recognition Library, release 205 or later.

Procedure

To set up inventory generation, follow these steps:

1. Download and save the .zip file linked at the bottom of this article.
2. Extract the MSI package contained in the .zip file.
3. To use the plugin with an inventory agent installed on a managed device:
   1. On the ManageSoft administration server, import the MSI package into your software library. (For more information please refer to the ManageSoft Software Deployment Guide supplied with Deployment Manager.)
   2. Add the package to your policy so it is rolled out to clients. (For more information, please refer to the same manual.)
4. To use the plugin when the inventory agent is executed remotely ("zero touch inventory"):
   1. Install the cmtrack.msi installer on the administration server and any distribution server used for remote execution. Note that the server must be running 7.9 or later; earlier releases are not supported by the plugin.
5. At the next inventory gather, you will begin to see the new inventory appear in Deployment Manager reports.
6. For release 7.8.6 and earlier releases of ManageSoft Compliance Manager, please ensure that, before you import the inventory into ManageSoft Compliance Manager, you have followed the steps outlined in the Additional customization section below.
7. Before you import inventory into ManageSoft Compliance Manager, ensure that you have ARL release 205 or later installed. Release 205 is the earliest ARL version that can detect the new inventory. The ARL is normally updated automatically by a scheduled task. To check the version currently installed, run the following SQL command in Microsoft SQL Server Management Studio, using your Compliance Manager database:

   select * from SoftwareRecognitionImport

8. Follow the regular procedure to import inventory into ManageSoft Compliance Manager.

If this procedure is followed correctly, you should see the new software titles in ManageSoft Compliance Manager with correct install counts.

Validation

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).

Limitations

• Instance names are not imported at this time, although the information is collected by the enhanced inventory agent.
• The plugin must be rolled out again after a managed device upgrade.
• No Itanium editions of Microsoft SQL are recognized (for more information, see http://www.microsoft.com/servers/64bit/itanium/overview.mspx).

Troubleshooting

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.

Additional information about the inventory agent plugin

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:

• Developer edition
• Evaluation edition
• Express edition
• Workgroup

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.

Additional information about the ARL update

From release #205, the ARL contains three sets of application titles (and associated evidence) across the Microsoft SQL Server product range.

• Titles that accurately reflect the various versions and components of Microsoft SQL Server, and are linked to WMI evidence. Note that WMI evidence can only be inspected by examining the properties of an application title, and checking the WMI tab.
• Titles that also accurately identify certain components (such as Reporting Services and Analysis Services, and so on) that can be correctly identified by the installer evidence that Microsoft provides.
• Titles that are quite general, and linked to installer evidence that is inadequate. These titles are required for those customers who do not have the enhanced inventory agent rolled out throughout their enterprise (that is, where they may get mixed old and new inventory returned), or who have SMS or other inventory tools that only return the standard Microsoft installer evidence. However, customers who do have the enhanced inventory agent rolled out globally may choose, in the Compliance Manager UI, to Ignore these titles, in order to clean up reporting and to help reconcile actual installations against entitlements. These titles are named SQL Server Component and have an explanatory comment in the application properties.

Additional customization for ManageSoft Compliance Manager releases prior to 7.8.6

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".

1. Replace the element named "GetWMIEvidence" with the one below:

     
       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)
     
   
   
   

2. Replace the element named "GetInstalledWMIEvidence" with the one below:

     
       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.

Download cmtrack.zip

Revision history

1.1.3, 2010/05/13

Stop spurious SQL 2000 error being reported to the Windows event log

1.1.2, 2009/11/25

Added support for Managed Devices version 8.2

1.1.1, 2009/02/10

Added support for Managed Devices version 8.0

1.1.0, 2008/03/14

Added support for Managed Devices version 7.9.5

1.0.0, 2007/12/03

Initial revision supporting Managed Devices version 7.9

Additional Information