functions/Get-DbaProcess.ps1

Function Get-DbaProcess
{
<#
.SYNOPSIS
This command displays SQL Server processes.
 
.DESCRIPTION
This command displays processes associated with a spid, login, host, program or database.
 
.PARAMETER SqlServer
The SQL Server instance.
 
.PARAMETER SqlCredential
Allows you to login to servers using SQL Logins as opposed to Windows Auth/Integrated/Trusted.
 
.PARAMETER Spids
This parameter is auto-populated from -SqlServer. You can specify one or more Spids (including blocking spids) to be displayed.
 
.PARAMETER Logins
This parameter is auto-populated from-SqlServer and allows only login names that have active processes. You can specify one or more logins whose processes will be displayed.
 
.PARAMETER Hosts
This parameter is auto-populated from -SqlServer and allows only host names that have active processes. You can specify one or more Hosts whose processes will be displayed.
 
.PARAMETER Programs
This parameter is auto-populated from -SqlServer and allows only program names that have active processes. You can specify one or more Programs whose processes will be displayed.
 
.PARAMETER Databases
This parameter is auto-populated from -SqlServer and allows only database names that have active processes. You can specify one or more Databases whose processes will be displayed.
 
.PARAMETER Exclude
This parameter is auto-populated from -SqlServer. You can specify one or more Spids to exclude from being displayed (goes well with Logins).
 
Exclude is the last filter to run, so even if a Spid matches, for example, Hosts, if it's listed in Exclude it wil be excluded.
     
.PARAMETER Detailed
Provides Detailed information
 
.PARAMETER NoSystemSpids
Ignores the System Spids
.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/Get-DbaProcess
 
.EXAMPLE
Get-DbaProcess -SqlServer sqlserver2014a -Logins base\ctrlb, sa
 
Shows information about the processes for base\ctrlb and sa on sqlserver2014a. Uses Windows Authentication to login to sqlserver2014a.
 
.EXAMPLE
Get-DbaProcess -SqlServer sqlserver2014a -SqlCredential $credential -Spids 56, 77
     
Shows information about the processes for spid 56 and 57. Uses alternative (SQL or Windows) credentials to login to sqlserver2014a.
 
.EXAMPLE
Get-DbaProcess -SqlServer sqlserver2014a -Programs 'Microsoft SQL Server Management Studio'
     
Shows information about the processes that were created in Microsoft SQL Server Management Studio.
 
.EXAMPLE
Get-DbaProcess -SqlServer sqlserver2014a -Hosts workstationx, server100
     
Shows information about the processes that were initiated by hosts (computers/clients) workstationx and server 1000.
     
#>

    [CmdletBinding()]
    Param (
        [parameter(Mandatory = $true, ValueFromPipeline = $true)]
        [Alias("ServerInstance", "SqlInstance")]
        [object]$SqlServer,
        [object]$SqlCredential,
        [switch]$NoSystemSpids,
        [switch]$Detailed
    )
    
    DynamicParam { if ($sqlserver) { Get-ParamSqlAllProcessInfo -SqlServer $sqlserver -SqlCredential $SqlCredential } }
    
    BEGIN
    {
        $sourceserver = Connect-SqlServer -SqlServer $sqlserver -SqlCredential $SqlCredential
        $source = $sourceserver.DomainInstanceName
        
        $logins = $psboundparameters.Logins
        $spids = $psboundparameters.Spids
        $exclude = $psboundparameters.Exclude
        $hosts = $psboundparameters.Hosts
        $programs = $psboundparameters.Programs
        $databases = $psboundparameters.Databases
    }
    
    PROCESS
    {
        $allsessions = @()
        
        $processes = $sourceserver.EnumProcesses()
        $servercolumn = $processes.Columns.Add("SqlServer", [object])
        $servercolumn.SetOrdinal(0)
        
        foreach ($row in $processes)
        {
            $row["SqlServer"] = $sourceserver
        }
        
        if ($logins.count -gt 0)
        {
            $allsessions += $processes | Where-Object { $_.Login -in $Logins -and $_.Spid -notin $allsessions.Spid }
        }
        
        if ($spids.count -gt 0)
        {
            $allsessions += $processes | Where-Object { ($_.Spid -in $spids -or $_.BlockingSpid -in $spids) -and $_.Spid -notin $allsessions.Spid }
        }
        
        if ($hosts.count -gt 0)
        {
            $allsessions += $processes | Where-Object { $_.Host -in $hosts -and $_.Spid -notin $allsessions.Spid }
        }
        
        if ($programs.count -gt 0)
        {
            $allsessions += $processes | Where-Object { $_.Program -in $programs -and $_.Spid -notin $allsessions.Spid }
        }
        
        if ($databases.count -gt 0)
        {
            $allsessions += $processes | Where-Object { $databases -contains $_.Database -and $_.Spid -notin $allsessions.Spid }
        }
        
        # feel like I'm doing this wrong but it's 2am ;)
        if ($logins -eq $null -and $spids -eq $null -and $spids -eq $exclude -and $hosts -eq $null -and $programs -eq $null -and $programs -eq $databases)
        {
            $allsessions = $processes
        }
        
        if ($nosystemspids -eq $true)
        {
            $allsessions = $allsessions | Where-Object { $_.Spid -gt 50 }
        }
        
        if ($exclude.count -gt 0)
        {
            $allsessions = $allsessions | Where-Object { $exclude -notcontains $_.SPID -and $_.Spid -notin $allsessions.Spid }
        }
        
        if ($Detailed)
        {
            $object = ($allsessions | Select-Object SqlServer, Spid, Login, Host, Database, BlockingSpid, Program, @{
                    name = "Status"; expression = {
                        if ($_.Status -eq "") { "sleeping" }
                        else { $_.Status }
                    }
                }, @{
                    name = "Command"; expression = {
                        if ($_.Command -eq "") { "AWAITING COMMAND" }
                        else { $_.Command }
                    }
                }, Cpu, MemUsage, IsSystem)
            
            Select-DefaultView -InputObject $object -Property Spid, Login, Host, Database, BlockingSpid, Program, Status, Command, Cpu, MemUsage, IsSystem
        }
        else
        {
            $object = ($allsessions | Select-Object SqlServer, Spid, Login, Host, Database, BlockingSpid, Program, @{
                    name = "Status"; expression = {
                        if ($_.Status -eq "") { "sleeping" }
                        else { $_.Status }
                    }
                }, @{
                    name = "Command"; expression = {
                        if ($_.Command -eq "") { "AWAITING COMMAND" }
                        else { $_.Command }
                    }
                })
            
            Select-DefaultView -InputObject $object -Property Spid, Login, Host, Database, BlockingSpid, Program, Status, Command
        }
    }
}