<#

.DESCRIPTION
This script will poll NSI and identify which accounts have the SKU for other departments that might need to be involved for a set of migration events.

.EXAMPLE
.\AdditionalSupportGenerator.ps1 -GUI
    Uses alternate path for PS modules if the script is invoked from the Migration UI

.EXAMPLE
.\AdditionalSupportGenerator.ps1 -TST1-01
    Polls NSI for ORD1 devices

.EXAMPLE
.\AdditionalSupportGenerator.ps1 -Dropouts
    Writes out all the devices for which we don't have classifications.  This is for debugging purposes.
#>
Param 
(
	[Parameter(Mandatory=$false,Position=0)][string]$Version = "2.4",   #Script version
	[Parameter(Mandatory=$True,Position=0)][string]$DC,	                #Migration Group
	[switch]$Dropouts,	                                                #User wants to see the items we're not capturing in the \Dropouts directory
	[switch]$GUI            											#Are we invoking this from the GUI
)
#############################################################################################################################

Function ExcelTranslate ($Column,$Row)
<#
***********************************************************************************************************
v2.3
This function is passed a column and a row and essentially translates them from numeric or alpha values 
into Excel row/column values.

Example 1:
ExcelTranslate 2 15
Returns: B15

Example 2:
ExcelTranslate H 9
Returns: H9

This function is useful when you have changing numeric data but need to reference specific columns in 
alpha Excel format.

***********************************************************************************************************
#>
{
	#If this is an integer we'll process it.  If not we'll assume it's alphanumeric and move on
	if ($Column -is [int])
    {
		#Divide the column by 26 in order to put it in a base 10 context
		$LetterCheck = $Column/26
		#Modulus divide that number by 2 to see if we have a whole number
		$WholeCheck = $LetterCheck%1
		#if we're further out than 26 columns (i.e. Lettercheck is >1) we need to process slightly differently because we have multiple alpha values (i.e. AZ3 rather than A3)
		if ($LetterCheck -gt 1)
		{
			#If WholeCheck is >0 we know we have a decimal number and we need to get the whole and decimal components of that number
			if ($WholeCheck -gt 0)
			{
				#Convert to string
				$LetterCheck = [string]$LetterCheck
				#Split on the decimal point
				$WholeNumber,$DecimalNumber = ([string]$LetterCheck).Split("{.}")
				#Add back the decimal point to the decimal number
				$DecimalNumber="."+$DecimalNumber
				#Convert to integer
				$WholeNumber = [int]$WholeNumber
				#Multiply the decimal number by 26 and convert to integer
				$DecimalNumber = [int]([decimal]$DecimalNumber*26)
			}
			else
			{
				#Divide the column by 26 and get the whole number  
				$WholeNumber = ([math]::floor([int] $Column / [int] 26))-1
				#Get the decimal remainder
				$DecimalNumber = $WholeCheck
			}
		}
		else 
		{
			#If we're <1 we're in the first 26 characters and the whole number is not relevant but needs to be statically defined
			$WholeNumber = 0
			$DecimalNumber = $Column
		}
		#Based on whether we're >26 we'll need a two-level alpha column (i.e. AD vs A).  If this is zero then we only need a single letter.
		switch ($WholeNumber)
		{
			0 {$ColumnProcessed=""}
			1 {$ColumnProcessed="A"}
			2 {$ColumnProcessed="B"}
			3 {$ColumnProcessed="C"}
			4 {$ColumnProcessed="D"}
			5 {$ColumnProcessed="E"}
			6 {$ColumnProcessed="F"}
			7 {$ColumnProcessed="G"}
			8 {$ColumnProcessed="H"}
			9 {$ColumnProcessed="I"}
			10 {$ColumnProcessed="J"}
			11 {$ColumnProcessed="K"}
			12 {$ColumnProcessed="L"}
			13 {$ColumnProcessed="M"}
			14 {$ColumnProcessed="N"}
			15 {$ColumnProcessed="O"}
			16 {$ColumnProcessed="P"}
			17 {$ColumnProcessed="Q"}
			18 {$ColumnProcessed="R"}
			19 {$ColumnProcessed="S"}
			20 {$ColumnProcessed="T"}
			21 {$ColumnProcessed="U"}
			22 {$ColumnProcessed="V"}
			23 {$ColumnProcessed="W"}
			24 {$ColumnProcessed="X"}
			25 {$ColumnProcessed="Y"}
			26 {$ColumnProcessed="Z"}
		}
		switch ($DecimalNumber)
		{
			1 {$ColumnProcessed+="A"}
			2 {$ColumnProcessed+="B"}
			3 {$ColumnProcessed+="C"}
			4 {$ColumnProcessed+="D"}
			5 {$ColumnProcessed+="E"}
			6 {$ColumnProcessed+="F"}
			7 {$ColumnProcessed+="G"}
			8 {$ColumnProcessed+="H"}
			9 {$ColumnProcessed+="I"}
			10 {$ColumnProcessed+="J"}
			11 {$ColumnProcessed+="K"}
			12 {$ColumnProcessed+="L"}
			13 {$ColumnProcessed+="M"}
			14 {$ColumnProcessed+="N"}
			15 {$ColumnProcessed+="O"}
			16 {$ColumnProcessed+="P"}
			17 {$ColumnProcessed+="Q"}
			18 {$ColumnProcessed+="R"}
			19 {$ColumnProcessed+="S"}
			20 {$ColumnProcessed+="T"}
			21 {$ColumnProcessed+="U"}
			22 {$ColumnProcessed+="V"}
			23 {$ColumnProcessed+="W"}
			24 {$ColumnProcessed+="X"}
			25 {$ColumnProcessed+="Y"}
			26 {$ColumnProcessed+="Z"}
			0 {$ColumnProcessed+="Z"}
		}
	}
	else
	{
		$ColumnProcessed = $Column
	}
	$ColumnRow = $ColumnProcessed+[string]$Row
	Return $ColumnRow
}

#############################################################################################################################

Function MessageCalc ($Count,$Message,$Addition)
{
	if ($Count -eq 1)
	{
		$Message = $Addition
	}
	else
	{
		$Message+=", "+$Addition
	}
	return $Message
}

#############################################################################################################################

Function MakeTable ($TableName,$BeginColumn,$BeginRow,$EndColumn,$EndRow)
{
	$BeginRange = ExcelTranslate $BeginColumn $BeginRow
	$EndRange = ExcelTranslate $EndColumn $EndRow
	
	$Range=$OutSheet.Range("${BeginRange}:$EndRange")
	$Range.Activate() |out-null
	$ListObject = $Excel.ActiveSheet.ListObjects.Add([Microsoft.Office.Interop.Excel.XlListObjectSourceType]::xlSrcRange, $Range, $null ,[Microsoft.Office.Interop.Excel.XlYesNoGuess]::xlYes)
	$ListObject.Name = "$TableName"
	$ListObject.TableStyle = "TableStyleMedium13"
}

#############################################################################################################################

Function Write-Device ($Dev,$Message, $Attribute)
{
	write-host "!"$Dev._id"!`t!"$Dev.account.name"!`t!"$Message"`t"$Attribute -foregroundcolor "green"
}

#############################################################################################################################

Function GoHome
{
	$Range=$OutSheet.Range("A1:A1")
	$Range.Activate() |out-null					
}

#############################################################################################################################

Function Get-CollectionMGS
{
	<#
	***********************************************************************************************************
	This function gets the move groups for the current collection and passes back the array of objects.
	***********************************************************************************************************
	#>
	######################################################################################
	#Variable declaration
	$URI = "https://dbapi.nsi.rackspace.com/migration/metadata/Find"          #Variable for URI to query
	$JSON = @"
	{
		"query": {
			"collection": "$CollectionName"
		},
		"sort": {
			"_id": {"int": false, "reverse": true}
		},
		"projection": ["_id"]
	}
"@
	######################################################################################
	$Results = Invoke-RestMethod -Uri $URI -Body $JSON -ContentType "application/json" -Method "POST" -Headers @{"X-Auth"=$Global:Token} -TimeoutSec 120
	$MGResults = @()
	ForEach ($Result in $Results.result._id)
	{
		if ($Result -notmatch "(-[mM]99+|-[nN]\d+)$") {
			$MGResults += $Result
		}
	}
	Return $MGResults
}


#############################################################################################################################

Function ProcessAccount ($PassedDeviceConfig,$PassedAccount)
{
	if ($PassedDeviceConfig.Oracle) {$PassedAccount.Oracle = $True}
	if ($PassedDeviceConfig.MySQL) {$PassedAccount.MySQL = $True}
	if ($PassedDeviceConfig.MSSQL) {$PassedAccount.MSSQL = $True}
	if ($PassedDeviceConfig.RCv3) {$PassedAccount.RCv3 = $True}
	if ($PassedDeviceConfig.CAS) {$PassedAccount.CAS = $True}
	if ($PassedDeviceConfig.RMSDI) {$PassedAccount.RMSDI = $True}
	if ($PassedDeviceConfig.RMSPDP) {$PassedAccount.RMSPDP = $True}
	if ($PassedDeviceConfig.DEX) {$PassedAccount.DEX = $True}
	if ($PassedDeviceConfig.DSP) {$PassedAccount.DSP = $True}
	if ($PassedDeviceConfig.SfB) {$PassedAccount.SfB = $True}
	if ($PassedDeviceConfig.RPC) {$PassedAccount.RPC = $True}
	if ($PassedDeviceConfig.MPC) {$PassedAccount.MPC = $True}
	if ($PassedDeviceConfig.DoubleTake) {$PassedAccount.DoubleTake = $True}
	if ($PassedDeviceConfig.ObjectRocket) {$PassedAccount.ObjectRocket = $True}
	if ($PassedDeviceConfig.Isilon) {$PassedAccount.Isilon = $True}
	if ($PassedDeviceConfig.Dvc) {$PassedAccount.DvC = $True}
	if ($PassedDeviceConfig.Duo) {$PassedAccount.Duo = $True}
	if ($PassedDeviceConfig.Vormetric) {$PassedAccount.Vormetric = $True}
	if ($PassedDeviceConfig.SNAS) {$PassedAccount.SNAS = $True}
	if ($PassedDeviceConfig.WebMux) {$PassedAccount.WebMux = $True}
	if ($PassedDeviceConfig.LeasedLine) {$PassedAccount.LeasedLines = $True}
	if ($PassedDeviceConfig.MBUEOLNIC) {$PassedAccount.MBUEOLNIC = $True}
	if ($PassedDeviceConfig.MBUEOL) {$PassedAccount.MBUEOL = $True}
	if ($PassedDeviceConfig.MBUManColo) {$PassedAccount.MBUManColo = $True}
	
	return $PassedAccount
}

#############################################################################################################################
######################################################################################
#Getting the current directory and importing the CTKCore module
$Dir=(Get-Item -Path . -Verbose).FullName
#If we're executing from the GUI the CTKCore module will be different
if ($GUI)
{
	$CTKModule = $Dir+"\Modules\CTKCore\CTKCore.psm1"
}
else
{
	$CTKModule = [string]((get-item $Dir).parent.fullname)+"\Modules\CTKCore\CTKCore.psm1"
}
Import-Module $CTKModule -force -DisableNameChecking
######################################################################################
#Setting TLS to v1.2
[Net.ServicePointManager]::SecurityProtocol = [Net.SecurityProtocolType]::Tls12
######################################################################################
#Checking CORE token
try 
{
    $Testing = Get-CoreDevice -Device 879665
}
catch 
{
    Get-CoreToken
}
######################################################################################
#Getting start time
$Stopwatch = [system.diagnostics.stopwatch]::startNew() 
#********************************
#Excel border definitions *******
#********************************
$BorderLeft = 7					#
$BorderTop = 8					#
$BorderBottom = 9				#
$BorderRight = 10				#
$BorderInsideVertical = 11		#
$BorderInsideHorizontal = 12	#
#********************************
#Variable definition
#Creating move groups
#Defining the collection, gathering the move groups, and assigning
$CollectionName = $DC.ToLower() -replace '-','_'
$URI = "https://dbapi.nsi.rackspace.com/migration/"+$CollectionName+"/Find"
$MoveGroups = Get-CollectionMGS
$TimeStamp = Get-Date -Format d | foreach {$_ -replace "/", "-"}
$Destination_File = ([Environment]::GetFolderPath("Desktop"))+"\AdditionalSupport - "+$CollectionName+" - "+$TimeStamp+".xlsx"		#Our Additional Support file
$OverviewColumn = 1
$OverviewRow = 2
$MGRow = 1
$MGColumn = 1
$Sheetcount = 1

$DeviceConfig = [PSCustomObject]@{
	Oracle 			= $False;
	MySQL 			= $False;
	MSSQL 			= $False;
	RCv3 			= $False;
	CAS 			= $False;
	RMSDI 			= $False;
	RMSPDP 			= $False;
	DEX 			= $False;
	DSP 			= $False;
	SfB 			= $False;
	RPC 			= $False;
	MPC				= $False;
	DoubleTake 		= $False;
	ObjectRocket 	= $Null;
	Dvc 			= $False;
	Duo	 			= $False;
	Vormetric		= $False;
	SNAS			= $False;
	Strategic 		= $False;
	WebMux 			= $False;
	LeasedLine 		= $False;
	MBUEOLNIC 		= $False;
	MBUEOL 			= $False;
	MBUManColo 		= $False
}

#Creating the Excel object
######################################################################################
$Excel = New-Object -ComObject excel.application
$Excel.visible = $False
$Excel.displayalerts = $False
$OutExcel = $Excel.Workbooks.Add()
######################################################################################
#Creating Excel sheets
foreach ($MG in $MoveGroups)
{
	#We don't need to create a new sheet if this is the first MG
	if ($Sheetcount -gt 1) {$OutExcel.Worksheets.Add()|out-null}
	$Sheetcount++
    $OutSheet = $OutExcel.Worksheets.Item(1)
    $OutSheet.Name = $MG
    ######################################################################################
    #Moving to the MG sheet
    $OutSheet = $OutExcel.Worksheets.Item($MG)
	$OutSheet.Activate()|out-null
	$MGRow = 1
	$MGColumn = 1
	$OutSheet.cells.Item($MGRow,$MGColumn) = "Support Needs"
	$MGColumn++
	$OutSheet.cells.Item($MGRow,$MGColumn) = "Account Name"
	$MGColumn++
	$OutSheet.cells.Item($MGRow,$MGColumn) = "Account Number"
	$MGColumn++
	$OutSheet.cells.Item($MGRow,$MGColumn) = "Comments"
}

$OutExcel.Worksheets.Add()|out-null
$OutSheet = $OutExcel.Worksheets.Item(1)
$OutSheet.Name = "Overview"
######################################################################################
#Writing row headings on the Overview sheet
$StartRange = $OverviewRow
$OutSheet.cells.Item($OverviewRow,$OverviewColumn) = "Oracle"
$OverviewRow++
$OutSheet.cells.Item($OverviewRow,$OverviewColumn) = "MySQL"
$OverviewRow++
$OutSheet.cells.Item($OverviewRow,$OverviewColumn) = "MS SQL"
$OverviewRow++
$OutSheet.cells.Item($OverviewRow,$OverviewColumn) = "RCv3"
$OverviewRow++
$OutSheet.cells.Item($OverviewRow,$OverviewColumn) = "CAS"
$OverviewRow++
$OutSheet.cells.Item($OverviewRow,$OverviewColumn) = "RMS-DI"
$OverviewRow++
$OutSheet.cells.Item($OverviewRow,$OverviewColumn) = "RMS-PDP"
$OverviewRow++
$OutSheet.cells.Item($OverviewRow,$OverviewColumn) = "DEX"
$OverviewRow++
$OutSheet.cells.Item($OverviewRow,$OverviewColumn) = "DSP"
$OverviewRow++
$OutSheet.cells.Item($OverviewRow,$OverviewColumn) = "SfB"
$OverviewRow++
$OutSheet.cells.Item($OverviewRow,$OverviewColumn) = "RPC"
$OverviewRow++
$OutSheet.cells.Item($OverviewRow,$OverviewColumn) = "MPC"
$OverviewRow++
$OutSheet.cells.Item($OverviewRow,$OverviewColumn) = "DoubleTake"
$OverviewRow++
$OutSheet.cells.Item($OverviewRow,$OverviewColumn) = "ObjectRocket"
$OverviewRow++
$OutSheet.cells.Item($OverviewRow,$OverviewColumn) = "Isilon"
$OverviewRow++
$OutSheet.cells.Item($OverviewRow,$OverviewColumn) = "Dedicated vCenter"
$OverviewRow++
$OutSheet.cells.Item($OverviewRow,$OverviewColumn) = "Multi or 2-Factor Authentication"
#Formatting heading borders
######################################################################################
$BeginRange = ExcelTranslate "A" 2
$EndRange = ExcelTranslate "A" $OverviewRow
$Range=$OutSheet.Range("${BeginRange}:$EndRange")
$Range.Activate() |out-null
$Range.Borders.item($BorderRight).LineStyle = 1
$Range.Borders.item($BorderInsideHorizontal).LineStyle = 1
#Continuing row headings
######################################################################################
$OverviewRow+=3
$BeginRow = $OverviewRow
$OutSheet.cells.Item($OverviewRow,$OverviewColumn) = "Webmux/CSS"
$OverviewRow++
$OutSheet.cells.Item($OverviewRow,$OverviewColumn) = "Strategic Customers"
$OverviewRow++
$OutSheet.cells.Item($OverviewRow,$OverviewColumn) = "Leased Lines"
$OverviewRow++
$OutSheet.cells.Item($OverviewRow,$OverviewColumn) = "MBU for EOL NIC"
$OverviewRow++
$OutSheet.cells.Item($OverviewRow,$OverviewColumn) = "MBU for EOL"
$OverviewRow++
$OutSheet.cells.Item($OverviewRow,$OverviewColumn) = "Vormetric"
$OverviewRow++
$OutSheet.cells.Item($OverviewRow,$OverviewColumn) = "SNAS"
$OverviewRow++
$OutSheet.cells.Item($OverviewRow,$OverviewColumn) = "MBU Managed CoLo Customers"
$OutSheet.columns.Item($OverviewColumn).columnWidth = 28
$OverviewColumn+=$MoveGroups.count+1
			
#Formatting heading borders
######################################################################################
$BeginRange = ExcelTranslate "A" $BeginRow
$EndRange = ExcelTranslate "A" $OverviewRow
$Range=$OutSheet.Range("${BeginRange}:$EndRange")
$Range.Activate() |out-null
$Range.Borders.item($BorderRight).LineStyle = 1
$Range.Borders.item($BorderInsideHorizontal).LineStyle = 1
Write-Progress -Activity "Processing move groups.  Current MG: $MG." -Status "%0 Complete" -PercentComplete 0
$CurrentMG = 0
foreach ($MG in $MoveGroups)
{
	######################################################################################
	#Tracking and writing progress
	$CurrentMG++
	$MGPercentageComplete = ($CurrentMG/$MoveGroups.count)*100
	$MGPercentageComplete = "{0:N0}" -f $MGPercentageComplete
	Write-Progress -Activity "Processing move groups.  Current MG: $MG." -Status "$MGPercentageComplete% Complete" -PercentComplete $MGPercentageComplete
	Write-Progress -Activity "Processing devices for $MG..." -Status "0% Complete" -PercentComplete 0 -id 1
	######################################################################################
    #Moving to the MG sheet
    $OutSheet = $OutExcel.Worksheets.Item("$MG")
	$OutSheet.Activate()|out-null
	######################################################################################
	#Setting navigation variables for the MG sheet
    $MGColumn = 1
	$MGRow = 1
	######################################################################################
	#JSON generation
$JSON = @"
{
    "query": {
    "move_group":"$MG"
    }
}
"@
	######################################################################################
	#Querying NSI
	write-host "Polling NSI for move group $MG..." -f "cyan" -nonewline
	$Results = Invoke-RestMethod -Uri $URI -Body "$JSON" -ContentType "application/json" -Method "POST" -Headers @{"X-Auth"=$Global:Token} -TimeoutSec 120
	write-host "complete.  $($Results.result.count) records identified." -f "cyan"
	######################################################################################
	#Resetting individual device counts and reinitializing the array of unique accounts
	$UniqueAccounts = @()
	$Oracle = 0
	$MySQL = 0
	$MSSQL = 0
	$RCv3 = 0
	$CAS = 0
	$RMSDI = 0
	$RMSPDP = 0
	$DEX = 0
	$DSP = 0
	$SfB = 0
	$RPC = 0
	$MPC = 0
	$DoubleTake = 0
	$ObjectRocket = 0
	$Isilon = 0
	$DvC = 0
	$Duo = 0
	$WebMux = 0
	$StrategicCount = 0
	$LeasedLines = 0
	$MBUEOLNIC = 0
	$MBUEOL = 0
	$Vormetric = 0
	$SNAS = 0
	$MBUManColo = 0
	######################################################################################
	#Tracking and writing progress#Iterating through the devices
	$CurrentDevice = 0	#Progress variable
    Foreach ($Device in $Results.Result)
    {
		######################################################################################
		#Tracking and writing progress
		$CurrentDevice++
		$DevicePercentageComplete = ($CurrentDevice/$Results.result.count)*100
		$DevicePercentageComplete = "{0:N0}" -f $DevicePercentageComplete
		Write-Progress -Activity "Processing devices for move group $MG..." -Status "$DevicePercentageComplete% Complete" -PercentComplete $DevicePercentageComplete -id 1
		######################################################################################
		#Assuming this is a new account
		$NewAccount = $True
		######################################################################################
		#Creating a new device PSO and setting the attribute flags to false
		$DeviceConfig = [PSCustomObject]@{
			Oracle 			= $False;
			MySQL 			= $False;
			MSSQL 			= $False;
			RCv3 			= $False;
			CAS 			= $False;
			RMSDI 			= $False;
			RMSPDP 			= $False;
			DEX 			= $False;
			DSP 			= $False;
			SfB 			= $False;
			RPC 			= $False;
			MPC				= $False;
			DoubleTake 		= $False;
			ObjectRocket 	= $Null;
			Isilon 			= $False;
			Dvc 			= $False;
			Duo	 			= $False;
			Vormetric		= $False;
			SNAS			= $False;
			Strategic 		= $False;
			WebMux 			= $False;
			LeasedLine 		= $False;
			MBUEOLNIC 		= $False;
			MBUEOL 			= $False;
			MBUManColo 		= $False
		}
#$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$			
		#Determining the type of device
		if ($Device.deleted -ne "True")
		{
			#Determining the type of device
			######################################################################################*****************************
			#Oracle		********************************************************************
			if ($Device.has_oracle_support -eq "True") {$DeviceConfig.Oracle = $True}
			#MySQL		********************************************************************
			if ($Device.has_mysql_support -eq "True") {$DeviceConfig.MySQL = $True}
			#MSSQL		********************************************************************
			if ($Device.has_mssql_support -eq "True") {$DeviceConfig.MSSQL = $True}
			#RCv3		********************************************************************
			if ($Device.rcv3 -eq "True") {$DeviceConfig.RCv3 = $True}
			#CAS		********************************************************************
			if ($Device.device.os -like "*CAS - Infrastructure Management Server*") {$DeviceConfig.CAS = $True}
			#RMSDI		********************************************************************
			if ($Device.device.os -like "*Managed Security*") {$DeviceConfig.RMSDI = $True}
			#RMSPDP		********************************************************************
			if ($Device.device.os -like "*Data Protection*") {$DeviceConfig.RMSPDP = $True}
			#DSP and DEX
			if (!($Device.device.hostname -like "*hyp*") -and !($Device.device.hostname -like "*fw*"))
			{
				#DSP		********************************************************************
				if ($Device.has_sharepoint -eq "True") {$DeviceConfig.DSP = $True}
				#DEX		********************************************************************
				if ($Device.has_dex -eq "True") {$DeviceConfig.DEX = $True} 
			}
			#SfB		********************************************************************
			if ($Device.has_skype -eq "True") {$DeviceConfig.SfB = $True}
			#RPC		********************************************************************
			if ($Device.device.os -like "*OpenStack*") {$DeviceConfig.RPC = $True;}
			#MPC		********************************************************************
			if ($Device.device.make_model -like "*Hyper-V*" -or $Device.device.make_model -like "*Microsoft Cloud OS") {$DeviceConfig.MPC = $True;}
			#DoubleTake	********************************************************************
			if ($Device.has_doubletake -eq "True") {$DeviceConfig.DoubleTake = $True}
			#DvC		********************************************************************
			if ($Device.device.os -like "*Dedicated vCenter*") {$DeviceConfig.DvC = $True}
			#DvC		********************************************************************
			if ($Device.device.os -like "*High Capacity Dedicated NAS*") {$DeviceConfig.Isilon = $True;}
			#Duo		********************************************************************
			if ($Device.device.os -like "*Duo*") {$DeviceConfig.Duo = $True}
			if ($Device.device.os -like "*Factor*") {$DeviceConfig.Duo = $True}
			#Vormetric	********************************************************************
			if ($Device.device.os -like "*Encryption and Key*") {$DeviceConfig.Vormetric = $True}
			#Shared NAS	********************************************************************
			if ($Device.device.os -like "*Shared NAS*") {$DeviceConfig.SNAS = $True}
			#WebMux		********************************************************************#
			if ($Device.device.container_component_configuration_type_name -like "*Webmux*") {$DeviceConfig.WebMux = $True}
			if ($Device.device.container_component_configuration_type_name -like "*CSS*") {$DeviceConfig.WebMux = $True}
			#LeasedLine	********************************************************************
			#if ($Device.device.os -like "*Leased Line*") {$DeviceConfig.LeasedLine = $True}; write-host $Device.device.hostname
			if ($Device.device.type -like "*Leased Line Platform*") {$DeviceConfig.LeasedLine = $True}
			#MBUMCoLo	********************************************************************
			if ($Device.device.os -like "*Colocation*" -and $Device.has_mbu -eq "true") {$DeviceConfig.MBUManColo = $True}
			#DvC	********************************************************************
			if ($Device.device.os -like "*RPC-V*") {$DeviceConfig.DvC = $True}
		}
#$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$
		#Checking to see if we've seen this account previously and if we have send to our function that will change the account flags appropriately
		Foreach ($Account in $UniqueAccounts)
        {
			if ($Device.account.number -eq $Account.Number) 
			{
				$NewAccount = $False
				$Account = ProcessAccount $DeviceConfig $Account
			}
        }
		######################################################################################*****************************
		#If this is a new account add it to our unique accounts list
		if ($NewAccount) 
		{
			$AccountPSO = [PSCustomObject]@{
				Name = $Device.account.name;
				Number = $Device.account.number;
                Oracle 			= $False;
                MySQL 			= $False;
                MSSQL 			= $False;
                RCv3 			= $False;
                CAS 			= $False;
                RMSDI 			= $False;
                RMSPDP 			= $False;
                DEX 			= $False;
                DSP 			= $False;
                SfB 			= $False;
				RPC 			= $False;
				MPC				= $False;
                DoubleTake 		= $False;
                ObjectRocket 	= $Null;
                Isilon 			= $False;
                Dvc 			= $False;
                Duo	 			= $False;
                Vormetric		= $False;
                SNAS			= $False;
                Strategic 		= $False;
                WebMux 			= $False;
                LeasedLines		= $False;
                MBUEOLNIC 		= $False;
                MBUEOL 			= $False;
				MBUManColo 		= $False
			}
			######################################################################################*****************************
			#Strategic has a very specific set of teams that manage it so if the account team is one of the list then we mark the account as Strategic
			if ($Device.account.team_name -like "*ENTC-STRAT*" -or
                $Device.account.team_name -like "*ENTC-EY*" -or
                $Device.account.team_name -like "*ENTC-AON*" -or
                $Device.account.team_name -like "*ENTC-VISTA*" -or
                $Device.account.team_name -like "*ENTC-Walmart*" -or
                $Device.account.team_name -like "*ENTC-EA*" -or
                $Device.account.team_name -like "*ENTC-AIR*" -or
                $Device.account.team_name -like "*ENTC-OLDE*" -or
                $Device.account.team_name -like "*ENTC-3M*")
            {
				$StrategicCount++
				$AccountPSO.Strategic = $True
			}
			######################################################################################*****************************
			#Processing this account on the device that we currently have and adding the PSO to the uniqe accounts list
			$AccountPSO = ProcessAccount $DeviceConfig $AccountPSO
			$UniqueAccounts+=$AccountPSO
		}
	}
#$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$			
	######################################################################################
    #Moving to the Overview sheet
    $OutSheet = $OutExcel.Worksheets.Item($MG)
	$OutSheet.Activate()|out-null
	
	#All devices in a MG done.  Process the accounts
	foreach ($Account in $UniqueAccounts)
	{
		$Highlight = ""
		$HighlightCount = 0
		$Write = $False
		if ($Account.Oracle) 
		{
			$Write = $True
			$HighlightCount++
			$Oracle++
			$Highlight = MessageCalc $HighlightCount $Highlight "Oracle"
		}
		if ($Account.MySQL)
		{
			$Write = $True
			$HighlightCount++
			$MySQL++
			$Highlight = MessageCalc $HighlightCount $Highlight "MySQL"
		}
		if ($Account.MSSQL)
		{
			$Write = $True
			$HighlightCount++
			$MSSQL++
			$Highlight = MessageCalc $HighlightCount $Highlight "MS SQL"
		}
		if ($Account.RCv3) 
		{
			$Write = $True
			$HighlightCount++
			$RCv3++
			$Highlight = MessageCalc $HighlightCount $Highlight "RCv3"
		}
		if ($Account.CAS)
		{
			$Write = $True
			$HighlightCount++
			$CAS++
			$Highlight = MessageCalc $HighlightCount $Highlight "CAS"
		}
		if ($Account.RMSDI)
		{
			$Write = $True
			$HighlightCount++
			$RMSDI++
			$Highlight = MessageCalc $HighlightCount $Highlight "RMS DI"
		}
		if ($Account.RMSPDP)
		{
			$Write = $True
			$HighlightCount++
			$RMSPDP++
			$Highlight = MessageCalc $HighlightCount $Highlight "RMS PDP"
		}
		if ($Account.DEX)
		{
			$Write = $True
			$HighlightCount++
			$DEX++
			$Highlight = MessageCalc $HighlightCount $Highlight "Dedicated Exchange"
		}
		if ($Account.DSP)
		{
			$Write = $True
			$HighlightCount++
			$DSP++
			$Highlight = MessageCalc $HighlightCount $Highlight "Dedicated SharePoint"
		}
		if ($Account.SfB)
		{
			$Write = $True
			$HighlightCount++
			$SfB++
			$Highlight = MessageCalc $HighlightCount $Highlight "Skype for Business"
		}
		if ($Account.RPC)
		{
			$Write = $True
			$HighlightCount++
			$RPC++
			$Highlight = MessageCalc $HighlightCount $Highlight "RPC"
		}
		if ($Account.MPC)
		{
			$Write = $True
			$HighlightCount++
			$MPC++
			$Highlight = MessageCalc $HighlightCount $Highlight "MPC"
		}
		if ($Account.DoubleTake)
		{
			$Write = $True
			$HighlightCount++
			$DoubleTake++
			$Highlight = MessageCalc $HighlightCount $Highlight "DoubleTake"
		}
		if ($Account.ObjectRocket)
		{
			$Write = $True
			$HighlightCount++
			$ObjectRocket++
			$Highlight = MessageCalc $HighlightCount $Highlight "Object Rocket"
		}
		if ($Account.Isilon)
		{
			$Write = $True
			$HighlightCount++
			$Isilon++
			$Highlight = MessageCalc $HighlightCount $Highlight "Isilon"
		}
		if ($Account.Dvc)
		{
			$Write = $True
			$HighlightCount++
			$DvC++
			$Highlight = MessageCalc $HighlightCount $Highlight "DvC"
		}
		if ($Account.Duo)
		{
			$Write = $True
			$HighlightCount++
			$Duo++
			$Highlight = MessageCalc $HighlightCount $Highlight "Duo"
		}
		if ($Account.Vormetric)
		{
			$Write = $True
			$HighlightCount++
			$Vormetric++
			$Highlight = MessageCalc $HighlightCount $Highlight "Vormetric"
		}
		if ($Account.SNAS)
		{
			$Write = $True
			$HighlightCount++
			$SNAS++
			$Highlight = MessageCalc $HighlightCount $Highlight "SNAS"
		}if ($Account.WebMux)
		{
			$Write = $True
			$HighlightCount++
			$WebMux++
			$Highlight = MessageCalc $HighlightCount $Highlight "WebMux"
		}
		if ($Account.LeasedLines)
		{
			$Write = $True
			$HighlightCount++
			$LeasedLines++
			$Highlight = MessageCalc $HighlightCount $Highlight "Leased Lines"
		}
		if ($Account.MBUEOLNIC)
		{
			$Write = $True
			$HighlightCount++
			$MBUEOLNIC++
			$Highlight = MessageCalc $HighlightCount $Highlight "MBU EOL NIC"
		}
		if ($Account.MBUEOL)
		{
			$Write = $True
			$HighlightCount++
			$MBUEOL++
			$Highlight = MessageCalc $HighlightCount $Highlight "MBU EOL"
		}
		if ($Account.MBUManColo)
		{
			$Write = $True
			$HighlightCount++
			$MBUManColo++
			$Highlight = MessageCalc $HighlightCount $Highlight "MBU Managed CoLo"
		}
		if ($Write)
		{
			$MGRow++
			$MGColumn = 1
			$OutSheet.cells.Item($MGRow,$MGColumn) = $Highlight
			$MGColumn++
			$OutSheet.cells.Item($MGRow,$MGColumn) = $Account.Name
			$MGColumn++
			$OutSheet.cells.Item($MGRow,$MGColumn) = $Account.Number
		}
	}
	$MGColumn = 1
	$OutSheet.columns.Item($MGColumn).columnWidth = 34
	$MGColumn++
	$OutSheet.columns.Item($MGColumn).columnWidth = 55
	$MGColumn++
	$OutSheet.columns.Item($MGColumn).columnWidth = 14.27
	$MGColumn++
	$OutSheet.columns.Item($MGColumn).columnWidth = 35
	MakeTable "$MG" "A" 1 "D" $MGRow
	######################################################################################
    #Moving to the Overview sheet
    $OutSheet = $OutExcel.Worksheets.Item("Overview")
	$OutSheet.Activate()|out-null
	######################################################################################
	#Writing row values on the Overview sheet
	$OverviewColumn--
	$OverviewRow = 1
	$OutSheet.cells.Item($OverviewRow,$OverviewColumn) = $MG
	$OverviewRow++
	$OutSheet.cells.Item($OverviewRow,$OverviewColumn) = $Oracle
	if ($Oracle -gt 0) {$OutSheet.cells.Item($OverviewRow,$OverviewColumn).Interior.ColorIndex = 27}
	$OverviewRow++
	$OutSheet.cells.Item($OverviewRow,$OverviewColumn) = $MySQL
	if ($MySQL -gt 0) {$OutSheet.cells.Item($OverviewRow,$OverviewColumn).Interior.ColorIndex = 27}
	$OverviewRow++
	$OutSheet.cells.Item($OverviewRow,$OverviewColumn) = $MSSQL
	if ($MSSQL -gt 0) {$OutSheet.cells.Item($OverviewRow,$OverviewColumn).Interior.ColorIndex = 27}
	$OverviewRow++
	$OutSheet.cells.Item($OverviewRow,$OverviewColumn) = $RCv3
	if ($RCv3 -gt 0) {$OutSheet.cells.Item($OverviewRow,$OverviewColumn).Interior.ColorIndex = 27}
	$OverviewRow++
	$OutSheet.cells.Item($OverviewRow,$OverviewColumn) = $CAS
	if ($CAS -gt 0) {$OutSheet.cells.Item($OverviewRow,$OverviewColumn).Interior.ColorIndex = 27}
	$OverviewRow++
	$OutSheet.cells.Item($OverviewRow,$OverviewColumn) = $RMSDI
	if ($RMSDI -gt 0) {$OutSheet.cells.Item($OverviewRow,$OverviewColumn).Interior.ColorIndex = 27}
	$OverviewRow++
	$OutSheet.cells.Item($OverviewRow,$OverviewColumn) = $RMSPDP
	if ($RMSPDP -gt 0) {$OutSheet.cells.Item($OverviewRow,$OverviewColumn).Interior.ColorIndex = 27}
	$OverviewRow++
	$OutSheet.cells.Item($OverviewRow,$OverviewColumn) = $DEX
	if ($DEX -gt 0) {$OutSheet.cells.Item($OverviewRow,$OverviewColumn).Interior.ColorIndex = 27}
	$OverviewRow++
	$OutSheet.cells.Item($OverviewRow,$OverviewColumn) = $DSP
	if ($DSP -gt 0) {$OutSheet.cells.Item($OverviewRow,$OverviewColumn).Interior.ColorIndex = 27}
	$OverviewRow++
	$OutSheet.cells.Item($OverviewRow,$OverviewColumn) = $SfB
	if ($SfB -gt 0) {$OutSheet.cells.Item($OverviewRow,$OverviewColumn).Interior.ColorIndex = 27}
	$OverviewRow++
	$OutSheet.cells.Item($OverviewRow,$OverviewColumn) = $RPC
	if ($RPC -gt 0) {$OutSheet.cells.Item($OverviewRow,$OverviewColumn).Interior.ColorIndex = 27}
	$OverviewRow++
	$OutSheet.cells.Item($OverviewRow,$OverviewColumn) = $MPC
	if ($MPC -gt 0) {$OutSheet.cells.Item($OverviewRow,$OverviewColumn).Interior.ColorIndex = 27}
	$OverviewRow++
	$OutSheet.cells.Item($OverviewRow,$OverviewColumn) = $DoubleTake
	if ($DoubleTake -gt 0) {$OutSheet.cells.Item($OverviewRow,$OverviewColumn).Interior.ColorIndex = 27}
	$OverviewRow++
	$OutSheet.cells.Item($OverviewRow,$OverviewColumn) = $ObjectRocket
	if ($ObjectRocket -gt 0) {$OutSheet.cells.Item($OverviewRow,$OverviewColumn).Interior.ColorIndex = 27}
	$OverviewRow++
	$OutSheet.cells.Item($OverviewRow,$OverviewColumn) = $Isilon
	if ($Isilon -gt 0) {$OutSheet.cells.Item($OverviewRow,$OverviewColumn).Interior.ColorIndex = 27}
	$OverviewRow++
	$OutSheet.cells.Item($OverviewRow,$OverviewColumn) = $DvC
	if ($DvC -gt 0) {$OutSheet.cells.Item($OverviewRow,$OverviewColumn).Interior.ColorIndex = 27}
	$OverviewRow++
	$OutSheet.cells.Item($OverviewRow,$OverviewColumn) = $Duo
	if ($Duo -gt 0) {$OutSheet.cells.Item($OverviewRow,$OverviewColumn).Interior.ColorIndex = 27}
	$OutSheet.columns.Item($OverviewColumn).columnWidth = 11
	#Formatting borders
	######################################################################################
	$BeginRange = ExcelTranslate $OverviewColumn 1
	$EndRange = ExcelTranslate $OverviewColumn $OverviewRow
	$Range=$OutSheet.Range("${BeginRange}:$EndRange")
	$Range.Activate() |out-null
	$Range.Borders.item($BorderRight).LineStyle = 1
	$Range.Borders.item($BorderInsideHorizontal).LineStyle = 1
	#Continuing row headings
	######################################################################################
	$OverviewRow+=3
	$BeginRow = $OverviewRow
	$OutSheet.cells.Item($OverviewRow,$OverviewColumn) = $WebMux
	if ($WebMux -gt 0) {$OutSheet.cells.Item($OverviewRow,$OverviewColumn).Interior.ColorIndex = 27}
	$OverviewRow++
	$OutSheet.cells.Item($OverviewRow,$OverviewColumn) = $StrategicCount
	if ($StrategicCount -gt 0) {$OutSheet.cells.Item($OverviewRow,$OverviewColumn).Interior.ColorIndex = 27}
	$OverviewRow++
	$OutSheet.cells.Item($OverviewRow,$OverviewColumn) = $LeasedLines
	if ($LeasedLines -gt 0) {$OutSheet.cells.Item($OverviewRow,$OverviewColumn).Interior.ColorIndex = 27}
	$OverviewRow++
	$OutSheet.cells.Item($OverviewRow,$OverviewColumn) = $MBUEOLNIC
	if ($MBUEOLNIC -gt 0) {$OutSheet.cells.Item($OverviewRow,$OverviewColumn).Interior.ColorIndex = 27}
	$OverviewRow++
	$OutSheet.cells.Item($OverviewRow,$OverviewColumn) = $MBUEOL
	if ($MBUEOL -gt 0) {$OutSheet.cells.Item($OverviewRow,$OverviewColumn).Interior.ColorIndex = 27}
	$OverviewRow++
	$OutSheet.cells.Item($OverviewRow,$OverviewColumn) = $Vormetric
	if ($Vormetric -gt 0) {$OutSheet.cells.Item($OverviewRow,$OverviewColumn).Interior.ColorIndex = 27}
	$OverviewRow++
	$OutSheet.cells.Item($OverviewRow,$OverviewColumn) = $SNAS
	if ($SNAS -gt 0) {$OutSheet.cells.Item($OverviewRow,$OverviewColumn).Interior.ColorIndex = 27}
	$OverviewRow++
	$OutSheet.cells.Item($OverviewRow,$OverviewColumn) = $MBUManColo
	if ($MBUManColo -gt 0) {$OutSheet.cells.Item($OverviewRow,$OverviewColumn).Interior.ColorIndex = 27}
	#Formatting heading borders
	######################################################################################
	$BeginRange = ExcelTranslate $OverviewColumn $BeginRow
	$EndRange = ExcelTranslate $OverviewColumn $OverviewRow
	$Range=$OutSheet.Range("${BeginRange}:$EndRange")
	$Range.Activate() |out-null
	$Range.Borders.item($BorderRight).LineStyle = 1
	$Range.Borders.item($BorderInsideHorizontal).LineStyle = 1
}
$OutExcel.Saveas($Destination_File)
GoHome
$OutExcel.Close($True)
$Excel.Quit() 
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($Excel) | Out-Null
Remove-Variable Excel
######################################################################################
#Timing functions
$Stopwatch.stop()
write-host
write-host "Complete." -foregroundcolor "green"
write-host
write-host "Execution time: "$Stopwatch.Elapsed.Minutes" minutes "$Stopwatch.Elapsed.Seconds" seconds." -foregroundcolor "green"
write-host