First off, I'm new to stack. I have referenced stack many times in the past, but recently I have been stuck on this issue for quite sometime. So here goes.
My goal: I am attempting to correlate an array output from VMware that matches a custom value on each VM machine. ( an asset ID ) to a value ( ID Key ) on a microsoft SQL 2000 server.
As such, since this server is pre 2005 I am unable to use the invoke-sqlcmd powershell command. I have to utilize the full SQL connection string and command structure to return a value out of this database. This sql statement and script works fine on its own. Meaning that the sql portion of this script, functioning on its own will pull results out of the database with a manual tag number put in place of my variable "$etag". I'm fairly new to powershell, and sql use from powershell.
So here is my script with names of the protected taken out.
#========================================================================
# Created on: 12/4/2013 2:01 PM
# Created by: Shaun Belcher
# Filename:
#========================================================================
function get-inventory
{
Add-PSSnapin VMware.VimAutomation.Core
$date=get-date
$vcenterserver = @("srv-1","srv-2","srv-3")
Connect-VIServer -server $vcenterserver
$toAddr="[email protected]"
$fromAddr="[email protected]"
$smtpsrv="mail.domain.com"
#Variables
$mdesks=@()
$sqlServer = "serverdb"
$sqlDBNAME = "instance"
$sqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$sqlConnection = New-Object System.Data.SqlClient.SqlConnection
$DataSet = New-Object System.Data.DataSet
$sqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.connection = $sqlConnection
$sqlAdapter.SelectCommand = $sqlCmd
#db Connection
$sqlConnection.ConnectionString = "Server = $sqlServer; Database = $sqlDBname; Integrated Security=True;"
$SqlCmd.connection = $SqlConnection
$SqlCmd.commandtext = $sqlQuery
$sqlAdapter.SelectCommand = $sqlCmd
$sqlQuery += "SELECT INVHARDW_PropTag as proptag, invhardw_clientID as ClientID, invhardw_notes as Notes FROM INV_Hardware where invhardw_proptag = '$etag';"
$SqlCmd.commandtext = $sqlQuery
$sqlAdapter.SelectCommand = $sqlCmd
$sqlAdapter.Fill($DataSet)
$DataSet.Tables[0]
$sqlConnection.Close()
$mdesks = @($DataSet.Tables[0] | select propTag, ClientID, Notes)
$virtuals= @(Get-VM | select Name,vmhost,memoryMB,@{N="Datastore";E={[string]::Join(',',(Get-Datastore -Id $_.DatastoreIdList | Select -ExpandProperty Name))}})
$etags = @(Get-vm | Get-Annotation |select value,@{N="mDeskNote";E={[string]::Join(',',($mdesk | Where-Object {$mdesks.propTag = $_;}))}},@{N="mDeskClientID";E={[string]::Join(',',($mdesk | Where-Object {$mdesks.propTag = $_;}))}})
if($virtuals -ne $null){
$body = @("
<center><table border=1 width=50 % cellspacing=0 cellpadding=8 bgcolor=Black cols=3>
<tr bgcolor=White><td>Virtual Machine</td><td>Host Machine</td><td>Memory Allocated</td><td>DatastoreList</td><td>Asset Tag</td><td>App Note</td><td>App Client ID</td></tr>")
$i = 0
do {
#if($i % 2){$body += "<tr bgcolor=#D2CFCF><td>$($virtuals[$i].Name)</td></tr>";$i++}
#else {$body += "<tr bgcolor=#EFEFEF><td>$($virtuals[$i].Name)</td></tr>";$i++}
if($i % 2){$body += "<tr bgcolor=#D2CFCF><td>$($virtuals[$i].Name)</td><td>$($virtuals[$i].VMHost)</td><td>$($virtuals[$i].MemorymB)</td><td>$($virtuals[$i].datastore)</td><td>$($etags[$i].value)</td><td>$mdesks[$i].notes</td><td>$mdesks[$i].ClientID</td></tr>";$i++}
else {$body += "<tr bgcolor=#EFEFEF><td>$($virtuals[$i].Name)</td><td>$($virtuals[$i].VMHost)</td><td>$($virtuals[$i].memorymb)</td><td>$($virtuals[$i].datastore)</td><td>$($etags[$i].value)</td><td>$mdesks[$i].notes</td><td>$mdesks[$i].ClientID</td></tr>";$i++}
}
while ($virtuals[$i] -ne $null)
$body += "</table></center>"
# Send email.
if($attachmentPref){
$virtuals | Export-CSV "Inventory $($date.month)-$($date.day)-$($date.year).csv"
Send-MailMessage -To "$toAddr" -From "$fromAddr" -Subject "$vcenterserver Inventory = $countvms" -Body "$body" -Attachments "Inventory $($date.month)-$($date.day)-$($date.year).csv" -SmtpServer "$smtpsrv" -BodyAsHtml
Remove-Item "Inventory $($date.month)-$($date.day)-$($date.year).csv"
}
Else{
Send-MailMessage -To "$toAddr" -From "$fromAddr" -Subject "Inventory $vcenterserver = $countvms" -Body "$body" -SmtpServer "$smtpsrv" -BodyAsHtml
}
}
Disconnect-VIServer -Server $vcenterserver -Confirm:$false exit
get-inventory
This returns the information and sends it in an email with columns and rows of the information. Again, these are two working scripts that just do not return the result that is sought after.
