<#

.DESCRIPTION
This script will poll NSI and classify the various devices and other metrics associated with an entire migration.

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

.EXAMPLE
.\DeviceCount.ps1 -ORD1
    Polls NSI for ORD1 devices

.EXAMPLE
.\DeviceCount.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 = "3.6",   #Script version
	[Parameter(Mandatory=$True,Position=0)][string]$DC,                 #DC Migration
	[switch]$GUI,            											#Are we invoking this from the GUI
    [switch]$Dropouts	                                                #User wants to see the items we're not capturing in the \Dropouts directory
)
#############################################################################################################################

Function ExcelTranslate ($Column,$Row)
<#
***********************************************************************************************************
v3.2
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 GoHome
{
	$Range=$OutSheet.Range("A1:A1")
	$Range.Activate() |out-null					
}

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

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 ShadeRow ($PRow,$PColumn,$PSheet)
{
	if ($PRow %2 -ne 0) 
	{
		$PSheet.cells.Item($PRow,$PColumn).Interior.ColorIndex = 23
		$PSheet.cells.Item($PRow,$PColumn).font.ColorIndex = 2
	}
	$PRow++
	return $PRow
}

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

Function ShadeColumn ($PRow,$PColumn,$PSheet)
{
	if ($PRow %2 -ne 0) 
	{
		$PSheet.cells.Item($PRow,$PColumn).Interior.ColorIndex = 23
		$PSheet.cells.Item($PRow,$PColumn).font.ColorIndex = 2
	}
	$PColumn++
	return $PColumn
}

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

Function Write-DeviceException ($Group,$Server,$Directory)
{
	$Outline = $Server._id+","+$Server.device.hostname+","+$Server.device.os+","+$Server.device.os_name+","+$Server.process.os_type
	$OutputFile = $Directory+"\"+$Group+" Counts.csv"
	add-content $OutputFile $Outline
}

#############################################################################################################################
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": false}
		},
		"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
}


#############################################################################################################################
#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"
}
$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"
$DateURI  = "https://dbapi.nsi.rackspace.com/migration/metadata/Find"
$MoveGroups = Get-CollectionMGS
$TimeStamp = Get-Date -Format d | foreach {$_ -replace "/", "-"}
$Destination_File = ([Environment]::GetFolderPath("Desktop"))+"\"+$CollectionName+" - Counts - "+$TimeStamp+".xlsx"		#Our Additional Support file
$AllColumn = 1
$AllRow = 1
$PhysicalColumn = 1
$PhysicalRow = 1
$MRRRow = 1
$MRRColumn = 1
$ConRow = 1
$ConColumn = 1
$CurrentMG = 0
$AllUniqueAccounts = @()
######################################################################################
#Cleaning our old dropouts
if ($Dropouts)
{
	$DropoutsDir = $Dir+"\Dropouts\"
	$DropoutsCSV = $DropoutsDir+"*Counts.csv"
	#Testing the dropouts directory and creating it if it doesn't exist
	if (!(test-path $DropoutsDir)) 
    {
        new-item $DropoutsDir -type directory | Out-Null
    }
	#Deleting the appropriate files in the dropouts directory
	remove-item $DropoutsCSV
}
#Creating the Excel object
######################################################################################
$Excel = New-Object -ComObject excel.application
$Excel.visible = $False
$Excel.displayalerts = $False
$OutExcel = $Excel.Workbooks.Add()
######################################################################################
#Creating Excel sheets
$OutSheet = $OutExcel.Worksheets.Item(1)
$OutSheet.Name = "Consolidated Account List"
$OutExcel.Worksheets.Add()|out-null
$OutSheet = $OutExcel.Worksheets.Item(1)
$OutSheet.Name = "Totals"
$OutExcel.Worksheets.Add()|out-null
$OutSheet = $OutExcel.Worksheets.Item(1)
$OutSheet.Name = "MRR Breakdown"
$OutExcel.Worksheets.Add()|out-null
$OutSheet = $OutExcel.Worksheets.Item(1)
$OutSheet.Name = "All Devices"
######################################################################################
#Moving to the OS sheet
$OutSheet = $OutExcel.Worksheets.Item("All Devices")
$OutSheet.Activate()|out-null
######################################################################################
#Writing headings on the OS sheet
$StartRange = $AllRow
$OutSheet.cells.Item($AllRow,$AllColumn) = "Move Group"
$AllRow++
$OutSheet.cells.Item($AllRow,$AllColumn) = "Date"
$OutSheet.cells.Item($AllRow,$AllColumn).Font.Bold=$True
$AllRow++
$OutSheet.cells.Item($AllRow,$AllColumn) = "Windows"
$AllRow = ShadeRow $AllRow $AllColumn $OutSheet
$OutSheet.cells.Item($AllRow,$AllColumn) = "Linux"
$AllRow = ShadeRow $AllRow $AllColumn $OutSheet
$OutSheet.cells.Item($AllRow,$AllColumn) = "ESX Hypervisors"
$AllRow = ShadeRow $AllRow $AllColumn $OutSheet
$OutSheet.cells.Item($AllRow,$AllColumn) = "Hyper-V Hypervisors"
$AllRow = ShadeRow $AllRow $AllColumn $OutSheet
$OutSheet.cells.Item($AllRow,$AllColumn) = "Hypervisor Clusters"
$AllRow = ShadeRow $AllRow $AllColumn $OutSheet
$OutSheet.cells.Item($AllRow,$AllColumn) = "Shared Storage"
$AllRow = ShadeRow $AllRow $AllColumn $OutSheet
$OutSheet.cells.Item($AllRow,$AllColumn) = "DSAN Storage"
$AllRow = ShadeRow $AllRow $AllColumn $OutSheet
$OutSheet.cells.Item($AllRow,$AllColumn) = "DNAS Storage"
$AllRow = ShadeRow $AllRow $AllColumn $OutSheet
$OutSheet.cells.Item($AllRow,$AllColumn) = "DAS Storage"
$AllRow = ShadeRow $AllRow $AllColumn $OutSheet
$OutSheet.cells.Item($AllRow,$AllColumn) = "SNAS Storage"
$AllRow = ShadeRow $AllRow $AllColumn $OutSheet
$OutSheet.cells.Item($AllRow,$AllColumn) = "Taps"
$AllRow = ShadeRow $AllRow $AllColumn $OutSheet
$OutSheet.cells.Item($AllRow,$AllColumn) = "Other"
$AllRow = ShadeRow $AllRow $AllColumn $OutSheet
$OutSheet.cells.Item($AllRow,$AllColumn) = "Total Devices"
$OutSheet.cells.Item($AllRow,$AllColumn).Font.Bold=$True
$EndRange = $AllRow
$AllRow+=2
$OutSheet.cells.Item($AllRow,$AllColumn) = "Customers Moved"
$AllRow++
$OutSheet.cells.Item($AllRow,$AllColumn) = "Total Physical Devices"
$AllRow+=2
$OutSheet.cells.Item($AllRow,$AllColumn) = "Windows Resources Needed"
$AllRow++
$OutSheet.cells.Item($AllRow,$AllColumn) = "Devices Per Resource"
#$AllRow-=6
$OutSheet.columns.Item($AllColumn).columnWidth = 17
$OutSheet.columns.Item(1).columnWidth = 26.14

$BeginRange = ExcelTranslate "A" $StartRange
$EndRange = ExcelTranslate "A" $EndRange
$Range=$OutSheet.Range("${BeginRange}:$EndRange")
$Range.Activate() |out-null
$Range.Borders.item($BorderRight).LineStyle = 1
$Range.Borders.item($BorderInsideHorizontal).LineStyle = 1

foreach ($MG in $MoveGroups)
{
	$CurrentMG++
	$MGPercentageComplete = ($CurrentMG/$MoveGroups.count)*100
	$MGPercentageComplete = "{0:N0}" -f $MGPercentageComplete
	Write-Progress -Activity "Processing move group $MG" -Status "$MGPercentageComplete% Complete" -PercentComplete $MGPercentageComplete
	Write-Progress -Activity "Processing devices for $MG" -Status "0% Complete" -PercentComplete 0 -id 1
$JSON = @"
{
    "query": {
	"move_group":"$MG",
	"deleted":{"`$ne": true}
    }
}
"@
	#Querying the database
	write-host "Polling NSI..." -f "cyan" -nonewline
	$Results = (Invoke-RestMethod -Uri $URI -Body "$JSON" -ContentType "application/json" -Method "POST" -Headers @{"X-Auth"=$Global:Token} -TimeoutSec 120).result
	write-host "complete.  $($Results.count) results found for move group $MG." -f "cyan"
	#Individual device counts
	$MRRRow = 1
	$PhysicalRow = 1
	$UniqueAccounts = @()
	$Microsoft = 0
	$MicrosoftVMs = 0
	$Linux = 0
	$LinuxVMs = 0
	$ESXHyps = 0
	$HyperVHyps = 0
	$PhysicalServers = 0
	$PhysicalDevices = 0
	$HypervisorClusters = 0
	$SharedStorage = 0
	$DSANStorage = 0
	$DASStorage = 0
	$FWLBWAFs = 0
	$AlertLogic = 0
	$Unknown = 0
	$WindowsClusters = 0
	$LinuxClusters = 0
	$TotalMRR = 0
	$LeasedLines = 0
	$Colo = 0
	$Duo = 0
	$Vormetric = 0
	$FibreSwitch = 0
	$DNASStorage = 0
	$SNASStorage = 0
	$JBODStorage = 0
	$RSA = 0
	$Taps = 0
	$UnsupportedVMs = 0
	$CurrentDevice = 0
	$TotalAllDevices = 0
	#Iterating through the devices
	Foreach ($Device in $Results)
    {
		$CurrentDevice++
		$DevicePercentageComplete = ($CurrentDevice/$Results.result.count)*100
		$ReceivedDevices = $Results.result.count
		$DevicePercentageComplete = "{0:N0}" -f $DevicePercentageComplete
		Write-Progress -Activity "Analyzing devices for $MG" -Status "Device $CurrentDevice of $ReceivedDevices, $DevicePercentageComplete% Complete" -PercentComplete $DevicePercentageComplete -id 1
		$NewAccount = $True
		#Determining the type of device
		if ($Device.deleted -ne "true" -and $Device.device.status -notlike "*ecommiss*" -and $Device.account.status -ne "Closed" -and $Device.deleted -ne "True")
		{
			$TotalAllDevices++
			Switch -casesensitive -wildcard ($Device.process.os_type)
			{
				#$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$			
				"Windows" 	#Microsoft devices
				{
					$Microsoft++
					$PhysicalServers++
				}
				"Windows VM"
				{
					$MicrosoftVMs++
					$Microsoft++
				}
				"Hyper-V"
				{
					$HyperVHyps++
					$Microsoft++
				}
				"Windows Cluster"
				{
					$WindowsClusters++
				}
				
				#$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$			
				"Linux"     	#Linux devices
				{
					$Linux++
					$PhysicalDevices++
				}
				"Linux VM"
				{
					$Linux++
					$LinuxVMs++
				}
				"Linux Cluster"
				{
					$LinuxClusters++
				}
				"Appliance VM"
				{
					$UnsupportedVMs++
				}
				#$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$			
				"*Firewall*"     	#Network devices
				{
					$FWLBWAFs++
					$PhysicalServers++
				}
				"*Load-Balancer*"     	#Network devices
				{
					$FWLBWAFs++
					$PhysicalServers++
				}
				"*WAF*"     	#Network devices
				{
					$FWLBWAFs++
					$PhysicalServers++
				}
				"Leased Line"
				{
					$LeasedLines++
				}
				
				#$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$			
				"Storage Managed" 		#SSAN
				{
					$SharedStorage++
				}
				"Storage Dedicated" 		#SSAN
				{
					$DNASStorage++
					$PhysicalDevices++
				}
				"Storage External" 		#SSAN
				{
					if ($Device.device.os -like "*DSAN*")
					{
						$DSANStorage++
						$PhysicalDevices++
					}
					else 
					{
						if ($Dropouts)
						{
							Write-DeviceException $MG $Device $DropoutsDir
						}
					}
				}
				"Storage Attached" 		#SSAN
				{
					if ($Device.device.os -eq "Shared NAS (sNAS)")
					{
						$SNASStorage++
					}
					elseif ($Device.device.os -like "*NAS*")
					{
						$DSANStorage++
						$PhysicalDevices++
					}
					elseif ($Device.device.os -like "*MD*")
					{
						$DASStorage++
						$PhysicalDevices++
					}
					elseif ($Device.device.os -like "*VNX*")
					{
						$DSANStorage++
						$PhysicalDevices++
					}
					else 
					{
						if ($Dropouts)
						{
							Write-DeviceException $MG $Device $DropoutsDir
						}
					}
				}
				"Cluster Hyp"
				{
					$PhysicalServers++
					$HypervisorClusters++
				}
				"VMWare ESX"
				{
					$PhysicalServers++
					$ESXHyps++
				}
				"Colocation"
				{
					$Colo++
					$PhysicalDevices++
				}
				"Managed Security"
				{
					$PhysicalDevices++
					if ($Device.device.os -like "*ogic*")
					{
						$AlertLogic++;
						$PhysicalDevices++
					}
					elseif ($Device.device.os -like "*actor*")
					{
						$RSA++
					}
					elseif ($Device.device.os -eq "Encryption and Key Management")
					{
						$Vormetric++
						$PhysicalDevices++
					}
					elseif ($Device.device.os -eq "Encryption and Key Management (Virtual)")
					{
						$Vormetric++
						$UnsupportedVMs++
					}
					elseif ($Device.device.os -eq "PS Net Device")
					{
						$PhysicalDevices++
						$Unknown++
					}
					else
					{
						if ($Dropouts)
						{
							Write-DeviceException $MG $Device $DropoutsDir
						}
					}
				}
				"Managed Security Unsupported"
				{
					$PhysicalDevices++
					$Unknown++
				}
				default
				{
					if ($Device.device.os_name -eq "Unknown")
					{
						if ($Device.device.hostname -like "*ESX*" -or $Device.device.hostname -like "*esx*" )
						{
							$ESXHyps++
							$PhysicalDevices++
						}
						elseif ($Device.device.hostname -like "*TAP*" -or $Device.device.hostname -like "*tap*" )
						{
							$Taps++
							$PhysicalDevices++
						}
						#$Server._id+","+$Server.device.hostname+","+$Server.device.os+","+$Server.device.os_name+","+$Server.process.os_type
					}
					elseif ($Device.device.os -like "*HP*" -or $Device.device.os -like "*Dell*")
					{
						$PhysicalDevices++
						if ($Device.device.os_name -like "*inux*")
						{
							$Linux++
						}
						elseif ($Device.device.os_name -like "*indows*")
						{
							$Microsoft++
						}
					}
					elseif ($Device.device.os -notlike "*Custom Monitoring*" -and
						$Device.device.os -notlike "*USB Drive*" -and
						$Device.device.os -notlike "*Virtual Server Backup Client*" -and
						$Device.device.os -notlike "*Rackspace DDoS Mitigation*" -and
						$Device.device.os -notlike "*Virtual Server Cluster for Hyper-V*" -and
						$Device.device.os -notlike "*VMware Shared vCenter Client*" -and
						$Device.device.os -notlike "*Replica Broker for Hyper-V*" -and
						$Device.process.os_type -ne "Switch" -and
						$Device.process.os_type -ne "Reserved Space" -and
						$Device.process.os_type -ne "One-Off Cloud Consolidation" -and
						$Device.process.os_type -ne "RackConnect RegionLink" -and
						$Device.process.os_type -ne "Logical Device" -and
						$Device.process.os_type -ne "Data Replication Virtual Device" -and
						$Device.process.os_type -notlike "*DDOS*" -and
						$Device.process.os_type -ne "Custom Monitoring" -and
						$Device.device.os -notlike "*Managed Security*")
					{
						if ($Dropouts) 
						{
							Write-DeviceException $MG $Device $DropoutsDir
						}
						$Unknown++
					}
				}
			}
		}
#$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$			
		#Counting unique accounts
		Foreach ($Account in $UniqueAccounts)
        {
			if ($Device.account.number -eq $Account.Number)# -or $Device.deleted -eq "true" -or $Device.deleted -eq "True" -or $Device.account.status -or "Closed") 
			{$NewAccount = $False}
        }
		if ($NewAccount) #-and $Device.deleted -ne "true" -and $Device.device.status -notlike "*ecommiss*" -and $Device.account.status -ne "Closed" -and $Device.deleted -ne "True") 
		{
			$AccountPSO = [PSCustomObject]@{
				Name 	= $Device.account.name;
				Number 	= $Device.account.number;
				DPAccount = $Device.dp_account;
				MRR 	= [decimal]$Device.account.mrr;
				AM 		= $Device.account.manager;
				LT 		= $Device.account.lead_tech;
				MG		= $MG
			 }
			$UniqueAccounts+=$AccountPSO
			$AllUniqueAccounts+=$AccountPSO
		}
	}
	######################################################################################
	#Moving to the OS sheet
	$OutSheet = $OutExcel.Worksheets.Item("All Devices")
	$OutSheet.Activate()|out-null
	######################################################################################
	$AllRow = 1
    $AllColumn++
	#Writing the move group
	$OutSheet.cells.Item($AllRow,$AllColumn) = $MG
    $AllRow++
	######################################################################################
	#Setting the dates
$DateJSON = @"
{
    "query": {
	"_id":"$MG",
	"deleted":{"`$ne": true}
    }
}
"@
	$Date = ((Invoke-RestMethod -Uri $DateURI -Body "$DateJSON" -ContentType "application/json" -Method "POST" -Headers @{"X-Auth"=$Global:Token} -TimeoutSec 120).result.start.datetime| foreach {$_ -replace "/", "-"}).Split("{ }")[0]
	$OutSheet.cells.Item($AllRow,$AllColumn) = $Date
	######################################################################################
	#Writing the individual totals for the All Devices Tab
	$TotalAllDevices = 0
    $OutSheet.cells.Item($AllRow,$AllColumn).Font.Bold=$True
	$AllRow++
	$OutSheet.cells.Item($AllRow,$AllColumn) = $Microsoft
	$TotalAllDevices+=$Microsoft
	$ResourcesNeeded = [int]([math]::Round($Microsoft/100))
	if ($ResourcesNeeded -lt 1) {$ResourcesNeeded = 1}
	$DevicesPerResource = $Microsoft/$ResourcesNeeded	
	$DevicesPerResource = "{0:N0}" -f $DevicesPerResource
	$AllRow = ShadeRow $AllRow $AllColumn $OutSheet
	$OutSheet.cells.Item($AllRow,$AllColumn) = $Linux
	$TotalAllDevices+=$Linux
    $AllRow = ShadeRow $AllRow $AllColumn $OutSheet
	$OutSheet.cells.Item($AllRow,$AllColumn) = $ESXHyps
	$TotalAllDevices+=$ESXHyps
    $AllRow = ShadeRow $AllRow $AllColumn $OutSheet
	$OutSheet.cells.Item($AllRow,$AllColumn) = $HyperVHyps
	$TotalAllDevices+=$HyperVHyps
    $AllRow = ShadeRow $AllRow $AllColumn $OutSheet
	$OutSheet.cells.Item($AllRow,$AllColumn) = $HypervisorClusters
	$TotalAllDevices+=$HypervisorClusters
    $AllRow = ShadeRow $AllRow $AllColumn $OutSheet
	$OutSheet.cells.Item($AllRow,$AllColumn) = $SharedStorage
	$TotalAllDevices+=$SharedStorage
    $AllRow = ShadeRow $AllRow $AllColumn $OutSheet
	$OutSheet.cells.Item($AllRow,$AllColumn) = $DSANStorage
	$TotalAllDevices+=$DSANStorage
    $AllRow = ShadeRow $AllRow $AllColumn $OutSheet
	$OutSheet.cells.Item($AllRow,$AllColumn) = $DNASStorage
	$TotalAllDevices+=$DNASStorage
    $AllRow = ShadeRow $AllRow $AllColumn $OutSheet
	$OutSheet.cells.Item($AllRow,$AllColumn) = $DASStorage
	$TotalAllDevices+=$DASStorage
    $AllRow = ShadeRow $AllRow $AllColumn $OutSheet
	$OutSheet.cells.Item($AllRow,$AllColumn) = $SNASStorage
	$AllRow = ShadeRow $AllRow $AllColumn $OutSheet
	$OutSheet.cells.Item($AllRow,$AllColumn) = $Taps
	$AllRow = ShadeRow $AllRow $AllColumn $OutSheet
	$OutSheet.cells.Item($AllRow,$AllColumn) = $Unknown
	$TotalAllDevices+=$Unknown
    $AllRow = ShadeRow $AllRow $AllColumn $OutSheet
    $OutSheet.cells.Item($AllRow,$AllColumn) = $TotalAllDevices
	$OutSheet.cells.Item($AllRow,$AllColumn).Font.Bold=$True
	$EndRange = $AllRow
    $AllRow+=2
	######################################################################################
	#Adjusting column width to fit
	$OutSheet.columns.Item($AllColumn).columnWidth = 10
	######################################################################################
	#Configuring borders
	$BeginRange = ExcelTranslate $AllColumn 1
	$EndRange = ExcelTranslate $AllColumn $EndRange
	$Range=$OutSheet.Range("${BeginRange}:$EndRange")
	$Range.Activate() |out-null
	$Range.Borders.item($BorderLeft).LineStyle = 1
	$Range.Borders.item($BorderInsideHorizontal).LineStyle = 1
	$OutSheet.cells.Item($AllRow,$AllColumn) = $UniqueAccounts.count
	$AllRow++
	$TotalPhysicals = $PhysicalDevices+$PhysicalServers
	$OutSheet.cells.Item($AllRow,$AllColumn) = $TotalPhysicals
	$AllRow+=2
	$OutSheet.cells.Item($AllRow,$AllColumn) = $ResourcesNeeded
	$AllRow++
	$OutSheet.cells.Item($AllRow,$AllColumn) = $DevicesPerResource
	GoHome
	######################################################################################
	#Moving to the MRR sheet
	$OutSheet = $OutExcel.Worksheets.Item("MRR Breakdown")
	$OutSheet.Activate()|out-null
	######################################################################################
	#Writing headings for  the MRR page
	$OutSheet.cells.Item($MRRRow,$MRRColumn) = $MG
	$OutSheet.cells.Item($MRRRow,$MRRColumn).Font.Size=14
	$MRRRow++
	$BeginRow = $MRRRow
	$BeginColumn =  $MRRColumn
	$OutSheet.cells.Item($MRRRow,$MRRColumn) = "Account #"
	$MRRColumn++
	$OutSheet.cells.Item($MRRRow,$MRRColumn) = "Name"
	$OutSheet.columns.Item($MRRColumn).columnWidth = 45
	$MRRColumn++
	$OutSheet.cells.Item($MRRRow,$MRRColumn) = "MRR"
	$MRRRow++
	######################################################################################
	#Processing the accounts
	$UniqueAccounts = $UniqueAccounts|sort-object -property MRR -Descending
	foreach ($Account in $UniqueAccounts)
	{
		$MRRColumn-= 2
		$OutSheet.cells.Item($MRRRow,$MRRColumn) = $Account.Number
		$MRRColumn++
		$OutSheet.cells.Item($MRRRow,$MRRColumn) = $Account.Name
		$MRRColumn++
		$OutSheet.cells.Item($MRRRow,$MRRColumn) = $Account.MRR
		$OutSheet.cells.Item($MRRRow,$MRRColumn).NumberFormat = "$#,##0.00"
		$MRRRow++
		if ($Account.MRR -gt 0) {$TotalMRR+= $Account.MRR}
	}
	$MRRRow++
	$MRRColumn-=2
	$OutSheet.cells.Item($MRRRow,$MRRColumn) = "Total MRR:"
	$OutSheet.cells.Item($MRRRow,$MRRColumn).Font.Bold = $True
	$OutSheet.cells.Item($MRRRow,$MRRColumn).NumberFormat = "$#,##0.00"
	$MRRColumn++
	$OutSheet.cells.Item($MRRRow,$MRRColumn) = $TotalMRR
	$OutSheet.cells.Item($MRRRow,$MRRColumn).Font.Bold = $True
	$OutSheet.cells.Item($MRRRow,$MRRColumn).NumberFormat = "$#,##0.00"
	$MRRColumn++

	$EndRow = $MRRRow - 2
	$EndColumn =  $MRRColumn
	MakeTable "$MG MRR" $BeginColumn $BeginRow $EndColumn $EndRow
	$MRRColumn+= 2
	
	######################################################################################
	#Moving to the Physical sheet
	$OutSheet = $OutExcel.Worksheets.Item("Totals")
	$OutSheet.Activate()|out-null
	######################################################################################
	#Writing headings for the Physical page
	$OutSheet.cells.Item($PhysicalRow,$PhysicalColumn) = $MG
	$OutSheet.cells.Item($PhysicalRow,$PhysicalColumn).Font.Size=14
	$PhysicalRow++
	$BeginRow = $PhysicalRow
	$BeginColumn =  $PhysicalColumn
	$OutSheet.cells.Item($PhysicalRow,$PhysicalColumn) = "Device Type"
	$OutSheet.cells.Item($PhysicalRow,$PhysicalColumn).Font.Bold = $True
	$PhysicalColumn++
	$OutSheet.cells.Item($PhysicalRow,$PhysicalColumn) = "Count"
	$OutSheet.cells.Item($PhysicalRow,$PhysicalColumn).Font.Bold = $True
	$PhysicalColumn--
	$PhysicalRow++
	######################################################################################
	#Processing the accounts
	$TotalDevices = 0
	$OutSheet.cells.Item($PhysicalRow,$PhysicalColumn) = "Physical Servers"
	$PhysicalColumn++
	$OutSheet.cells.Item($PhysicalRow,$PhysicalColumn) = $PhysicalServers
	$TotalDevices+=$PhysicalServers
	$PhysicalColumn--
	$PhysicalRow++
	$VMCount = $MicrosoftVMs+$LinuxVMs+$UnsupportedVMs
	$OutSheet.cells.Item($PhysicalRow,$PhysicalColumn) = "VMs"
	$PhysicalColumn++
	$OutSheet.cells.Item($PhysicalRow,$PhysicalColumn) = $VMCount
	$TotalDevices+=$VMCount
	$PhysicalColumn--
	$PhysicalRow++
	$OutSheet.cells.Item($PhysicalRow,$PhysicalColumn) = "Networking"
	$PhysicalColumn++
	$OutSheet.cells.Item($PhysicalRow,$PhysicalColumn) = $FWLBWAFs
	$TotalDevices+=$FWLBWAFs
	$PhysicalColumn--
	$PhysicalRow++
	$OutSheet.cells.Item($PhysicalRow,$PhysicalColumn) = "Alert Logic"
	$PhysicalColumn++
	$OutSheet.cells.Item($PhysicalRow,$PhysicalColumn) = $AlertLogic
	$TotalDevices+=$AlertLogic
	$PhysicalColumn--
	$PhysicalRow++
	$StorageTotal = $DASStorage+$DNASStorage+$JBODStorage+$FibreSwitch+$DSANStorage
	$OutSheet.cells.Item($PhysicalRow,$PhysicalColumn) = "Storage"
	$PhysicalColumn++
	$OutSheet.cells.Item($PhysicalRow,$PhysicalColumn) = $StorageTotal
	$TotalDevices+=$StorageTotal
	$PhysicalColumn--
	$PhysicalRow++
	$OutSheet.cells.Item($PhysicalRow,$PhysicalColumn) = "Leased Lines"
	$PhysicalColumn++
	$OutSheet.cells.Item($PhysicalRow,$PhysicalColumn) = $LeasedLines
	$TotalDevices+=$LeasedLines
	$PhysicalColumn--
	$PhysicalRow++
	$OutSheet.cells.Item($PhysicalRow,$PhysicalColumn) = "Colocation"
	$PhysicalColumn++
	$OutSheet.cells.Item($PhysicalRow,$PhysicalColumn) = $Colo
	$TotalDevices+=$Colo
	$PhysicalColumn--
	$PhysicalRow++
	$OutSheet.cells.Item($PhysicalRow,$PhysicalColumn) = "RSA Appliance"
	$PhysicalColumn++
	$OutSheet.cells.Item($PhysicalRow,$PhysicalColumn) = $RSA
	$TotalDevices+=$RSA
	$PhysicalColumn--
	$PhysicalRow++
	$OutSheet.cells.Item($PhysicalRow,$PhysicalColumn) = "Vormetric"
	$PhysicalColumn++
	$OutSheet.cells.Item($PhysicalRow,$PhysicalColumn) = $Vormetric
	$TotalDevices+=$Vormetric
	#Creating a table for the device counts
	$EndRow = $PhysicalRow
	$EndColumn =  $PhysicalColumn
	MakeTable "$MG Physicals" $BeginColumn $BeginRow $EndColumn $EndRow
	$PhysicalColumn--
	$PhysicalRow+=2
	$OutSheet.cells.Item($PhysicalRow,$PhysicalColumn) = "Total Devices:"
	$OutSheet.columns.Item($PhysicalColumn).columnWidth = 21
	$OutSheet.cells.Item($PhysicalRow,$PhysicalColumn).Font.Bold=$True
	$PhysicalColumn++
	$OutSheet.cells.Item($PhysicalRow,$PhysicalColumn) = $TotalDevices
	$OutSheet.cells.Item($PhysicalRow,$PhysicalColumn).Font.Bold=$True
	$PhysicalColumn+= 2
}
######################################################################################
#Moving to the Physical sheet to bring the cursor back home
$OutSheet = $OutExcel.Worksheets.Item("Totals")
$OutSheet.Activate()|out-null
GoHome
######################################################################################
#Moving to the MRR sheet
$OutSheet = $OutExcel.Worksheets.Item("MRR Breakdown")
$OutSheet.Activate()|out-null
GoHome
######################################################################################
#Moving to the OS sheet
$OutSheet = $OutExcel.Worksheets.Item("All Devices")
$OutSheet.Activate()|out-null
#Stacked line chart
$BeginRange = ExcelTranslate "A" 3
$AllRow=13
$EndRange = ExcelTranslate $AllColumn $AllRow
$Range=$OutSheet.Range("${BeginRange}:$EndRange")
$Range.Activate() |out-null
$Chart = $OutSheet.Shapes.AddChart().Chart
$Chart.ChartType = 76
$Chart.setSourceData($Range)
#Setting X axis values
$AllRow-=4
$BeginRange = ExcelTranslate "B" 1
$EndRange = ExcelTranslate $AllColumn 1
$Range=$OutSheet.Range("${BeginRange}:$EndRange")
$Range.Activate() |out-null
$Chart.SeriesCollection(1).XValues = $Range
#Setting chart title
$Chart.HasTitle = $True
$Chart.ChartTitle.Text = "Device Count by Move Group and Device Type"
$Chart.Axes(1).CategoryType = 2
#Moving and resizing chart
$ChartObject=$Chart.Parent
$ChartObject.Top = "315"
$ChartObject.Left = "1"
$ChartObject.Height = 300
$ChartObject.Width = 1250
GoHome
######################################################################################
#Moving to the Physical sheet
$OutSheet = $OutExcel.Worksheets.Item("Consolidated Account List")
$OutSheet.Activate()|out-null
######################################################################################
#Writing headings for the Physical page
$OutSheet.cells.Item($ConRow,$ConColumn) = "Account Number"
$ConColumn++
$OutSheet.cells.Item($ConRow,$ConColumn) = "Account Name"
$ConColumn++
$OutSheet.cells.Item($ConRow,$ConColumn) = "DP Account Number"
$ConColumn++
$OutSheet.cells.Item($ConRow,$ConColumn) = "MRR"
$ConColumn++
$OutSheet.cells.Item($ConRow,$ConColumn) = "AM"
$ConColumn++
$OutSheet.cells.Item($ConRow,$ConColumn) = "LT"
$ConColumn++
$OutSheet.cells.Item($ConRow,$ConColumn) = "MG"
$AllUniqueAccounts = $AllUniqueAccounts | Sort-Object "MRR" -Descending
foreach ($UniqueAccount in $AllUniqueAccounts)
{
	$ConRow++
	$ConColumn = 1
	$OutSheet.cells.Item($ConRow,$ConColumn) = $UniqueAccount.Number
	$OutSheet.columns.Item($ConColumn).columnWidth = 18
	$ConColumn++
	$OutSheet.cells.Item($ConRow,$ConColumn) = $UniqueAccount.Name
	$OutSheet.columns.Item($ConColumn).columnWidth = 61
	$ConColumn++
	$OutSheet.cells.Item($ConRow,$ConColumn) = $UniqueAccount.DPAccount
	$OutSheet.columns.Item($ConColumn).columnWidth = 18
	$ConColumn++
	$OutSheet.cells.Item($ConRow,$ConColumn) = $UniqueAccount.MRR
	$OutSheet.cells.Item($ConRow,$ConColumn).NumberFormat = "$#,##0.00"
	$OutSheet.columns.Item($ConColumn).columnWidth = 12
	$ConColumn++
	$OutSheet.cells.Item($ConRow,$ConColumn) = $UniqueAccount.AM
	$OutSheet.columns.Item($ConColumn).columnWidth = 18
	$ConColumn++
	$OutSheet.cells.Item($ConRow,$ConColumn) = $UniqueAccount.LT
	$OutSheet.columns.Item($ConColumn).columnWidth = 18
	$ConColumn++
	$OutSheet.cells.Item($ConRow,$ConColumn) = $UniqueAccount.MG
	$OutSheet.columns.Item($ConColumn).columnWidth = 12
}
MakeTable "Consolidated Account List" 1 1 $ConColumn $ConRow	
GoHome
$OutSheet = $OutExcel.Worksheets.Item(1)
$OutSheet.Activate()|out-null
$OutExcel.Saveas($Destination_File)
if (Test-Path "C:\Users\ira.babb\OneDrive - Rackspace Inc\Pye Hole\") 
{
	copy $Destination_File "C:\Users\ira.babb\OneDrive - Rackspace Inc\Pye Hole\"
}
$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