The official blog of the Microsoft System Center Configuration Manager Product Group
[Today's post comes from Yvette O'Meally]
We have received a number of requests from customers who would like to create their own Microsoft License file for import, similar to the method used for the non-Microsoft License Import File.
Disclaimer: While it is possible to produce the xml file manually, you should keep in mind that this was not the original intent or design of the feature so the parsing of the xml file may seem surprisingly picky about things that seem harmless.
For Microsoft License import, the Asset Intelligence License Import Wizard requires an xml file with the following columns in this order and with the specified data types.
Column Name
Data Type
Required
Product Pool
Text
Yes
License Product Family
License Version
Effective Quantity
Number
Unresolved Quantity
The expected format of the rows is a pivot table. Attached to this post is a small manual example of what the table should look like. If you have a lot of data you may put the raw data on one worksheet and use the excel pivot tools to generate the pivot table on a different worksheet.
To Create a Microsoft License Import File Using Microsoft Office Excel
1. Open Microsoft Office Excel and create a new spreadsheet.
2. Name the first worksheet License Summary and the second worksheet License Data.
3. On the first row of the License Data worksheet, enter all software license data field names.
4. On the second and subsequent rows of the License Data worksheet, enter software license information as required. If you have access to your license information on the Volume License Service Center (VLSC) portal, this information may be obtained by cutting and pasting the first 5 columns from the License Summary table. NOTE: Excel may import the License Version column as numbers. If that occurs convert the cells to text.
5. Create a pivot table on the License Summary worksheet. The source of the pivot table should be the table that was created on the License Data worksheet. Select all 5 columns (Product Pool, License Product Family, License Version, Effective Quantity and Unresolved Quantity) as Row labels in that order. Turn off all totals and subtotals using Pivot Table options and Field settings.
6. From the Microsoft Office Excel file menu, save the file in .xml format. When using Microsoft Office Excel 2003, the file created should be saved as an XML Spreadsheet formatted file. When using Microsoft Excel 2007, the file created should be saved as an XML Spreadsheet 2003 formatted file.
7. Copy the .xml file to the file share that will be used to import software license information into the Asset Intelligence catalog using the Asset Intelligence License Import Wizard.
8. From within the Configuration Manager console, use the Asset Intelligence License Import Wizard to import the newly created .xml license information file.
9. Run the Asset Intelligence License 14A - Microsoft Volume Licensing Reconciliation Report to verify that the licensing information has been successfully imported into the Asset Intelligence catalog.
For more information see
--Yvette O'Meally
This posting is provided "AS IS" with no warranties and confers no rights.
We're working on our TrueUP and can't seem to get an official license spreadsheet that works.
I'm trying to follow your steps to create the xml file and I still can't get it to work. The pivot table just doesn't look right. There's only one column.
When I save and run the import wizard, I get the ever-so-helpful "an unknown error has occurred" message.
Unfortunately, your example doesn't include the pivot table. Can you provide any assistance?
I can provide you with more detail if you like. The important thing is to correctly create the pivot table then save as XML which removes all the pivot table features and leaves the raw data in the correct format.