Cmdlets/Get-SdtProcessForDBA.ps1
|
#Get-SdtProcessForDBA function Get-SdtProcessForDBA { <# .SYNOPSIS Displays memory and other important information for all processes of Server name(s) passed as parameter. .DESCRIPTION Displays ComputerName, ProcessId, ProcessName, Description, StartTime, Threads, Memory(MB), Path, Company, Product for all processes of Server name(s) passed as parameter. .PARAMETER ComputerName List of computer or machine names. This list can be passed either as computer name or through pipeline. .PARAMETER CPU When this switch is used, then CPU is also provided in resultset. .EXAMPLE Get-SdtProcessForDBA -ComputerName $env:COMPUTERNAME | Format-Table -AutoSize; Ouput:- ComputerName Id ProcessName Description StartTime Threads Memory(MB) Path ------------ -- ----------- ----------- --------- ------- ---------- ---- BAN-2ADWIVEDI-L 7924 AccelerometerSt Hp Accelerometer System Tray 4/19/2018 8:30:18 AM {7928} 3.94140625 C:\Program Files (x86)\Hewlett-Pac... BAN-2ADWIVEDI-L 2672 armsvc Adobe Acrobat Update Service 4/19/2018 8:27:29 AM {2676, 2700, 2708, 2900...} 4.51953125 C:\Program Files (x86)\Common File... BAN-2ADWIVEDI-L 8868 audiodg 4/20/2018 3:07:09 PM {15488, 15696, 6148, 9876...} 33.44140625 BAN-2ADWIVEDI-L 7652 Balloon32 McAfee Data Protection Notification 4/19/2018 8:30:18 AM {7656, 7664, 7668} 5.5625 C:\Program Files\McAfee\Endpoint E... BAN-2ADWIVEDI-L 5164 CcmExec Host Process for Microsoft Configuration Manager 4/19/2018 8:29:41 AM {3492, 3512, 2272, 5504...} 53.3828125 C:\Windows\CCM\CcmExec.exe BAN-2ADWIVEDI-L 1500 chrome Google Chrome 4/19/2018 7:54:34 PM {14684, 14108, 11772, 2860...} 4729.66015625 C:\Program Files (x86)\Google\Chro... BAN-2ADWIVEDI-L 4832 CmRcService Configuration Manager Remote Control Service 4/19/2018 8:29:41 AM {4680, 5516, 3708, 5440...} 9.43359375 C:\Windows\CCM\RemCtrl\CmRcService... BAN-2ADWIVEDI-L 2188 conhost Console Window Host 4/19/2018 8:27:28 AM {2196} 27.06640625 C:\Windows\system32\conhost.exe BAN-2ADWIVEDI-L 620 csrss Client Server Runtime Process 4/19/2018 8:27:16 AM {652, 656, 660, 664...} 130.1015625 C:\Windows\system32\csrss.exe BAN-2ADWIVEDI-L 2800 CxMonSvc CxMonSvc 4/19/2018 8:27:29 AM {2804, 2816, 2820, 2824...} 14.0234375 C:\Windows\CxSvc\CxMonSvc.exe BAN-2ADWIVEDI-L 2888 CxUtilSvc Utility Service 4/19/2018 8:27:30 AM {2892, 2904, 2908, 2164...} 5.57421875 C:\Windows\CxSvc\CxUtilSvc.exe BAN-2ADWIVEDI-L 968 CylanceSvc Cylance Native Agent 4/19/2018 8:27:17 AM {972, 996, 1000, 1004...} 70.1171875 C:\Program Files\Cylance\Desktop\C... BAN-2ADWIVEDI-L 7964 CylanceUI Cylance Protect 4/19/2018 8:30:19 AM {7968, 7996, 8000, 8004...} 3.2109375 C:\Program Files\Cylance\Desktop\C... BAN-2ADWIVEDI-L 6132 devmonsrv Bluetooth Device Monitor 4/19/2018 8:29:40 AM {5340, 5344, 5608, 5576...} 9 C:\Program Files (x86)\Intel\Bluet... BAN-2ADWIVEDI-L 16120 dnscrypt-proxy Cisco DNS Proxy 4/21/2018 10:29:38 AM {13388} 5.57421875 C:\Program Files (x86)\OpenDNS\Umb... BAN-2ADWIVEDI-L 7240 dwm Desktop Window Manager 4/19/2018 8:30:15 AM {7244, 7260, 7264, 12656...} 48.80859375 C:\Windows\system32\Dwm.exe BAN-2ADWIVEDI-L 7596 EpePcMonitor McAfee Drive Encryption Monitor 4/19/2018 8:30:17 AM {7600, 7604, 7608, 7644...} 7.71875 C:\Program Files\McAfee\Endpoint E... BAN-2ADWIVEDI-L 4248 ERCService Umbrella RC Service 4/19/2018 8:27:35 AM {4252, 4296, 4300, 4316...} 46.0078125 C:\Program Files (x86)\OpenDNS\Umb... BAN-2ADWIVEDI-L 2948 EvtEng Intel(R) PROSet/Wireless Event Log Service 4/19/2018 8:27:30 AM {2952, 2964, 2968, 2976...} 27.71484375 c:\Program Files\Intel\WiFi\bin\Ev... BAN-2ADWIVEDI-L 7276 explorer Windows Explorer 4/19/2018 8:30:15 AM {7280, 7288, 7316, 7320...} 103.53125 C:\Windows\Explorer.EXE Returns information about processes on current machine. .EXAMPLE PS C:\> Get-SdtProcessForDBA -ComputerName $env:COMPUTERNAME | Where-Object {$_.Description -like "*sql*"} | ft -AutoSize ComputerName Id ProcessName Description StartTime Threads Memory(MB) Path ------------ -- ----------- ----------- --------- ------- ---------- ---- BAN-2ADWIVEDI-L 5552 fdhost SQL Full Text host 4/19/2018 8:27:41 AM {5556, 5620, 5628, 5632...} 5.9609375 C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Binn\fdhost.exe BAN-2ADWIVEDI-L 3944 fdlauncher SQL Full-text Filter Daemon Launch Service 4/19/2018 8:27:39 AM {4192, 5132, 4396, 13796} 4.43359375 C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Binn\fdlauncher.exe BAN-2ADWIVEDI-L 4848 SQLAGENT SQLAGENT - SQL Server Agent 4/19/2018 8:27:39 AM {4852, 5000, 5028, 5036...} 17.34375 C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Binn\SQLAGENT.EXE BAN-2ADWIVEDI-L 3728 sqlbrowser SQL Browser Service EXE 4/19/2018 8:27:32 AM {3732, 3760, 3768, 3772...} 5.3828125 C:\Program Files (x86)\Microsoft SQL Server\90\Shared\sqlbrowser.exe BAN-2ADWIVEDI-L 3400 sqlservr SQL Server Windows NT - 64 Bit 4/19/2018 8:27:31 AM {3404, 3664, 3700, 3956...} 274.9453125 C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Binn\sqlservr.exe BAN-2ADWIVEDI-L 3796 sqlwriter SQL Server VSS Writer - 64 Bit 4/19/2018 8:27:32 AM {3800, 3812, 3836, 14248...} 7.0546875 C:\Program Files\Microsoft SQL Server\90\Shared\sqlwriter.exe BAN-2ADWIVEDI-L 6312 Ssms SQL Server Management Studio 4/20/2018 8:34:13 AM {7648, 12620, 15436, 14308...} 985.37890625 C:\Program Files (x86)\Microsoft SQL Server\120\Tools\Binn\ManagementStudio\Ssms.exe Returns information about processes with keywork *sql* in their description on current machine. .EXAMPLE PS C:\> Get-SdtProcessForDBA -ComputerName $env:COMPUTERNAME | Out-GridView Gets all processes from current computer in Grid View. .LINK https://github.com/imajaydwivedi/SQLDBATools #> [CmdletBinding()] Param( [Parameter(ValueFromPipeline=$true, ValueFromPipelineByPropertyName=$true)] [Alias('ServerName','MachineName')] [String[]]$ComputerName = $env:COMPUTERNAME, [parameter( Mandatory=$false)] [Switch]$CPU = $false ) BEGIN { $processDetails = @(); } PROCESS { foreach ($Comp in $ComputerName) { Write-Host "Finding Process details for ComputerName: $Comp" -ForegroundColor Green; $processes = Get-Process -ComputerName $Comp; # If -Verbose switch is used if($PSCmdlet.MyInvocation.BoundParameters["Verbose"].IsPresent) { $processes | Select-Object * | Out-GridView; } # Get Process Names $processNames = @($processes | Select-Object Name -Unique | Select-Object -ExpandProperty Name); # To match the CPU usage to for example Process Explorer you need to divide by the number of cores $cpu_cores = (Get-WMIObject Win32_ComputerSystem -ComputerName $Comp).NumberOfLogicalProcessors; if($CPU) { $Counters = (Get-Counter "\Process(*)\ID Process" -ComputerName $Comp -ErrorAction SilentlyContinue).CounterSamples; # If -Verbose switch is used if($PSCmdlet.MyInvocation.BoundParameters["Verbose"].IsPresent) { $Counters | ft -AutoSize; } } foreach($proc in $processNames) { Write-Verbose " Finding details of process '$proc'"; # Get Process Details $p = $processes | Where-Object {$_.Name -eq $proc} | Select-Object MachineName, Id, ProcessName, Description, StartTime, Threads, @{l='WS';e={if([String]::IsNullOrEmpty($_.WorkingSet64)) {$_.WorkingSet}else{$_.WorkingSet64}}}, @{l='VM';e={if([String]::IsNullOrEmpty($_.VirtualMemorySize64)){$_.VirtualMemorySize}else{$_.VirtualMemorySize64}}}, CPU, Path, Company, Product -First 1; # Get Sum of Workingset Memory for Process $processMemoryMB = ($processes | Where-Object {$_.Name -eq $proc} | Measure-Object WS -Sum).Sum/1MB; # Get Sum of Virtual Memory for Process $virtualMemoryMB = ($processes | Where-Object {$_.Name -eq $proc} | Measure-Object VM -Sum).Sum/1MB; #Write-Verbose "`$processMemory = $processMemory"; $MachineName = if($p.MachineName -eq '.'){$Comp}else{$p.MachineName}; $cpuPercentage = 0; $pIds = @($processes | Where-Object {$_.Name -eq $proc} | Select-Object -ExpandProperty Id); Write-Verbose " Process Ids for process '$proc' are $($pIds -join ',')"; if($CPU -and $pIds.Count -ne 0) { Write-Verbose " Calculating CPU(%) for process $proc"; foreach ($proc_pid in $pIds) { # This is to find the exact counter path, as you might have multiple processes with the same name $proc_path = ($Counters | Where-Object {$_.RawValue -eq $proc_pid}).Path; # We now get the CPU percentage if ([String]::IsNullOrEmpty($proc_path) -eq $false) { $prod_percentage_cpu = [Math]::Round(((Get-Counter ($proc_path -replace "\\id process$","\% Processor Time")).CounterSamples.CookedValue) / $cpu_cores); $cpuPercentage += $prod_percentage_cpu; } else { Write-Verbose " Counter for `$proc_pid = $proc_pid is not found"; } } } if ([String]::IsNullOrEmpty($cpuPercentage)) { Write-Verbose "No CPU utilization found for process $proc."; $cpuPercentage = 0; } $prop = [Ordered]@{ 'ComputerName'= $MachineName; 'Id' = $p.Id; 'ProcessName' = $p.ProcessName; 'Description' = $p.Description; 'StartTime' = $p.StartTime; 'Threads' = $p.Threads; 'Memory(MB)' = $processMemoryMB; 'VirtualMemory(MB)' = [bigint]$virtualMemoryMB; 'VirtualMemory(GB)' = [bigint]$virtualMemoryMB/1024; 'CPU(%)' = [int]$cpuPercentage; 'Path' = $p.Path; 'Company' = $p.Company; 'Product' = $p.Product; } $obj = New-Object -TypeName psobject -Property $prop; $processDetails += $obj; } } } END { if ($CPU) { $processDetails | Sort-Object -Property 'Memory(MB)' -Descending | Select-Object ComputerName,Id,ProcessName,Description,StartTime,Threads,'Memory(MB)','VirtualMemory(MB)','VirtualMemory(GB)','CPU(%)',Path,Company,Product | Write-Output; } else { $processDetails | Sort-Object -Property 'Memory(MB)' -Descending | Select-Object ComputerName,Id,ProcessName,Description,StartTime,Threads,'Memory(MB)','VirtualMemory(MB)','VirtualMemory(GB)',Path,Company,Product | Write-Output; } } } |