Virtual Machine Manager - The OS list is Incomplete

The list of Operating Systems for VMM is very out of date if you compare it to the list of VM types in VCenter (or reality, where we all like to live occasionally). You won't find FreeBSD, or Debian versions later than v7 (and V11 is in testing now). You won't see current RHEL or Centos, or anything even vaguely esoteric.

And if you're like me, and you like to have that information available in VMM either for audit, or because you want to be able to automate things based on the guest OS, this is annoying.

Where The Data Lives

This is all stored in the Virtual Machine Manager database, which is usually VirtualManagerDB on your SQL server. There's a table called tbl_IL_OS which has these fields. Note that I can't find documentation for these, so the purpose and appropriate values are a best guess not necessarily correct:

Column Name Data Type Purpose Sample Values
OSId uniqueidentifier (that's GUID, for the rest of us) Primary Key - this is unique and other tables will refer to the data in this table using this key value c0e5b0a9-3c18-4cd9-8fe8-affc6652f2c1
Name nvarchar(64), cannot be NULL The name of the OS, displayed in the VMM console when creating or editing a VM FreeBSD 12.0 x64
Description nvarchar(2048), cannot be NULL A description for the OS. I haven't yet figured out where this is shown FreeBSD 12.0 x64
Edition int, can be NULL
Value Used For
0 Windows Server Standard
1 Windows Server Enterprise
2 Windows Server Datacenter
3 Windows Server Web
4 Small Business Server
5 Windows Server Essentials
NULL Everything else
ProductType int, can be NULL
Value Used For
1 Windows Desktops (whether Home, Business, Enterprise)
3 Windows Servers
NULL Everything Else
Version nvarchar(32), can be NULL A suitable version string for the OS - match to the major.minor version if it's significant 12.0
Architecture nvarchar(32), cannot be NULL The CPU platform - I've seen only x86 and AMD64 here AMD64
OSFlags int, cannot be NULL No idea. Seems to be set to 28 for most Linux AMD64 platforms, 17 for Linux x86, but it's 252 for Windows 10 and 764 for Server 2016, so it's probably some sort of bitmasked value and it's inconsistent. 28
VMWareGuestId nvarchar(32), can be NULL This is effectively a foreign key into the list of Guest IDs in the VMWare VCenter API. I used the VMWare API reference for these values freebsd12_64Guest
OSType int, cannot be NULL Looks like this is set to 0 for Windows, 1 for Linux/BSD, and 2 for other stuff (like NetWare) 1

How to Add a New OS

Since this looks like a normal SQL database table, you can add your own entries. Note that you do this at your own risk - I don't know what else might tie into this table (or any stored procedures that reference it). You will want to take precautions - back up the database, understand what you're doing rather than blindly running scripts from the Internet - but in my lab testing it worked well.

Connect to your VMM database server with SQL Management Studio. Create a new query in your VMM database and run a query like this:

INSERT INTO tbl_IL_OS (OSId, Name, Description, Edition, ProductType, Version, Architecture, OSFlags, VMWareGuestID, OSType)
VALUES (NEWID(), 'FreeBSD 12.0 x64', 'FreeBSD 12.0 x64', NULL, NULL, 12.0, 'amd64', 28, 'freebsd12_64Guest', 1)

If this goes well, you should get a result like this:

(1 row(s) affected)

You might need to close and re-open the console to see your new OS in the list of options for a VM.

Add All the Operating Systems

If you're feeling particularly lucky, here's the script I used to create all the OSs to match the VMWare API for version 6.7. It's long, and after running it, the VMM console didn't work. I don't yet know why but hey - have at it. Note that previously I had added a few new OS entries, principally Debian and FreeBSD, and the console was fine, suggesting perhaps some assumptions about the number of OSs in the list, or an array running out of space or something: