Migration Automation Toolkit (MAT) Installation and Usage Guide

General Information

The MAT relies on a number of technologies to work properly. You must have the following installed before the MAT can operate properly:

  • A fully licensed VMware environment running one or more of the following:
    • vCenter Server 5.0
    • vCenter Server 4.1
    • ESXi Server 5.0
    • ESXi/ESX Server 4.1
  • VMware PowerCLI 5.1
  • Microsoft Virtual Machine Converter
  • SQL Server 2008 (or later) Express or other SQL Server Editions
  • Windows Server 2012 (This version of the MAT does NOT work with Windows Server 2012 R2)
  • The MAT package

This document will cover the prerequisites and general installation procedure for the MAT. Detailed installation steps for specific applications may be provided in other documents online.

The MAT Package contains eight items:

  • ConvertVM.ps1
  • ConvertVM-Process.ps1
  • ConvertVM-Logging.ps1
  • ConvertVM-Functions.ps1
  • Variable.XML
  • Setup MAT.sql
  • MAT.Readme.1.5.0.txt
  • MAT FAQ 1.5.0.txt
  • MAT_1_5_Installation_Guide (this document)

Hardware Requirements

Conversion Servers

Control Server (one required) – controls overall conversion workflow and converts VMs

  • 4 vCPU
  • 8 GB RAM
  • Application: 250 MB Disk Free Space
  • Conversion Storage: 250GB (Minimum) / 500GB (Preferred) Disk Free Space

Helper Server (optional) – remote servers which help with conversions

  • 2 vCPU
  • 4 GB RAM
  • Application: 250 MB Disk Free Space
  • Conversion Storage: 250GB (Minimum) / 500GB (Preferred) Disk Free Space

*Servers can be either virtual or physical

Hyper-V

You will need at least one Hyper-V host to accommodate the converted VMs. Right-sizing the Hyper-V environment is something that should be done by MCS or a certified partner in advance of conversion. Generally speaking, you want one Hyper-V host for each ESX server (assuming normal utilization and similar hardware).

Note: Servers are listed here as the minimum requirements. It is possible to increase conversion throughput by having more than one Helper Server installed.

Software Requirements

Control Server

  • Windows Server 2012 (earlier versions will also work)
  • PowerCLI 5.1
  • Microsoft .NET Framework 4.5
  • SQL Express 2012 or SQL Server (earlier versions were not tested but should work)
  • The Microsoft Virtual Machine Converter (MVMC) v1.0
  • The MAT PowerShell scripts
  • The VMlist.txt (this is a product of running the MAT and collecting VM data)

Helper Server

  • Windows Server 2012 (earlier versions will also work)
  • The Microsoft Virtual Machine Converter (MVMC) v1.0
  • Microsoft .NET Framework 4.5
  • The MAT PowerShell scripts
  • PowerCLI 5.1 (if using MigrateNICs)

Guest Virtual Machine

  • In order to use the NIC features of the MAT, UAC must be disabled in the guest VM.

Supported Configurations

Any combination of the following is supported using either the MAT or the MVMC:

VMware sources

  • vCenter Server 5.0
  • vCenter Server 4.1
  • ESXi Server 5.0
  • ESXi/ESX Server 4.1

Note: MVMC supports ESXi/ESX 4.0 if the host is managed by vCenter 4.1 or vCenter 5.0. In this case, you must connect to vCenter 4.1 or 5.0 through MVMC to convert virtual machines on the 4.0 hosts.

Destination Host Server

  • Hyper-V on Windows Server 2008 R2 SP1 Standard
  • Hyper-V on Windows Server 2008 R2 SP1 Enterprise
  • Hyper-V on Windows Server 2008 R2 SP1 Datacenter
  • Hyper-V on Windows Server 2012
  • Microsoft Hyper-V Server 2008 R2
  • Microsoft Hyper-V Server 2012

Guest operating systems supported for conversion

  • Windows Server 2003 Standard Edition with SP2 x86
  • Windows Server 2003 Standard Edition with SP2 x64
  • Windows Server 2003 Enterprise Edition with SP2 x86
  • Windows Server 2003 Enterprise Edition with SP2 x64
  • Windows Server 2003 R2 Enterprise Edition with SP2 x86
  • Windows Server 2003 R2 Enterprise Edition with SP2 x64
  • Windows Server 2003 R2 Standard Edition with SP2 x86
  • Windows Server 2003 R2 Standard Edition with SP2 x64
  • Windows Vista Enterprise x64
  • Windows Vista Enterprise x32
  • Windows 7 Enterprise x86
  • Windows 7 Enterprise x64
  • Windows 7 Professional x86
  • Windows 7 Professional x64
  • Windows 7 Ultimate x86
  • Windows 7 Ultimate x64
  • Windows Server 2008 Enterprise x86
  • Windows Server 2008 Enterprise x64
  • Windows Server 2008 Datacenter x86
  • Windows Server 2008 Datacenter x64
  • Windows Server 2008 R2 Standard
  • Windows Server 2008 R2 Enterprise x64
  • Windows Server 2008 R2 Datacenter x64

Supported Configurations for Disk Conversion

The following VMware virtual disk types are supported for conversion:

  • monolithicSparse
  • vmfsSparse
  • monolithicFlat
  • vmfs
  • twoGbMaxExtentSparse
  • twoGbMaxExtentFlat
  • delta disk conversion
  • Stream optimized disks

Assumptions

MVMC will successfully perform virtual machine conversions when the following conditions are met:

  • The virtual machine to be converted is in a running state.
  • The virtual machine has VMware tools installed.
  • The virtual machine is joined to an Active Directory® domain.
  • Remote access through Windows Management Instrumentation (WMI) is enabled on the VMware-based virtual machine to be converted and the destination Hyper-V host. See the "Error! Reference source not found." section in this guide for more details.
  • The account used for connecting to the VMware-based virtual machine that needs to be converted is part of an Active Directory domain and also a local administrator on that machine.
  • You have the correct credentials to connect to the required environments.
  • The Windows user account that you are using has write access to the UNC path specified on the destination Hyper-V host for copying the virtual hard disks.
  • The Hyper-V host has the required disk space available for the converted virtual hard disks.

The following assumptions are valid after a successful conversion:

  • The destination virtual machine will be in a started or stopped state depending on the settings chosen by the user.
  • Once the virtual disks attached to the virtual machine are copied successfully to the converter machine, the source virtual machine will be restored to a started or stopped state, depending on the settings chosen by the user.
  • Product activation requires each instance of a Windows operating system installation to be activated with Microsoft. Because conversion creates a second instance of the virtual machine on Hyper-V, this instance needs to be activated.

Upgrade Information

Upgrades from earlier versions of the MAT are not supported.

Architecture Diagram

The following shows the basic architecture and components for the MAT:

Installing Individual Software Components

Install SQL 2008 Express or later

Install SQL 2008 or R2 on ServerA following the standard installation procedure

You can install either SQL 2008 or R2 or 2012.

Install Microsoft Virtual Machine Converter v1.0

  1. Download the Microsoft Virtual Machine Converter Solution v1.0 Accelerator MSI to your local machine.
  2. Launch the "Microsoft Virtual Machine Converter Solution Accelerator.msi" installer package.
  3. If you accept the terms of the License Agreement, check the box and click Next.
  4. Assign a destination folder. The default of C:\Program Files (x86)\Microsoft Virtual Machine Converter Solution Accelerator\ is valid. Click Next.

  1. Click Install to begin the installation.
  2. When the User Account Control dialog appears, select Yes.

  1. Click Finish to complete the installation

Install vSphere PowerCLI 5.1

Install the vSphere PowerCLI 5.1 on the Control Server following the standard installation procedure.

Install the Migration Automation Toolkit

Create a MAT folder

  1. Create a folder named C:\MAT

NOTE: You can choose any name or folder destination provided it does not contain any spaces.

Unpack the toolkit

The installer should contain eight files

  • ConvertVM.ps1
  • ConvertVM-Process.ps1
  • ConvertVM-Logging.ps1
  • ConvertVM-Functions.ps1
  • Variable.XML
  • MAT.Readme.1.5.txt
  • MAT FAQ 1.5.txt
  • MAT_1_5_Installation_Guide (this document)

Create a share for the Target Host

  1. On the Hyper-V host you have selected to accept the converted VMs, select or create a folder to house the vhd files that are created by MVMC and copied to the target host. 2. Right-click the target folder then select Share with and Advanced Sharing…

  1. Select Advanced Sharing…

  1. Check the box to Share this folder (this document assumed you used the default Share name Share)
  2. Click Permissions

  1. Add the account you use for conversion and give it Change rights

Run the Setup MAT.sql script

  1. Launch SQL Server Management Studio

  1. Set Server Name to localhost and click Connect
  2. From the File menu, select Open and then File (or press Ctrl-O)
  3. Navigate to C:\MAT
  4. Select Setup MAT.sql and click Open.
  5. Verify that the current context is master, then Execute the script by clicking the Execute button.

  1. The script should produce the following output:

Creating the MAT Database v1.5.0

MAT Database created.

(1 row(s) affected) Version Table created.

VMQueue Table created.

GlobalSettings Table created.

Populating GlobalSettings Table...

(1 row(s) affected)

VMConversionData_PS

Stored Proceedure sp_TransferVMConversionData_PS created.

MAT Database Setup Complete.

NOTE: This script only needs to be run once. The script is destructive and will drop any tables that exist and recreate them, thereby removing all conversion related data. This will reset your environment to a clean install state.

Configure MAT Settings

  1. Open the Variable.xml file and supply the correct parameters for all the required XML values. You should supply values in the following sections:

NOTE: Take great care when editing the XML file that you do not alter the XML formatting. Ideally use an XML editor.

General

  1. Set the value Logpath to the temp path for the user which will be running the conversions.
  2. Replace the value (username) with the appropriate value. For example: C:\Users\administrator\AppData\Local\Temp
  3. Set the value Datasource to the name of the SQL Server you are using or the name of the default instance in you are using names instances.
  4. Set the MigrateNICs value to either 1 or 0. 1 = Migrate NICs to Hyper-V, 0 = do not migrate nics. If you enable this option, you must set sPower = 0.
  5. If you wish to alter the behavior of the actions Show-Status or Show-StatusLoop you can change the value of Showrows. This value limits how many rows are returned by these two actions.

<General>

<Variable Name="XMLVersion" Value="1.5.0" />

<Variable Name="Logpath" Value="C:\Users\(username)\AppData\Local\Temp\" />

<Variable Name="Datasource" Value="SERVER1"/>

<Variable Name="MigrateNICs" Value="1"/>

<Variable Name="Showrows " Value="30"/>

</General>

SchedCred

(If you are not using Helper Servers you can ignore this section)

  1. Set the value WinAccount to an account which has the rights to schedule and execute tasks as an admin on the remote system.
  2. Set the value WinPassword to the password of the account used in WinAccount.

<SchedCreds>

<Variable Name="WinAccount" Value="contoso\administrator" />

<Variable Name="WinPassword" Value="passsword" />

</SchedCreds>

HyperV

  1. Set the value tpath to the share you created in section 10.3
  2. Set the value thost to name of the Hyper-V server which will accept and host (at least temporarily) the converted VMs.
  3. Set the value sPower to either 1 or 0. The value controls the final power state of the Source VM. 1 = on 0 = off
  4. Set the value tPower to either 1 or 0. The value controls the final power state of the Target VM. 1 = on 0 = off
  5. Set the value DyMac to either 1 or 0. The value controls the whether or not Hyper-V dynamically assigns a MAC address or if it uses the one assigned by VMware. 1 = Use a dynamic address, 0 = use the VMware MAC address (This setting only applies if MigrateNICs = 1)
  6. Set the value dynamicdisk to either 1 or 0. The value whether the Hyper-V VM will use dynamic or fixed disks. 1 = dynamic 0 = fixed

<HyperV>

<Variable Name="tpath" Value="\\SERVER1\Share" />

<Variable Name="thost" Value="SERVER1" />

<Variable Name="sPower" Value="1" />

<Variable Name="tPower" Value="0" />

<Variable Name="DynMac" Value="0" />

<Variable Name="dynamicdisk" Value="1" />

</HyperV>

VMware

  1. Set the value sHost to the FQDN or IP address of your VMware source host (ESX or vSphere)
  2. Set the value shusername to an account which has admin rights to the source host
  3. Set the value shpwd to the password of the shusername account
  4. Set the value gUser to an account which has admin rights to the Guest VM. This account is used to remove VMware Tools. This can be a domain or local account
  5. Set the value gPwd to the password of the gUser account

<VMware>

<Variable Name="shost" Value="10.10.10.1" />

<Variable Name="shusername" Value="contoso\administrator" />

<Variable Name="shpwd" Value="passsword" />

<Variable Name="gUser" Value="contoso\administrator" />

<Variable Name="gPwd" Value="passsword" />

</VMware>

RemoteHost

If you plan to use Helper Servers, add a line for each server using the following format. The following example shows three Helper Servers named Server1, Server2 and Server3.

<RemoteHost>

<ConvertServer>SERVER1</ConvertServer>

<ConvertServer>SERVER2</ConvertServer>

<ConvertServer>SERVER3</ConvertServer>

</RemoteHost>

Review the value for Queuelength which is stored near the top of ConvertVM.ps1 in the Variables section. The default is set to 2, which limits the total number of concurrent conversions per server to 2. If you wish, you can raise this value to 3 or lower it to 1. Raising the value above 3 is not supported as the MAT only allows for three logs to be creates simultaneously.

###### Variables ######

$Queuelength = 3 ### Do not set Queuelength above 3 ###

$CurrentPath = "C:\MAT"

$XMLPath = "C:\MAT\Variable.xml"

$VMList = "$CurrentPath\VMlist.txt"

$VerboseLogging = 3

$MaxCapacityLoopTimer = 60

$Lookback = 1

NOTE: If you altered the default path from C:\MAT during installation, you need to update the values for $CurrentPath and $XMLPath in the Variables section of ConvertVM.ps1.

Install MAT on Helper Servers (Optional)

If you intend to run conversions on more than one server, each Helper server needs to be configured to participate in the MAT conversions

  1. Install the MVMC package on your Helper(s) following the same steps as in Section 8.
  2. Verify your credentials in the <SchedCreds> section in Variable.xml
  3. Add one <ConvertServer>HELPERx</ConvertServer> to the <RemoteHost> section of the Variable.xml file for each Helper you wish to use.
  4. Copy the MAT files to your Helper(s) in C:\MAT (assuming you haven't edited that variable in ConvertVM.ps1)
  5. Verify your SQL server is configured to accept remote connections
  6. Log into each Helper using the account assigned in <SchedCreds>.

NOTE: You must be logged into each Helper Server in order for the remote to execute properly.

The easiest way to do this is to connect to the remote via RDP and then disconnect your session. Any remote session, even a disconnected one will allow the MAT to run properly. The MVMC.exe will not run via Task Manager if the user profile is not loaded, since it requires access to the user's temp folder.

  1. If you plan to use the RemoveCD, RemoveFloppy or MigrateNICs function, you will need to install PowerCLI on Helpers.

Collect VM information

NOTE: All MAT functions are exposed as parameters and can be viewed using tab-complete within PowerShell

  1. From a PowerShell prompt run .\ConvertVM.ps1 Collect

OR

  1. To start the MAT run .\ConvertVM.ps1 in PowerShell. Run this as an administrator.
  2. On first use, run a collection cycle using the [L] Collect VMs option.

In large environments collection may takes minutes or even hours. All VMs collected will be displayed briefly as they are written to the database.

List Management

  1. From a PowerShell prompt run .\ConvertVM.ps1 Create-List
  2. Edit the list as described below
  3. Run .\ConvertVM.ps1 Update

OR

  1. After you have completed collection, select [M]anage List
  2. Select [C]reate VMList and the script will create a VMList.txt file in your MAT folder.

All available VMs which are valid for conversion will be added to this file. Notepad will automatically open this file.

Edit the list so that it only contains the names of VMs you want to convert in one batch. A batch is a series of conversions that will run continuously until all the VMs have been converted (or failed). It is best to limit this to a reasonable value of no more than 30 VMs. If you are just getting started, begin with a small list. You can re-create the VMlist as often as you wish.

As you successfully convert VMs they will no longer appear in the VMList, so there is not risk of converting a VM that is already done. If you want, you can reorder this list and this will affect the order in which VMs are converted. The first VM listed will be the first VM converted.

NOTE: If you use Helper Servers, they will take VMs from the list in the order they appear and Helper Servers access the list before the local machine (Control Server) does.

Save the list.

Select the [U]pdate DB option. This will update the records in the database to reflect the current list.

If you wish, you can use the [D]isplay VMs option to confirm the changes were updated.

Conversion

Once the VMList has been prepared and the database has been updated, you can begin your conversion. The VMs listed will be the only ones converted by this 'batch'.

  1. From a PowerShell prompt run .\ConvertVM.ps1 Convert

OR

  1. To start the MAT run ConvertVM.ps1 in PowerShell. Run this as an administrator.
  2. At the [M]ain menu select [C]onvert.

As the conversion begins, all the VMs that will be converted in this batch will be listed. If Helper Servers are used, they will start after the list of VMs is displayed. Once all Helper Servers have been serviced, the local conversion will start on the Control Server.

Monitoring Conversions

Conversions happen without interaction on both the Control Server and any Helper Servers. This can make it difficult to assess the state of current conversions. For this reason, two monitoring functions are included with the MAT. One function will to give an immediate status and exit; the other will run continuously until the user stops it by issuing Control-C

The following Actions can be run at the MAT command line by passing the Action as the first parameter.

Syntax: PS> .\ConvertVM.ps1 <action> (optional)

  1. Show-Status - Displays the Status table once and exits
  2. Show-StatusLoop - Displays the Status table in an endless loop refreshing every 5 seconds, and then supplying the instructions to exit after another 5 seconds. The total refresh duration for data is 10 seconds.

The total number of records displayed in the view will be 30 by default. If you wish to change this value, alter the $Showrows value in Variable.xml.

Items are collected from the SQL table using a TOP function, ordered by Position (which was set when you updated the database from the VMList.txt file.

By default the Show-Status Actions will show data for any active conversion and any conversion that completed within the last hour. If you want to alter this behavior you can change the value of $Lookback in ConvertVM.ps1.

If a VM shows Warning = True, then some part of the conversion did not work properly. See the log for more details.

For more information, see the PowerShell Parameter and Variable Reference section.

The PrepareVM function

A new function in version 1.5, the PrepareVM function prepares the VM for conversion and provides the following services (all of which are optional)

  • Removing or unmounting all CD\DVDs from the VM
  • Removing all floppy drives from the VM
  • Capturing and recreating the network interface settings for the VM

Removing CD\DVD

By default the MAT will unmounts any optical media from the VM before conversion.

If you wish to disable this function, edit line 38 of ConvertVM.ps1 to match the following:

$RemoveCD = 0

Removing Floppy disks

By default the MAT will remove any floppy disks from the VM before conversion.

If you wish to disable this function, edit line 39 of ConvertVM.ps1 to match the following:

$RemoveFloppy = 0

Network Interface Card information capture and restore

By default, the MAT will store and recreate the information from up to three (3) network cards on a VM. The information captured and restored is:

  • Static IP addresses
  • DNS information
  • MAC addresses
  • VLAN Tagging

If you wish to disable this function, edit line 9 of Variable.XML to match the following:

<Variable Name="MigrateNICs" Value="0"/>

NOTE: In order to use the NIC features of the MAT, User Account Control (UAC) must be disabled in the guest VM.

How the MigrateNICs function works

The MAT uses PowerCLI to send scripts to the target VM which enable the VM to collect it's own networking information and store this until after the migration.

Because of limits in the size of script blocks that can be sent to the VM, this is done in five steps. These steps create the following files which are run during the final pre-conversion shutdown and at the first post-conversion reboot.

Step 1

  • Creates C:\Windows\Temp\nicCreds.txt (deleted in Step 2)

Step 2

  • Sets HKLM:\Software\Microsoft\Windows\CurrentVersion\RunOnce\ConfigureServer = C:\Windows\Temp\nicRestore.cmd
  • Sets HKLM:\Software\Microsoft\Windows NT\CurrentVersion\Winlogon\DefaultUserName = (defined username)
  • Sets HKLM:\Software\Microsoft\Windows NT\CurrentVersion\Winlogon\DefaultPassword = (defined password)
  • Sets HKLM:\Software\Microsoft\Windows NT\CurrentVersion\Winlogon\AutoAdminLogon = 1
  • Sets HKLM:\Software\Microsoft\Windows NT\CurrentVersion\Winlogon\DefaultDomainName = (defined domain)
  • Delete C:\Windows\Temp\nicCreds.txt
  • Creates C:\Windows\Temp\nicRestore.cmd (encoded)
  • Creates C:\Windows\Temp\nicConfig.xml
  • Creates C:\Windows\Temp\nicDNS.xml
  • Creates C:\Windows\Temp\nicConfig.txt

Step 3

  • Backs up existing shutdown scripts (if they exist)
  • Creates C:\Windows\System32\GroupPolicy\Machine\Scripts\Shutdown\MATcleanup.ps1, which sets nics to DHCP and appends _VMware to the name of each and restores any shutdown scripts that were backed up. This file contains the following code:

# You should not see this file. It is used during virtual machine migration. # Consult the MAT Installation and Usage guide for more info.

Remove-Item 'HKLM:\SOFTWARE\Microsoft\Windows\CurrentVersion\Group Policy\Scripts\Shutdown' -Recurse

Remove-Item 'HKLM:\SOFTWARE\Microsoft\Windows\CurrentVersion\Group Policy\State\Machine\Scripts\Shutdown' -Recurse Reg Import C:\Windows\Temp\gposcriptsShutdown.reg Reg Import C:\Windows\Temp\gpostateShutdown.reg

Remove-Item C:\Windows\Temp\gposcriptsShutdown.reg

Remove-Item C:\Windows\Temp\gpostateShutdown.reg

Remove-Item C:\Windows\System32\GroupPolicy\Machine\Scripts\Shutdown\MATcleanup.ps1

gwmi win32_NetworkAdapterConfiguration | ? {! $_.DHCPEnabled -and $_.IPAddress } | %{ $_.EnableDHCP()}

$NetworkConnections = (New-Object -com shell.application).Namespace(0x31)

Foreach ($NIC in (gwmi win32_NetworkAdapter | where { $_.NetConnectionID }|Select-Object -

ExpandProperty NetConnectionID))

{$NetworkConnections.Items() |Where-Object {$_.Name -eq $NIC} |ForEach-Object { $_.Name="$($_.Name)_VMware"}

}

Step 4

  • Creates and populates HKLM:\SOFTWARE\Microsoft\Windows\CurrentVersion\Group Policy\Scripts\Shutdown\0
  • Creates and populates HKLM:\SOFTWARE\Microsoft\Windows\CurrentVersion\Group Policy\Scripts\Shutdown\0\0

Step 5

  • Creates and populates HKLM:\SOFTWARE\Microsoft\Windows\CurrentVersion\Group Policy\State\Machine\Scripts\Shutdown\0
  • Creates and populates HKLM:\SOFTWARE\Microsoft\Windows\CurrentVersion\Group Policy\State\Machine\Scripts\Shutdown\0\0

NOTE: Only the files created in C:\Windows\Temp\ should persist after reboot. These files contain your network information and are not deleted in the event that you need this information. If you need to restore this information because of a problem with the MAT (on either the Source or Target VM) run C:\Windows\Temp\nicRestore.cmd . Otherwise these files can be deleted safely.

If you find that C:\Windows\System32\GroupPolicy\Machine\Scripts\Shutdown\MATcleanup.ps1 exists after conversion, you can run this file manually to reset any GPO-based shutdown scripts and remove any other artifacts of the conversion.

Reporting

The MAT provides reporting in the form of Comma Separated Values (CSV) files. These reports are designed to be used by someone with no SQL experience. Additional reports may be provided in the future or end users can generate their own based on the SQL schema.

The following Actions can be run at the MAT command line by passing the Action as the first parameter.

NOTE: All Reports will be saved in C:\MAT\Reports (unless you have modified the location of MAT)

Syntax: PS> .\ConvertVM.ps1 <action> (optional)

  1. Report-All - Generates a CSV file with all records from the database. The file will be named Alldata.csv
  2. Report-Complete - Generates a CSV file with all completed VMs from the database. The file will be named Completed.csv
  3. Report-Incomplete - Generates a CSV file with all incomplete Vms from the database. The file will be named Incomplete.csv
  4. Report-Unsupported - Generates a CSV file with all unsupported VMs from the database. The file will be named Notsupported.csv

Report output will look like this:

"Summary","ID","ConvertServer","Supported","Notes","ReadytoConvert","Completed","Statu s","VMName","StartTime","PID","EndTime"

"Successful Conversion","85","","1","VM automatically collected by script","False","True","1","clcs-0002.achilles.com","4/9/2013 9:20:21 AM","","4/9/2013 9:43:06 AM"

"Ready to Convert","86","SCDEMO099","1","VM automatically collected by script","True","False","0","clcx-0001.achilles.com","4/9/2013 8:56:07 AM","",""

"Successful Conversion","87","","1","VM automatically collected by script","False","True","1","clcx-0003.achilles.com","4/9/2013 9:04:24 AM","","4/9/2013 10:03:12 AM"

"Successful Conversion","88","","1","VM automatically collected by script","False","True","1","clcx-0004.achilles.com","4/9/2013 9:04:34 AM","","4/9/2013 10:03:11 AM"

Each report type will follow the same field structure (found on line 1) which is: "Summary","ID","ConvertServer","Supported","Notes","ReadytoConvert","Completed","Status", "VMName","StartTime","PID","EndTime"

Database Maintenance

The MAT provides some tools to manage and maintain the MAT database. These tools are designed to be used by someone with no SQL experience. These tasks can also be performed with SQL but direct manipulation of the database can lead to corrupted data and is not recommended.

The following Actions can be run at the MAT command line by passing the Action as the first parameter.

Syntax: PS> .\ConvertVM.ps1 <action> (optional)

  1. Reset-List – This will reset the VM record for any VM listed in C:\MAT\VMlist.txt. Reset records will have the following fields updated: ReadytoConvert = 0, ConvertServer = NULL, InUse = NULL, Position = NULL, Starttime = NULL, EndTime = NULL, Status = 0, Summary = NULL, Notes = 'Record reset by Manage.ps1'
  2. Reset-Incomplete - Resets every incomplete VM record in the database. Reset records will have the following fields updated: ReadytoConvert = 0, ConvertServer = NULL, InUse = NULL, Position = NULL, Starttime = NULL, EndTime = NULL, Status = 0, Summary = NULL, Notes = 'Record reset by Manage.ps1'
  3. Reset-All - Resets EVERY VM record in the database. Reset records will have the following fields updated: ReadytoConvert = 0, ConvertServer = NULL, InUse = NULL, Position = NULL, Starttime = NULL, EndTime = NULL, Status = 0, Summary = NULL, Notes = 'Record reset by Manage.ps1'
  4. Purge – This option will delete ALL records from database. Use caution when issuing this command as it is irrevocable. You will be prompted to confirm deletion before continuing.

Duplicate Conversions

The MAT provides checks to keep from duplicating a conversion, which is to say converting a VM that has already been converted.

However if you encounter a situation where the same VM is converted twice, the situation is not destructive. The second VM will be created with an incremental value appended to the end of the name. You will see this on the file system and in Hyper-V Manager. Simply delete one of the VMs, presumably the newer conversion, and continue with your migration.

Interrupting a Conversion

In some situations, you may need to interrupt a conversion. Either because something has gone wrong, or the conversion is taking an unexpectedly long time and may exceed your service window.

To interrupt a running conversion take the following steps.

Run .\ConvertVM.ps1 Show-Status – Review the output to see which conversion(s) you wish to stop. Locate the PID value and the ConvertServer value for that record.

Logon to the appropriate machine listed in the ConvertServer field.

Kill the PID (Process ID) for the conversion you wish to terminate. From a command prompt type "taskkill /PID 000" where 000 is the value from the PID field in the desired record.

NOTE: Interrupting a conversion using this method will leave the VM record in an invalid state.

Restart the VM source as required.

Update the VMList.txt so that it only contains the names of the VMs you have interrupted.

Run.\ConvertVM.ps1 Reset-List which will reset the VM records for all the VMs you listed in VMList.txt.

Locate any temporary files that were created by the MVMC process and delete them. They are found in C:\MAT\MVMC and may be in a subdirectory under the user's temp folder.

Delete the log files associated with the VM conversion.

Advanced Concepts

Overall PowerShell workflow

When you start a set of conversions from the .\ConvertVM.ps1 menu (and then select Convert) or if you launch the script with the Convert-Global action the process will read the Variable.xml file and load all the <ConvertServer> values you have added. The processes run by the Control Server and the Helper Servers are listed below:

Control Server

  1. On the Control Server, the process starts by validating the format of the Variable.xml file. Then it will check to see if the Variable.xml contains entries for Helper Servers (i.e. if you added them). If Helpers are found, it will create a list of the servers and iterate through them in the order they appear in the xml file. The process will create a new job on the Helper server(s) using the task scheduler and immediately start that job. Once all the remotes have been serviced, the local Convert function will be triggered.
  2. The Convert function will first call CheckSemaphore. CheckSemaphore will check the server's semaphore counter. It does this by calling SQL to see how many conversions are listed for that machine (fieldname = Queuelength). As long as the value of the semaphore is greater than the Queuelength the process continues. If it is not, the server is considered to have reached capacity and no more conversions will be launched until at least one of the existing conversions completes (successfully or otherwise). The process will loop for the number of seconds specified by the $MaxCapacityLoopTimer variable, doing so until one of the current conversions finishes and thereby freeing up a 'slot'.
  3. Next the process checks for unassigned VMs (ConvertServer = NULL) from the database, which are marked for conversion (fieldname = ReadyToConvert). If the query returns one or more, it assigns one VM (using GetNextVM) to itself and thereby increments the Queuelength by one. VMs are taken using a TOP 1 SQL call but they are ordered by 'Priority' which is established when (or if) you re-order the VMlist.txt. If no more VMs are found in the table, the batch is complete and the script will exit.
  4. The next stage is Convert. If you have set the values to remove CD, floppies and to
  5. MigrateNICs, these steps will happen before the conversion is started. The script will call the ConvertVM-Process.ps1 using the Start-Process cmdlet. As each server starts to process a VM for conversion it updates the record's ConvertServer field with its localhost name, so that no other server will attempt to convert the same machine. The time between the two steps is usually less than one second and delays exist in other locations to minimize the possibility of a record collision. It may seem odd that the script will start another process but this is done for several reasons. First is to let one ConvertVM-Process.ps1 run for every MVMC.exe that runs. Ideally, this would have been done with PowerShell v3 workflow but some of the vagaries of the MVMC.exe made this approach more practical. This approach also allows you to extend the ConvertVM-Process.ps1 without messing with the overall flow.
  6. Once ConvertVM-Process.ps1 is called, the script starts back over at the Convert function (admittedly somewhat confusing naming) and the process continues anew.
  7. While the MAT workflow has looped and moved one, the ConvertVM-Process.ps1 will call ConvertVM-Logging.ps1 (assuming $LogOption = 1). One ConvertVM-Logging.ps1 is called for every MVMC.exe (both tracking the conversion by its Process ID) and the job of ConvertVMLogging.ps1 is to watch the contents of mvmc.log (or mvmc1.log, or mvmc2.log…) and 'translate' the messages there into more human readable information. It will then write that information to the main log (and the database in case you are watching using the Show-Status functions). Ideally the process would capture the stdout messages from the MVMC.exe, but it doesn't actually make any, so we must rely on ConvertVM-Logging.ps1 to parse the log.
  8. When ConvertVM-Logging.ps1 sees that the PID for its particular MVMC.exe exits, it will spin down and exit too. Shortly thereafter ConvertVM-Process.ps1 will exit too. ConvertVMProcess.ps1 sees that the monitored PID exits and then it 'waits' for ConvertVM-Logging.ps1 before closing. It does this by waiting X seconds, where X is the value of $LogMonitorDelay multiplied by the value of the $SleepMulitplier (both set in ConvertVM-Process.ps1). The default delay is 39 seconds (30 x 1.5.0).

Helper Server

The helper servers are started via a Task Scheduler Job using the command " .\ConvertVM.ps1 Convert". The Convert parameter bypasses the remote logic and jumps directly into the Convert function where it checks the $Queuelength and starts the process identically to the Control Server at step 2.

MAT Extensibility

MAT was designed to be fully extensible for users. If you want to extend the functionality of the

MAT for any post-conversion functions, the right place to do this is at the end of the ConvertVMProcess.ps1 script. Because ConvertVM-Process.ps1 runs in parallel with the conversion as a whole, any commands would be run specifically against the target conversion and would not delay the overall process.

NOTE: Extensions could also be added between the GetNextVM and Convert functions but this isn't as desirable as it will slow your conversion flow down. Adding it to ConvertVM-Process.ps1 leaves the overall workflow unchanged and is least likely to be disrupted by future versions of the MAT.

Below are the final lines of the ConvertVM-Process.ps1 script, ideal placement of additional tasks is listed.

###### Start ######

write-log 3 "-------Starting Conversion"

$time = get-date

SQLWrite "UPDATE VMQueue SET [Summary] = 'Starting Conversion',

[StartTime] = '$time' WHERE JobID = $ID"

DoConvert

Your code goes here

Extensions which hold the queue until finished

If you want to hold execution of additional VMs while your post-processing is running, you should move the following two lines from the end of the DoConvert function and place them at the end of your additions. This will keep the $Queuelength from decrementing before your tasks complete.

###### Start ######

write-log 3 "-------Starting Conversion"

$time = get-date

SQLWrite "UPDATE VMQueue SET [Summary] = 'Starting Conversion',

[StartTime] = '$time' WHERE JobID = $ID"

DoConvert

Your code goes here

Write-log 1 "Done with Conversion."

SQLWrite "UPDATE VMQueue SET InUse = NULL, ConvertServer = NULL WHERE JobID = $ID"

Shared Logs

It is possible to alter the MAT so that in situation where multiple Helper Servers are in use, they will produce a single MAT.log file.

To do this, edit the value for <Variable Name="Logpath"> (found in the general section of variable.xml) and point it to a shared folder.

WARNING: Using a single log file in a multi-server environment can produce a number of issues. First, if you have a large number of conversions running, the log can be a challenge to read.

Although each log entry will identify the convert server and VM, the data can be overwhelming.

Additionally there is a chance you will have file contention issues when the Helpers write to the log file. If you plan to use more than two Helper servers to the same log file, test it carefully beforehand.

For the reasons stated above, it is recommended that you leave the MAT.log on each Helper.

Shared XML

Like the shared log scenario above, it is possible to alter the MAT so that in situation where multiple Helper Servers are in use, they will read a single XML file.

To do this, share your C:\MAT folder (or whatever location you chose) edit the value for $XMLPath = "C:\MAT\Variable.xml" (found at line 21 of ConvertVM.ps1) and point it to your shared XML file.

Although only limited testing was done using a shared XML file, there were no file contention issues detected since the XML file is only read once when the each server begins to process its batch.

NOTE: You can only use the shared XML model if all your accounts are identical, including the account used to remove VMware tools. Also be sure to secure this file if you share it, since it contains sensitive information.

Balancing the load

As documented above it is possible to use a common XML file and in some situations common logging. However, this approach is not always ideal.

One Hyper-V host for each Helper

If you are running at a large scale you run the risk of overloading your Hyper-V host (as all Helpers will be sending data to it) both in terms on network traffic and storage. A better approach is to customize each Helper server's XML file (tpath) so that it has a dedicated HyperV server that it uses. For example, "Helper1" points to "Hyper-VHost1", "Helper2" points to "Hyper-VHost2" and so on.

This paired approach limits the total number of VMs being written to each Hyper-V host and will improve your overall performance. Your Hyper-V hosts should be able to easily accommodate the maximum of three VMs being sent to it by its Helper. Following this approach, you should be able to scale out Helpers limited only by your Hyper-V hosts that will accept the VMs.

Additional Functions

The ConvertVM.ps1 script also supports a number of additional functions that can be activated by passing the appropriate parameter to the script. These options are below:

  • Syntax: PS> .\ConvertVM.ps1 <action> (optional) <delay in seconds> (optional)

Actions

  • Convert - Starts Conversion locally. (Used on Remote servers)
  • Convert-Global - Starts Conversion on Remotes and locally
  • Create-List - Creates a VMList.txt based on unconverted VMs in database
  • Help - Shows the Help menu
  • Menu - Starts Main Menu <Default>
  • Purge - Deletes ALL records from database
  • Report-All - Generates a CSV file with all records from the database
  • Report-Complete - Generates a CSV file with all completed VMs from the database
  • Report-Incomplete - Generates a CSV file with all incomplete Vms from the database
  • Report-Unsupported - Generates a CSV file with all unsupported VMs from the database
  • Report-Warning - Generates a CSV file with all VMs that produced warnings during conversion
  • Reset-All - Resets EVERY VM record in the database
  • Reset-Incomplete - Resets every incomplete VM record in the database
  • Reset-List - Resets only those VM records listed in C:\MAT\VMlist.txt
  • Show-Status - Displays the Status table once
  • Show-StatusLoop - Displays the Status table in an endless loop
  • Update - Updates the records for all VMs in VMList.txt marking them as "Ready to Convert"

Start Delay

  • <Delay in seconds> [int] Delays the start time of conversions (default is 0) These options can also be viewed using .\ConvertVM.ps1 Help

PowerShell Parameter and Variable Reference

ConvertVM.ps1

Parameters

  • [string]$Task, - option to call any of the Actions supported by MAT. The default action is "Menu"
  • [string]$DelayTimer, - Delays the start of the batch of conversions by the specified value (in seconds)

Variables

  • $Queuelength = 2 - Total number of concurrent conversional allowed on local machine, values above 3 are not supported.
  • $CurrentPath = "C:\MAT" – Folder location of the MAT files.
  • $XMLPath = "C:\MAT\Variable.xml" – Full path of the variable.xml file.
  • $VMList = "$CurrentPath\VMlist.txt" – full path of the VMList.txt file.
  • $VerboseLogging = 3 – Logging level. 1 = minimum, 3 = normal, 7 = verbose
  • $MaxCapacityLoopTimer = 60 – Length of sleep delay between checks if
  • $Semaphore -ge $Queuelength
  • $Lookback = 1 – Used by ShowStatus, limits entries displayed to those which have finished in the last ($Lookback) hours. Default is 1.
  • $BuildVersion = "1.5.0 " – Current build version – should be consistent across all files in MAT
  • $Catalog = "MAT" – Database catalog for MAT, default = MAT
  • $RemoteJobName = "MAT" – Name of Task Scheduler job created on Helper Servers
  • $TaskArgues = "$CurrentPath\ConvertVM.ps1 Convert" – Argument passed to Helper apps to kick off the MAT remotely
  • $Computername = Get-Childitem env:computername – Gets the name of the local machine for use with $localhost
  • $localhost = $Computername.Value – Sets $localhost name as name of computer running the script.
  • $Preflight = 1 - Enables or disables preflight checks. 1 = True
  • $RemoveCD = 1 – Defines whether or not all CD\DVD media is unmounted before conversion. 1 = True
  • $RemoveFloppy = 1 – Defines whether or not all floppy disks are removed before conversion. 1 = True

ConvertVM-Process.ps1

Parameters

  • [string]$ID, - Record ID from the database table VMQueue, field = JobID
  • [string]$DataSource, - location of the database or instance
  • [String]$Catalog, - Database catalog, by default = MAT
  • [string]$MATLog, - location of the main log produced by the MAT
  • [string]$VMName, - Name of the VM being converted, from field = VMName
  • [string]$Logpath, - location of folder path for logging
  • [string]$CurrentPath, - use to abbreviate execution path of ps1
  • [int]$VerboseLogging – Level of logging verbosity, currently 1,3 or 7
  • [string]$tPower, - value of Target VM power option
  • [string]$sPower, - value of Source VM power option
  • [String]$XMLPath – location of the Variable.xml file
  • [String]$MigrateNICs – value of MigrateNICs in variable.xml file

Variables

  • $LogOption = 1 – binary, whether or not ConvertVM-Logging.ps1 is invoked. 1= on.
  • $LogMonitorDelay = 30 - duration of sleep issued between log checks (passed to ConvertVM-Logging.ps1)
  • $SleepMulitplier = 1.5.0 – value multiplied by $LogMonitorDelay to provide a sleep value to allowing ConvertVM-Logging.ps1 to finish running before ConvertVM-Process.ps1 writes final database entry for active VM.
  • $Computername = Get-Childitem env:computername – Gets the name of the local machine for use with $localhost
  • $localhost = $Computername.Value – Sets $localhost name as name of computer running the script.
  • $fpath = "C:\Program Files (x86)\Microsoft Virtual Machine Converter Solution Accelerator\MVMC.exe" – Sets path of MVMC.exe

ConvertVM-Logging.ps1

Parameters

  • [string]$ID, - Record ID from the database table VMQueue, field = JobID
  • [string]$DataSource, - location of the database or instance
  • [String]$Catalog, - Database catalog, by default = MAT
  • [string]$mvmclog, - location of the log produced by the MVMC.exe
  • [string]$MATLog, - location of the main log produced by the MAT
  • [string]$VMName, - Name of the VM being converted, from field = VMName
  • [int]$LogMonitorDelay, - duration of sleep issued between log checks
  • [int]$VerboseLogging – Level of logging verbosity, currently 1,3 or 7

Variables

  • $Status = "zzz" - An arbitrary value that should not be produced by any log status message. If this value is still set to "zzz" after the logging script has run, it indicates a failure.

Permissions

VMware Virtual Infrastructure

Administrator Rights and Root access to the ESX Sandbox Host

Guest Operating System (VM being converted)

Local Administrator Rights

Control Server and Helper Server(s)

Administrator Rights

Getting help

The MAT is provided as-is with most of the information you should require available in this document. However if you find a bug or have improvements you would like to share, or if you have questions use the TechNet forum.

If you are reporting an issue with the MAT, please be sure to attach your logs. The logging level of the MAT should be set to '7' in the ConvertVM.ps1 file (line 28 by default)

$VerboseLogging = 7

Once you have recreated the issue with full logging enabled, please include that log with any communications along with any other information that is relevant including:

  • OS and version of Conversion server
  • OS and version of Hyper-V host
  • OS and version of guest VM (if issue is specific to a VM)
  • Version of VMware guest
  • Version of VMware ESX host
  • Version of vCenter/vSphere (if applicable)

NOTE: The MAT logs will contain the display names and fully qualified domain names of your virtual machines. If your company treats this information as sensitive, you can use a find/replace function to change the names into something simple like "Server1". As long as you do not alter the other contents of the log, this will not affect our ability to review the logging data.

MAT FAQ

For more information be sure to download the MAT Installation and Usage Guide.

Q: How many VMs should I convert in a batch?

A: Test your performance to see but generally limit your batches to 20-30 VMs. Since you will want to test the converted machines and deal with the source for each. You should also limit your initial testing to one ESX host.

Q: What versions of vCenter and ESX is MAT compatible with?

A: MAT is able to pull data from vCenter/ESX 3.5 Update 2 - 5.1 since it uses PowerCLI 5.1. However the MVMC engine is still limited to vCenter Server or ESXi Server 5.0 and vCenter Server or ESXi/ESX Server 4.1. There are pre-flight checks in the MAT to verify you are using the correct version.

Q: Does the MAT re-create my network cards on the resulting Hyper-V guest?

A: Yes, up to three nics and their settings can be converted. See the installation guide section on MigrateNICs.

Q: What are the disk space requirements?

A: The MVMC engine will export the VM to a local temp folder on the machine responsible for conversion and then it will copy it to the Hyper-V share. If you plan to run multiple VM conversions at once, plan to accommodate for the temp copies as well as the final destination.

Q: Do I have to install MAT in C:\MAT?

A: No, the MAT can be moved to any folder path that DOES NOT contain spaces as long as you update the PowerShell script variables appropriately. You CANNOT move MAT to a mapped drives since PowerShell does not respect mapped drives. You can use full UNC paths. The location of the MAT files does not have any impact on the output created by MVMC in the user's %temp% path.

Q: What kind of firewall exceptions are needed for the main server to talk to the remote servers?

A: Open access to and from your SQL server and on any MAT servers open access to/from VMware environment and your Hyper-V host.

Q: Is there any limit to how many 'Helper' servers I can use?

A: In theory, no, you can add as many helpers as you wish. Take care not to point all the helpers to the same Hyper-V host however. You will saturate the host's network and disk IO. If you have the capacity point each helper to its own Hyper-V host for the best performance.

Q: Does it matter which version of SQL I use?

A: No. You can use SQL Express 2012 or any version of SQL Server. 32 or 64-bit versions will work

Q: Occasionally MAT produces false positive messages in status, even though the conversion job terminated incorrectly. Why is this?

A: We rely on the exit code produced by each instance of MVMC.exe to determine the final state of the conversion. Always check you VM conversion log to verify the results. In future versions we will add logic to throw a mixed message if the final status collected from the log does not match the final exit code from the executable.

Q: I have MAT questions or suggestions for improvement, where can I send them?

A: MAT specific questions can be mailed to: mat-questions@microsoft.com. If you are reporting a problem please set the value "$VerboseLogging = 7" and collect new logs. This will produce the most detailed logs possible.

Appendix

ConvertVM.ps1

##########################################
#      Migration Automation Script       #
#             version 1.5.2              #
#                                        #
#           ConvertVM.ps1                #
#                                        #
#         Copyright 2014 Microsoft       #
#                                        #
#      http:\\aka.ms\buildingclouds      #
##########################################

#BuildVersion = "1.5.2007"

###### Start Options ######
param (
    [Parameter(Position=0,Mandatory=$False, HelpMessage="Use .\ConvertVM.ps1 Help for script options")]
    [ValidateSet("Menu","Convert","Convert-Global","Collect","Create-List","Update","Show-VM", "Show-Status","Show-StatusLoop","Reset-List","Reset-Incomplete","Reset-All","Report-All","Report-Complete","Report-Incomplete","Report-Unsupported", "Report-Warning", "Purge","Update","Help")]
    [string]$Task = "Menu"
,   [Parameter(Position=1,Mandatory=$False)]
    [ValidateRange (0,86400)]
    [int]$DelayTimer = 0
)


###### Variables ######
$Queuelength = 2   ### Do not set Queuelength above 3 ###
$CurrentPath = "C:\MAT"
$XMLPath = "$CurrentPath\Variable.xml"
$VMList = "$CurrentPath\VMlist.txt"
$VerboseLogging = 3
$MaxCapacityLoopTimer = 60 
$Lookback = 1
$BuildVersion = "1.5.2"
$Catalog = "MAT"
$RemoteJobName = "MAT"
$TaskArgues = "$CurrentPath\ConvertVM.ps1 Convert"
$Computername = Get-Childitem env:computername 
$localhost = $Computername.Value
$Preflight = 1
$RemoveCD = 1
$RemoveFloppy = 1
$error.clear()

. $CurrentPath\ConvertVM-Functions.ps1    

##########################
###    Start Script    ###
##########################
cls
write-host "--------------------------------------------"
write-host "+        Migration Automation Script       +" 
write-host "+                version 1.5               +" 
write-host "+                                          +" 
write-host "+         Copyright 2014 Microsoft         +" 
write-host "+                                          +" 
write-host "+        http:\\aka.ms\buildingclouds      +"
write-host "--------------------------------------------"
write-host ""
sleep 1

# Elevate
Write-Host "Checking for elevation... " 
$Path = Get-Location
$CurrentUser = New-Object Security.Principal.WindowsPrincipal $([Security.Principal.WindowsIdentity]::GetCurrent())
if (($CurrentUser.IsInRole([Security.Principal.WindowsBuiltinRole]::Administrator)) -eq $false)  
    {
    $ArgumentList = "-noprofile -noexit -file `"{0}`""
    If ($Task) {$ArgumentList = $ArgumentList + " $Task"}
    If ($DelayTimer) {$ArgumentList = $ArgumentList + " $DelayTimer"}
    Write-Host "Elevating..."
    Start-Process powershell.exe -Verb RunAs -ArgumentList ($ArgumentList -f ($myinvocation.MyCommand.Definition))
    Exit
    }





###### Verify XML file ######
If (Test-Path $XMLPath) 
    {
        Write-Host "Found $XMLPath"
        try {$Variable = [XML] (Get-Content $XMLPath)} 
        catch 
            {
            $Validate = $false;Write-Host "$XMLPath is invalid. Check XML syntax - Unable to proceed" -ForegroundColor Red
            exit
            }
    } 
Else 
    {
        $Validate = $false
        Write-Host "Missing $XMLPath - Unable to proceed" -ForegroundColor Red
        exit
    }


###### Load XML values as variables ######
Write-Host "Loading values from Variable.xml"
$Variable = [XML] (Get-Content "$XMLPath")
$Variable.MAT.General | ForEach-Object {$_.Variable} | Where-Object {$_.Name -ne $null} | ForEach-Object {Set-ScriptVariable -Name $_.Name -Value $_.Value}
$Variable.MAT.HyperV | ForEach-Object {$_.Variable} | Where-Object {$_.Name -ne $null} | ForEach-Object {Set-ScriptVariable -Name $_.Name -Value $_.Value}
$Variable.MAT.VMware | ForEach-Object {$_.Variable} | Where-Object {$_.Name -ne $null} | ForEach-Object {Set-ScriptVariable -Name $_.Name -Value $_.Value}
$MATLog = $logpath+"mat.log"

if ($Preflight -eq  1)
{Check-Preflight}

if ($MigrateNICs -eq 1)
{Check-MigrateNICs}

sleep 1
switch ($Task)
{
    "Menu" {Menu}
    "Convert-Global" {Start-Remotes}
    "Convert" 
        {
        Write-Log 1 $MATLog "---------------------------------------------"
        Write-Log 1 $MATLog "     Starting new batch of VMs               "
        Write-Log 1 $MATLog "---------------------------------------------"
        Write-Log 1 $MATLog "Logging events to $MATLog"
        Write-Log 1 $MATLog "Log Level = $VerboseLogging"
        Write-Log 7 $MATLog "Timer when Server is at Capacity = $MaxCapacityLoopTimer"
        Write-Log 1 $MATLog "Current Path = $CurrentPath"
        Write-Log 3 $MATLog "XML Path = $XMLPath"
        Write-Log 3 $MATLog "VMList = $VMList"
        Write-Log 3 $MATLog "Task Scheduler Job Name = $RemoteJobName"
        Write-Log 7 $MATLog "Task Arugements on Remotes = $TaskArgues"
        Write-Log 7 $MATLog "PowerShell Version = $BuildVersion"
        Write-Log 1 $MATLog "Delay Timer = $DelayTimer"
        Write-Log 3 $MATLog "Localhost = $Localhost"
        Write-Log 3 $MATLog "SQL Datasource = $Datasource"
        Write-Log 3 $MATLog "Preflight Checks = $Preflight"
        Write-Log 3 $MATLog "Migrate NICs = $MigrateNICs"
        Write-Log 3 $MATLog "Remove CD Drives = $RemoveCD"
        Write-Log 3 $MATLog "Remove Floppy drives = $RemoveFloppy"
        Write-Log 3 $MATLog "SQL Catalog = $Catalog"
        Write-Log 1 $MATLog "Max Concurrent Conversions = $Queuelength"

        ###### Use Delay Timer for executions in the future ######
        if ($DelayTimer -ge 1)
            {
            Write-Log 1 $MATLog "Delay Requested:True - Delaying execution for $DelayTimer seconds"
            Sleep -s $DelayTimer
            }
        else
            {
            Write-Log 3 $MATLog "Delay Requested:False - Starting immediate execution"
            }
        Convert
        }
    "Collect" {StartCollection}
    "Create-List" {List "USE $Catalog SELECT DisplayName FROM VMDetails_VIEW where [ReadytoConvert] = 0 AND [Completed] <> 1 AND [Supported] = 1 ORDER BY DisplayName"} 
    "Update" {UpdateDB}
    "Show-VM" {ShowVM}
    "Show-Status" {ShowStatus $Lookback}
    "Show-StatusLoop" 
        {
        do
            {
            ShowStatus $Lookback
            Sleep 5
            Write-Host ""
            Write-Host "Press 'Ctrl + C' to terminate script" -ForegroundColor DarkGray
            Sleep 5
            }
        while (1 -eq 1)
        }
    "Reset-List" {Reset List}
    "Reset-Incomplete" {Reset Incomplete}
    "Reset-All" {Reset All}
    "Report-Complete" {CSVReport "WHERE Completed = 1" $CurrentPath\Reports\Completed}
    "Report-Incomplete" {CSVReport "WHERE Completed <> 1 AND Supported = 1" $CurrentPath\Reports\Incomplete}
    "Report-Unsupported" {CSVReport "WHERE Supported = 3" $CurrentPath\Reports\Notsupported}
    "Report-Warning" {CSVReport "WHERE Warning = 1" $CurrentPath\Reports\Warning}
    "Report-All" {CSVReport $null $CurrentPath\Reports\AllData}
    "Purge" {Purge-DB}
    "Help" {Help}
  
}

ConvertVM-Functions.ps1

##########################################
#      Migration Automation Script       #
#             version 1.5.2              #
#                                        #
#       ConvertVM-Functions.ps1          #
#                                        #
#         Copyright 2014 Microsoft       #
#                                        #
#      http:\\aka.ms\buildingclouds      #
##########################################

#BuildVersion = "1.5.2007"

##################################################################
### FUNCTIONS ###
##################################################################

################################
###### General Region     ######
################################
#region General

###### Create Variables from XML ######
Function Set-ScriptVariable ($Name,$Value) 
 
{
Invoke-Expression ("`$Script:" + $Name + " = `"" + $Value + "`"")
If (($Name.Contains("Account")) -and !($Name.Contains("Password")) -and ($Value -ne "")) 
    {
    Invoke-Expression ("`$Script:" + $Name + "Domain = `"" + $Value.Split("\")[0] + "`"")
    Invoke-Expression ("`$Script:" + $Name + "Username = `"" + $Value.Split("\")[1] + "`"") 
    }
}

###### Write-Log ######
function write-log([int]$level, $logname, [string]$info, $color = "white")
{
    if ($level -le $VerboseLogging)
    {
        $time = get-date
        add-content $logname -value "$time - $info" 
        if ($color -eq "Red" -OR $color -eq "Yellow")
        {Write-Host "$time - $info" -ForegroundColor $color -BackgroundColor Black}

        else 
        {Write-Host "$time - $info" -ForegroundColor $color}
    }
} 

###### SQLWrite ######
function SQLWrite([string]$Query)
{
    $conn = New-Object System.Data.SqlClient.SqlConnection("Data Source=$DataSource; Initial Catalog=$Catalog; Integrated Security=SSPI")
	$conn.Open()
	$cmd = $conn.CreateCommand()
	$cmd.CommandText = "$Query"
	$cmd.ExecuteNonQuery() |Out-Null
	$conn.Close()

    if ($VerboseLogging -ge 7)
        {Write-Log 7 $MATLog "$Query"}
}
#endregion

#################################
###### Preflight Region    ######
#################################
#region Preflight

###### Verify Preflight checks ######
function Check-Preflight 
{
Write-Host "Running Preflight checks..."
$PSver = $PSVersionTable.PSVersion.Major
   if ($PSver -le 2)
        {
        Fail-Preflight "Powershell version 3 or higher is required."
        }

$Ver1 = $BuildVersion.CompareTo($XMLVersion)
Write-Log 7 $MATLog "Powershell version: $BuildVersion"
Write-Log 7 $MATLog "XML version: $XMLVersion" 
   if ($ver1 -ne 0)
        {
        Fail-Preflight "Powershell and XML versions DO NOT match. Exiting.", "Version incompatability, please download the MAT again."
        }

Try 
{
    $Query = "USE $Catalog SELECT TOP 1 [DBVersion] FROM [MAT].[dbo].[VERSION]"
    $conn = New-Object System.Data.SqlClient.SqlConnection("Data Source=$DataSource; Initial Catalog=$Catalog; Integrated Security=SSPI")
    $conn.Open()
    $cmd = $conn.CreateCommand()
    $cmd.CommandText = "$Query"
    $Reader = $cmd.ExecuteReader()
    while ($Reader.Read())
	    {$DBVersion  = $Reader.GetValue(0)}
}
Catch
{
    Fail-Preflight "ERROR - DATABASE ERROR: $($_.exception.message)" 
}

$Ver2 = $BuildVersion.CompareTo($DBVersion)
Write-Log 7 $MATLog "Powershell version: $BuildVersion"
Write-Log 7 $MATLog "Database version: $DBVersion" 
   if ($ver2 -ne 0)
        {
        Fail-Preflight "Powershell and Database versions DO NOT match." "Version incompatability, please download the MAT again."
        }
}

###### Verify Startup Power States ######
function Check-MigrateNICs
{

if ([Environment]::Is64BitProcess)
    {
    Fail-Preflight "You must use 32-bit PowerShell if you want to use the MigrateNIC option." 
    }

if ($spower -ne 0)
    {
    Fail-Preflight "You have enabled the MigrateNIC feature but you have a conflicting setting." "The spower variable must = 0"
    }

}

###### Preflight Failure ######
function Fail-Preflight ($errormsg1, $errormsg2="Exiting.")

{
    Write-Host
    Write-Log 1 $MATLog "ERROR - Preflight failed. Critical Error." Red
    Write-Log 1 $MATLog "$errormsg1" Red
    Write-Log 1 $MATLog "$errormsg2" Red
    Write-Host   
    exit
}

#endregion

################################
###### Task Sched Section ######
################################
#region TaskSched
###### Connect to Task Service ######
Function Connect-TaskService ($ConvertServer, $WinAccountUsername, $WinAccountDomain, $WinPassword)
{
$Global:TaskService = New-Object -ComObject Schedule.Service
$TaskServiceConnected = $false
$TaskServiceTries = 5
$TaskServiceInterval = 60
$i = 0
While ((!($TaskServiceConnected)) -and ($TaskServiceTries -ne $i)) {
    $i++
    Write-host "Connecting to task service attempt $i"
    $Global:TaskService.Connect($ConvertServer,"$WinAccountUsername","$WinAccountDomain","$WinPassword")
    If (!(($Global:TaskService.Connected -eq "True") -and ($Global:TaskService.TargetServer -eq $ConvertServer))) {
        If ($i -eq $TaskServiceTries) {
            Fail -Server $ConvertServer
            Write-host "Failed to connect to task service"
        } Else {
            Write-host "Failed to connect to task service"
            Write-host "Waiting $TaskServiceInterval seconds" 
            Start-Sleep $TaskServiceInterval
        }
    } Else {
        Write-log 3 $MATLog "Connected to task service on $ConvertServer"
        $TaskServiceConnected = $true
    }
}
$Global:TaskFolder = $Global:TaskService.GetFolder("\")
Write-Log 3 $MATLog "Task Folder $Global:TaskFolder"
}
            
###### Connect to Task Service ######
Function Get-Task ($TaskName) 
{
    $Tasks = $Global:TaskFolder.GetTasks(0)
    Return $Tasks | Where-Object {$_.Name -eq $TaskName}
    Write-Log 3 $MATLog "Found $Task on $ConvertServer"
}

###### Create a new task ######
Function New-Task ($TaskName,$Command,$Arguments,$User,$Password) 
{
    $Task = $Global:TaskService.NewTask(0)
    $TaskPrincipal = $Task.Principal
    $TaskPrincipal.RunLevel = 1
    $TaskAction = $Task.Actions.Create(0)
    $TaskAction.Path = $Command
    $TaskAction.Arguments = $Arguments
    Write-Log 1 $MATLog "Registering task $TaskName on $ConvertServer"
    $Global:TaskFolder.RegisterTaskDefinition($TaskName,$Task,6,$User,$Password,1)
}
 
###### Start Task ######
Function Start-Task ($TaskName) {
    # Start a task
    $Task = $Global:TaskFolder.GetTask($TaskName)
    Write-Log 1 $MATLog "Starting task $TaskName on $ConvertServer"
    $Task.Run(0)
}

###### Remove Task ######
Function Remove-Task ($TaskName) {
    # Remove a task
    Write-Log 1 $MATLog "Removing task: $TaskName from $ConvertServer"
    $Global:TaskFolder.DeleteTask($TaskName,0)
}
#endregion

################################
###### Conversion Region  ######
################################
#region Conversion

######  Start remote conversions ######
function Start-Remotes
{
$Variable.MAT.SchedCreds | ForEach-Object {$_.Variable} | Where-Object {$_.Name -ne $null} | ForEach-Object {Set-ScriptVariable -Name $_.Name -Value $_.Value}
Write-Log 3 $MATLog "--------------Looking for Remote Servers to help with conversion"
$RemoteCount = $Variable.MAT.RemoteHost.ConvertServer.Count
    if ($RemoteCount -ge 1)
        {
        Write-Log 1 $MATLog "Found $Remotecount Remote Servers in $XMLPath"
        $ConvertHost = $Variable.MAT.RemoteHost.ConvertServer
        foreach ($ConvertServer in $ConvertHost) 
            {
                Write-Host $ConvertHost
                Connect-TaskService $ConvertServer $WinAccountUsername $WinAccountDomain $WinPassword
                    if (Get-Task $RemoteJobName)
                    {Remove-Task $RemoteJobName}
                New-Task $RemoteJobName %SystemRoot%\syswow64\WindowsPowerShell\v1.0\powershell.exe $TaskArgues $WinAccount $WinPassword
                Start-Task $RemoteJobName
                # Add delay to let each server have a turn at the queue
                Write-Log 1 $MATLog "Waiting 15 seconds to let server have a turn at the queue"
                Sleep 15
                ShowStatus $Lookback
                sleep 5
            }
         }
    else {Write-Log 1 $MATLog "No Remote Servers found"}
Convert
}

######  Start local conversion ######
function Convert 
{
######  Verify Capacity ######
CheckSemaphore
    while ($Semaphore -ge $Queuelength) 
    {
    Write-Log 1 $MATLog "(Capacity Check) <$localhost> Server is at capacity. Waiting for next free slot" Cyan
    Sleep 5
    ShowStatus $Lookback
    Write-Host "Waiting 30 seconds and then rechecking"
    sleep $MaxCapacityLoopTimer
    cls
    CheckSemaphore
    }
$Slots = $Queuelength - $Semaphore
Write-Log 1 $MATLog "(Capacity Check) <$localhost> $Slots slot(s) available"

######  Check Queue for VMs ######
GetUnassignedVMCount
Write-Log 3 $MATLog "Unassigned VM Count is $UnassignedVMCount"
    if ($UnassignedVMCount -ge 1)
        {
            GetNextVM
        }

    else
        {
        Write-Log 1 $MATLog "Done. There are no unassigned VMs marked as ReadyToConvert in the queue"
        Write-Log 1 $MATLog "***** End of batch conversions *****" Cyan
        CheckSemaphore
        if ($Semaphore -ge 1)
            {
            Write-Log 1 $MATLog "$Semaphore Conversions are still running but this script will exit."
            Write-Log 1 $MATLog "You can continue monitor using the Show-StatusLoop parameter."
            }
        else
            {Write-Log 1 $MATLog "No more conversions are running locally. Exiting."}
        exit
        }
}

######  Check Server capacity ######
function CheckSemaphore
{
$Query = "USE $Catalog SELECT Count(*) FROM VMQueue WHERE [InUse] = 1 AND ConvertServer = '$localhost'"

	$conn = New-Object System.Data.SqlClient.SqlConnection("Data Source=$DataSource; Initial Catalog=$Catalog; Integrated Security=SSPI")
	$conn.Open()
	$cmd = $conn.CreateCommand()
	$cmd.CommandText = "$Query"
	$Reader = $cmd.ExecuteReader()
	while ($Reader.Read())
	    {$global:Semaphore  = $Reader.GetValue(0)}
	$conn.Close()

Write-Log 3 $MATLog "(Capacity Check) <$localhost> $Semaphore of $Queuelength in use."
}

###### Get Unassigned VMs ######
function GetUnassignedVMCount
{
Write-Log 3 $MATLog  "Checking Global VM Queue for VMs that are unassigned"
$Query = "USE $Catalog SELECT Count(*) FROM VMQueue WHERE [ReadytoConvert] = 1 AND ([ConvertServer] IS NULL)"

	$conn = New-Object System.Data.SqlClient.SqlConnection("Data Source=$DataSource; Initial Catalog=$Catalog; Integrated Security=SSPI")
	$conn.Open()
	$cmd = $conn.CreateCommand()
	$cmd.CommandText = "$Query"
	$Reader = $cmd.ExecuteReader()
	while ($Reader.Read())
	    {$global:UnassignedVMCount  = $Reader.GetValue(0)}
	$conn.Close()

Write-Log 1 $MATLog "Found $UnassignedVMCount VMs ready for conversion" Cyan
}

###### Get next VM Info ######
function GetNextVM
{
Write-Log 3 $MATLog "<$localhost> Getting next VM from database"
$Query = "USE $Catalog SELECT TOP 1 JobID, VMName, DisplayName FROM VMDetails_VIEW WHERE [ReadytoConvert] = 1 AND ([ConvertServer] IS NULL) ORDER by Position"

	$conn = New-Object System.Data.SqlClient.SqlConnection("Data Source=$DataSource; Initial Catalog=$Catalog; Integrated Security=SSPI")
	$conn.Open()
	$cmd = $conn.CreateCommand()
	$cmd.CommandText = "$Query"
	$Reader = $cmd.ExecuteReader()
	while ($Reader.Read())
	    {
		$ID  = $Reader.GetValue(0)
		$VMName = $Reader.GetValue(1)
        $DisplayName = $Reader.GetValue(2)
	    }
	$conn.Close()

###### Kick off Convert.ps1 ######
SQLWrite "UPDATE VMQueue SET [ConvertServer] = '$localhost', [Inuse] = 1, [ReadytoConvert] = 0, [Position] = NULL, [Summary] = 'Submitting for Conversion' WHERE JobID = $ID" 

Write-Log 1 $MATLog "Starting BackupVM Network" 
PrepareVM $DisplayName

$StartConvert = Start-Process -FilePath powershell.exe -ArgumentList "$CurrentPath\ConvertVM-Process.ps1 $ID $Datasource $Catalog $MATLog $DisplayName $VMName $Logpath $CurrentPath $VerboseLogging $XMLPath $MigrateNICs" -PassThru -WindowStyle Minimized
Write-Log 7 $MATLog "Start-Process -FilePath powershell.exe -ArgumentList '$CurrentPath\ConvertVM-Process.ps1 $ID $Datasource $Catalog $DisplayName $VMName $Logpath $CurrentPath $VerboseLogging $XMLPath $MigrateNICs' -PassThru -WindowStyle Minimized"
Write-Log 3 $MATLog "Waiting 10 seconds to let process settle"  
Sleep 10

  # IF not the last VM loop through GetVMInfo again. (lather, rinse, repeat.) #
Write-Log 1 $MATLog "Launched $VMName conversion" Cyan
Write-Log 1 $MATLog "Checking Global list for more unassigned VMs"
Convert
}

###### Prepare for conversion #####
Function PrepareVM ($VMTarget)
{
    #Split $guserDomain and $guserName
    Invoke-Expression ("`$Script:" + "guser" + "Domain = `"" + $guser.Split("\")[0] + "`"")
    Invoke-Expression ("`$Script:" + "guser" + "Name = `"" + $guser.Split("\")[1] + "`"") 
 
    SQLWrite "UPDATE VMQueue SET [Summary] = 'Backing up VM NIC info' WHERE JobID = $ID"  
    Write-Log 1 $MATLog "Backing up VM Network function for $VMTarget" 
    Write-Log 3 $MATLog "Adding Snapin VMware.VimAutomation.Core"
    Add-PSSnapin "VMware.VimAutomation.Core"
    

    Write-Log 1 $MATLog "Connecting to VMware Server" 

    $ConnectVIServer = Connect-VIServer -Server $shost -Protocol https -User $shusername -Password $shpwd
    $VIPort = $ConnectVIServer.Port
    $VIVer = $ConnectVIServer.Version
    #Check for supported versions
    if ($VIVer -ge 5.1 -OR $VIVer -le 4.0)
        {
        Write-Log 1 $MATLog "ERROR - Your version of VMware $VIVer is not supported with the current version of MVMC/MAT" Red
        Write-Log 1 $MATLog "Unable to contiue. See the MVMC readme for more details on supported versions" Red
        exit
        }
    Write-Log 1 $MATLog "Connected to $ConnectVIServer on $VIPort version: $VIVer"
        

    #Make sure VM is running
    $VMstate = VMware.VimAutomation.Core\Get-VM -Name $VMTarget
    if ($VMState.PowerState -ne "PoweredOn")
        {
        Write-Log 1 $MATLog "ERROR - $VMTarget is not Powered On." Red
        $time = get-date
        SQLWrite "UPDATE VMQueue SET [Warning] = 1, InUse = NULL, ConvertServer = NULL, Completed = 0,  Summary = 'VM is not powered on', PID = NULL, EndTime = '$time'  WHERE JobID = $ID" 

        #Go to next VM
        Convert
        }
        
    #Disconnect any CD image or device
    if ($RemoveCD -eq 1)
        {
            Write-Log 1 $MATLog "Removing CD drives from $VMTarget" Cyan
            Try
                {
                $VMmedia = VMware.VimAutomation.Core\Get-VM -Name $VMTarget
                $VMCD = Get-CDDrive -VM $VMmedia 
                Set-CDDrive $VMCD -NoMedia -Confirm:$False | Out-Null
                }
            Catch
                {
                    Write-Log 1 $MATLog "WARNING - Unable to remove CD drives from $VMTarget $($_.exception.message)" Yellow
                    SQLWrite "UPDATE VMQueue SET [Warning] = 1, [Summary] = 'Unable to remove CDs' WHERE JobID = $ID" 
                }
        }

    #Disconnect any Floppy disk 
    if ($RemoveFloppy -eq 1)
        {
            Write-Log 1 $MATLog "Removing floppy drives from $VMTarget" Cyan
            Try
            {
                $VMmedia = VMware.VimAutomation.Core\Get-VM -Name $VMTarget
                $VMfloppy = Get-FloppyDrive -VM $VMmedia 
                Set-FloppyDrive $VMfloppy -NoMedia -Confirm:$False | Out-Null
            }
            Catch
            {
                Write-Log 1 $MATLog "WARNING - Unable to remove floppies from $VMTarget $($_.exception.message)" Yellow
                SQLWrite "UPDATE VMQueue SET [Warning] = 1, [Summary] = 'Unable to remove floppies' WHERE JobID = $ID" 
            }
        }

    #Prepare NICs for conversion
    if ($MigrateNICs -eq 1)
    {
        Write-Log 3 $MATLog "Preparing to Migrate NICs for $VMTarget"
        
        #Prepare scriptSaveCreds
        $Step1 = '{3}{0}{3},{3}{1}{3},{3}{2}{3} | out-file C:\Windows\Temp\nicCreds.txt' -f $guserName,$gPwd,$guserDomain,"'"
        $Step1 = $Step1 + "`n Write-Host 'Done'"

        #Prepare Regedit & Network Restore
        $Step2 = 
        {
            $RunOnceKey = "HKLM:\Software\Microsoft\Windows\CurrentVersion\RunOnce"
            set-itemproperty $RunOnceKey "ConfigureServer" "C:\Windows\Temp\nicRestore.cmd"
            $WinLogonKey ="HKLM:\Software\Microsoft\Windows NT\CurrentVersion\Winlogon"
            $guserName,$gPwd,$guserDomain = Get-Content "C:\Windows\Temp\nicCreds.txt"
            set-itemproperty $WinLogonKey "DefaultUserName" $guserName
            set-itemproperty $WinLogonKey "DefaultPassword" $gPwd
            set-itemproperty $WinLogonKey "AutoAdminLogon" "1"
            set-itemproperty $WinLogonKey "DefaultDomainName" $guserDomain
            Remove-Item "C:\Windows\Temp\nicCreds.txt"
        $restorecode =
            {
                Start-Sleep -Seconds 30;$na = (New-Object -com shell.application).Namespace(0x31)
                Foreach ($n in (Import-Clixml -Path "C:\Windows\Temp\nicConfig.xml")){$na.Items()|?{$_.Name -eq $(gwmi win32_networkadapter -F "MACAddress='$($N.MACAddress)'").NetConnectionID}|%{ $_.Name="$($_.Name)_old" }}
                Foreach ($n in (Import-Clixml -Path "C:\Windows\Temp\nicConfig.xml")){$na.Items()|?{$_.Name -eq $(gwmi win32_networkadapter -F "MACAddress='$($N.MACAddress)'").NetConnectionID}|%{ $_.Name=$n.NetConnectionID }}
                Foreach ($n in (Import-Clixml -Path "C:\Windows\Temp\nicDNS.xml")){([wmiclass]'Win32_NetworkAdapterConfiguration').SetDNSSuffixSearchOrder($n.DNSDomainSuffixSearchOrder);gwmi win32_networkadapterconfiguration -F "MACAddress='$($N.MACAddress)'" |%{$_.SetDNSServerSearchOrder($n.DNSServerSearchOrder);$_.SetDNSDomain($n.DNSDomain)}}
                netsh -f "C:\Windows\Temp\nicConfig.txt"
                $WinLogonKey ="HKLM:\Software\Microsoft\Windows NT\CurrentVersion\Winlogon"
                Remove-ItemProperty $WinLogonKey "AutoAdminLogon"
                Remove-itemproperty $WinLogonKey "DefaultPassword"
                Remove-itemproperty $WinLogonKey "DefaultUserName"
                C:\Windows\system32\shutdown.exe -s -t 5 -f
            }
        "C:\Windows\System32\WindowsPowerShell\v1.0\Powershell.exe -encodedcommand {0}" -f [convert]::ToBase64String([Text.Encoding]::Unicode.GetBytes($restorecode)) | Out-File -FilePath "C:\Windows\Temp\nicRestore.cmd" -Encoding ascii
        gwmi win32_NetworkAdapter | where { $_.NetConnectionID } | Select-Object -Property Name, MacAddress,NetConnectionID | Export-Clixml -Path "C:\Windows\Temp\nicConfig.xml"
        gwmi win32_networkadapterconfiguration | ? {$_.IPEnabled -eq "True"} | select MACAddress, DNSDomain, DNSDomainSuffixSearchOrder, DNSServerSearchOrder | Export-Clixml -Path "C:\Windows\Temp\nicDNS.xml"
        netsh dump |  ?{$_ -notmatch "^\s|#"} | Out-File -FilePath "C:\Windows\Temp\nicConfig.txt" -Encoding ascii
        Write-Host "Done"
        }

        #Prepare shutdown script
        $Step3 = 
        { 
            $StartDHCP = 
            {gwmi win32_NetworkAdapterConfiguration | ? {! $_.DHCPEnabled -and $_.IPAddress } | %{ $_.EnableDHCP()}
            $NetworkConnections = (New-Object -com shell.application).Namespace(0x31)
            Foreach ($NIC in (gwmi win32_NetworkAdapter | where { $_.NetConnectionID }|Select-Object -ExpandProperty NetConnectionID))
                {
                        $NetworkConnections.Items() |Where-Object {$_.Name -eq $NIC} |ForEach-Object { $_.Name="$($_.Name)_VMware"}
                }
        }
 
        # Backup existing Shutdown Scripts (they will not be run during migration)
        if (Test-Path "HKLM:\SOFTWARE\Microsoft\Windows\CurrentVersion\Group Policy\Scripts\Shutdown") 
                                {
            Reg export "HKLM\SOFTWARE\Microsoft\Windows\CurrentVersion\Group Policy\Scripts\Shutdown" C:\Windows\Temp\gposcriptsShutdown.reg /y
            Reg export "HKLM\SOFTWARE\Microsoft\Windows\CurrentVersion\Group Policy\State\Machine\Scripts\Shutdown" C:\Windows\Temp\gpostateShutdown.reg /y
            Remove-Item "HKLM:\SOFTWARE\Microsoft\Windows\CurrentVersion\Group Policy\Scripts\Shutdown" -Recurse
            Remove-Item "HKLM:\SOFTWARE\Microsoft\Windows\CurrentVersion\Group Policy\State\Machine\Scripts\Shutdown" -Recurse
        }
 
        # Add clean-up to $scriptContents including restoration of previous Shutdown Scripts
        $scriptContents = "# You should not see this file.  It is used during virtual machine migration.`n# Consult the MAT Installation and Usage guide for more info.`n`nRemove-Item 'HKLM:\SOFTWARE\Microsoft\Windows\CurrentVersion\Group Policy\Scripts\Shutdown' -Recurse`nRemove-Item 'HKLM:\SOFTWARE\Microsoft\Windows\CurrentVersion\Group Policy\State\Machine\Scripts\Shutdown' -Recurse`nReg Import C:\Windows\Temp\gposcriptsShutdown.reg`nReg Import C:\Windows\Temp\gpostateShutdown.reg`nRemove-Item C:\Windows\Temp\gposcriptsShutdown.reg`nRemove-Item C:\Windows\Temp\gpostateShutdown.reg`nRemove-Item C:\Windows\System32\GroupPolicy\Machine\Scripts\Shutdown\MATcleanup.ps1`n`n" + $StartDHCP
 
        # Write script to GPO 
        if (!(Test-Path "C:\Windows\System32\GroupPolicy\Machine\Scripts\Shutdown"))
        {New-Item -path "C:\Windows\System32\GroupPolicy\Machine\Scripts\Shutdown" -Type directory }
        $scriptContents | out-file C:\Windows\System32\GroupPolicy\Machine\Scripts\Shutdown\MATcleanup.ps1 -Force
        Write-Host "Done"
        }
 
        #Prepare \Group Policy\Scripts\
        $Step4 = 
        {
            if (!(Test-Path "HKLM:\SOFTWARE\Microsoft\Windows\CurrentVersion\Group Policy\Scripts"))
                {new-item "HKLM:\SOFTWARE\Microsoft\Windows\CurrentVersion\Group Policy\Scripts"}
            new-item "HKLM:\SOFTWARE\Microsoft\Windows\CurrentVersion\Group Policy\Scripts\Shutdown"
            new-item "HKLM:\SOFTWARE\Microsoft\Windows\CurrentVersion\Group Policy\Scripts\Shutdown\0"
            new-item "HKLM:\SOFTWARE\Microsoft\Windows\CurrentVersion\Group Policy\Scripts\Shutdown\0\0"
 
            new-itemproperty -path "HKLM:\SOFTWARE\Microsoft\Windows\CurrentVersion\Group Policy\Scripts\Shutdown\0" -PropertyType String -Name "DisplayName" -Value "Local Group Policy"
            new-itemproperty -path "HKLM:\SOFTWARE\Microsoft\Windows\CurrentVersion\Group Policy\Scripts\Shutdown\0" -PropertyType String -Name "FileSysPath" -Value "C:\WINDOWS\System32\GroupPolicy\Machine"
            new-itemproperty -path "HKLM:\SOFTWARE\Microsoft\Windows\CurrentVersion\Group Policy\Scripts\Shutdown\0" -PropertyType String -Name "GPO-ID" -Value "LocalGPO"
            new-itemproperty -path "HKLM:\SOFTWARE\Microsoft\Windows\CurrentVersion\Group Policy\Scripts\Shutdown\0" -PropertyType String -Name "GPOName" -Value "Local Group Policy"
            new-itemproperty -path "HKLM:\SOFTWARE\Microsoft\Windows\CurrentVersion\Group Policy\Scripts\Shutdown\0" -PropertyType DWord -Name "PSScriptOrder" -Value 1
            new-itemproperty -path "HKLM:\SOFTWARE\Microsoft\Windows\CurrentVersion\Group Policy\Scripts\Shutdown\0" -PropertyType String -Name "SOM-ID" -Value "Local"
 
            new-itemproperty -path "HKLM:\SOFTWARE\Microsoft\Windows\CurrentVersion\Group Policy\Scripts\Shutdown\0\0" -PropertyType DWord -Name "ExecTime" -Value 0
            new-itemproperty -path "HKLM:\SOFTWARE\Microsoft\Windows\CurrentVersion\Group Policy\Scripts\Shutdown\0\0" -PropertyType DWord -Name "IsPowerShell" -Value 1
            new-itemproperty -path "HKLM:\SOFTWARE\Microsoft\Windows\CurrentVersion\Group Policy\Scripts\Shutdown\0\0" -PropertyType String -Name "Parameters" -Value ""
            new-itemproperty -path "HKLM:\SOFTWARE\Microsoft\Windows\CurrentVersion\Group Policy\Scripts\Shutdown\0\0" -PropertyType String -Name "Script" -Value "MATcleanup.ps1"
            Write-Host "Done"
        }
        
        #Prepare \Group Policy\State\Machine\Scripts\
        $Step5 = 
        {
            if (!(Test-Path "HKLM:\SOFTWARE\Microsoft\Windows\CurrentVersion\Group Policy\State\Machine\Scripts"))
                {new-item "HKLM:\SOFTWARE\Microsoft\Windows\CurrentVersion\Group Policy\State\Machine\Scripts"}
            new-item "HKLM:\SOFTWARE\Microsoft\Windows\CurrentVersion\Group Policy\State\Machine\Scripts\Shutdown"
            new-item "HKLM:\SOFTWARE\Microsoft\Windows\CurrentVersion\Group Policy\State\Machine\Scripts\Shutdown\0"
            new-item "HKLM:\SOFTWARE\Microsoft\Windows\CurrentVersion\Group Policy\State\Machine\Scripts\Shutdown\0\0"

            new-itemproperty -path "HKLM:\SOFTWARE\Microsoft\Windows\CurrentVersion\Group Policy\State\Machine\Scripts\Shutdown\0" -PropertyType String -Name "DisplayName" -Value "Local Group Policy"
            new-itemproperty -path "HKLM:\SOFTWARE\Microsoft\Windows\CurrentVersion\Group Policy\State\Machine\Scripts\Shutdown\0" -PropertyType String -Name "FileSysPath" -Value "C:\WINDOWS\System32\GroupPolicy\Machine"
            new-itemproperty -path "HKLM:\SOFTWARE\Microsoft\Windows\CurrentVersion\Group Policy\State\Machine\Scripts\Shutdown\0" -PropertyType String -Name "GPO-ID" -Value "LocalGPO"
            new-itemproperty -path "HKLM:\SOFTWARE\Microsoft\Windows\CurrentVersion\Group Policy\State\Machine\Scripts\Shutdown\0" -PropertyType String -Name "GPOName" -Value "Local Group Policy"
            new-itemproperty -path "HKLM:\SOFTWARE\Microsoft\Windows\CurrentVersion\Group Policy\State\Machine\Scripts\Shutdown\0" -PropertyType DWord -Name "PSScriptOrder" -Value 1
            new-itemproperty -path "HKLM:\SOFTWARE\Microsoft\Windows\CurrentVersion\Group Policy\State\Machine\Scripts\Shutdown\0" -PropertyType String -Name "SOM-ID" -Value "Local"

            new-itemproperty -path "HKLM:\SOFTWARE\Microsoft\Windows\CurrentVersion\Group Policy\State\Machine\Scripts\Shutdown\0\0" -PropertyType DWord -Name "ExecTime" -Value 0
            new-itemproperty -path "HKLM:\SOFTWARE\Microsoft\Windows\CurrentVersion\Group Policy\State\Machine\Scripts\Shutdown\0\0" -PropertyType String -Name "Parameters" -Value ""
            new-itemproperty -path "HKLM:\SOFTWARE\Microsoft\Windows\CurrentVersion\Group Policy\State\Machine\Scripts\Shutdown\0\0" -PropertyType String -Name "Script" -Value "MATcleanup.ps1" 
            Write-Host "Done"
        }
        
   
        Try 
        {
            ### STEP 1 - ScriptSaveCreds section
            Write-Log 3 $MATLog "Step 1 - Sending credsstore script to $VMTarget"
            $CredsStore = Invoke-VMScript -ScriptText $Step1 -vm $VMTarget -GuestUser $Guser -GuestPassword $gPwd -HostUser $shusername -HostPassword $shpwd -ScriptType powershell -ErrorAction STOP 
            if ($CredsStore.ScriptOutput -match "Done")
                {write-log 3 $MATLog "Step 1 completed successfully for $VMTarget" Cyan}
            else
                {
                Write-log 1 $MATLog "WARNING - CredStore failed - aborting Regedit section" Yellow
                SQLWrite "UPDATE VMQueue SET [Warning] = 1, [Summary] = 'Invoke-Script Credstore terminated' WHERE JobID = $ID"
                return 
                }

            if ($CredsStore.State -eq "Error")
                {
                Write-Log 1 $MATLog "ERROR - Invoke-Script terminated with: $CredsStore.TerminatingError" Red
                SQLWrite "UPDATE VMQueue SET [Warning] = 1, [Summary] = 'Invoke-Script CredStore terminated' WHERE JobID = $ID"
                return 
                }


            ### STEP 2 - Regedit section
            Write-Log 3 $MATLog "Step 2 - Sending Regedit and Restore script to $VMTarget"
            $Regedit = Invoke-VMScript -ScriptText $Step2 -vm $VMTarget -GuestUser $Guser -GuestPassword $gPwd -HostUser $shusername -HostPassword $shpwd -ScriptType powershell -ErrorAction STOP
                        
            if ($Regedit.ScriptOutput -match "Done")
                {write-log 3 $MATLog "Step 2 completed successfully for $VMTarget" Cyan}
            else
                {
                Write-log 1 $MATLog "WARNING - Regeditscripts failed - aborting Regedit section" Yellow
                SQLWrite "UPDATE VMQueue SET [Warning] = 1, [Summary] = 'Invoke-Script Regedit terminated' WHERE JobID = $ID"
                return 
                }

            if ($Regedit.State -eq "Error")
                {
                Write-Log 1 $MATLog "ERROR - Invoke-Script terminated with: $Regedit.TerminatingError" Red
                SQLWrite "UPDATE VMQueue SET [Warning] = 1, [Summary] = 'Invoke-Script Regedit terminated' WHERE JobID = $ID"
                return 
                }

           ### STEP 3 - NetworkBackup Section
            Write-Log 3 $MATLog "Step 3 - Preparing shutdown script on $VMTarget"
            $NetworkBackup = Invoke-VMScript -ScriptText $Step3 -vm $VMTarget -GuestUser $Guser -GuestPassword $gPwd -HostUser $shusername -HostPassword $shpwd -ScriptType powershell -ErrorAction STOP
            if ($NetworkBackup.ScriptOutput -match "Done")
                {write-log 3 $MATLog "Step 3 completed successfully for $VMTarget" Cyan}
            else
                {
                Write-log 1 $MATLog "WARNING - NetworkBackup failed - aborting Network Rebuild section" Yellow
                SQLWrite "UPDATE VMQueue SET [Warning] = 1, [Summary] = 'Invoke-Script Networkbackup terminated' WHERE JobID = $ID"
                return 
                }

            if ($NetworkBackup.State -eq "Error")
                {
                Write-Log 1 $MATLog "ERROR - Invoke-Script terminated with: $CredsStore.TerminatingError" Red
                SQLWrite "UPDATE VMQueue SET [Warning] = 1, [Summary] = 'Invoke-Script Networkbackup terminated' WHERE JobID = $ID"
                return 
                }

           ### STEP 4 - GPO  Section 1
            Write-Log 3 $MATLog "Step 4 - Preparing GPO on $VMTarget"
            $GPO1 = Invoke-VMScript -ScriptText $Step4 -vm $VMTarget -GuestUser $Guser -GuestPassword $gPwd -HostUser $shusername -HostPassword $shpwd -ScriptType powershell -ErrorAction STOP
            if ($GPO1.ScriptOutput -match "Done")
                {write-log 3 $MATLog "Step 4 completed successfully for $VMTarget" Cyan}
            else
                {
                Write-log 1 $MATLog "WARNING - GPO1 failed - aborting Network Rebuild section" Yellow
                SQLWrite "UPDATE VMQueue SET [Warning] = 1, [Summary] = 'Invoke-Script GPO1 terminated' WHERE JobID = $ID"
                return 
                }

            if ($GPO1.State -eq "Error")
                {
                Write-Log 1 $MATLog "ERROR - Invoke-Script terminated with: $CredsStore.TerminatingError" Red
                SQLWrite "UPDATE VMQueue SET [Warning] = 1, [Summary] = 'Invoke-Script GPO1 terminated' WHERE JobID = $ID"
                return 
                }   
                  
           ### STEP 5 - GPO  Section 2
            Write-Log 3 $MATLog "Step 5 - Fininalizing GPO on $VMTarget"
            $GPO2 = Invoke-VMScript -ScriptText $Step5 -vm $VMTarget -GuestUser $Guser -GuestPassword $gPwd -HostUser $shusername -HostPassword $shpwd -ScriptType powershell -ErrorAction STOP
            if ($GPO2.ScriptOutput -match "Done")
                {write-log 3 $MATLog "Step 5 completed successfully for $VMTarget" Cyan}
            else
                {
                Write-log 1 $MATLog "WARNING - GPO2 failed - aborting Network Rebuild section" Yellow
                SQLWrite "UPDATE VMQueue SET [Warning] = 1, [Summary] = 'Invoke-Script GPO2 terminated' WHERE JobID = $ID"
                return 
                }

            if ($GPO2.State -eq "Error")
                {
                Write-Log 1 $MATLog "ERROR - Invoke-Script terminated with: $CredsStore.TerminatingError" Red
                SQLWrite "UPDATE VMQueue SET [Warning] = 1, [Summary] = 'Invoke-Script GPO2 terminated' WHERE JobID = $ID"
                return 
                }    
               

        }
        catch
        {
            Write-Log 1 $MATLog "WARNING - Unable to send scripts to $VMTarget $($_.exception.message) on Target:${VMTarget}" Yellow
            SQLWrite "UPDATE VMQueue SET [Warning] = 1, [Summary] = 'Unable to collect networking info' WHERE JobID = $ID" 
        }
    }
    
    Write-Log 3 $MATLog "Removing Snapin VMware.VimAutomation.Core"
    Remove-PSSnapin "VMware.VimAutomation.Core"
    SQLWrite "UPDATE VMQueue SET [Summary] = 'Completed VM NIC backup' WHERE JobID = $ID"  
}


#endregion

################################
###### Collection Region ######
################################
#region Collection
###### Collect valid VMs ######
Function StartCollection 
{
    ###### Get VLAN ID ######
    Function Get-VLanID ($NetworkName){
        Get-VirtualPortGroup -Name $nic.NetworkName | ForEach-Object {
            $VirtualPortGroup = $_
            Switch ($VirtualPortGroup.GetType().Name) {
                DistributedPortGroupImpl { $VirtualPortGroup.ExtensionData.Config.DefaultPortConfig.Vlan.VlanId }
                VirtualPortGroupImpl     { $VirtualPortGroup.VlanId }
            }
        }
    }
    
    cls
    $VMData = @()
    $CollectLog = $logpath+"vmdata.log"
    $error.clear()
    
    #Clearing space for progress bar
    Write-Host
    Write-Host
    Write-Host
    Write-Host
    Write-Host
    Write-Host
    Write-Host
    Write-Log 1 $CollectLog "---------------------------------------------"
    Write-Log 1 $CollectLog "        Starting new VM collection           "
    Write-Log 1 $CollectLog "---------------------------------------------"

    ###### Connect to VC ######
    Write-Log 3 $CollectLog "Adding Snapin VMware.VimAutomation.Core"
    Add-PSSnapin "VMware.VimAutomation.Core"
    
    Write-Log 1 $CollectLog "Connecting to VMware Server"
    Try
    {
        $ConnectVIServer = Connect-VIServer -Server $shost -Protocol https -User $shusername -Password $shpwd
        $VIPort = $ConnectVIServer.Port
        $VIVer = $ConnectVIServer.Version
        #Check for supported versions
        if ($VIVer -ge 5.1 -OR $VIVer -le 4.0)
        {
            Write-Log 1 $CollectLog "ERROR - Your version of VMware $VIVer is not supported with the current version of MVMC/MAT" Red
            Write-Log 1 $CollectLog "Unable to contiue. See the MVMC readme for more details on supported versions" Red
            exit
        }
        Write-Log 1 $CollectLog "Connected to $ConnectVIServer on $VIPort version: $VIVer"
    }
    Catch
    {
       Write-Log 1 $CollectLog "ERROR - Connecting to VMware Server $($_.exception.message)" Red
       exit
    }


    ###### Grab list of VMs ######
    ### Machines must be powered on! and have tools installed or we will not capture them
    Try
    {
        $VMsList = VMware.VimAutomation.Core\Get-VM | where { $_.PowerState -eq 'PoweredOn' }
        $VMCount = $VMsList.Count
        Write-Log 1 $CollectLog "Collected $VMCount VMs"
    }
    catch
    {
        Write-Log 1 $CollectLog "ERROR - The was an error in collection: $error" Red
        exit
    }
    if ($VMcount -gt 0) 
    {
        $connString = "Data Source=$Datasource;Initial Catalog=$Catalog;Integrated Security=SSPI"
        $connection = New-Object System.Data.SqlClient.SqlConnection($connString)
        $connection.Open()
            
        $sqlDelcmd = $connection.CreateCommand()
        $DelCommandText = "DELETE FROM [VMConversionData_PS]"
        $sqlDelcmd.CommandText = $DelCommandText
        $sqlDelcmd.ExecuteNonQuery()
    
        $sqlcmd = $connection.CreateCommand()
        Write-Log 1 $CollectLog "Collecting information about each VM"
        Write-host "Hang in there... this could take a while."
        $peri = 0
        $VMData = foreach ($vm in $VMsList) 
        {
            $VMobj = $null
            $Network0 = $null
            $IPv6Address0 = $null
            $IPv4Address0 = $null
            $Network1 = $null
            $IPv6Address1 = $null
            $IPv4Address1 = $null
            $Network2 = $null
            $IPv6Address2 = $null             
            $IPv4Address2 = $null             
            $DiskLocation0 = $null            
            $DiskLocation1 = $null            
            $DiskLocation2 = $null            
            $Mac0  = $null                    
            $Mac1  = $null                    
            $Mac2  = $null
            $VLAN0 = $null
            $VLAN1 = $null
            $VLAN2 = $null

            write-progress -Activity "Collecting VMs..." -status "Enumerating $($VM.Name)" -percentcomplete (($peri++/($VMsList.Count))*100)
            $VMobj = New-Object -typename System.Object
            if ($vm.Guest.State -eq "Running") 
            {
                $VMobj | Add-Member -MemberType noteProperty -name GuestVMFQDN -value $vm.ExtensionData.Guest.HostName
                $VMobj | Add-Member -MemberType noteProperty -name DisplayName -value $vm.Name
                $VMobj | Add-Member -MemberType noteProperty -name GuestOS -value $vm.Guest.OSFullName
                $VMobj | Add-Member -MemberType noteProperty -name GuestID -value $vm.ExtensionData.Guest.GuestId
            }
            else 
            {
                $VMobj | Add-Member -MemberType noteProperty -name GuestVMFQDN -value $vm.Name
                $VMobj | Add-Member -MemberType noteProperty -name DisplayName -value $vm.Name
                $VMobj | Add-Member -MemberType noteProperty -name GuestOS -value $vm.Guest
            }
            $VMobj | Add-Member -MemberType noteProperty -name VMHostName -value $vm.Host.Name
            $VMobj | Add-Member -MemberType noteProperty -name VMHostID -value $vm.HostId
            $VMobj | Add-Member -MemberType noteProperty -name GuestVMID -value $vm.Id
            $VMobj | Add-Member -MemberType noteProperty -name GuestVMMB -value $vm.MemoryMB
            $VMobj | Add-Member -MemberType noteProperty -name GuestVMCPUCount -value $vm.NumCpu
            $VMobj | Add-Member -MemberType noteProperty -name GuestVMHDProvisionedGB -value $vm.ProvisionedSpaceGB
            $VMobj | Add-Member -MemberType noteProperty -name GuestVMHDUsedGB -value $vm.UsedSpaceGB
            $VMobj | Add-Member -MemberType noteProperty -name VMwareToolsVerion -value $($vm | Get-View).config.tools.toolsVersion
            $VMobj | Add-Member -MemberType noteProperty -name VMwareHardwareVersion -value $vm.Version
            $VMobj | Add-Member -MemberType noteProperty -name FtInfo -value $vm.ExtensionData.Config.FtInfo
            $VMobj | Add-Member -MemberType noteProperty -name MoRef -value $vm.ExtensionData.MoRef.Value
      
            $di=0
            foreach ($disk in $vm.HardDisks) 
            {
                $VMobj | Add-Member -MemberType noteProperty -name DiskLocation$di -value $disk.Filename
                $di++
            }
            $VMobj | Add-Member -MemberType noteProperty -name DiskCount -value $di

            $ni=0
            foreach ($nic in $vm.Guest.Nics) 
            {
                $VMobj | Add-Member -MemberType noteProperty -name Network$ni -value $nic.NetworkName
                $VMobj | Add-Member -MemberType noteProperty -name IPAddress$ni -value $nic.IPAddress
                $VMobj | Add-Member -MemberType noteProperty -name VLAN$ni -value (Get-VLanID $nic.NetworkName)
                $VMobj | Add-Member -MemberType noteProperty -name Mac$ni -value $nic.MacAddress
                $ni++
            }
            $VMobj | Add-Member -MemberType noteProperty -name NICCount -value $ni -PassThru

            $Network0 = $null
            $IPv6Address0 = $null
            $IPv4Address0 = $null
            $Network1 = $null
            $IPv6Address1 = $null
            $IPv4Address1 = $null
            $Network2 = $null
            $IPv6Address2 = $null             
            $IPv4Address2 = $null             
            $DiskLocation0 = $null            
            $DiskLocation1 = $null            
            $DiskLocation2 = $null            
            $Mac0  = $null                    
            $Mac1  = $null                    
            $Mac2  = $null
            $VLAN0 = $null
            $VLAN1 = $null
            $VLAN2 = $null
            switch ($VMobj.DiskCount)
            {
                1 { 
                    $DiskLocation0 = $VMobj.DiskLocation0
                }
                2 {
                    $DiskLocation0 = $VMobj.DiskLocation0
                    $DiskLocation1 = $VMobj.DiskLocation1
                }
                3 { 
                    $DiskLocation0 = $VMobj.DiskLocation0
                    $DiskLocation1 = $VMobj.DiskLocation1
                    $DiskLocation2 = $VMobj.DiskLocation2
                }
            }
            switch ($VMobj.NICCount)
            {
                1 {
                    switch ($VMobj.IPAddress0.Count)
                    {
                        1   {
                                $VLAN0 = $VMobj.VLAN0
                                $Network0 = $VMobj.Network0
                                $IPv4Address0 = $VMobj.IPAddress0[0]
                        }
                        2   {
                                $VLAN0 = $VMobj.VLAN0
                                $Network0 = $VMobj.Network0
                                $IPv6Address0 = $VMobj.IPAddress0[0]
                                $IPv4Address0 = $VMobj.IPAddress0[1]
                        }
                    }
                }
                2 {
                    switch ($VMobj.IPAddress1.Count)
                    {
                        1 {
                            $VLAN0 = $VMobj.VLAN0
                            $Network0 = $VMobj.Network0
                            $IPv6Address0 = $VMobj.IPAddress0[0]
                            $IPv4Address0 = $VMobj.IPAddress0[1]
                            $VLAN1 = $VMobj.VLAN1
                            $Network1 = $VMobj.Network1
                            $IPv6Address1 = $VMobj.IPAddress1[0]
                        }
                        2 {
                            $VLAN0 = $VMobj.VLAN0
                            $Network0 = $VMobj.Network0
                            $IPv6Address0 = $VMobj.IPAddress0[0]
                            $IPv4Address0 = $VMobj.IPAddress0[1]
                            $VLAN1 = $VMobj.VLAN1
                            $Network1 = $VMobj.Network1
                            $IPv6Address1 = $VMobj.IPAddress1[0]
                            $IPv4Address1 = $VMobj.IPAddress1[1]
                        }
                    }
                }
                3 {
                    switch ($VMobj.IPAddress2.Count)
                    {
                        1 
                            {
                            $VLAN0 = $VMobj.VLAN0
                            $Network0 = $VMobj.Network0
                            $IPv6Address0 = $VMobj.IPAddress0[0]
                            $IPv4Address0 = $VMobj.IPAddress0[1]
                            $VLAN1 = $VMobj.VLAN1
                            $Network1 = $VMobj.Network1
                            $IPv6Address1 = $VMobj.IPAddress1[0]
                            $IPv4Address1 = $VMobj.IPAddress1[1]
                            $VLAN2 = $VMobj.VLAN2
                            $Network2 = $VMobj.Network2
                            $IPv6Address2 = $VMobj.IPAddress2[0]
                            }
                        2 
                            {
                            $VLAN0 = $VMobj.VLAN0
                            $Network0 = $VMobj.Network0
                            $IPv6Address0 = $VMobj.IPAddress0[0]
                            $IPv4Address0 = $VMobj.IPAddress0[1]
                            $VLAN1 = $VMobj.VLAN1
                            $Network1 = $VMobj.Network1
                            $IPv6Address1 = $VMobj.IPAddress1[0]
                            $IPv4Address1 = $VMobj.IPAddress1[1]
                            $VLAN2 = $VMobj.VLAN2
                            $Network2 = $VMobj.Network2
                            $IPv6Address2 = $VMobj.IPAddress2[0]
                            $IPv4Address2 = $VMobj.IPAddress2[1]
                            }
                    }
                }
            }

            $CommandText = "INSERT [VMConversionData_PS] VALUES ('"+$VMobj.MoRef+"','"+$VMobj.DisplayName+"','"+$VMobj.VMHostName+"','"+$VMobj.VMHostID+"','"+$VMobj.GuestVMFQDN+"','"+$VMobj.GuestVMID+"','"+$VMobj.GuestID+"','"+$VMobj.GuestOS+"','"+$VMobj.GuestVMMB+"','"+$VMobj.GuestVMCPUCount+"','"+$VMobj.GuestVMHDProvisionedGB+"','"+$VMobj.GuestVMHDUsedGB+"','"+$VMobj.VMwareToolsVerion+"','"+$VMobj.VMwareHardwareVersion+"','"+$VMobj.FtInfo+"','"+$DiskLocation0+"','"+$DiskLocation1+"','"+$DiskLocation2+"','"+$Network0+"','"+$IPv6Address0+"','"+$IPv4Address0+"','"+$Network1+"','"+$IPv6Address1+"','"+$IPv4Address1+"','"+$Network2+"','"+$IPv6Address2+"','"+$IPv4Address2+"', '"+$VMobj.MAC0+"','"+$VMobj.MAC1+"','"+$VMobj.MAC2+"','"+$null+"','"+$null+"','"+$null+"', CURRENT_TIMESTAMP)"
         
            Write-Log 3 $CollectLog "Adding $($VMObj.DisplayName) to temp table"
            Write-Log 7 $CollectLog "Issuing $CommandText"
            $sqlcmd.CommandText = $CommandText
            $sqlcmd.ExecuteNonQuery() | out-null
        }
        Write-Log 1 $CollectLog "Executing Stored Procedure to sort by OS and transfer qualifying VMs to from Temp table into VMQueue"
        $sqlSPExeccmd = $connection.CreateCommand()
        $SPExecCommandText = "EXEC [sp_TransferVMConversionData_PS]"
        $sqlSPExeccmd.CommandText = $SPExecCommandText
        $sqlSPExeccmd.ExecuteNonQuery()
        $connection.Close()
    }
    if ($error.Count -ge 1)
        {
            Write-Log 1 $CollectLog "Error thrown. $error"
            Write-Log 1 $CollectLog "Collection completed with errors"
            exit
        }
    else
        {
            Write-Log 1 $CollectLog "Collection completed."
            Sleep 2
        }
}
#endregion

################################
###### Menu Region        ######
################################
#region menu

###### Main Menu ######
function Menu 
{
cls
$title = "     Main Menu"
$message = " "

$Collect = New-Object System.Management.Automation.Host.ChoiceDescription "Co&llect VMs", `
    "Collect VMs from VMware"

$Manage = New-Object System.Management.Automation.Host.ChoiceDescription "&Manage List", `
    "Manage List Menu"

$Start = New-Object System.Management.Automation.Host.ChoiceDescription "&Convert", `
    "Start Converting VMs in list"

$bye = New-Object System.Management.Automation.Host.ChoiceDescription "E&xit", `
    "Exit the script."

$options = [System.Management.Automation.Host.ChoiceDescription[]]($collect, $Manage, $start, $bye)

$result = $host.ui.PromptForChoice($title, $message, $options,3) 

switch ($result)
    {
        0 
            {
            StartCollection
            menu
            }
        1 {SubMenu}
        2 
            {
            Write-Log 1 $MATLog "---------------------------------------------"
            Write-Log 1 $MATLog "     Starting new batch of VMs      "
            Write-Log 1 $MATLog "---------------------------------------------"
            Write-Log 1 $MATLog "Logging events to $MATLog"
            Write-Log 1 $MATLog "Log Level = $VerboseLogging"
            Write-Log 7 $MATLog "Timer when Server is at Capacity = $MaxCapacityLoopTimer"
            Write-Log 1 $MATLog "Current Path = $CurrentPath"
            Write-Log 3 $MATLog "XML Path = $XMLPath"
            Write-Log 3 $MATLog "VMList = $VMList"
            Write-Log 3 $MATLog "Task Scheduler Job Name = $RemoteJobName"
            Write-Log 7 $MATLog "Task Arugements on Remotes = $TaskArgues"
            Write-Log 7 $MATLog "PowerShell Version = $BuildVersion"
            Write-Log 1 $MATLog "Delay Timer = $DelayTimer"
            Write-Log 3 $MATLog "Localhost = $Localhost"
            Write-Log 3 $MATLog "SQL Datasource = $Datasource"
            Write-Log 3 $MATLog "SQL Catalog = $Catalog"
            Write-Log 1 $MATLog "Max Concurrent Conversions = $Queuelength"

            ShowStatus $Lookback
            sleep 5
            ###### Use Delay Timer for executions in the future ######
            if ($DelayTimer -ge 1)
                {
                Write-Log 1 $MATLog "Delay Requested:True - Delaying execution for $DelayTimer seconds"
                Sleep -s $DelayTimer
                }
            else
                {
                Write-Log 3 $MATLog "Delay Requested:False - Starting immediate execution"
                }
            Start-Remotes
            }
        3 {exit}
    }
}

###### Submenu ######
function submenu 
{
cls
$title = "     Manage Menu"
$message = " "

$List = New-Object System.Management.Automation.Host.ChoiceDescription "&Create VMList", `
    "Writes available VMs to a text file"

$Update = New-Object System.Management.Automation.Host.ChoiceDescription "&Update DB", `
    "Update Database with VMs to convert and order"

$Show = New-Object System.Management.Automation.Host.ChoiceDescription "&Display VMs", `
    "Displays VMs ready to be converted"

$UpLevel = New-Object System.Management.Automation.Host.ChoiceDescription "&Main Menu", `
    "Return to main menu"

$options = [System.Management.Automation.Host.ChoiceDescription[]]($List, $Update, $show, $UpLevel)

$result = $host.ui.PromptForChoice($title, $message, $options,3) 

switch ($result)
    {
 
        0 
            {
            List "USE $Catalog SELECT DisplayName FROM VMDetails_VIEW where [ReadytoConvert] = 0 AND [Completed] <> 1 AND [Supported] = 1 ORDER BY DisplayName" 
            submenu
            }
        1 
            {
            UpdateDB
            submenu
            }
        2 
           {
            ShowVM   
            submenu            
            }
        3 {menu}
    }
}

###### Show VMs ######
function ShowVM
{
#Show machines ready for conversion
    cls
    Write-Host ""
    Write-Host "The following VMs are ready to be converted:"
    Write-Host ""
    $conn = New-Object System.Data.SqlClient.SqlConnection("Data Source=$DataSource; Initial Catalog=$Catalog; Integrated Security=SSPI")
    $conn.Open()
    $cmd = $conn.CreateCommand()
    $cmd.CommandText = "USE $Catalog SELECT DisplayName FROM VMDetails_VIEW where [ReadytoConvert] = 1 AND Completed = 0 ORDER BY Position"
    $Reader = $cmd.ExecuteReader()
    $gotrows = $Reader.HasRows
    if (!$gotrows) {Write-Host "No VMs set to convert"}
    while ($Reader.Read())
	    {
	    $VMname  = $Reader.GetValue(0)
	    Write-Host "$VMName"
        }
    $conn.Close()
    Write-Host ""
    read-host "Press Enter to continue"
}

###### List VMs ######
function List([string]$Query)
{
    cls
    if (Test-Path $VMList)
        {
        Write-Host ""
        Write-Log 3 $MATLog "VMList found"
        $ListExist = Get-Content $VMList
            if ($ListExist)
                { 
                Write-Log 1 $MATLog "VMList  not empty."
                $title = "Delete File"
                $message = "Do you want to delete $VMList ?"

                $yes = New-Object System.Management.Automation.Host.ChoiceDescription "&Yes", `
                    "Deletes $VMList."

                $no = New-Object System.Management.Automation.Host.ChoiceDescription "&No", `
                    "Keeps existing file"

                $options = [System.Management.Automation.Host.ChoiceDescription[]]($yes, $no)

                $result = $host.ui.PromptForChoice($title, $message, $options, 1) 

                switch ($result)
                    {
                        0 {
                            Write-Log 1 $MATLog "Deleting $VMList" Cyan
                            Sleep 1
                            Remove-item $VMList
                            List $Query
                           }
                        1 {
                            Write-Log 3 $MATLog "Returning to submenu"
                            sleep 1 
                            submenu
                          }
                    }
                
                }
            else 
                {
                    Write-Log 1 $MATLog "File found but it is empty"
                    Write-Log 1 $MATLog "Deleting $VMList"
                    Sleep 1
                    Remove-item $VMList
                    List $Query
                } 
            
        }
    else
    {
    Write-Host 
	$conn = New-Object System.Data.SqlClient.SqlConnection("Data Source=$DataSource; Initial Catalog=$Catalog; Integrated Security=SSPI")
	$conn.Open()
	$cmd = $conn.CreateCommand()
	$cmd.CommandText = "$Query"
	$Reader = $cmd.ExecuteReader()
    $gotrows = $Reader.HasRows
    if (!$gotrows) {Write-Log 1 $MATLog "No supported VMs found"}
	while ($Reader.Read())
	    {
		$DisplayName = $Reader.GetValue(0)
		add-content $VMList -value "$DisplayName"
        Write-Host "Adding $DisplayName"
        }
	$conn.Close()
    
    
    ###### Set permission so anyone can use $VMList ######
    if (Test-Path $VMList)
        {
        Write-Log 3 $MATLog "Setting file permissions on $VMList to Everyone - Full Control"
        $ACL = Get-ACL $VMList
        $FullControl = New-Object  system.security.accesscontrol.filesystemaccessrule("Everyone","FullControl","Allow")
        $ACL.SetAccessRule($FullControl)
        Set-ACL $VMList $ACL
        Write-Host ""
        Write-Host "Records added to $VMList" 
        Write-Host "--------------------------------------------------------------------------" -ForegroundColor Cyan
        Write-Host "This file will act as the list of VMs to convert in one 'batch'" -ForegroundColor Cyan
        Write-Host "Delete any VMs from this file that you don't want to convert right now" -ForegroundColor Cyan
        Write-Host "You may also reorder the list file to affect conversion order" -ForegroundColor Cyan
        Write-Host "Run the" -nonewline -ForegroundColor Cyan
        Write-Host " UPDATE" -ForegroundColor Yellow -NoNewline
        Write-Host " option when you have completed your edits" -ForegroundColor Cyan
        Write-Host "--------------------------------------------------------------------------" -ForegroundColor Cyan
        Write-Host ""
        read-host "Press Enter to continue"
        write-host $VMList
        notepad $VMList
        }
    }
}

###### Update database ######
function UpdateDB
{
  if (Test-Path $VMList)
        {
        cls
        Write-Host ""
            $a = Get-Content $VMList
            $length = $a.count 
            foreach ($vm in $a)
                {
                    $position = $vm.ReadCount
                    SQLWrite "UPDATE VMDetails_VIEW SET ReadytoConvert = 1, Position = $position, Summary = 'Ready to Convert' WHERE DisplayName = '$vm'"
                    Write-Host "$VM added to conversion queue"
                }
        }
    else 
        {
        Write-Log 1 $MATLog "Unable to find $VMList"
        Write-Host "Run the Create List function"
        Write-Host "Aborting"
        read-host "Press Enter to continue"
        submenu
        }
    Write-Host ""
    read-host "Press Enter to continue"

}
#endregion

###################################
###### Management Region     ######
###################################
#region Management
function Help
{
cls
    Write-Host "Syntax: PS>" -NoNewline
    Write-Host " .\ConvertVM.ps1" -ForegroundColor yellow -NoNewline
    Write-Host " " -ForegroundColor Green  -NoNewline
    Write-Host " (optional)" -ForegroundColor Gray -NoNewline
    Write-Host " " -ForegroundColor Cyan -NoNewline
    Write-Host " (optional)" -ForegroundColor Gray 
    Write-Host ""
    Write-Host "Valid" -NoNewline
    Write-Host " " -ForegroundColor Green -NoNewline
    Write-Host " choices:"
    Write-Host ""
    Write-Host "Collect" -ForegroundColor Green -NoNewline
    Write-Host " - Starts collection cycle"
    Write-Host "Convert" -ForegroundColor Green -NoNewline
    Write-Host " - Starts Conversion locally (Used on Remote servers)"
    Write-Host "Convert-Global" -ForegroundColor Green -NoNewline
    Write-Host " - Starts Conversion on Remotes and locally"
    Write-Host "Create-List" -ForegroundColor Green -NoNewline
    Write-Host " - Creates a VMList.txt based on unconverted VMs in database"
    Write-Host "Help" -ForegroundColor Green -NoNewline
    Write-Host " - Displays this screen"
    Write-Host "Menu" -ForegroundColor Green -NoNewline
    Write-Host " - Starts Main Menu" -NoNewline
    Write-Host " " -ForegroundColor Gray
    Write-Host "Purge" -ForegroundColor Green -NoNewline
    Write-Host " - Deletes ALL records from database" 
    Write-Host "Report-All" -ForegroundColor Green -NoNewline
    Write-Host " - Generates a CSV file with all records from the database"
    Write-Host "Report-Complete" -ForegroundColor Green -NoNewline
    Write-Host " - Generates a CSV file with all completed VMs from the database"
    Write-Host "Report-Incomplete" -ForegroundColor Green -NoNewline
    Write-Host " -  Generates a CSV file with all incomplete Vms from the database"
    Write-Host "Report-Unsupported" -ForegroundColor Green -NoNewline
    Write-Host "  - Generates a CSV file with all unsupported VMs from the database"
    Write-Host "Report-Warning" -ForegroundColor Green -NoNewline
    Write-Host "  - Generates a CSV file with all VMs that produced a warning during conversion"
    Write-Host "Reset-All" -ForegroundColor Green -NoNewline
    Write-Host " - Resets EVERY VM record in the database"
    Write-Host "Reset-Incomplete" -ForegroundColor Green -NoNewline
    Write-Host " - Resets every incompleted VM record in the database"
    Write-Host "Reset-List" -ForegroundColor Green -NoNewline
    Write-Host " - Resets only those incomplete VM records listed in $VMList"
    Write-Host "Show-Status" -ForegroundColor Green -NoNewline
    Write-Host " - Dislays the Status table once"
    Write-Host "Show-StatusLoop" -ForegroundColor Green -NoNewline
    Write-Host " - Dislays the Status table in an endless loop"
    Write-Host "Show-VM" -ForegroundColor Green -NoNewline
    Write-Host " - Dislays the VM that are Ready to Convert"
    Write-Host "Update" -ForegroundColor Green -NoNewline
    Write-Host " - Updates the records for all VMs in VMList.txt marking them as Ready to Convert"
    Write-Host ""
    Write-Host " [int]" -ForegroundColor Cyan -NoNewline
    Write-Host " Delays the start time of conversions (default is 0)"
    Write-Host ""
}

###### Reset records ######
function Reset ($option)
{
cls
switch ($option)
    {
        "List" 
            {
            if (Test-Path $VMList)
                {
                    $a = Get-Content $VMList
                    $length = $a.count 
                    foreach ($vm in $a)
                        {
                            $position = $vm.ReadCount
                            SQLWrite "UPDATE VMDetails_VIEW SET ReadytoConvert = 0, ConvertServer = NULL, InUse = NULL, Warning = NULL, Position = NULL, Starttime = NULL, EndTime = NULL, Status = 0, Summary = NULL, Notes = 'Record reset by Manage.ps1', PID = NULL WHERE Completed <> 1 AND DisplayName = '$vm'"
                            Write-Log 1 $MATLog "Record for $VM was reset"
                        }
                }
            else 
                {
                Write-Log 1 $MATLog "Unable to find $VMList"
                Write-Host "Run the Create List function."
                Write-Host "Aborting."
                read-host "Press Enter to continue"
                submenu
                }
        
                Write-Log 1 $MATLog "All VM records from $VMList were reset"
            }
        "Incomplete"
            {
            SQLWrite "UPDATE VMQueue SET ReadytoConvert = 0, ConvertServer = NULL, InUse = NULL, Warning = NULL, Position = NULL, Starttime = NULL, EndTime = NULL, Status = 0, Summary = NULL, Notes = 'Record reset by Manage.ps1', PID = NULL WHERE Completed <> 1"
            Write-Log 1 $MATLog "All database records not maked as complete reset."
            }
        "All" 
            {
            SQLWrite "UPDATE VMQueue SET ReadytoConvert = 0, ConvertServer = NULL, InUse = NULL, Warning = NULL, Position = NULL, Starttime = NULL, EndTime = NULL, Summary = NULL, Status = 0, Notes = 'Record reset by Manage.ps1', PID = NULL"
            Write-Log 1 $MATLog "All database records reset."
            }
    }
}
 
###### Delete ALL records ######
function Purge-DB
{
 cls
    $title = "Purge Database"
    $message = "Do you want to delete EVERY record from the database?"

    $yes = New-Object System.Management.Automation.Host.ChoiceDescription "&Yes", `
        "Wipes all records from datase."

    $no = New-Object System.Management.Automation.Host.ChoiceDescription "&No", `
        "Exit"

    $options = [System.Management.Automation.Host.ChoiceDescription[]]($yes, $no)

    $result = $host.ui.PromptForChoice($title, $message, $options, 1) 

    switch ($result)
        {
            0 {
                SQLWrite "USE $Catalog Delete from VMQueue"
                SQLWrite "USE $Catalog Delete from VMConversionData_PS"
                Write-Log 1 $MATLog "Deleted ALL records from database" Cyan
                }
            1 {
                exit
                }
        }
}        

###### Create CSV Report ######
function CSVReport ($Clause, $Filename)
{
$timestamp = get-date -format "MM.dd.yyyy-HH.mm"
$Reportname = "$Filename.$timestamp.csv"

if (Test-path $CurrentPath\Reports)
    {Write-Log 3 $MATLog "\Reports folder exists"}
else
    {New-Item $CurrentPath\Reports -Type directory
    Write-Log 3 $MATLog "\Reports folder created"}

if (Test-path $Reportname)
    {
    write-log 3 $MATLog "$Reportname exists, deleting it"
    Remove-Item $Reportname
    }

$Query = "USE $Catalog SELECT [JobID], [VMName], [Supported], [ReadytoConvert], [ConvertServer], [StartTime], [EndTime], [Status], [Summary], [Notes], [Completed], [PID], [Warning] FROM VMQueue $Clause"
Write-Log 7 $MATLog "Query: USE $Catalog SELECT [JobID], [VMName], [Supported], [ReadytoConvert], [ConvertServer], [StartTime], [EndTime], [Status], [Summary], [Notes], [Completed], [PID], [Warning] FROM VMQueue $Clause"
	$conn = New-Object System.Data.SqlClient.SqlConnection("Data Source=$DataSource; Initial Catalog=$Catalog; Integrated Security=SSPI")
	$conn.Open()
	$cmd = $conn.CreateCommand()
	$cmd.CommandText = "$Query"
	$Reader = $cmd.ExecuteReader()
    $gotrows = $Reader.HasRows
    if (!$gotrows) 
        {
        Write-Log 1 $MATLog "WARNING - No records found" Yellow
        exit
        }
    $table = @()
    $intCounter = 0

While ($Reader.Read())
	    {
		$ID  = $Reader.GetValue(0)
		$VMGuest = $Reader.GetValue(1)
        $Supported  = $Reader.GetValue(2)
        $Ready  = $Reader.GetValue(3)
        $CServer = $Reader.GetValue(4)
        $StartTime  = $Reader.GetValue(5)
        $EndTime  = $Reader.GetValue(6)
        $Status = $Reader.GetValue(7)
        $Summary  = $Reader.GetValue(8)
        $Notes  = $Reader.GetValue(9)
        $Complete  = $Reader.GetValue(10)
        $ProcID = $Reader.GetValue(11)
        $Warn = $Reader.GetValue(12)

        $CSVrow = New-Object psObject -Property @{'ID'=$ID;'VMName'=$VMGuest; 'Supported'=$Supported; 'ReadytoConvert'=$Ready; 'ConvertServer'=$CServer; 'StartTime'=$StartTime; 'EndTime'=$EndTime; 'Status'=$Status; 'Warnings'=$Warn; 'Summary'=$Summary; 'Notes'=$Notes; 'Completed'=$Complete; 'PID'=$ProcID}
        Export-Csv -InputObject $CSVrow -Append -Encoding UTF8 -Path $Reportname
        }
	$conn.Close()

#PURGE TOP LINE
(Get-Content $Reportname | Select-Object -Skip 1) | Set-Content $Reportname
Write-Log 1 $MATLog "CSV Report was created: $Reportname"
}
#endregion

##################################
###### Status Table Region  ######
##################################
#region status table

###### Show Table ######
function ShowStatus ([int]$Lookback)
{
cls
$Query = "USE $Catalog SELECT TOP $Showrows JOBID, DisplayName, ConvertServer, PID, Warning, Summary FROM VMDetails_VIEW WHERE (ReadytoCOnvert = 1) OR (ConvertServer is not NULL OR INuse = 1) OR (DATEDIFF(HOUR, EndTime, SYSDATETIME()) <= $Lookback)  ORDER by Position"

    #Setup datatable
    $ds = new-object System.Data.DataSet
    $ds.Tables.Add("Status")
        [void]$ds.Tables["Status"].Columns.Add("JobID",[int])
        [void]$ds.Tables["Status"].Columns.Add("VM Name",[string])
        [void]$ds.Tables["Status"].Columns.Add("PID",[int])
        [void]$ds.Tables["Status"].Columns.Add("ConvertServer",[string])
        [void]$ds.Tables["Status"].Columns.Add("Warning",[bool])
        [void]$ds.Tables["Status"].Columns.Add("Summary",[string])

	$conn = New-Object System.Data.SqlClient.SqlConnection("Data Source=$DataSource; Initial Catalog=$Catalog; Integrated Security=SSPI")
	$conn.Open()
	$cmd = $conn.CreateCommand()
	$cmd.CommandText = "$Query"
	$Reader = $cmd.ExecuteReader()
	while ($Reader.Read())
	    {
		$SID  = $Reader.GetValue(0)
		$SName = $Reader.GetValue(1)
        $SCServer = $Reader.GetValue(2)
        $SPID = $Reader.GetValue(3)
        $Warn = $Reader.GetValue(4)
        $SStatus = $Reader.GetValue(5)
        
    $dr = $ds.Tables["Status"].NewRow()
    $dr["JobID"] = $SID
    $dr["VM Name"] = $SName
    $dr["ConvertServer"] = $SCServer
    $dr["PID"] = $SPID
    $dr["Summary"] = $SStatus
    $dr["Warning"] = $Warn
    $ds.Tables["Status"].Rows.Add($dr)
            
	    }
	$conn.Close()

$ds.Tables["Status"] | Format-Table -autosize
Write-Host ""
Write-Host "Displaying queued, active conversions and any finished in the last $Lookback hour(s)" -ForegroundColor DarkGray
Write-Host "Displaying a maximum of $Showrows rows" -ForegroundColor DarkGray
Write-Host ""
}
#endregion 

##################################################################
### END FUNCTIONS ###
##################################################################

ConvertVM-Logging.ps1

##########################################
#      Migration Automation Script       #
#             version 1.5.2              #
#                                        #
#              logging.ps1               #
#                                        #
#         Copyright 2014 Microsoft       #
#                                        #
#      http:\\aka.ms\buildingclouds      #
##########################################
#BuildVersion = "1.5.2007"


###### Collect Params ######
param(
[string]$ID,
[string]$DataSource,
[String]$Catalog,
[string]$mvmclog,
[string]$MATLog,
[string]$VMName,
[int]$LogMonitorDelay,
[int]$VerboseLogging
)


###### Variables ######
$Status = "zzz"
$error.clear()

##################################################################
### FUNCTIONS  ###
##################################################################
#region functions

###### Write-Log ######
function write-log([int]$level, [string]$info)
{
    if ($level -le $VerboseLogging)
    {
        $time = get-date
        Write-Host "$time - (Log Monitor) [$VMName] $info"
        add-content $MATLog -value "$time - (Log Monitor) [$VMName] $info"
    }
} 

###### SQL Writer ######
function SQLWrite([string]$Query)
{
	$conn = New-Object System.Data.SqlClient.SqlConnection("Data Source=$DataSource; Initial Catalog=$Catalog; Integrated Security=SSPI")
	$conn.Open()
	$cmd = $conn.CreateCommand()
	$cmd.CommandText = "$Query"
	$cmd.ExecuteNonQuery() |Out-Null
	$conn.Close()

    if ($VerboseLogging -ge 7)
        {Write-Log 7 "$Query"}
}


###### Check the Log ######
function Invoke-LogCheck
{
#verify Log file exists
if (Test-Path $mvmclog)
        {
        Write-Log 1 "Found $mvmclog. Looking for updates."
        }
        else
        {
        Write-Log 1 "Terminating. Unable to find $mvmclog. Monitoring can not proceed. This is an unexpected condition."
        exit
        }


$LogCheck = Select-String -Path $mvmclog -Pattern "_4127   End - Machine conversion" -CaseSensitive -quiet
    If ($LogCheck)
        {
        $Status = "End" 
        Write-Log 1 "Conversion Ended"
        SQLWrite "UPDATE VMQueue SET Summary = 'Conversion Ended' WHERE JobID = $ID"
        }
    else {

        $LogCheck = Select-String -Path $mvmclog -Pattern "_6570 Start - Creating and configuring virtual machine on the Hyper-V host" -CaseSensitive -quiet
            If ($LogCheck)
                {
                $Status = "Stage 6 - Creating virtual machine on the Hyper-V host"
                Write-Log 3 "$Status"
                SQLWrite "UPDATE VMQueue SET Summary = '$Status' WHERE JobID = $ID" 
                }
            else {

             $LogCheck = Select-String -Path $mvmclog -Pattern "_2562 Start - Copying converted disks" -CaseSensitive -quiet
                If ($LogCheck)
                    {
                    $Status = "Stage 5 - Copying converted disks"
                    Write-Log 3 "$Status"
                    SQLWrite "UPDATE VMQueue SET Summary = '$Status' WHERE JobID = $ID" 
                    }
                else {

                 $LogCheck = Select-String -Path $mvmclog -Pattern "_5758 Start - Converting VMware virtual disks" -CaseSensitive -quiet
                    If ($LogCheck)
                        {
                        $Status = "Stage 4 - Converting Disks"
                        Write-Log 3 "$Status"
                        SQLWrite "UPDATE VMQueue SET Summary = '$Status' WHERE JobID = $ID" 
                        }
                    else {
                     $LogCheck = Select-String -Path $mvmclog -Pattern "Restoring source virtual machine to original state" -CaseSensitive -quiet
                        If ($LogCheck)
                            {
                            $Status = "Stage 3 - Restoring source VM to original state"
                            Write-Log 3 "$Status"
                            SQLWrite "UPDATE VMQueue SET Summary = '$Status' WHERE JobID = $ID" 
                            }
                        else {

                        $LogCheck = Select-String -Path $mvmclog -Pattern "Downloading VMware virtual disks from source virtual machine" -CaseSensitive -quiet
                            If ($LogCheck)
                                {
                                $Status = "Stage 2 - Downloading VM disks"
                                Write-Log 3 "$Status"
                                SQLWrite "UPDATE VMQueue SET Summary = '$Status' WHERE JobID = $ID"   
                                }
                            else {

                            $LogCheck = Select-String -Path $mvmclog -Pattern "_3609 Start - Preparing source virtual machine" -CaseSensitive -quiet
                                If ($LogCheck)
                                    {
                                    $Status = "Stage 1 - Preparing VM"
                                    Write-Log 3 "$Status"
                                    SQLWrite "UPDATE VMQueue SET Summary = '$Status' WHERE JobID = $ID"   
                                    }
                                else {

                                $LogCheck = Select-String -Path $mvmclog -Pattern "_4127 Start - Machine conversion" -CaseSensitive -quiet
                                    If ($LogCheck)
                                        {
                                        $Status = "Stage 0 - Conversion Started"
                                        Write-Log 3 "$Status"
                                        SQLWrite "UPDATE VMQueue SET Summary = '$Status' WHERE JobID = $ID"   
                                        }
                                    else
                                        {
                                        #IF we get this far without $LogCheck = True something is wrong
                                        Write-Log  "Unable to find any events in MVMC.log file. Unable to monitor log"
                                        SQLWrite "UPDATE VMQueue SET Summary = 'Unable to monitor log' WHERE JobID = $ID" 
                                        }
}}}}}}}
$Status
}


##################################################################
### END FUNCTIONS ###
##################################################################
#endregion

##################################################################
### SCRIPT BODY ###
##################################################################

Write-Log 1 "Log Monitor has started. Monitoring MVMC.log for activity about $VMName"

#Give exe a chance to settle and write first log entry
sleep 5 

Write-Log 3 "Looking for MVMC.exe for $VMName in memory"
if (Get-Process MVMC)
    {
    write-log 1 "Found MVMC.exe for $VMName. Starting Monitor"
    $Status = Invoke-LogCheck
    }
else
    {
    Write-Log 1 "Terminating. Unable to find MVMC.exe for $VMName in memory. Unable to monitor."
    exit
    }

     Write-Log 1 $Status
If ($Status -match "zzz")
    {
    Write-Log 1 "Logging monitor failed to execute properly."
    exit
    }

while ($Status -notmatch "End")
    {
    Sleep $LogMonitorDelay
    Write-Log 3 "Looking for MVMC.exe for $VMName in Memory"
        if (Get-Process MVMC)
            {
            write-log 3 "Found MVMC.exe for $VMName. Starting Monitor"
            $Status = Invoke-LogCheck
            }
        else
            {
            Write-Log 1 "Terminating. MVMC.exe for $VMName is no longer in memory. It may have been termiated by the user. Unable to monitor."
            exit
            }
    }

if ($Status -match "End")
    {
    #Log that we are done
    Write-Log 1 "Conversion of $VMName has finished. Exiting log monitor."
    exit 
    }

ConvertVM-Process.ps1

##########################################
#      Migration Automation Script       #
#             version 1.5.2              #
#                                        #
#        ConvertVM-Process.ps1           #
#                                        #
#         Copyright 2014 Microsoft       #
#                                        #
#      http:\\aka.ms\buildingclouds      #
##########################################

#BuildVersion = "1.5.2007"


###### Collect Params ######
param(
[string]$ID,
[string]$DataSource,
[String]$Catalog,
[string]$MATLog,
[string]$DisplayName,
[string]$VMFQDN,
[string]$Logpath,
[string]$CurrentPath,
[int]$VerboseLogging,
[string]$XMLPath,
[string]$MigrateNICs
)

###### Variables ######
$error.clear()
$LogOption = 1
$LogMonitorDelay = 30
$SleepMulitplier = 1.3
$Computername = Get-Childitem env:computername 
$localhost = $Computername.Value
$fpath = "C:\Program Files (x86)\Microsoft Virtual Machine Converter Solution Accelerator\MVMC.exe"

#Option to monitor the MVMC.log file created during conversion (0 = off)
#How Verbose should logging be (0 = limted, 3 = normal, 7 = full)

##################################################################
### FUNCTIONS ###
##################################################################
#region functions

Function Set-ScriptVariable ($Name,$Value) 
    {
    Invoke-Expression ("`$Script:" + $Name + " = `"" + $Value + "`"")
    }

function write-log([int]$level, [string]$info)
{
    if ($level -le $VerboseLogging)
    {
        $time = get-date
        Write-Host "$time - [$DisplayName] $info"
        add-content $MATLog -value "$time - [$DisplayName] $info"
    }
} 

###### SQL Writer ######
function SQLWrite([string]$Query)
{
	$conn = New-Object System.Data.SqlClient.SqlConnection("Data Source=$DataSource; Initial Catalog=$Catalog; Integrated Security=SSPI")
	$conn.Open()
	$cmd = $conn.CreateCommand()
	$cmd.CommandText = "$Query"
	$cmd.ExecuteNonQuery() |Out-Null
	$conn.Close()

    if ($VerboseLogging -ge 7)
        {Write-Log 7 "$Query"}
}

###### Do Convert ######
function DoConvert
{

###### Determine MVMC.log file ######
$trylog = $Logpath+"mvmc.log"
    if (Test-Path $trylog) 
        {
        $trylog = $Logpath+"mvmc_1.log"
        if (Test-Path $trylog) 
            {
            $trylog = $Logpath+"mvmc_2.log"
            if (Test-Path $trylog) 
                {
                $trylog = $Logpath+"mvmc_3.log"
                if (Test-Path $trylog) 
                {$mvmclog = $Logpath+"mvmc_3.log"}
                    else 
                    {
                    Write-Log 1 "Please clear the log files from the $logpath"
                    Write-Log 1 "Terminating conversion of $DisplayName"
                    exit}
                        }                                            
                else {$mvmclog = $logpath+"mvmc_2.log"}
                    }
        else {$mvmclog = $logpath+"mvmc_1.log"}
        }
    else {$mvmclog = $trylog}
    Write-Log 3 "Logfile is $mvmclog"
        
    ###### Call Log monitor (if used) ######
    if ($LogOption -eq 1)
        {
        Write-Log 3 "Log monitoring Requested:True - Calling Log monitor"
        Start-Process -FilePath powershell.exe -ArgumentList "$CurrentPath\ConvertVM-Logging.ps1 $ID $Datasource $Catalog $mvmclog $MATLog $DisplayName $LogMonitorDelay $VerboseLogging" -WindowStyle Minimized
        Write-Log 7 "Start-Process -FilePath powershell.exe -ArgumentList '$CurrentPath\ConvertVM-Logging.ps1 $ID $Datasource $Catalog $mvmclog $MATLog $DisplayName $LogMonitorDelay $VerboseLogging' -WindowStyle Minimized"
        }
    else
        {
        Write-Log 3 "Log monitoring Requested:False"
        }
   
    #Create Parameters
    $params = "$dynamicdisk /SourceHost:$shost /SourceHostUser:$shusername /SourceHostPwd:$shpwd /GuestVM:$VMFQDN /GuestUser:$gUser /GuestPwd:$gPwd /TargetHost:$thost /TargetVHDPath:$tpath $sPower $tPower"
    $DisplayNamelog = $DisplayName+".log"
    $time = get-date
        
    Write-Log 1 "-------------- Starting conversion of $DisplayName ($ID) on $localhost targeting Hyper-V host:$thost via share:$tpath " 

           
    #CONVERT and collect PID
    Write-Log 3 "Command Issued: $dynamicdisk $fpath /SourceHost:$shost /SourceHostUser:$shusername /SourceHostPwd:*** /GuestVM:$VMFQDN /GuestUser:$gUser /GuestPwd:*** /TargetHost:$thost /TargetVHDPath:$tpath $sPower $tPower"


        #This is the actual conversion command"
        $doconvert = Start-Process -FilePath $fpath -ArgumentList $params -PassThru -WindowStyle Minimized
                  
        $convertPID = $doconvert.Id
        Write-Log 1 "MVMC started using PID:$convertPID"
        SQLWrite "UPDATE VMQueue SET PID = $convertPID  WHERE JobID = $ID"   

        try
        {
            while (Get-Process -id $convertPID -ErrorAction Stop) 
            {
            Write-log 3 "(Process Monitor) [$DisplayName] MVMC.exe is still running. PID $ConvertPID is still in memory."
            Sleep $SleepTime
            }
        }
        catch
        {
             write-log 1 "MVMC.exe has exited"
        }
       
        $doconvert.HasExited | Out-Null  # this will calculate the “exitCode” field
        $convertExitCode = $doconvert.GetType().GetField("exitCode", "NonPublic,Instance").GetValue($doconvert)

        #Wait for Log monitor to end
        Sleep $SleepTime 

        #Check Error Codes
        # ERROR CODE LIST
        # 0 = Success
        # -1 = HelpRequested
        # -2 = InvalidCommandLine
        # -3 = SourceHostNotFound
        # -4 = GuestVirtualMachineNotFound
        # -5 = TargetHostNotFound
        # -6 = UnexpectedException in MVMC.exe

            #Set results based on Exit codes
            $convertResults = "Unknown Error"
            $time = get-date
            if ($convertExitCode -eq 0)
                {
                $convertResults = "Successful Conversion"
                SQLWrite "UPDATE VMQueue SET Completed = 1, Status = 1, Summary = '$convertResults', PID = NULL, EndTime = '$time'  WHERE JobID = $ID" 
                }
            if ($convertExitCode -eq -1)
                {
                $convertResults = "Help Request by Application"
                SQLWrite "UPDATE VMQueue SET Completed = 0, Status = 3, Summary = '$convertResults', PID = NULL, EndTime = '$time'  WHERE JobID = $ID" 
                }
            if ($convertExitCode -eq -2)
                {
                $convertResults = "Invalid Command line"
                SQLWrite "UPDATE VMQueue SET Completed = 0, Status = 3, Summary = '$convertResults', PID = NULL, EndTime = '$time'  WHERE JobID = $ID" 
                }
            if ($convertExitCode -eq -3)
                {
                $convertResults = "Source VMware Host Not Found"
                SQLWrite "UPDATE VMQueue SET Completed = 0, Status = 3, Summary = '$convertResults', PID = NULL, EndTime = '$time'  WHERE JobID = $ID" 
                }
            if ($convertExitCode -eq -4)
                {
                $convertResults = "Guest VM Not Found"
                SQLWrite "UPDATE VMQueue SET Completed = 0, Status = 3, Summary = '$convertResults', PID = NULL, EndTime = '$time'  WHERE JobID = $ID" 
                }
            if ($convertExitCode -eq -5)
                {
                $convertResults = "Target Hyper-V Host Not Found"
                SQLWrite "UPDATE VMQueue SET Completed = 0, Status = 3, Summary = '$convertResults', PID = NULL, EndTime = '$time'  WHERE JobID = $ID" 
                }
            if ($convertExitCode -eq -6)
                {
                $convertResults = "FAILED - Unexpected Execption in MVMC.exe"
                SQLWrite "UPDATE VMQueue SET Completed = 0, Status = 3, Summary = '$convertResults', PID = NULL, EndTime = '$time'  WHERE JobID = $ID" 
                }
        
        Write-Log 1 "[$DisplayName] Exitcode = $convertExitCode Errorlevel:$convertResults"
                       
            #If we return "Unknown error" something is WRONG - bail from process
            if ($convertResults -eq "Unknown Error")
                {
                Write-Log 1 "[$DisplayName] ERROR Terminating. Unknow error. MVMC did not return an exit code."
                SQLWrite "UPDATE VMQueue SET Completed = 0, Status = 3, Summary = 'Unknown Error', PID = NULL, EndTime = '$time'  WHERE JobID = $ID"
                exit
                }
                
        
        #Handle log file renames
        if (Test-Path $logpath$DisplayNamelog)
        { 
            $datestamp = get-date -Format hhmmssMMddyy
            $oldVMlog = "$DisplayName-" + $datestamp +".log"
            Rename-Item $mvmclog $oldVMlog
            Write-Log 1 "Renaming existing $DisplayName.log to $oldVMlog"
        }


        if (Test-Path $mvmclog)
            {
            Rename-Item $mvmclog $DisplayNamelog
            write-log 1 "Renaming $mvmclog to $DisplayNamelog"
            }
        sleep 5
}
 
###### Handles rebuilding function
function RebuildNICs  
{
    Import-Module -Name Hyper-V
    Write-log 1 "Connecting to new VM for $DisplayName on Hyper-V"
    $NewVM = Hyper-V\Get-VM -Name $DisplayName -ComputerName $thost
    if (!$NewVM)
        {
        Write-log 1 "Unable to find VM: $DisplayName on Hyper-V"
        SQLWrite "UPDATE VMDetails_VIEW SET [Warning] = 1 WHERE JobID = $ID" 
        return
        }
 
    #Collect data about VM
    $conn = New-Object System.Data.SqlClient.SqlConnection("Data Source=$DataSource; Initial Catalog=$Catalog; Integrated Security=SSPI")
	$conn.Open()
	$cmd = $conn.CreateCommand()
	$cmd.CommandText = "USE $Catalog SELECT Status, Network0, MAC0, VLAN0, Network1, MAC1, VLAN1, Network2, MAC2, VLAN2 FROM VMDetails_VIEW WHERE JobID = $ID"
	$Reader = $cmd.ExecuteReader()
	while ($Reader.Read())
	{
        $status =  $Reader.GetValue(0)
        $Network0 = $Reader.GetValue(1)
        $MACAddress0 = $Reader.GetValue(2)
        $VLAN0 = $Reader.GetValue(3)
        $Network1 = $Reader.GetValue(4)
        $MACAddress1 = $Reader.GetValue(5)
        $VLAN1 = $Reader.GetValue(6)
        $Network2 = $Reader.GetValue(7)
        $MACAddress2 = $Reader.GetValue(8)
        $VLAN2 = $Reader.GetValue(9)
    }
    $conn.Close()
    Write-log 7 "Collected VM info from database"

    if ($status -ne 3)
    {
        Write-Log 7 "VM converted successfully" 
        #Rebuild Networks
        Try
        {   
            $start = get-date
            VM-Rebuild 
        }
        Catch
        {
            write-log 1 "ERROR: Network Rebuild for $DisplayName failed: $_.exception.message" Red
            SQLWrite "UPDATE VMDetails_VIEW SET [Warning] = 1, [Summary] = 'Network rebuild failed' WHERE JobID = $ID" 
        }
    }
    else
    {Write-Log 1 "ERROR Unable to Rebuild settings for $DisplayName since the conversion failed"  Yellow}
 
}

###### Recreate NICs from db settings 
Function VM-Rebuild
{
SQLWrite "UPDATE VMDetails_VIEW SET [Summary] = 'Conversion Completed restoring VM Network Settings' WHERE JobID = $ID"
Write-Log 1 "Rebuilding networks for $DisplayName" Yellow 

    Function AddVMNetAdapter
    {
        Param($VM,$NetworkName,$MAC,$VLAN)
        Try
        {
            Write-log 3 "Creating $NetworkName NetAdapter on $($VM.Name)"
            $VMNic = $VM | Hyper-V\Add-VMNetworkAdapter -SwitchName $NetworkName -StaticMacAddress $MAC
            if ($VLAN0 -ne 0)
            {
                write-log 3 "Setting NetAdapter to VLAN $VLAN"
                $VM | Hyper-V\Get-VMNetworkAdapter | Where { $_.SwitchName -eq $NetworkName} | Hyper-V\Set-VMNetworkAdapterVlan -VlanId $VLAN -Access
            }
        }
        catch
        {
            SQLWrite "UPDATE VMDetails_VIEW SET [Completed] = 0, [Status] = 3, [EndTime] = '$(Get-Date)', [Summary] = 'Error: Creating VM Network Adapter.' WHERE JobID = $ID" 
            write-log 1 "Error: Creating VM Network Adapter. $($_.exception.message)" Red
        }
    }

        
    Try
    {
        Write-log 1 "Removing default NICs from $DisplayName on Hyper-V"
        $NewVM|Hyper-V\Get-VMNetworkAdapter|Hyper-V\Remove-VMNetworkAdapter -Confirm:$false
    }
    catch
    {
        write-log 1 "WARNING - Unable to Remove Adapaters for $DisplayName $($_.exception.message)" Red
        SQLWrite "UPDATE VMDetails_VIEW SET [Warning] = 1, [Summary] = 'Error: Creating VM $($_.exception.message)' WHERE JobID = $ID" 
        return
    }
    
    # Add Network
    if ($Network0)
    {
        if (Get-VMNetworkAdapter -ComputerName $thost -SwitchName $Network0 -ManagementOS)
            {
            Write-log 1 "Adding Network0"
            AddVMNetAdapter -VM $NewVM -NetworkName $Network0 -MAC $MACAddress0 -VLAN $VLAN0
            }
        else 
            {
            Write-log 1 "WARNING - Hyper-V Switch/Adapter $Network0 does not exist"
            SQLWrite "UPDATE VMDetails_VIEW SET [Warning] = 1, [Summary] = 'Hyper-V Switch/Adapter $Network0 does not exist' WHERE JobID = $ID" 
            }

    }
    if ($Network1)
    {
        if (Get-VMNetworkAdapter -ComputerName $thost -SwitchName $Network1 -ManagementOS)
            {
            Write-log 1 "Adding Network1"
            AddVMNetAdapter -VM $NewVM -NetworkName $Network1 -MAC $MACAddress1 -VLAN $VLAN1
            }
        else 
            {
            Write-log 1 "WARNING - Hyper-V Switch/Adapter $Network1 does not exist"
            SQLWrite "UPDATE VMDetails_VIEW SET [Warning] = 1, [Summary] = 'Hyper-V Switch/Adapter $Network1 does not exist' WHERE JobID = $ID" 
            }
    }
    if ($Network2)
    {
        if (Get-VMNetworkAdapter -ComputerName $thost -SwitchName $Network2 -ManagementOS)
            {
            Write-log 1 "Adding Network2"
            AddVMNetAdapter -VM $NewVM -NetworkName $Network2 -MAC $MACAddress2 -VLAN $VLAN2
            }
        else 
            {
            Write-log 1 "WARNING - Hyper-V Switch/Adapter $Network2 does not exist"
            SQLWrite "UPDATE VMDetails_VIEW SET [Warning] = 1, [Summary] = 'Hyper-V Switch/Adapter $Network2 does not exist' WHERE JobID = $ID" 
            }
    }
       
    Write-log 3 "Starting $NewVM to rebuild network interfaces"
    $NewVM | Hyper-V\Start-VM
    
    #Call VM wait
    Write-log 7 "Calling VM-Wait function"
    VM-Wait 
}

###### Wait for the network to fix the Mac Address #####
Function VM-Wait
{
SQLWrite "UPDATE VMQueue SET [Summary] = 'Waiting for $DisplayName to finish network rebuild' WHERE JobID = $ID"
Write-Log 1 "Waiting for network rebuild and reboot" Yellow 

        Import-Module -Name Hyper-V
        # wait until the VM is powered off and then set all net adapeters 
        # to use dynamic mac addresses unless the global is set to keep.
        $watchDog = 20
        While ($NewVM.State -ne 'Off')
        {
            if ($Current -lt $watchDog)
                {
                    $Current++
                    Start-Sleep -Seconds 15
                    Write-Log 7 "Current counter at $Current, Waiting until it reaches $Watchdog"
                }
            else
                {
                    SQLWrite "UPDATE VMDetails_VIEW SET [Warning] = 1, [Summary] = 'WARNING - $DisplayName failed to Power on in the time allowed' WHERE JobID = $ID" 
                    Write-Log 3 "WARNING - $DisplayName failed to Power on in the time allowed." Yellow
                }

        }

        if ($DynMac -eq 1)
            {Get-VM $DisplayName | Get-VMNetworkAdapter | Set-VMNetworkAdapter -DynamicMacAddress}
        Get-VM $DisplayName | Start-VM
        SQLWrite "UPDATE VMQueue SET [InUse] = NULL, [ConvertServer] = NULL, [EndTime] = '$(Get-Date)', [Completed] = 1, [Status] = 1, [Summary] = 'Conversion Completed in $([math]::Round((New-TimeSpan $time).TotalMinutes)) Minutes' WHERE JobID = $ID" 
        Write-Log 1 "Completed $DisplayName conversion" Green


        #power on VM
        if ($finalpowerstate -eq 1)
            {        
                Write-log 3 "Starting $NewVM and completeing conversion"
                $NewVM | Hyper-V\Start-VM
            }
}   

 #endregion
  
##################################################################
### SCRIPT BODY ###
##################################################################
Write-Log 1 "Starting ConvertVM-Process"
Write-Log 1 "Record ID = $ID"
Write-Log 1 "Datasource = $DataSource"
Write-Log 1 "Catalog = $Catalog"
Write-Log 1 "MAT log = $MATLog"
Write-Log 1 "DisplayName = $DisplayName"
Write-Log 1 "FQDN = $VMFQDN"
Write-log 1 "Log path = $Logpath"
Write-log 1 "Current Path = $CurrentPath"
Write-log 1 "Logging level = $VerboseLogging"
Write-Log 1 "XML Path = $XMLPath"
Write-log 1 "Migrate NICs = $MigrateNICs"

$Variable = [XML] (Get-Content "$XMLPath")
$Variable.MAT.VMware | ForEach-Object {$_.Variable} | Where-Object {$_.Name -ne $null} | ForEach-Object {Set-ScriptVariable -Name $_.Name -Value $_.Value}
$Variable.MAT.HyperV | ForEach-Object {$_.Variable} | Where-Object {$_.Name -ne $null} | ForEach-Object {Set-ScriptVariable -Name $_.Name -Value $_.Value}
 
###### Get Remaining XML Settings
Write-Log 1 "Hyper-V Share = $tpath"
Write-Log 3 "Source Power = $sPower"
Write-Log 3 "Target Power = $tPower"
Write-Log 1 "VMware Host = $shost"
Write-Log 1 "Hyper-V Host = $thost"
Write-Log 1 "Dynamic Disk = $dynamicdisk"
Write-Log 3 "Log Monitor Delay = $LogMonitorDelay"
Write-Log 3 "Sleep Multiplier = $SleepMulitplier"
$SleepTime = ($LogMonitorDelay * $SleepMulitplier)
write-log 1 "Process Monitor interval = $SleepTime"

#Set Power On Options for CLI
if ($sPower -eq 1) 
    {$sPower = "/PowerOnSourceVM"}
else
    {$sPower = ""}

#Set final powerstates for CLI and Network options
if ($tPower -eq 1) 
    {
    if ($MigrateNICs = 1)
        {
        $tPower = ""
        $finalpowerstate = 1
        }
    else
        {
        $tPower = "/PowerOnDestinationVM"
        $finalpowerstate = 1
        }
    }
else
    {
    $tPower = ""
    $finalpowerstate = 0
    }

if ($dynamicdisk -eq 1) 
    {$dynamicdisk = "/Dynamic"}
else
    {$dynamicdisk = $null}

###### Start ######
write-log 1 "-------Starting Conversion"
$time = get-date
SQLWrite "UPDATE VMQueue SET [Summary] = 'Starting Conversion', [StartTime] = '$time' WHERE JobID = $ID" 
DoConvert
if ($MigrateNICs -eq 1)
    {RebuildNICs}
Write-log 1 "Done with Conversion." yellow 
SQLWrite "UPDATE VMQueue SET InUSe = NULL, ConvertServer = NULL WHERE JobID = $ID" 

Setup MAT.sql

/******    Migration Automation Script    ******/
/******           Database setup          ******/
/******           version 1.5.2           ******/
/******                                   ******/
/******     Copyright 2014 Microsoft      ******/
/******                                   ******/
/******    http:\\aka.ms\buildingclouds   ******/


USE [master]

/****** Create Database [MAT]   ******/
DECLARE @data_path nvarchar(256);
SET @data_path = (SELECT SUBSTRING(physical_name, 1, CHARINDEX(N'master.mdf', LOWER(physical_name)) - 1)
                  FROM master.sys.master_files
                  WHERE database_id = 1 AND file_id = 1);

IF EXISTS(SELECT * FROM sys.sysdatabases where name='MAT')
        GOTO MATDB_Exists
ELSE 
        Print 'Creating the MAT Database v1.5.2'

Execute('CREATE DATABASE [MAT]
ON  PRIMARY 
	(
	NAME = MAT
	,FILENAME = ''' + @data_path + 'MAT.mdf''
	,SIZE = 4096KB 
	,MAXSIZE = UNLIMITED
	,FILEGROWTH = 1024KB )
	LOG ON 
	(
	NAME = MAT_log
	,FILENAME = ''' + @data_path + 'MAT_log.ldf''
	,SIZE = 1024KB 
	,MAXSIZE = 2048MB 
	,FILEGROWTH = 10%
	)'
)
ALTER DATABASE [MAT] SET COMPATIBILITY_LEVEL = 100


IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
begin
EXEC [MAT].[dbo].[sp_fulltext_database] @action = 'enable'
end


ALTER DATABASE [MAT] SET ANSI_NULL_DEFAULT OFF 
ALTER DATABASE [MAT] SET ANSI_NULLS OFF 
ALTER DATABASE [MAT] SET ANSI_PADDING OFF 
ALTER DATABASE [MAT] SET ANSI_WARNINGS OFF 
ALTER DATABASE [MAT] SET ARITHABORT OFF 
ALTER DATABASE [MAT] SET AUTO_CLOSE OFF 
ALTER DATABASE [MAT] SET AUTO_CREATE_STATISTICS ON 
ALTER DATABASE [MAT] SET AUTO_SHRINK OFF 
ALTER DATABASE [MAT] SET AUTO_UPDATE_STATISTICS ON 
ALTER DATABASE [MAT] SET CURSOR_CLOSE_ON_COMMIT OFF 
ALTER DATABASE [MAT] SET CURSOR_DEFAULT  GLOBAL 
ALTER DATABASE [MAT] SET CONCAT_NULL_YIELDS_NULL OFF 
ALTER DATABASE [MAT] SET NUMERIC_ROUNDABORT OFF 
ALTER DATABASE [MAT] SET QUOTED_IDENTIFIER OFF 
ALTER DATABASE [MAT] SET RECURSIVE_TRIGGERS OFF 
ALTER DATABASE [MAT] SET  DISABLE_BROKER 
ALTER DATABASE [MAT] SET AUTO_UPDATE_STATISTICS_ASYNC OFF 
ALTER DATABASE [MAT] SET DATE_CORRELATION_OPTIMIZATION OFF 
ALTER DATABASE [MAT] SET TRUSTWORTHY OFF 
ALTER DATABASE [MAT] SET ALLOW_SNAPSHOT_ISOLATION OFF 
ALTER DATABASE [MAT] SET PARAMETERIZATION SIMPLE 
ALTER DATABASE [MAT] SET READ_COMMITTED_SNAPSHOT OFF 
ALTER DATABASE [MAT] SET HONOR_BROKER_PRIORITY OFF 
ALTER DATABASE [MAT] SET  READ_WRITE 
ALTER DATABASE [MAT] SET RECOVERY FULL 
ALTER DATABASE [MAT] SET  MULTI_USER 
ALTER DATABASE [MAT] SET PAGE_VERIFY CHECKSUM  
ALTER DATABASE [MAT] SET DB_CHAINING OFF
Print 'MAT Database created.' 
GOTO MATDB_Done

MATDB_Exists:
Print 'The MAT Database already exists.'
GOTO MATDB_Done

MATDB_Done: 
GO

/****** Finshied Creating Database [MAT]   ******/
USE [MAT]

/****** Creat Version Table  ******/
IF EXISTS (SELECT 1 FROM sysobjects WHERE xtype='u' AND name='VERSION') 
        GOTO VERSION_Exists
ELSE 
	SET ANSI_NULLS ON
	SET QUOTED_IDENTIFIER ON
	
CREATE TABLE [dbo].[VERSION](
	[InternalVersion] [int] NOT NULL,
	[DBVersion] [varchar](10),
 CONSTRAINT [PK_VERSION] PRIMARY KEY CLUSTERED 
(
	[InternalVersion] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
INSERT INTO [dbo].[VERSION] VALUES ('152', '1.5.2')
Print 'Version Table created.'

GOTO VERSION_Done

VERSION_Exists:
Print 'VERSION already exists.'
/****** Verify newer version is NOT Installed   ******/
	IF (Select [InternalVersion]from [VERSION]) > 152
		BEGIN
		Print 'The Exitsing database version is newer than this version. Terminating.'
		Print 'No changes were made.' 
		set noexec on 
		END
	IF (Select [InternalVersion]from [VERSION]) < 152
		BEGIN
		Print 'Updating the database version.'
		Update [Version] SET [InternalVersion] = '152'
		Update [Version] SET [DBVersion] = '1.5.2'
		
		END
GOTO VERSION_Done

VERSION_Done: 
GO


/******  Create Table [dbo].[VMQueue] ******/
IF EXISTS (SELECT 1 FROM sysobjects WHERE xtype='u' AND name='VMQueue') 
        BEGIN
        Drop Table VMQueue 
        Print 'Dropped Table VMQueue'
        END
	ELSE         
	SET ANSI_NULLS ON
	SET QUOTED_IDENTIFIER ON

CREATE TABLE [dbo].[VMQueue](
	[JobID] [int] IDENTITY(1,1) NOT NULL,
	[VMName] [nvarchar](255) NOT NULL,
	[objectID] [nvarchar](255) NULL,
	[Supported] [tinyint] NOT NULL,
	[ReadytoConvert] [bit] NULL,
	[ConvertServer] [nvarchar](255) NULL,
	[StartTime] [datetime] NULL,
	[EndTime] [datetime] NULL,
	[Status] [tinyint] NULL,
	[Warning] [bit] NULL,
	[Summary] [nvarchar](255) NULL,
	[Notes] [nvarchar](255) NULL,
	[Completed] [bit] NULL,
	[PID] [int] NULL,
	[Position] [int] NULL,
	[InUse] [bit] NULL,
 CONSTRAINT [PK_VMQueue] PRIMARY KEY CLUSTERED 
(
	[JobID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

Print 'VMQueue Table created.'
GOTO VMQueue_Done

VMQueue_Done: 
GO

/****** Creat  Table [dbo].[VMTools]  ******/
IF EXISTS (SELECT 1 FROM sysobjects WHERE xtype='u' AND name='VMTools') 
        BEGIN
        Drop Table VMTools 
        Print 'Dropped Table VMTools'
        END 

ELSE 
	SET ANSI_NULLS ON
	SET QUOTED_IDENTIFIER ON
	SET ANSI_PADDING ON

CREATE TABLE [dbo].[VMTools](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[VMwareToolsVerion] [int] NOT NULL,
	[GUID] [nvarchar] (max) NOT NULL,
	[GUESTID] [char](50) NOT NULL,
 CONSTRAINT [PK_VMTools] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]


Print 'VMTools Table created.'

/****** Populate VMTools   ******/
Print 'Populating VMTools Table...'
INSERT INTO VMTools  VALUES (8196,'{FE2F6A2C-196E-4210-9C04-2B1BC21F07EF}','winNetEnterpriseGuest')
INSERT INTO VMTools  VALUES (8198,'{FE2F6A2C-196E-4210-9C04-2B1BC21F07EF}','winNetEnterpriseGuest')
INSERT INTO VMTools  VALUES (8295,'{FE2F6A2C-196E-4210-9C04-2B1BC21F07EF}','winNetEnterpriseGuest')
INSERT INTO VMTools  VALUES (8384,'{A5CD39D8-F8A7-494F-9357-878A4AB6537F}','win7Server64Guest')
INSERT INTO VMTools  VALUES (8384,'{62198C42-974B-4F90-9AD2-12763AB58C97}','winNetEnterpriseGuest')
INSERT INTO VMTools  VALUES (8384,'{62198C42-974B-4F90-9AD2-12763AB58C97}','winLonghornGuest')
INSERT INTO VMTools  VALUES (8384,'{A5CD39D8-F8A7-494F-9357-878A4AB6537F}','winLonghorn64Guest')
INSERT INTO VMTools  VALUES (8384,'{A5CD39D8-F8A7-494F-9357-878A4AB6537F}','winNetEnterprise64Guest')
INSERT INTO VMTools  VALUES (8196,'{FE2F6A2C-196E-4210-9C04-2B1BC21F07EF}','win7Server64Guest')
INSERT INTO VMTools  VALUES (8196,'{FE2F6A2C-196E-4210-9C04-2B1BC21F07EF}','winLonghornGuest')
INSERT INTO VMTools  VALUES (8196,'{FE2F6A2C-196E-4210-9C04-2B1BC21F07EF}','winLonghorn64Guest')
INSERT INTO VMTools  VALUES (8196,'{FE2F6A2C-196E-4210-9C04-2B1BC21F07EF}','winNetEnterprise64Guest')
INSERT INTO VMTools  VALUES (8198,'{FE2F6A2C-196E-4210-9C04-2B1BC21F07EF}','win7Server64Guest')
INSERT INTO VMTools  VALUES (8198,'{FE2F6A2C-196E-4210-9C04-2B1BC21F07EF}','winLonghornGuest')
INSERT INTO VMTools  VALUES (8198,'{FE2F6A2C-196E-4210-9C04-2B1BC21F07EF}','winLonghorn64Guest')
INSERT INTO VMTools  VALUES (8198,'{FE2F6A2C-196E-4210-9C04-2B1BC21F07EF}','winNetEnterprise64Guest')
INSERT INTO VMTools  VALUES (8295,'{FE2F6A2C-196E-4210-9C04-2B1BC21F07EF}','winNetEnterprise64Guest')
INSERT INTO VMTools  VALUES (8295,'{FE2F6A2C-196E-4210-9C04-2B1BC21F07EF}','winLonghorn64Guest')
INSERT INTO VMTools  VALUES (8295,'{FE2F6A2C-196E-4210-9C04-2B1BC21F07EF}','winLonghornGuest')
INSERT INTO VMTools  VALUES (8295,'{FE2F6A2C-196E-4210-9C04-2B1BC21F07EF}','win7Server64Guest')
INSERT INTO VMTools  VALUES (8389,'{DD3770AA-8012-453F-AD8D-0B6D91ED40D5}','winNetEnterpriseGuest')  
INSERT INTO VMTools  VALUES (8389,'{A2CC6F0B-E888-4485-82F5-587699B3CDB7}','winLonghorn64Guest')
INSERT INTO VMTools  VALUES (8384,'{A5CD39D8-F8A7-494F-9357-878A4AB6537F}','windows7Server64Guest')
INSERT INTO VMTools  VALUES (8384,'{62198C42-974B-4F90-9AD2-12763AB58C97}','windowsLonghornGuest')
INSERT INTO VMTools  VALUES (8384,'{A5CD39D8-F8A7-494F-9357-878A4AB6537F}','windowsLonghorn64Guest')
INSERT INTO VMTools  VALUES (8196,'{FE2F6A2C-196E-4210-9C04-2B1BC21F07EF}','windows7Server64Guest')
INSERT INTO VMTools  VALUES (8196,'{FE2F6A2C-196E-4210-9C04-2B1BC21F07EF}','windowsLonghornGuest')
INSERT INTO VMTools  VALUES (8196,'{FE2F6A2C-196E-4210-9C04-2B1BC21F07EF}','windowsLonghorn64Guest')
INSERT INTO VMTools  VALUES (8198,'{FE2F6A2C-196E-4210-9C04-2B1BC21F07EF}','windows7Server64Guest')
INSERT INTO VMTools  VALUES (8198,'{FE2F6A2C-196E-4210-9C04-2B1BC21F07EF}','windowsLonghornGuest')
INSERT INTO VMTools  VALUES (8198,'{FE2F6A2C-196E-4210-9C04-2B1BC21F07EF}','windowsLonghorn64Guest')
INSERT INTO VMTools  VALUES (8295,'{FE2F6A2C-196E-4210-9C04-2B1BC21F07EF}','windowsLonghorn64Guest')
INSERT INTO VMTools  VALUES (8295,'{FE2F6A2C-196E-4210-9C04-2B1BC21F07EF}','windowsLonghornGuest')
INSERT INTO VMTools  VALUES (8295,'{FE2F6A2C-196E-4210-9C04-2B1BC21F07EF}','windows7Server64Guest')
INSERT INTO VMTools  VALUES (8389,'{A2CC6F0B-E888-4485-82F5-587699B3CDB7}','windowsLonghorn64Guest')
INSERT INTO VMTools  VALUES (9216,'{4D80C805-67C3-4525-A7BA-DC43215E9167}','windows7Server64Guest')
INSERT INTO VMTools  VALUES (8384,'/usr/bin/vmware-uninstall-tools.pl | shutdown -h +3','centos64Guest')
INSERT INTO VMTools  VALUES (8384,'/usr/bin/vmware-uninstall-tools.pl | shutdown -h +3','redhatGuest')
INSERT INTO VMTools  VALUES (8384,'/usr/bin/vmware-uninstall-tools.pl | shutdown -h +3','sles11_64Guest')
INSERT INTO VMTools  VALUES (8384,'/usr/bin/vmware-uninstall-tools.pl | shutdown -h +3','centos64Guest')
INSERT INTO VMTools  VALUES (8384,'/usr/bin/vmware-uninstall-tools.pl | shutdown -h +3','redhatGuest')
INSERT INTO VMTools  VALUES (8384,'/usr/bin/vmware-uninstall-tools.pl | shutdown -h +3','sles11_64Guest') 
INSERT INTO VMTools  VALUES (8295,'/usr/bin/vmware-uninstall-tools.pl | shutdown -h +3','centos64Guest')
INSERT INTO VMTools  VALUES (8295,'/usr/bin/vmware-uninstall-tools.pl | shutdown -h +3','rhel5_64Guest') 
INSERT INTO VMTools  VALUES (8295,'/usr/bin/vmware-uninstall-tools.pl | shutdown -h +3','sles10_64Guest') 
INSERT INTO VMTools  VALUES (8295,'/usr/bin/vmware-uninstall-tools.pl | shutdown -h +3','rhel6_64Guest') 
INSERT INTO VMTools  VALUES (8295,'/usr/bin/vmware-uninstall-tools.pl | shutdown -h +3','sles11_64Guest')

GOTO VMTools_Done

VMTools_Exists:
Print 'VMTools already exists.'
GOTO VMTools_Done

VMTools_Done: 
GO



/******  Create Table [dbo].[VMConversionData_PS] ******/
IF EXISTS (SELECT 1 FROM sysobjects WHERE xtype='u' AND name='VMConversionData_PS') 
        BEGIN
        Drop Table VMConversionData_PS
        Print 'Dropped Table VMConversionData_PS'
        END
ELSE         
	SET ANSI_NULLS ON
	SET QUOTED_IDENTIFIER ON

CREATE TABLE [dbo].[VMConversionData_PS](
	[MoRef] [nvarchar](10) NULL,
	[DisplayName] [nvarchar](255) NULL,
	[VMHostName] [nvarchar](255) NULL,
	[VMHostID] [nvarchar](max) NULL,
	[GuestVMFQDN] [nvarchar](max) NULL,
	[GuestVMID] [nvarchar](255) NULL,
	[GUESTID] [char](50) NULL,
	[GuestOS] [nvarchar](255) NULL,
	[GuestVMMB] [int] NULL,
	[GuestVMCPUCount] [int] NULL,
	[GuestVMHDProvisionedGB] [float] NULL,
	[GuestVMHDUsedGB] [float] NULL,
	[VMwareToolsVerion] [int] NULL,
	[VMwareHardwareVersion] [nvarchar](10) NULL,
	[FtInfo] [nvarchar](max) NULL,
	[DiskLocation0] [nvarchar] (max) NULL,
	[DiskLocation1] [nvarchar] (max) NULL,
	[DiskLocation2] [nvarchar] (max) NULL,
	[Network0] [nvarchar](255) NULL,
	[IPv6Address0] [nvarchar](50) NULL,
	[IPv4Address0] [nvarchar](15) NULL,
	[Network1] [nvarchar](255) NULL,
	[IPv6Address1] [nvarchar](50) NULL,
	[IPv4Address1] [nvarchar](15) NULL,
	[Network2] [nvarchar](255) NULL,
	[IPv6Address2] [nvarchar](50) NULL,
	[IPv4Address2] [nvarchar](15) NULL,
	[MAC0] [nvarchar](255) NULL,
	[MAC1] [nvarchar](255) NULL,
	[MAC2] [nvarchar](255) NULL,
	[VLAN0] [int] NULL,
	[VLAN1] [int] NULL,
	[VLAN2] [int] NULL,
	[DataInsertTimeStamp] [datetime] NULL
) ON [PRIMARY]


Print 'VMConversionData_PS'
GOTO VMConversionData_PS_Done

VMConversionData_PS_Done: 
GO

/****** Create Details View   ******/
IF EXISTS(SELECT * from INFORMATION_SCHEMA.VIEWS WHERE table_name = 'VMDetails_VIEW')
Begin
		DROP VIEW [VMDetails_VIEW]
		Print 'VMDetails_VIEW already exists, droppping view.'
END

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO

CREATE VIEW [dbo].[VMDetails_VIEW]
AS
SELECT        dbo.VMConversionData_PS.GuestVMFQDN, dbo.VMConversionData_PS.GuestVMID, dbo.VMConversionData_PS.VMHostID, 
                         dbo.VMConversionData_PS.VMHostName, dbo.VMConversionData_PS.GUESTID, dbo.VMConversionData_PS.GuestOS, dbo.VMQueue.StartTime, 
                         dbo.VMQueue.EndTime, dbo.VMQueue.Notes, dbo.VMQueue.PID, dbo.VMConversionData_PS.GuestVMMB, dbo.VMConversionData_PS.GuestVMCPUCount, 
                         dbo.VMConversionData_PS.GuestVMHDProvisionedGB, dbo.VMConversionData_PS.GuestVMHDUsedGB, dbo.VMConversionData_PS.FtInfo, 
                         dbo.VMConversionData_PS.DiskLocation0, dbo.VMConversionData_PS.DiskLocation1, dbo.VMConversionData_PS.DiskLocation2, 
                         dbo.VMConversionData_PS.Network0, dbo.VMConversionData_PS.IPv6Address0, dbo.VMConversionData_PS.IPv4Address0, 
                         dbo.VMConversionData_PS.Network1, dbo.VMConversionData_PS.IPv6Address1, dbo.VMConversionData_PS.IPv4Address1, 
                         dbo.VMConversionData_PS.Network2, dbo.VMConversionData_PS.IPv6Address2, dbo.VMConversionData_PS.IPv4Address2, 
                         dbo.VMConversionData_PS.DataInsertTimeStamp, dbo.VMQueue.Supported, dbo.VMQueue.ReadytoConvert, dbo.VMQueue.InUse, dbo.VMQueue.Completed, 
                         dbo.VMQueue.Status, dbo.VMQueue.Position, dbo.VMQueue.ConvertServer, dbo.VMConversionData_PS.MoRef, dbo.VMQueue.VMName, dbo.VMQueue.JobID, 
                         dbo.VMConversionData_PS.MAC0, dbo.VMConversionData_PS.MAC1, dbo.VMConversionData_PS.MAC2, dbo.VMConversionData_PS.VLAN0, 
                         dbo.VMConversionData_PS.VLAN1, dbo.VMConversionData_PS.VLAN2, dbo.VMConversionData_PS.DisplayName, dbo.VMQueue.Summary, 
                         dbo.VMQueue.Warning, dbo.VMConversionData_PS.VMwareToolsVerion
FROM            dbo.VMConversionData_PS INNER JOIN
                         dbo.VMQueue ON dbo.VMConversionData_PS.GuestVMFQDN = dbo.VMQueue.VMName
GO


Print 'VMDetails_VIEW created.'

/****** Create sp_TransferVMConversionData_PS  ******/
IF object_id('sp_TransferVMConversionData_PS') IS NOT NULL
Begin
		DROP PROCEDURE [sp_TransferVMConversionData_PS]
		Print 'sp_TransferVMConversionData_PS already exists, droppping stored proccedure.'
END

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[sp_TransferVMConversionData_PS] 

AS
BEGIN

--SET NOCOUNT ON
--INSERT UNIQUE RECORDS (only specific fields) FROM VMConversionData_PS INTO VMQueue
INSERT INTO [VMQueue] ([objectID],[VMName],[Supported],[ReadytoConvert],[Completed],[Status],[Notes])
SELECT vmcd.[MoRef] AS [objectID]
	  ,vmcd.[GuestVMFQDN] AS [VMName]
      ,CASE
			WHEN vmcd.[FtInfo] = '' AND vmcd.[GuestOS] = 'Microsoft Windows Server 2008 R2 (64-bit)' THEN 1
			WHEN vmcd.[FtInfo] = '' AND vmcd.[GuestOS] = 'Microsoft Windows Server 2008 (32-bit)' THEN 1
			WHEN vmcd.[FtInfo] = '' AND vmcd.[GuestOS] = 'Microsoft Windows Server 2008 (64-bit)' THEN 1
			WHEN vmcd.[FtInfo] = '' AND vmcd.[GuestOS] = 'Microsoft Windows XP Professional (32-bit)' THEN 3
			WHEN vmcd.[FtInfo] = '' AND vmcd.[GuestOS] = 'Microsoft Windows 7 (32-bit)' THEN 1
			WHEN vmcd.[FtInfo] = '' AND vmcd.[GuestOS] = 'Microsoft Windows 7 (64-bit)' THEN 1
			WHEN vmcd.[FtInfo] = '' AND vmcd.[GuestOS] = 'Microsoft Windows 8 (32-bit)' THEN 3
			WHEN vmcd.[FtInfo] = '' AND vmcd.[GuestOS] = 'Microsoft Windows 8 (64-bit)' THEN 3
			WHEN vmcd.[FtInfo] = '' AND vmcd.[GuestOS] = 'Microsoft Windows Vista (32-bit)' THEN 1
			WHEN vmcd.[FtInfo] = '' AND vmcd.[GuestOS] = 'Microsoft Windows Vista (64-bit)' THEN 1
			WHEN vmcd.[FtInfo] = '' AND vmcd.[GuestOS] = 'Microsoft Windows Server 2003 Standard (32-bit)' THEN 1
			WHEN vmcd.[FtInfo] = '' AND vmcd.[GuestOS] = 'Microsoft Windows Server 2003 Standard (64-bit)' THEN 1
			WHEN vmcd.[FtInfo] = '' AND vmcd.[GuestOS] = 'Microsoft Windows Server 2003, Enterprise Edition (32-bit)' THEN 1
			WHEN vmcd.[FtInfo] = '' AND vmcd.[GuestOS] = 'Microsoft Windows Server 2003, Enterprise Edition (64-bit)' THEN 1
			WHEN vmcd.[FtInfo] = '' AND vmcd.[GuestOS] = 'Microsoft Windows Server 2003, Datacenter Edition (32-bit)' THEN 1
			WHEN vmcd.[FtInfo] = '' AND vmcd.[GuestOS] = 'Microsoft Windows Server 2003, Datacenter Edition (64-bit)' THEN 1
			WHEN vmcd.[FtInfo] = '' AND vmcd.[GuestOS] = 'Microsoft Windows Server 2003, Standard Edition (32-bit)' THEN 1
			WHEN vmcd.[FtInfo] = '' AND vmcd.[GuestOS] = 'Microsoft Windows Server 2003, Standard Edition (64-bit)' THEN 1
			WHEN vmcd.[FtInfo] = '' AND vmcd.[GuestOS] = 'Microsoft Windows Server 2003 (32-bit)' THEN 1
			WHEN vmcd.[FtInfo] = '' AND vmcd.[GuestOS] = 'Microsoft Windows Server 2003, Web Edition' THEN 1
			WHEN vmcd.[FtInfo] = '' AND vmcd.[GuestOS] = 'Microsoft Windows Small Business Server 2003' THEN 1
			ELSE 3
	   END AS [Supported]
	   ,0 AS [ReadytoConvert]
	   ,0 AS [Completed]
	   ,0 AS [Status]
	   ,'VM automatically collected by script' AS [Notes]
  FROM [VMConversionData_PS] vmcd
			LEFT JOIN
	   [VMQueue] vmq
			ON vmcd.[MoRef] = vmq.[objectID]
WHERE vmq.[objectID] IS NULL

SET NOCOUNT OFF

END

GO
Print 'Stored Proceedure sp_TransferVMConversionData_PS created.'


/****** Done with MAT   ******/
/****** MAT Database Changes Complete  ******/
Print 'MAT Database Setup Complete.'