functions/Show-SqlWhoIsActive.ps1

Function Show-SqlWhoIsActive
{
<#
.SYNOPSIS
Output results of Adam Machanic's sp_WhoIsActive to a GridView (default) or DataTable, and installs it if necessary.

.DESCRIPTION
GridView is good for analysis while DataTable is good for SqlBulkCopy uploads to keep track
    
Initially, there will be a simple output, but eventually, we plan to support passing params and specifying columns.
    
This script was built 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
"Beta" Builds: http://sqlblog.com/files/folders/beta/tags/who+is+active/default.aspx
    
Also, consider donating to Adam if you find this stored procedure helpful! http://tinyurl.com/WhoIsActiveDonate
    
.PARAMETER SqlServer
The SQL Server instance. You must have sysadmin access and server version must be SQL Server version 2000 or higher.

.PARAMETER Database
The database where sp_WhoIsActive is installed. Defaults to master. If the sp_WhoIsActive is not installed, it will install it for you.
    
.PARAMETER SqlCredential
Allows you to login to servers using SQL Logins as opposed to Windows Auth/Integrated/Trusted. To use:

$scred = Get-Credential, then pass $scred object to the -SqlCredential parameter.

Windows Authentication will be used if SqlCredential is not specified. SQL Server does not accept Windows credentials being passed as credentials. To connect as a different Windows user, run PowerShell as that user.

.NOTES
dbatools PowerShell module (https://dbatools.io, clemaire@gmail.com)
Copyright (C) 2016 Chrissy LeMaire

This program is free software: you can redistribute it and/or modify
it under the terms of the GNU General Public License as published by
the Free Software Foundation, either version 3 of the License, or
(at your option) any later version.

This program is distributed in the hope that it will be useful,
but WITHOUT ANY WARRANTY; without even the implied warranty of
MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
GNU General Public License for more details.

You should have received a copy of the GNU General Public License
along with this program. If not, see <http://www.gnu.org/licenses/>.

.LINK
https://dbatools.io/Show-SqlWhoIsActive

.EXAMPLE
Show-SqlWhoIsActive -SqlServer sqlserver2014a

More text coming soon
    
.EXAMPLE
Show-SqlWhoIsActive -SqlServer sqlserver2014a -SqlCredential $credential

More text coming soon
#>

    
    [CmdletBinding(SupportsShouldProcess = $true)]
    Param (
        [parameter(Mandatory = $true, ValueFromPipeline = $true)]
        [Alias("ServerInstance", "SqlInstance")]
        [object]$SqlServer,
        [object]$SqlCredential,
        [ValidateSet('Datatable', 'GridView')]
        #PsCustomObject needed? What else?
        [string]$OutputAs = 'GridView'
    )
    
    DynamicParam { if ($SqlServer) { return (Get-ParamSqlDatabase -SqlServer $SqlServer -SqlCredential $SourceSqlCredential) } }
    
    BEGIN
    {
        function Install-SpWhoisActive
        {
            if ($database.length -eq 0)
            {
                $database = Show-SqlDatabaseList -SqlServer $sourceserver -Title "Install sp_WhoisActive" -Header "Select a database. Adam installs it to master by default." -DefaultDb "master"
                
                if ($database.length -eq 0)
                {
                    throw "You must select a database to install the procedure"
                }
            }
            
            $parentPath = Split-Path -parent $PSScriptRoot
            $sql = [IO.File]::ReadAllText("$parentPath\sql\sp_WhoIsActive.sql")
            $sql = $sql -replace 'USE master', ''
            $batches = $sql -split "GO\r\n"
            
            foreach ($batch in $batches)
            {
                try
                {
                    
                    $null = $sourceserver.databases[$database].ExecuteNonQuery($batch)
                    
                }
                catch
                {
                    Write-Exception $_
                    throw "Can't install stored procedure. See exception text for details."
                }
            }
            
            return $database
        }
        
        $sourceserver = Connect-SqlServer -SqlServer $sqlserver -SqlCredential $SqlCredential
        $source = $sourceserver.DomainInstanceName
        
        if ($sourceserver.VersionMajor -lt 9)
        {
            throw "sp_WhoIsActive is only supported in SQL Server 2005 and above"
        }
        
        $database = $psboundparameters.Database
    }
    
    PROCESS
    {
        # Will build more on this later and do some parameterization
        $sql = "dbo.sp_WhoIsActive"
        
        try
        {
            if ($database.length -eq 0)
            {
                $datatable = $sourceserver.databases["master"].ExecuteWithResults($sql)
            }
            else
            {
                $datatable = $sourceserver.databases[$database].ExecuteWithResults($sql)
            }
        }
        catch
        {
            Write-Output "Procedure not found, installing."
            $database = Install-SpWhoisActive
            Write-Warning $database
            try
            {
                $datatable = $sourceserver.databases[$database].ExecuteWithResults($sql)
            }
            catch
            {
                Write-Exception $_
                throw "Cannot execute procedure."
            }
        }
    }
    
    END
    {
        $sourceserver.ConnectionContext.Disconnect()
        
        if ($OutputAs -eq "DataTable")
        {
            return $datatable
        }
        else
        {
            $datatable.Tables.Rows | Out-GridView
        }
    }
}