functions/Install-DbaWhoIsActive.ps1

function Install-DbaWhoIsActive {
<#
    .SYNOPSIS
        Automatically installs or updates sp_WhoisActive by Adam Machanic.
     
    .DESCRIPTION
        This command downloads, extracts and installs sp_WhoisActive with Adam's permission. To read more about sp_WhoisActive, please visit:
         
        Updates: http://sqlblog.com/blogs/adam_machanic/archive/tags/who+is+active/default.aspx
         
        Also, consider donating to Adam if you find this stored procedure helpful: http://tinyurl.com/WhoIsActiveDonate
         
        Note that you will be prompted a bunch of times to confirm an action. To disable this behavior, pass the -Confirm:$false parameter (see example below).
     
    .PARAMETER SqlInstance
        The SQL Server instance.You must have sysadmin access and server version must be SQL Server version 2000 or higher.
     
    .PARAMETER SqlCredential
        PSCredential object to connect under. If not specified, currend Windows login will be used.
     
    .PARAMETER Database
        The database to install the procedures to.
        This parameter is mandatory when executing this command unattendedly.
     
    .PARAMETER Update
        Looks online for the most up to date version, replacing the local one.
     
    .PARAMETER Silent
        Replaces user friendly yellow warnings with bloody red exceptions of doom!
        Use this if you want the function to throw terminating errors you want to catch.
     
    .PARAMETER WhatIf
        Shows what would happen if the command were to run. No actions are actually performed.
     
    .PARAMETER Confirm
        Prompts you for confirmation before executing any changing operations within the command.
     
    .EXAMPLE
        Install-DbaWhoIsActive -SqlInstance sqlserver2014a -Database master
         
        Installs sp_WhoisActive to sqlserver2014a's master database. Logs in using Windows Authentication.
     
    .EXAMPLE
        Install-DbaWhoIsActive -SqlInstance sqlserver2014a -SqlCredential $cred
         
        Pops up a dialog box asking which database on sqlserver2014a you want to install the proc to. Logs into SQL Server using SQL Authentication.
         
     
    .EXAMPLE
        Install-DbaWhoIsActive -SqlInstance sqlserver2014a -Database master -Update
         
        Installs sp_WhoisActive to sqlserver2014a's master database. Forces a retrieval of the script from internet
     
    .EXAMPLE
        $instances = Get-DbaRegisteredServerName sqlserver
        Install-DbaWhoIsActive -SqlInstance $instances -Database master
         
     
    .NOTES
        Website: https://dbatools.io
        Copyright: (C) Chrissy LeMaire, clemaire@gmail.com
        License: GNU GPL v3 https://opensource.org/licenses/GPL-3.0
     
    .LINK
        https://dbatools.io/Install-DbaWhoIsActive
#>

    
    [CmdletBinding(SupportsShouldProcess)]
    param (
        [parameter(Mandatory = $true, ValueFromPipeline = $true, Position = 0)]
        [Alias("ServerInstance", "SqlServer")]
        [DbaInstanceParameter[]]$SqlInstance,
        [PsCredential]$SqlCredential,
        [object]$Database,
        [switch]$Update,
        [switch]$Silent
    )
    
    begin {
        
        $version = "who_is_active_v11_17"
        $temp = ([System.IO.Path]::GetTempPath()).TrimEnd("\")
        $sqlfile = (Get-ChildItem "$temp\$version.sql" -ErrorAction SilentlyContinue | Select-Object -First 1).FullName
        
        if ($sqlfile -and (-not ($Update))) {
            Write-Message -Level Verbose -Message "Found local $sqlfile"
        }
        else {
            if ($PSCmdlet.ShouldProcess($env:computername, "Downloading sp_WhoisActive")) {
                try {
                    Write-Message -Level Verbose -Message "Downloading sp_WhoisActive zip file, unzipping and installing."
                    
                    $url = "http://whoisactive.com/downloads/$version.zip"
                    $temp = ([System.IO.Path]::GetTempPath()).TrimEnd("\")
                    $zipfile = "$temp\spwhoisactive.zip"
                    
                    try {
                        Invoke-WebRequest $url -OutFile $zipfile -ErrorAction Stop
                    }
                    catch {
                        #try with default proxy and usersettings
                        (New-Object System.Net.WebClient).Proxy.Credentials = [System.Net.CredentialCache]::DefaultNetworkCredentials
                        Invoke-WebRequest $url -OutFile $zipfile -ErrorAction Stop
                    }
                    
                    # Unblock if there's a block
                    Unblock-File $zipfile -ErrorAction SilentlyContinue
                    
                    # Keep it backwards compatible
                    $shell = New-Object -ComObject Shell.Application
                    $zipPackage = $shell.NameSpace($zipfile)
                    $destinationFolder = $shell.NameSpace($temp)
                    
                    Get-ChildItem "$temp\who*active*.sql" | Remove-Item
                    
                    $destinationFolder.CopyHere($zipPackage.Items())
                    
                    Remove-Item -Path $zipfile
                    
                    $sqlfile = (Get-ChildItem "$temp\who*active*.sql" | Select-Object -First 1).FullName
                }
                catch {
                    Stop-Function -Message "Couldn't download sp_WhoisActive. Please download and install manually from http://whoisactive.com/who_is_active_v11_17.zip." -ErrorRecord $_
                    return
                }
            }
        }
        
        if ($PSCmdlet.ShouldProcess($env:computername, "Reading SQL file into memory")) {
            Write-Message -Level Verbose -Message "Using $sqlfile"
            
            $sql = [IO.File]::ReadAllText($sqlfile)
            $sql = $sql -replace 'USE master', ''
            $batches = $sql -split "GO\r\n"
        }
    }
    
    process {
        if (Test-FunctionInterrupt) { return }
        
        foreach ($instance in $SqlInstance) {
            try {
                Write-Message -Level Verbose -Message "Connecting to $instance"
                $server = Connect-SqlInstance -SqlInstance $instance -SqlCredential $sqlcredential
            }
            catch {
                Stop-Function -Message "Failure" -Category ConnectionError -ErrorRecord $_ -Target $instance -Continue
            }
            
            if (-not $Database) {
                if ($PSCmdlet.ShouldProcess($instance, "Prompting with GUI list of databases")) {
                    $Database = Show-DbaDatabaseList -SqlInstance $server -Title "Install sp_WhoisActive" -Header "To deploy sp_WhoisActive, select a database or hit cancel to quit." -DefaultDb "master"
                    
                    if (-not $Database) {
                        Stop-Function -Message "You must select a database to install the procedure" -Target $Database
                        return
                    }
                    
                    if ($Database -ne 'master') {
                        Write-Message -Level Warning -Message "You have selected a database other than master. When you run Invoke-DbaWhoIsActive in the future, you must specify -Database $Database"
                    }
                }
            }
            
            if ($PSCmdlet.ShouldProcess($instance, "Installing sp_WhoisActive")) {
                $allprocedures_query = "select name from sys.procedures where is_ms_shipped = 0"
                $databases = $server.Databases | Where-Object Name -eq $Database
                if ($databases.Count -eq 0) {
                    Stop-Function -Message "Failed to find database $Database on $instance" -ErrorRecord $_ -Continue -Target $instance
                }
                $allprocedures = ($server.Query($allprocedures_query, $Database)).Name
                foreach ($batch in $batches) {
                    try {
                        $null = $server.databases[$Database].ExecuteNonQuery($batch)
                    }
                    catch {
                        Stop-Function -Message "Failed to install stored procedure." -ErrorRecord $_ -Continue -Target $instance
                    }
                }
                $baseres = @{
                    ComputerName = $server.NetName
                    InstanceName = $server.ServiceName
                    SqlInstance = $server.DomainInstanceName
                    Database = $Database
                    Name = 'sp_WhoisActive'
                }
                if ('sp_WhoisActive' -in $allprocedures) {
                    $status = 'Updated'
                }
                else {
                    $status = 'Installed'
                }
                [PSCustomObject]@{
                    ComputerName = $server.NetName
                    InstanceName = $server.ServiceName
                    SqlInstance = $server.DomainInstanceName
                    Database = $Database
                    Name = 'sp_WhoisActive'
                    Status = $status
                }
            }
        }
    }
    end {
        Test-DbaDeprecation -DeprecatedOn "1.0.0" -Silent:$false -Alias Install-SqlWhoIsActive
    }
}