HI All,
i realize that this is more a PowerShell question than PowerCLI but i've been trying to figure this our for a week now and am getting nowhere fast. Hoping someone on here may have done this already.
I have an Array with basic VM information $VMinfo = @()
Date | vCenter | Cluster | VM_Name | IP Address | PowerState |
---|---|---|---|---|---|
01/01/2020 | MyVC1 | MyCluster1 | MyVMname1 | 1.1.1.1 | PoweredOn |
02/02/2020 | MyVC1 | MyCluster2 | MyVMName2 | 2.2.2.2 | PoweredOff |
03/03/2020 | MyVC2 | AnotherCluster | AnotherVMname | 3.3.3.3 | PoweredOn |
With the below function, i can insert the data into a SQL Database but, everything goes in on a single row, i.e.
Date | vCenter | Cluster | VM_Name | IP_Address | PowerState |
---|---|---|---|---|---|
01/01/2020 02/02/2020 03/03/2020 | MyVC1 MyVC1 MyVC2 | MyCluster1 MyCluster2 AnotherCluster | MyVMname1 MyVMname2 AnotherVMname | 1.1.1.1 2.2.2.2 3.3.3.3 | PoweredOn Power |
function Out-SQL {
[CmdletBinding()]
$output_Server = "MySQLserver.domain.com"
$output_Database = "My_Database"
$output_Username = "myUsername"
$output_Password = "myPassword!"
$Date = Get-Date
$output_vCenter = $VMinfo.vCenter
$output_Cluster = $VMinfo.Cluster
$output_VMname = $VMinfo.'VM Name'
$output_IP = $VMinfo.'IP Address'
$output_PowerState = $VMinfo.PowerState
$SQL_Property = "USE $output_Database
INSERT INTO [My_Database].[dbo].[VM_Info] (Date, vCenter, Cluster, VM_Name, IP_Address, PowerState)
VALUES('$Date', '$output_vCenter', '$output_Cluster', '$output_VMname', '$output_IP', '$output_PowerState');"
#echo $SQL_Property
$SQL_Property_Insert = Invoke-Sqlcmd -Query $SQL_Property -ServerInstance $output_Server -Username $output_Username -Password $output_Password
}
$VMinfo | OUT_SQL
While this gets the data into SQL, the formatting of the table is bad. So i tried the following
$SQL_Info = $VMinfo
$Connection = New-Object System.Data.SQLClient.SQLConnection
$Connection.ConnectionString = "server='$SQL_Server';database='$SQL_Database';user=$SQL_Username;password=$SQL_Password"
$Connection.Open()
$command = New-Object System.Data.SqlClient.SqlCommand
$command.connection = $Connection
for($r=0; $r -lt $SQL_Info.Length; $r++){
[datetime]$Date = (Get-Date).ToString($TimeTormat)
$vCenter = $SQL_Info[$r].vCenter
$Cluster = $SQL_Info[$r].Cluster
$VM_Name = $SQL_Info[$r].'VM Name'
$IP_Address = $SQL_Info[$r].'IP Address'
$PowerState = $SQL_Info[$r].PowerState
$SQL_Info_Query = "USE $SQL_Database
INSERT INTO [My_Database].[dbo].[VM_Info] (Date, vCenter, Cluster, VM_Name, IP_Address, PowerState)
VALUES('$Date', '$vCenter', '$Cluster', '$VM_Name', '$IP_Address', '$PowerState');"
$command.CommandText = $SQL_Info_Query
$command.ExecuteNonQuery()
}
The above just throws errors but, i think i need to break the array down row-by-row, treat each row as a PowerShell Custom Object and, add the properties of each object to the database, i.e Date, vCenter, Cluster, VM_Name, IP_Address & PowerState.
Has anyone done this or, able to offer any suggestions as to how if can complete it ?
Thanks
Did you already try passing the array row by row and declaring the parameters?
[CmdletBinding()]
param(
[string]$vCenter,
[string]$Cluster,
[string]$VMName,
[string]$IPAddr,
[string]$PowerState
)
$output_Server = "MySQLserver.domain.com"
$output_Database = "My_Database"
$output_Username = "myUsername"
$output_Password = "myPassword!"
$Date = Get-Date
$output_vCenter = $VMinfo.vCenter
$output_Cluster = $VMinfo.Cluster
$output_VMname = $VMinfo.'VM Name'
$output_IP = $VMinfo.'IP Address'
$output_PowerState = $VMinfo.PowerState
$SQL_Property = "USE $output_Database
INSERT INTO [My_Database].[dbo].[VM_Info] (Date, vCenter, Cluster, VM_Name, IP_Address, PowerState)
VALUES('$Date', '$vCenter', '$Cluster', '$VMName', '$IPAddr', '$PowerState');"
#echo $SQL_Property
$SQL_Property_Insert = Invoke-Sqlcmd -Query $SQL_Property -ServerInstance $output_Server -Username $output_Username -Password $output_Password
}
$VMinfo | ForEach-Object -process {
Out_SQL $_.vCenter $_.Cluster $_.'VM Name' $_.'IP Address' $_.PowerState
}
Blog: lucd.info Twitter: @LucD22 Co-author PowerCLI Reference
Did you already try passing the array row by row and declaring the parameters?
[CmdletBinding()]
param(
[string]$vCenter,
[string]$Cluster,
[string]$VMName,
[string]$IPAddr,
[string]$PowerState
)
$output_Server = "MySQLserver.domain.com"
$output_Database = "My_Database"
$output_Username = "myUsername"
$output_Password = "myPassword!"
$Date = Get-Date
$output_vCenter = $VMinfo.vCenter
$output_Cluster = $VMinfo.Cluster
$output_VMname = $VMinfo.'VM Name'
$output_IP = $VMinfo.'IP Address'
$output_PowerState = $VMinfo.PowerState
$SQL_Property = "USE $output_Database
INSERT INTO [My_Database].[dbo].[VM_Info] (Date, vCenter, Cluster, VM_Name, IP_Address, PowerState)
VALUES('$Date', '$vCenter', '$Cluster', '$VMName', '$IPAddr', '$PowerState');"
#echo $SQL_Property
$SQL_Property_Insert = Invoke-Sqlcmd -Query $SQL_Property -ServerInstance $output_Server -Username $output_Username -Password $output_Password
}
$VMinfo | ForEach-Object -process {
Out_SQL $_.vCenter $_.Cluster $_.'VM Name' $_.'IP Address' $_.PowerState
}
Blog: lucd.info Twitter: @LucD22 Co-author PowerCLI Reference
Thanks Luc,
I don't understand the mechanics of what you did but, it worked for me
Thank you
I used most of your code, but I feed the array row by row to SQL function.
And I defined parameters on the function.
Blog: lucd.info Twitter: @LucD22 Co-author PowerCLI Reference
Hi Luc,
one question to help my understanding if you don't mind.
in the Out-SQL Function,
$output_Date = $VMinfo.Date
$output_vCenter = $VMinfo.vCenter
$output_Cluster = $VMinfo.Cluster
Can they be removed from the function or, am i totally missing their purpose ?
No, you are right, I forgot to remove those.
Blog: lucd.info Twitter: @LucD22 Co-author PowerCLI Reference