Altiris Inventory Solution for Windows 6.1 provides comprehensive information about installed hardware, software, operating system settings and more. Although the forthcoming Inventory Solution for Servers will provide additional detail for management of Microsoft SQL Servers, Inventory Solution 6.1 still lacks this capability. This customization is for those that require the detail now, or for those that cannot upgrade to Inventory Solution for Servers until much later.

The solution described in this article provides:

• Installed SQL Server editions per server
• SQL Server licensing type per server
• Quantity of SQL Server licenses

Altiris products covered in this article:

• Inventory Solution 6.1 (required)

Supported SQL Server versions:

• Microsoft SQL Server® 2000 or 2005, RTM or greater

Understanding the Customization Files

This customization creates a new inventory .ini file that when run, will execute an accompanying VBScript in order to run a SQL statement against the target node's database and return values to the Notification Server database through the custom inventory XML format.

During the install the following occurs:

• A new file called AeXInvSolnSQL1.ini is created and copied to "<install_path>\NSCap\Bin\Win32\X86\Inventory Solution."
  • The contents of this file are as follows:

    wscript querysqleditions.vbs
    
    aexnsinvcollector.exe /hidden /nsctransport /v default /useguid
    
    

• Second, a script called querysqleditions.vbs is also copied to "<install_path>\NSCap\Bin\Win32\X86\Inventory Solution."
  

  * Note: This script uses Integrated Security to access the SQL Server. While it can certainly be adapted to authenticate with a username/password convention, both would be sent in clear text which is therefore a security risk. If database access issues arise as a result, you may be forced to use other alternatives to get this data.
  Of course, if you have no problem with passwords in clear text, you can alter this line in the script to suit your needs (with no line breaks):

  oCn.ConnectionString = "PROVIDER=SQLOLEDB.1;Integrated Security=SSPI;Server=" & sServer

  -to-

  oCn.ConnectionString = "PROVIDER=SQLOLEDB.1;User Id=<myUsername>;Password=<myPassword>;Server=" & sServer

• Last, a report file called "report - SQL Servers, Editions, and Licenses.xml" is imported into the Notification Server

Setup

Part 1

1. Unzip the attached file to a temporary location and install the custom inventory files by clicking 'Install.vbs':
   - You will be prompted for the drive letter where your NS is installed. For example, if the NS is installed on the c: drive, just enter "c" without the colon. Enter "d" for the d: drive, and so on.

Part 2

1. Go to the Tasks tab in the Altiris Console. Navigate to Assets and Inventory>Inventory>Windows and select one of the Inventory tasks listed. (Any of them will work because you just need to be able to edit the Inventory Agent Package, which is not available anywhere else in the Altiris Console.)
2. Next to the Program Name in the task, select the link 'Go To Program'
3. Create a new Program with the following properties (leave any properties not listed set to their default values):
   

   Name: SQL editions definition file

   Command line: AeXInvSoln.exe /hidden /s AeXInvSolnSQL1.ini

   Estimated disk space: 3000

   Estimated run time: 5

   Terminate after: 360

   After running: No action required

   Starting window: Normal

   Run with rights: System Account

   Program can run: Whether or not a user is logged on

   Minimum connection speed: No network connection required

   * Note: The /cleanbeforerun option is not used in the Program's Command line. For proper execution, it is required to remove prior SQL Server custom inventory scans (.nsi/.bak), but the cleanbeforerun option would remove the entire ..\eXpress\Inventory folder, thereby removing other important delta inventory files. Instead, the VBScript simply deletes the MSSQLServer.nsi and MSSQLServer.bak files prior to generating new versions of them.

4. Apply changes and update distribution points
5. Next, clone the task you had selected and give the new task a name of "SQL Server Inventory".
6. In the 'SQL Server Inventory task', change the Program dropdown to the new program you just created, and select 'All Microsoft SQL Servers' for the collection. You will have to deselect the default collection.
7. Choose a schedule that makes sense for you and apply your changes.
   

   *Note: The 'All Microsoft SQL Servers' collection may also need a collection update before it will work. You can do this by simply clicking on the collection in the Resources tab, or updating all collections on the Configuration tab at Server Settings>Notification Server Settings>Automatic Collection Updating.

Figure 1. Link to the Inventory Agent Package to add the custom program line.

Click to view.

Figure 2. Add the program line that will execute the custom inventory.

Click to view.

Figure 3. Select the new program from the 'Program name' dropdown. Change the target collection to 'All Microsoft SQL Servers'.

Click to view.

(SQL Servers are initially discovered when the Inventory Agent finds the "MSSQLSERVER" service present. If no Inventory has been run on the target machines, you will need to specify a collection with broader criteria or run an initial full inventory.)

Test the Custom Inventory

1. To test, select a managed SQL Server and force a configuration request. When the server gets its new Inventory Task, you will need to select the task to run it.

   *Note: When the custom inventory runs, it will create a new .nsi file called 'MSSQLServer.nsi'. This file is picked up by the collector and sent to the NS event queue. Upon insert into the database a new data class called 'MS SQL Servers' is created to store the data. This of course results in a new database table called Inv_MS_SQL_Servers.

2. Once the task is complete, go to the Reports tab in the console. Navigate to Reports>Custom Reports>SQL Servers, Editions, and Licenses.
3. Run the report.

Figure 4. Reporting on a SQL Server 2000 install.

Click to view.

Figure 5. The same report with a SQL Server 2005 install. In this case the license was recorded as 'Disabled'. Licenses will have values of 'Per Processor', 'Per User', or 'Disabled'.

Click to view.

Possible SQL Server 2000 Editions:

• Developer Edition
• Enterprise Edition
• Enterprise Evaluation Edition
• Personal Edition
• Standard Edition
• Desktop Engine

Possible SQL Server 2005 Editions:

• Workgroup Edition
• Developer Edition
• Enterprise Edition
• Mobile Edition
• Evaluation Edition
• Standard Edition
• Express Edition

Removal

If it becomes necessary to remove the custom inventory for any reason, the following steps will ensure complete removal:

1. Disable or delete the Inventory task 'SQL Server Inventory'. Allow enough time to ensure that all target nodes have refreshed their configuration. This will prohibit any scheduled inventories from recreating the custom data class after you delete it.
2. On the Configuration tab, navigate to Resource Settings>Data Classes>Default Folder.
3. Right click on the data class 'MS SQL Servers' and delete it.
4. Open up SQL Server Enterprise Manager (2000) or SQL Server Management Studio (2005). In the 'Tables' view in the Altiris database, find the table 'Inv_MS_SQL_Servers'. Right click and select delete.
   

   * Note: In SQL Server 2000, this is done in the view on the right-hand pane but in 2005, this can be done on either the left or right side. Do not drop the table using the SQL DROP command but rather do it through the GUI. Several stored procedures are created when the data class is created and deleting the table through the GUI removes the stored procedures as well.

5. Delete the files AeXInvSolnSQL1.ini and querysqleditions.vbs from "<install_path>\NSCap\Bin\Win32\X86\Inventory Solution."

Figure 6. The Default Folder node in the Data Classes tree.

Click to view.

Figure 7. Delete the 'MS SQL Servers' data class.

Click to view.

Figure 8. The Tables node in SQL Server Management Studio (2005). In SQL Server 2000 Enterprise Manager the tables appear on the right side.

Click to view.

Figure 9. Delete the Inv_MS_SQL_Servers table.

Click to view.

Additional Background

The following is the SQL statement executed by the script. 'SERVERPROPERTY' is a SQL Server function that returns descriptive values about the SQL Server. More information about the function is available in SQL Server Books Online, usually available wherever you have SQL Server installed:

SELECT
      SERVERPROPERTY ('productversion'),
      SERVERPROPERTY ('edition'),
      SERVERPROPERTY ('productlevel'),
      SERVERPROPERTY ('licensetype'),
      CAST(SERVERPROPERTY('numlicenses') AS VARCHAR(255))

The custom report uses this SQL:

IF (SELECT s.[Product Version] FROM Inv_MS_SQL_Servers s) = 8  
BEGIN  
SELECT	ac._resourceguid,    
	ac.[name] AS 'SQL Server Host',    
	CASE    
		WHEN s.[SQL Server Edition] = 'Desktop Engine' THEN 'Microsoft SQL Server 2000 Desktop Engine'    
		WHEN s.[SQL Server Edition] = 'Developer Edition' THEN 'Microsoft SQL Server 2000 Developer Edition'    
		WHEN s.[SQL Server Edition] = 'Enterprise Edition' THEN 'Microsoft SQL Server 2000 Enterprise Edition'    
		WHEN s.[SQL Server Edition] = 'Enterprise Evaluation Edition' THEN 'Microsoft SQL Server 2000 Enterprise Evaluation Edition'    
		WHEN s.[SQL Server Edition] = 'Personal Edition' THEN 'Microsoft SQL Server 2000 Personal Edition'    
		WHEN s.[SQL Server Edition] = 'Standard Edition' THEN 'Microsoft SQL Server 2000 Standard Edition'     
	END AS 'Edition',    
	CASE    
		WHEN s.[Product Level] = 'SP4' THEN 'Service Pack 4'  
		WHEN s.[Product Level] = 'SP3' THEN 'Service Pack 3'    
		WHEN s.[Product Level] = 'SP2' THEN 'Service Pack 2'    
		WHEN s.[Product Level] = 'SP1' THEN 'Service Pack 1'    
		ELSE 'No Service Pack'    
	END AS 'Product Level',    
	CASE    
		WHEN s.[License Type] = 'PER_PROCESSOR' THEN 'Per Processor'    
		WHEN s.[License Type] = 'PER_SEAT' THEN 'Per Seat'    
		ELSE 'Disabled'    
	END AS 'License Type',    
	s.[Number of Licenses] AS 'License Count'   
	FROM Inv_AeX_AC_Identification ac    
	JOIN Inv_MS_SQL_Servers s ON ac._resourceguid = s._resourceguid  
END  
ELSE  
SELECT	ac._resourceguid,    
	ac.[name] AS 'SQL Server Host',    
	CASE    
		WHEN s.[SQL Server Edition] = 'Workgroup Edition' THEN 'Microsoft SQL Server 2005 Workgroup Edition'    
		WHEN s.[SQL Server Edition] = 'Developer Edition' THEN 'Microsoft SQL Server 2005 Developer Edition'    
		WHEN s.[SQL Server Edition] = 'Enterprise Edition' THEN 'Microsoft SQL Server 2005 Enterprise Edition'    
		WHEN s.[SQL Server Edition] = 'Mobile Edition' THEN 'Microsoft SQL Server 2005 Mobile Edition'    
		WHEN s.[SQL Server Edition] = 'Evaluation Edition' THEN 'Microsoft SQL Server 2005 Evaluation Edition'    
		WHEN s.[SQL Server Edition] = 'Standard Edition' THEN 'Microsoft SQL Server 2005 Standard Edition'     
		WHEN s.[SQL Server Edition] = 'Express Edition' THEN 'Microsoft SQL Server 2005 Express Edition'   
	END AS 'Edition',    
	CASE    
		WHEN s.[Product Level] = 'SP4' THEN 'Service Pack 4'  
		WHEN s.[Product Level] = 'SP3' THEN 'Service Pack 3'    
		WHEN s.[Product Level] = 'SP2' THEN 'Service Pack 2'    
		WHEN s.[Product Level] = 'SP1' THEN 'Service Pack 1'    
		ELSE 'No Service Pack'    
	END AS 'Product Level',    
	CASE    
		WHEN s.[License Type] = 'PER_PROCESSOR' THEN 'Per Processor'    
		WHEN s.[License Type] = 'PER_SEAT' THEN 'Per Seat'    
		ELSE 'Disabled'    
	END AS 'License Type',    
	s.[Number of Licenses] AS 'License Count'   
	FROM Inv_AeX_AC_Identification ac    
	JOIN Inv_MS_SQL_Servers s ON ac._resourceguid = s._resourceguid