How to Inventory SQL Server Edition (Express, Enterprise, etc.) with Custom Inventory

Products

• Inventory Solution
• Inventory Solution for Mac

Issue/Introduction

 

Resolution

Software and File Inventory gather version information for SQL Server but do not gather information on the edition of SQL Server. This document provides a custom inventory script that detects edition for SQL Server 2005, 2008, 2008 R2, 2012, and 2014 using WMI calls and steps for setting up the custom inventory.

Note: Symantec does not support custom scripts; any fixes or modifications to the script must be done by the user.

1. Create a new custom Data Class that will store the SQL Server Edition information.
   1. Go to Settings > All Settings, and then Settings > Discovery and Inventory > Inventory Solution > Manage Custom Data Classes.
   2. Click New data class.
   3. Name the Data Class "SQL Server Edition" (figure 1) and click OK. 
   4. Click Add attribute.
   5. Name the Attribute "Edition" (figure 2), set Key to "No", and click OK. 
   6. Add two more attributes the same way, name the next attribute "SKUName" and the last attribute "Architecture".
   7. Enable Allow multiple rows from a single computer resource, then click Save changes.
   8. Finally click the button that looks like a pointing hand (figure 3), right next to New data class, and copy the GUID field for the new custom data class.
      
       
2. Create a Custom Inventory Script Task.
   1. Go to Manage > Jobs and Tasks.
   2. Browse the folder drop-down menu to where you would like to add a custom inventory script task.
   3. Right-click on the folder, then select New > Task.
   4. Select the Run Script task
   5. Name the task appropriately.
   6. Select Script type: VBScript.
   7. Download and edit the sqleditions.vbs file attached to this document.
   8. Find the 13th line of the file and replace the GUID with the GUID copied from step 1-h (figure 5).
   9. Copy and paste the entire vbscript into the large text box of the script task.
3. Run the Custom Inventory Script Task on computers with SQL Server.
4. Create a Custom Report to display the SQL Server Edition data collected by the Custom Inventory.
   1. Go to Reports > All Reports.
   2. Browse to a folder where you would like to add the custom report and Right Click on the folder.
   3. Select New > Report > SQL Report.
   4. Give the Report an appropriate name and then replace the text under Parameterized Query (figure 6) with the following query:
      
      select vItem.Name [Computer], Inv_SQL_Server_Edition.SKUName [SQL Edition] from Inv_SQL_Server_Edition
      left join vItem on Inv_SQL_Server_Edition._ResourceGuid = vItem.Guid
      
       
   5. Click Save Changes and view the SQL Server Editions for each computer.
   6. Modify the Custom Report SQL query and Custom Inventory vbscript as needed.