| Task Name | LOV creation from SQL / Powershell |
| Description |
|
| Prerequisites |
|
| Category | Workflow |
| Components | vSphere 5.x |
| User Inputs |
|
| Output |
A big thank you goes out to Jeremiah Jung
Instructions for Regular Workflow Use:
This is a different take of #195 from the community site
UCSD LOV Creation from CSV File
The workflow
The Powershell Task
#Stuff that you should edit to match your settings
$SQLserverName = "localhost"
$databaseName = "CM_KEP"
$query = 'select "CollectionID","ServiceWindowID","Name","Enabled" from "dbo"."CEP_ServiceWindows" as "CEP_ServiceWindows"'
#Stuff shamelessly stolen from the internet (provided by Martin9700 @ http://community.spiceworks.com/topic/388926-sql-query-with-powershell)
$dataAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$connString = "Server=$SQLserverName;Database=$databaseName;Integrated Security=SSPI;"
$dataAdapter.SelectCommand = new-object System.Data.SqlClient.SqlCommand ($query,$connString)
$commandBuilder = new-object System.Data.SqlClient.SqlCommandBuilder $dataAdapter
$dt = New-Object System.Data.DataTable
$dataAdapter.fill($dt) | Out-Null
#Taking the returned data and formatting it to fit the LOV creation task – This will have to match whatever your SQL query returns
$dt = $dt | sort name
$patch_schedules = ""
ForEach($schedule in $dt){
if ($schedule.enabled -eq "True") {continue}
$patch_schedules = ($patch_schedules + $schedule.name + "=" + $schedule.CollectionID + ",")
}
$patch_schedules
Output:
Prod - EXCH - 4th Fri - 12AM-1AM=16777690,Prod - EXCH - 4th Thur - 10PM-11PM=16777688,Prod - EXCH - 4th Thur - 11PM-12AM=16777689,Prod - EXCH - 4th Wed - 10PM-11PM=16777691,Prod - EXCH (Excl. EAS) - 4th Thur - 10PM-11PM=16777693,Prod - EXCH (Excl. EAS) - 4th Thur - 11PM-12AM=16777692,Prod - General - 4th Fri - 12AM-1AM=16777804,Prod - General - 4th Fri - 1AM-2AM=16777805,Prod - General - 4th Fri - 2AM-3AM=16777806,Prod - General - 4th Fri - 3AM-4AM=16777807,Prod - General - 4th Thur - 10PM-11PM=16777657,Prod - General - 4th Thur - 11PM-12AM=16777658,Prod - General - 4th Thur - 12AM-1AM=16777659,Prod - General - 4th Thur - 1AM-2AM=16777660,Prod - General - 4th Thur - 2AM-3AM=16777661,Prod - General - 4th Thur - 3AM-4AM=16777662,Prod - General - 4th Tue - 10PM-11PM=16777645,Prod - General - 4th Tue - 11PM-12AM=16777646,Prod - General - 4th Tue - 12AM-1AM=16777648,Prod - General - 4th Tue - 1AM-2AM=16777649,Prod - General - 4th Tue - 2AM-3AM=16777650,Prod - General - 4th Tue - 3AM-4AM=16777651,Prod - General - 4th Wed - 10PM-11PM=16777647,Prod - General - 4th Wed - 11PM-12AM=16777652,Prod - General - 4th Wed - 12AM-1AM=16777653,Prod - General - 4th Wed - 1AM-2AM=16777654,Prod - General - 4th Wed - 2AM-3AM=16777655,Prod - General - 4th Wed - 3AM-4AM=16777656,Prod - SharePoint - 4th Tue - 12AM-1AM=16777696,Test - EXTEST - 3rd Thur - 12AM-1AM=16777686,Test - EXTEST - 3rd Wed - 10PM-11PM=16777685,Test - EXTEST - NR - 2nd Wed - 10PM-11PM=16777742,Test - General - 3rd Thur - 12AM-1AM=16777684,Test - General - 3rd Thur - 1AM-2AM=16777672,Test - General - 3rd Thur - 2AM-3AM=16777681,Test - General - 3rd Thur - 3AM-4AM=16777682,Test - General - 3rd Wed - 10PM-11PM=16777683,Test - General - 3rd Wed - 11PM-12AM=16777671,Test - SharePoint - 3rd Thur - 12AM-1AM=16777687,
