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.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:
- Download and save the .zip file linked at the bottom of this article.
- Extract the MSI package contained in the .zip file.
- To use the plugin with an inventory agent installed on a managed device:
- 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.)
- Add the package to your policy so it is rolled out to clients. (For more information, please refer to the same manual.)
- To use the plugin when the inventory agent is executed remotely ("zero touch inventory"):
- 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.
- At the next inventory gather, you will begin to see the new inventory appear in Deployment Manager reports.
- 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.
- 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
- 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".
- 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) - 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.
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
This article only applies to agents 8.5 and below. From 8.6 the cmtrack plugin is not required.Related Articles
SQL Server edition not recognized if SQL Server WMI provider is inaccessible 114Number of Views Configure Microsoft Endpoint Configuration Manager (SCCM) Software Inventory used by SVM server 22Number of Views Snow License Manager and Snow Inventory: How to set up a SQL trace to provide to Support? 325Number of Views Error 27502.Could Not Connect to Microsoft SQL Server '(local) Using MSOLEDBSQL 19.0 31Number of Views Microsoft SQL server editions can't be determined correctly from SCCM 17Number of Views
Hi, I am Reva - Ask me anything.
No new updates
Thanks for the feedback!
Your feedback has been saved.Rate this response:
Add Additional feedback ( Optional )
Are you sure you want to cancel
the case creation?
Are you sure you want to cancel the case creation?
Are you sure you want to close this case
| Products | Region | Phone Numbers |
|---|---|---|
| FlexNet Operations FlexNet Embedded FlexNet Publisher FlexNet Connect FlexNet Code Insight InstallAnywhere InstallShield |
North America * |
+1 630-332-2513 (toll) +1 877-279-2853 (toll-free in North America) |
| Europe * |
+44 1925 944367 (toll) +44 800 047 8642 (toll-free in Europe) |
|
| Japan * | +81 3-4540-5335 (select option 2) | |
| Australia * |
+61 3 9895 2177 +61 1800 560 603 (toll-free in Australia) |
|
|
Usage Intelligence (formerly
Revulytics) Compliance Intelligence |
Please use the Case Portal to submit your support ticket or reach out to your Revenera contact. | |
Case id: 00001065
Activity: Status change: 2 hours ago