Powershell script to query SQL requests

This powershell scripts queries an SQL table for all open VM request and starts another powershell scripts for provision the requested VM(s)

ps: please note : I’m no Powershell guru and don’t have much powershell experience yet. These scripts is not nice and clean but it does the job…
I’m no powershell script guru , so this script will hurt the real Powershell guys but I decided to post it anyway.

cls

function checksnapin(){
#
#
# Add in Snapins Before launching Script. I’ve written this, so you could run a pile of these checks
# and until it’s all ‘GOOD’ the script will quickly fail out.
#
#
# Variable to remember the $STATUS of your modules loaded
$STATUSLOADED=$FALSE 

# The name of whatever snapin being dealt with
$SNAPIN='VMware.VimAutomation.Core' 

# Try to add the Snapin and be VEWY QWIET if it DOESN’T load ;-)
#
ADD-PSSNAPIN $SNAPIN –erroraction SilentlyContinue
#
# Check to see if it successfully loaded
#
IF ((GET-PSSNAPIN $SNAPIN) –eq $NULL)
{
# If not loaded – Notify user with required details
#
write-host "This script required POWERCLI from vmware"

}
ELSE
{
# If it DID, Flag Status as GOOD
#
$STATUSLOADED=$TRUE
}
# And we treat the WHOLE script as an “IF” to make it work only “IF”
# the require modules have been loaded!
#
}

checksnapin

$SQLSERVER = "sqlbeheerdb"
$DATABASE = "beheer"
$afgehandeld = "JA"

$SqlQuery = "select vmnaam, "
$SqlQuery = $SqlQuery + "vmfunctie, TypeVM, [vmlogin],[naam],[email],[os],[iscsi],[numbit],[memory_MB],[harddisk_GB],[numcpu],[monNagios],[monMOM],[gegrespid],[secrespid],[commentaar],[firmaversie],[afgehandeld] "
$SqlQuery = $SqlQuery + "from [beheer].[uz].[parkbeheer_vmAanvraag] "
$SqlQuery = $SqlQuery + "where not [afgehandeld] = '$afgehandeld' and auto = 'ja'"

$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Server=$SQLSERVER;Database=$DATABASE;Integrated Security=SSPI;"
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.CommandText = $SqlQuery

$SqlCmd.Connection = $SqlConnection
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $SqlCmd
$DataSet = New-Object System.Data.DataSet
$nSet = $SqlAdapter.Fill($DataSet)
$OutputTable = $DataSet.Tables[0]

$OutputTable | ft -autosize
$vc = "vCenter5"
Connect-VIServer $vc

$FileToExecute = "$pshome\powershell.exe"
ForEach ($Row In $OutputTable)
{
$server= $Row.vmnaam
$function = $Row.vmfunctie
$OS = $Row.OS
$TypeVM = $Row.TypeVM
$login = $Row.vmlogin
write-Host " $server - $Function - $OS - $TypeVM"
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Server=$SQLSERVER;Database=$DATABASE;Integrated Security=SSPI;"
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlConnection.Open()

$SqlCmd.Connection = $SqlConnection
$SqlQuery2 = "UPDATE [beheer].[uz].[parkbeheer_vmAanvraag] SET [afgehandeld] = 'STARTED' where vmnaam = '$server' and auto = 'ja'"
$SqlCmd.CommandText = $SqlQuery2
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $SqlCmd
$SqlCmd.ExecuteNonQuery()

# example of this command : .\uzlvmRequestVM.ps1 -MyVM "erikschils" -requestType "Crosscluster"-requestOS "2003"

Start-Sleep -s 5
$powershellscript = "\\filserver\data\software\PS\Admin\Scripts\VMWARE\vmaanvraag\uzlvmRequestVM.ps1"
# put "-noexit" parameter for easier debug
Start-Process -FilePath c:\Windows\System32\WindowsPowerShell\v1.0\powershell.exe -ArgumentList $powershellscript,"-noexit","-MyVM",$server,"-requestType",$TypeVM,"-requestOS",$OS,"-login",$login
}
$SqlConnection.Close();

# SIG # Begin signature block