cancel
Showing results for 
Search instead for 
Did you mean: 
cancel

UCSD LOV Creation from SQL / Powershell

1177
Views
0
Helpful
0
Comments
Task NameLOV creation from SQL / Powershell
Description
  1. UCSD LOV creation Example
Prerequisites
  1. Tested on 5.3.1
CategoryWorkflow
ComponentsvSphere 5.x
User Inputs
  1. userid to change to
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

Unknown-1.png

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,

Unknown.png

Content for Community-Ad
This widget could not be displayed.