Zack Meier
67 lines
3.0 KiB
PowerShell
67 lines
3.0 KiB
PowerShell
<# This is how VMware data is sent to Billing
|
|
|
|
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"
|
|
new-"C:\Program Files\PowerShell\7\pwsh.exe" -noninteractive -file "F:\SyncVMwareVMsToSql\SyncVMwareVMsToSql.ps1"
|
|
"C:\Program Files\PowerShell\7\pwsh.exe" -noninteractive -file "F:\SyncVMwareVMsToSql\SyncVMwareVMsToSql.ps1"
|
|
newV3 - PSUniversal script and schedule
|
|
|
|
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
|
|
|
|
SQL Query to check for most recent 2500 records
|
|
SELECT TOP (2500) [ServerName]
|
|
,[SnapshotDate]
|
|
,[VMName]
|
|
,[Memory_MB]
|
|
,[Num_VCPU]
|
|
,[Disk_MB]
|
|
,[ESXHostName]
|
|
FROM [ITD-SRS-Billing].[dbo].[VMWare_VCenter_VMs]
|
|
ORDER BY SnapshotDate DESC, VMName ASC
|
|
|
|
#>
|
|
|
|
$TimeStamp = Get-Date -UFormat "%Y%m%d%H%M%S"
|
|
#Start-Transcript F:\SyncVMwareVMsToSql\Logs\SyncVMwareVMsToSql-$Timestamp.log
|
|
|
|
Write-Verbose -Message "Connect to vCenter" -Verbose
|
|
Set-PowerCLIConfiguration -DefaultVIServerMode multiple -Scope Session -Confirm:$false
|
|
Connect-ITDvCenter -Credential $Secret:ndgov_svcitdvmvcauto
|
|
|
|
Write-Verbose -Message "Get current virtual machines, minus filters"
|
|
$Datacenters = Get-Datacenter | Where-Object {$_.Name -notlike "*Normandy*" -and $_.Name -notlike "*Vantis*"}
|
|
$VMs = $Datacenters | Get-VM | Where-Object { $_.ExtensionData.summary.config.ManagedBy.Type -ne "placeholderVm" -and $_.Name -notlike "itdzmtest*"} | Select Name, NumCPU, @{label="MemoryMB"; expression={$_.MemoryGB * 1024}}, @{label="HardDiskSizeGB"; expression={(Get-HardDisk -VM $_ | Measure-Object -Sum CapacityGB).Sum * 1024}}, VMHost | Sort-Object Name
|
|
|
|
Write-Verbose "Prep SQL connection" -Verbose
|
|
$SqlServer = "itdsql22p1.nd.gov\SQL22P1"
|
|
$Database = "ITD-SRS-Billing"
|
|
$Date = "'" + (Get-Date).ToString('yyyy/MM/dd') + "'"
|
|
|
|
Write-Verbose -Message "Remove today's entries from SQL if already there"
|
|
$SqlQuery = "delete from [VMware_VCenter_VMs] where snapshotdate = $Date;"
|
|
Invoke-SQLCmd -ServerInstance $SqlServer -Database $Database -Query $SqlQuery
|
|
|
|
Write-Verbose -Message "Start loop to create new record for each VM found" -Verbose
|
|
foreach($VM in $VMs) {
|
|
Write-Verbose -Message ("Begin " + $VM.Name)
|
|
$VMName = "'" + $VM.Name + "'"
|
|
$VMMemoryMB = $VM.MemoryMB
|
|
$VMNumCPU = $VM.NumCPU
|
|
$VMHardDiskSizeGB = $VM.HardDiskSizeGB
|
|
$VMHost = "'" + $VM.VMHost + "'"
|
|
$SqlQuery ="INSERT INTO [VMware_VCenter_VMs] (ServerName, SnapshotDate, VMName, Memory_MB, Num_VCPU, Disk_MB, ESXHostName) Values ('None', $Date, $VMName, $VMMemoryMB, $VMNumCPU, $VMHardDiskSizeGB, $VMHost);"
|
|
Invoke-SQLCmd -ServerInstance $SqlServer -Database $Database -Query $SqlQuery
|
|
}
|
|
|
|
#Stop-Transcript
|
|
Disconnect-ITDvCenter |