Functions/Get-SdtWhoIsActive.ps1

Function Get-SdtWhoIsActive
{
    [CmdletBinding()]
    Param(
        [Parameter(ValueFromPipeline=$true,
                   ValueFromPipelineByPropertyName=$true)]
        [Alias('ServerName','SqlInstance')]
        [String[]]$ServerInstance,

        [String]$Database = 'master'        

        ,[Switch]$ResultToExcel = $false
    )
    BEGIN 
    {
        $Servers = @();
        $RunningQueries = @();
        $sqlQuery = @"
exec sp_whoIsActive @get_full_inner_text=1,
                    @get_transaction_info=1, @get_task_info=2,
                    @get_locks=1, @get_avg_time=1, @get_additional_info=1,
                    @find_block_leaders=1
"@
;
    }
    PROCESS
    {
        foreach($InstanceName in $ServerInstance)
        {
            $RunningQueries += Invoke-Sqlcmd -ServerInstance $InstanceName  -Database $Database -Query $sqlQuery |
                                    Add-Member -NotePropertyName ServerInstance -NotePropertyValue $InstanceName -PassThru;
            $Servers += $InstanceName;
        }
    }
    END
    {
        $ServerCommaSeparated = $Servers -join ', ';
        $ExcelSheet = "C:\Temp\WhoIsActive_Result_$ServerCommaSeparated_$(Get-Date -Format 'yyyy-MM-dd HHmm').xlsx";

        if($ResultToExcel) {
            Write-Host "Generating excel file=> $ExcelSheet" -ForegroundColor Green;
            $RunningQueries | Export-Excel -Path $ExcelSheet;
        }
        else {
            $RunningQueries | Out-GridView -Title "WhoIsActive on $ServerCommaSeparated";
        }
    }
<#
        .SYNOPSIS
            Execute sp_WhoIsActive procedure and displays the result either in gridview or excel
 
        .DESCRIPTION
            Execute sp_WhoIsActive procedure and displays the result either in gridview or excel
 
        .NOTES
            Name: Get-SdtWhoIsActive
            Author: Ajay Dwivedi
 
        .EXAMPLE
            Get-SdtWhoIsActive -ServerInstance ServerName
 
            Description
            -----------
            Executes sp_WhoIsActive procedure and displays the result either in gridview or excel
 
        .EXAMPLE
            Get-SdtWhoIsActive -ServerInstance ServerName -ResultToExcel
 
            Description
            -----------
            Executes sp_WhoIsActive procedure and displays the result either in gridview or excel
 
        .LINK
            http://ajaydwivedi.com
#>

}