PowerShell и другие скрипты

Всем привет!

Спасибо всем кто не отписался от моего блога несмотря на отсутствие новых постов в последнее время. У меня для вас есть кое что вкусное

Я думаю большинству читателей моего блога, так или иначе приходится сталкиваться и взаимодействовать с серверами MSSQL, ведь большинстов современных серверных продуктов используют их для хранения данных. И я думаю что вам, так же как и мне, часто хотелось автоматизировать взаимодействие с этими хранилищами данных с помощью PowerShell. И Microsoft даже пошла нам на встречу выпустив SQL Server 2008 с интеграцией PowerShell. Это позволило нам автоматизировать настройку серверов SQL, но возможностей работы с данными содержащимися в базах к сожалению не прибавило. То же относится к недавно выпущенному MSSQL 2008 R2. А ведь казалось бы, нам нужно совсем немного – хотя бы простой командлет который смог бы принимать на вход SQL запрос, и возвращать данные не в текстовом представлении, как это делают osql.exe или isql.exe, а в виде объектов, со свойствами – столбцами. Аналогичный командлет на самом деле уже написала компания SoftwareFX, но он поставляется в составе их платного продукта PowerGadgets, который хоть и хорош сам по себе, но покупать его только для того чтобы удобно выполнять SQL запросы из PowerShell – просто нецелесообразно. Поэтому я решил написать собственный командлет (advanced function), с таким функционалом, являющийся на самом деле обёрткой над .NET классами для работы с SQL.

Вот несколько примеров его использования:

PS C:\> Invoke-SQLCommand -database sms_lab 'select collectionname,collectionid from dbo.collections'

collectionname                                  collectionid
--------------                                  ------------
All Active Directory Security Groups                       6
All Desktops and Servers                                  19
All Systems                                                2
All Unknown Computers                                     24
All User Groups                                            4
All Users                                                  3
All Windows 2000 Professional Systems                     14
All Windows 2000 Server Systems                           15
All Windows Mobile Devices                                18
All Windows Mobile Pocket PC 2003 Devices                 20
All Windows Mobile Pocket PC 5.0 Devices                  21
All Windows Mobile Smartphone 2003 Devices                22
All Windows Mobile Smartphone 5.0 Devices                 23
All Windows Server 2003 Systems                           17
All Windows Server Systems                                13
All Windows Workstation or Professional Systems            5
All Windows XP Systems                                    16
Root Collection                                            1

PS C:\> $Reports = Invoke-SQLCommand -database sms_lab `
>> -command 'select reportid,name,category,sqlquery from dbo.report'
>>

PS C:\> $Reports | where {$_.category -eq 'Operating System'} |
>> Format-Table reportid, name, category -AutoSize
>>

reportid name                                                        category
-------- ----                                                        --------
      38 Computer operating system version history                   Operating System
      39 Services - Computers running Remote Access Server           Operating System
      40 Services - Computers running a specific service             Operating System
      41 Services - Services information for a specific computer     Operating System
      42 Windows Server computers                                    Operating System
     100 Computers with a specific operating system and service pack Operating System
     101 Computers with a specific operating system                  Operating System
     102 Count operating systems and service packs                   Operating System
     103 Count operating system versions                             Operating System

Кроме простых запросов можно выполнять и другие операции, например изменение или удаление записей. В таком случае в качестве результата возвращается количество измененных записей.

PS C:\> Invoke-SQLCommand -server sccm -database sms_lab `
>> -Command "update dbo.smspackages set description='Updated from PowerShell' where Name='Test Package'"
>>
1

Кроме того, так как эта команда позволяет изменять данные, я реализовал и поддержку ключей –whatif и –confirm.

Ну и наконец, исходный код, с кучей комментариев

function Invoke-SQLCommand
{
    #Объявление о том что мы будем использовать возможности Advanced 
    #Functions, в частности несколько наборов параметров, и поддержку
    #ключей -WhatIf -Confirm -Verbose (ShouldProcess)
    [CmdletBinding(
    SupportsShouldProcess=$True,
    ConfirmImpact="Low",
    DefaultParameterSetName="Default")]            

    Param
    (
        #Команда SQL. Обязательный параметр присутствующий во всех наборах 
        #параметров, по умолчанию находится на первом месте, и принимает
        #значения из конвейера. Тип - массив строк.
        [Parameter(Mandatory=$True, Position=1, ValueFromPipeLine=$True)]
        [String[]]$Command,            

        #Имя сервера SQL. Присутствует только в наборе параметров Default.
        #По умолчанию равен имени текущего компьютера.
        [Parameter(Position=2, ParameterSetName="Default")]
        [String]$Server = $Env:computername,            

        #База данных. По умолчанию - "master".
        [Parameter(Position=3, ParameterSetName="Default")]
        [String]$Database = "master",            

        #Надо ли отключить интегрированную аутентикацию и использовать
        #родную SQL (менее безопасно). Тип - switch (наличие параметра 
        #означает $true.
        [Parameter(ParameterSetName="Default")]
        [switch]$SQLAuthentication,            

        #Учетные данные для подключения, тип - PSCredential.
        #Можно получить выполнив командлет Get-Credential
        [Parameter(ParameterSetName="Default")]
        [System.Management.Automation.PSCredential]$Credential,            

        #Строка для подключения сервера. Позволяет указать имя сервера,
        #базы, параметры аутентикации, и прочее, вручную. Принадлежит 
        #набору параметров ConnectionString.
        [Parameter(Mandatory=$True,
        Position=2,
        ParameterSetName="ConnectionString")]
        [String]$ConnectionString            

    )            

    #Блок BEGIN выполняется 1 раз, в начале выполнения командлета, до
    #начала обработки данных из конвейера. Обычно здесь происходит инициализация.
    BEGIN
    {
        #Создаем объект подключения к SQL
        $Connection = New-Object System.Data.SQLClient.SQLConnection            

        #Если использовался набор параметров Default, то формируем
        #строку подключения самостоятельно.
        if ($PSCmdlet.ParameterSetName -eq "Default")
        {
            $Trusted = if ($SQLAuthentication) {"False"} else {"True"}
            $String = "Server=$Server;Database=$Database;Trusted_Connection=$Trusted;"            

            #Если указан параметр $Credential, то вытаскиваем из него имя и пароль
            #и помещаем в строку подключения.
            if ($Credential)
            {
                #При необходимости удаляем \ в начале строки.
                $String += "User ID=$($Credential.UserName -replace '^\\');"
                $String += "Password=$($Credential.GetNetworkCredential().password);"
            }
            #Присваиваем строку объекту подключения.
            $Connection.ConnectionString = $String
        }
        else
        {
            #Присваиваем строку объекту подключения.
            $Connection.ConnectionString = $ConnectionString
        }
        #Выводим отладочную информацию.
        Write-Verbose ("ConnectionString is: " + $Connection.ConnectionString)
        #Открываем соединение. Если не закрыть его методом .Close() то оно
        #будет закрыто автоматически по таймауту (15 мин по умолчанию).
        $Connection.Open()
        #Если не получилось открыть соединение - вываливаем ошибку.
        if ($Connection.State -ne "Open")
        {
            Throw "Unable to open SQL connection"
        }
        #Создаем строку $Commands. Она будет содержать полный текст сценария SQL
        #если по конвейеру будет передано несколько команд.
        $Commands = ""
    }            

    #Блок PROCESS выполняется по разу для каждого объекта полученного по конвейеру
    #или для каждого элемента массива $Command если он был передан как аргумент.
    #Если как аргумент была передана одна строка, то блок выполняется 1 раз.
    #Обычно в этом блоке происходит непосредственная обработка и вывод данных, но 
    #в данном случае он нужен лишь для склейки строк из конвейера в одну.
    PROCESS
    {
        #Прицепляем к $Commands содержимое элемента $Command и перенос строки.
        $Commands += "$Command`n"
    }            

    #Блок END Выполняется 1 раз, в конце работы командлета, когда все элементы 
    #переданные по конвейеру уже были обработаны в блоке PROCESS.
    #В этом блоке принято производить удаление временных данных, завершение сессий
    #и т.п. Однако в данном командлете здесь выполняется непосредственно команда SQL,
    #а уже затем происходит завершение сессии SQL.
    END
    {
        #Конструкция Try, Catch, Finally нужна для обработки ошибок. Если внутри try
        #произойдет ошибка, то она будет перехвачена, и управление будет передано 
        #блоку Catch. Finally выполняется всегда, независимо от того произошла ошибка
        #или нет - соединение к SQL нам надо закрыть в любом случае.
        try
        {
            #Это условие единственное что необходимо для реализации стандартных ключей
            #-WhatIf, -Confirm и -Verbose. Как аргумент для ShouldProcess передается
            #текущий элемент (команда SQL). Это имя будет использоваться для вывода 
            #информации при использовании аргументов. Внутри блока If находится код 
            #который будет выполнен в том случае если не указан ключ -WhatIf. Если же
            #указан ключ -Confirm, то выполнение этого кода будет зависеть от ответа
            #пользователя.
            if ($PSCmdlet.ShouldProcess($Commands.Trim()))
            {
                #Создаем объект SQLCommand, и присваиваем ему текст команды и объект
                #соединения.
                $SqlCommand = New-Object System.Data.SQLClient.SQLCommand
                $SqlCommand.Connection = $Connection
                $SqlCommand.CommandText = $Commands
                #Выполняем команду, и получаем объект Reader, который будем использовать
                #для чтения результатов.
                $Reader = $SQLCommand.ExecuteReader()            

                #Если в результатах есть строки, то выводим их содержимое.
                If ($Reader.HasRows)
                {
                    #Инициализируем переменную для подсчета строк.
                    $RowsCount = 0
                    #Смотрим количество возвращенных столбцов.
                    $FieldCount = $Reader.FieldCount
                    #Пока есть необработанные данные, читаем их.
                    while ($Reader.Read())
                    {
                        #Увеличиваем счетчик строк.
                        $RowsCount ++
                        #Создаем "пустой" объект.
                        $Obj = New-Object psobject
                        #Для каждого столбца...
                        for ($i=0; $i -lt $FieldCount; $i++)
                        {
                            #Добавляем к объекту свойство с именем столбца, где 
                            #значение - значение столбца.
                            $Obj | Add-Member -MemberType NoteProperty `
                            -Name ($Reader.GetName($i)) -Value ($Reader.GetValue($i))
                        }
                        #Выводим получившийся объект.
                        Write-Output $Obj
                    }
                }            

                #Если были затронуты (созданы/изменены/удалены) записи, то выводим
                #их количество.
                If ($Reader.RecordsAffected -gt 0)
                {
                    Write-Output $Reader.RecordsAffected
                }
            }
        }
        Catch
        {
            #Если произошла ошибка, то достаем и выводим её текст, плюс помещаем
            #оригинальный объект исключения в поле Exception.
            Write-Error -Message ($_.exception.innerexception.message) `
            -Exception $_.Exception
        }
        Finally
        {
            #Закрываем соединение с SQL.
            $Connection.Close()
        }
    }
}            

Напоследок небольшое замечание. Помните что MSSQL был создан для быстрой обработки данных, а PowerShell – для быстрого написания сценариев и удобной интерактивной работы. Поэтому старайтесь максимум обработки оставить на стороне MSSQL – он справится гораздо быстрее.