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