UCSD LOV Creation from SQL / Powershell

Version 1
    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