<# Scheduled Task metadata
General
    Old-VMware Billing
    run as ndgov\!itdvcenterscript (required for SQL Database access)
        run whether user is logged on or not
 Triggers
    Daily, 5am
Actions
    old-C:\Windows\System32\WindowsPowerShell\v1.0\powershell.exe -noninteractive -file "C:\itdscript\vmconfig.ps1"

Settings
    allow task to be run on demand
    stop the task if it runs longer than 1 hour -eq $true
    if the running task does not end when requested, force it to stop
#>


Start-Transcript C:\ITDSCRIPT\Logs\VMConfig.txt
Add-PSSnapin VMware.VimAutomation.Core
Set-PowerCLIConfiguration -DefaultVIServerMode multiple -Scope Session -Confirm:$false
Connect-VIServer -Server itdvmvcp1.nd.gov,itdvmvcp2.nd.gov,itdvmvc1.nd.gov,itdvmvc2.nd.gov
#Connect-VIServer -Server itdvmvcp2.nd.gov


###########################
## Functions              #
###########################

###
### Override Invoke-SQLCmd cmdlet in order to display proper error Handling.
###
function Invoke-SQLCmd ([string] $ServerInstance, [string] $Database, [String] $Query)
{
    Try
    {
        $conn = new-object system.data.SqlClient.SqlConnection("Data Source=$ServerInstance;Integrated Security=TRUE;Initial Catalog=$Database");
        $ds = new-object "System.Data.DataSet" "dsChildSites"

        $da = new-object "System.Data.SqlClient.SqlDataAdapter" ($Query, $conn)
        $fillcnt = $da.Fill($ds)
        $conn.Close()

        $dtChild = new-object "System.Data.DataTable" "dsChildSites"
        $dtChild = $ds.Tables[0]
        $dtChild | FOREACH-OBJECT -process {
            $_
        }
    }
    Catch
    {
        write-output $Query
        Throw $_
    }
}

#$Servers = Get-VM | Where-Object {$_.PowerState -like 'PoweredOn'} | Select Name, NumCPU, @{label="MemoryMB"; expression={$_.MemoryGB * 1024}}, @{label="HardDiskSizeGB"; expression={(Get-HardDisk -VM $_ | Measure-Object -Sum CapacityGB).Sum * 1024}}, VMHost | Sort-Object Name
$Servers = Get-VM | Where-Object {($_ | Get-Harddisk).count -ne 0} | Select Name, NumCPU, @{label="MemoryMB"; expression={$_.MemoryGB * 1024}}, @{label="HardDiskSizeGB"; expression={(Get-HardDisk -VM $_ | Measure-Object -Sum CapacityGB).Sum * 1024}}, VMHost | Sort-Object Name


$SQLServer = "ITDSQL16P1\SQL16P1"
$DB = "ITD-SRS-Billing"

$Connection = New-Object System.Data.SQLClient.SQLConnection
$Connection.ConnectionString = "server='$SQLServer';database='$DB';Integrated Security=TRUE;"
$Connection.Open()
$Command = New-Object System.Data.SQLClient.SQLCommand
$Command.Connection = $Connection

$Date = "'" + (Get-Date).ToString('yyyy/MM/dd') + "'"

$sql = "delete from [VMware_VCenter_VMs] where snapshotdate = $Date;"
Invoke-SQLCmd -ServerInstance $SQLServer -Database $DB -Query $sql

foreach($Server in $Servers) {
	$ServerName = "'" + $Server.Name + "'"
	$ServerMemoryMB = $Server.MemoryMB
	$ServerNumCPU = $Server.NumCPU
	$ServerHardDiskSizeGB = $Server.HardDiskSizeGB
    $ServerHost = "'" + $Server.VMHost + "'"
	$sql ="INSERT INTO [VMware_VCenter_VMs] (ServerName, SnapshotDate, VMName, Memory_MB, Num_VCPU, Disk_MB, ESXHostName) Values ('None', $Date, $ServerName, $ServerMemoryMB, $ServerNumCPU, $ServerHardDiskSizeGB, $ServerHost);" 
    Invoke-SQLCmd -ServerInstance $SQLServer -Database $DB -Query $sql

}

$Connection.Close()
Stop-Transcript