Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

PowerSQL By Prashanth Jayaram

Technology enthusiast with 7+ years of experience in Database Technology. I am Microsoft Certified Professional with certificates of OCA, MCP, MCTS, MCITP developer, MCITP administration and backed with a degree in Master of Computer Application. My expertise lies in T-SQL programming, Replication, PowerShell and Performance Tuning. Hobbies are Drawing, playing soccer and listening to Melodies songs.

PowerShell – Perfmon Counters into CSV File – Multiple remote servers

Introduction

This script can be used for exporting specified performance counter values from multiple remote target servers to CSV file. The challenge is to gather the specific counter information and append it to CSV file with Powershell2.0.

Performance counters

The script will pull the below mentioned counter values

  • Processor (_total)\% processor time
  • system\processor queue length
  • Memory\Available MBytes
  • Memory\% committed bytes in use
  • PhysicalDisk (*)\Current Disk Queue Length
  • PhysicalDisk (*)\Avg. Disk sec/Transfer
  • PhysicalDisk (*)\% Disk Time
  • PhysicalDisk (*)\Disk Read Bytes/sec
  • PhysicalDisk (*)\Disk Write Bytes/sec
  • Network Interface (*)\Bytes Total/sec

 Scenarios

IT Administrators may want to analyze the performance the servers with the help of perfmon counters information’s.

 Prerequisites

  • The source machine should have PowerShell 2.0 installed

Download the code:- http://gallery.technet.microsoft.com/PowerShell-Perfmon-0f013da8

Script

You can use this script in following ways:

1. Download the script.

2. Open the script file with Notepad or any other script editors (preferably Windows PowerShell ISE)

3.  Change the Input file path and Output file if required other than the default location as shown in below screenshot.

4. Counters can be selected based on your requirement by changing the below code

5. Save the file then run the script via PowerShell in “Run as administrator” mode.

 

 You can run this script manually or by scheduler task

 

How to create a scheduler task:

1. Open “Task Scheduler” (Goto START—RUN —Type Tasks and hit enter)

2. Click “Create task”

3. Pick a name, and choose “Run whether user is logged on or not”

 

Choose “Triggers” Tab, Click “New”

5. Specify option you like, and then click “OK” to create a trigger

 

Choose “Actions” tab, Click “New”

7. Copy following command to “Program/script” textbox, click C:\Windows\System32\WindowsPowerShell\v1.0\powershell.exe

8Enter the path of the saved script file in “Add arguments (optionally)” textbox

As per the screenshot I saved the file under C:\Perform_script.ps1 hence I updated the add arguments text box as

C:\Perform_script.ps1; exit” 

 

Code:

 

<# 
# Script: Get-CounterStats 
# Author: Prashanth and Praveen 
# Comments: This script will collect the specific counters value from the multiple target machines/servers  
which will be used to analayze the performance of target servers. 
#> 
 
 
 
#Define Input and output filepath 
 
$servers=get-content "C:\servers.txt" 
$outfile="C:\perfmon.csv" 
 
################################################################################################################ 
 
 
################################################################################################################ 
 
 
#Actual script starts here  
 
function Global:Convert-HString {       
[CmdletBinding()]             
 Param              
   ( 
    [Parameter(Mandatory=$false, 
               ValueFromPipeline=$true, 
               ValueFromPipelineByPropertyName=$true)] 
    [String]$HString 
   )#End Param 
 
Begin  
{ 
    Write-Verbose "Converting Here-String to Array" 
}#Begin 
Process  
{ 
    $HString -split "`n" | ForEach-Object { 
     
        $ComputerName = $_.trim() 
        if ($ComputerName -notmatch "#") 
            { 
                $ComputerName 
            }     
         
         
        } 
}#Process 
End  
{ 
    # Nothing to do here. 
}#End 
 
}#Convert-HString 
 
 
#Function to have the customized output in CSV format 
function Export-CsvFile { 
[CmdletBinding(DefaultParameterSetName='Delimiter', 
  SupportsShouldProcess=$true, ConfirmImpact='Medium')] 
param( 
[Parameter(Mandatory=$true, ValueFromPipeline=$true, 
           ValueFromPipelineByPropertyName=$true)] 
[System.Management.Automation.PSObject] 
${InputObject}, 
 
[Parameter(Mandatory=$true, Position=0)] 
[Alias('PSPath')] 
[System.String] 
${Path}, 
 
#region -Append  
[Switch${Append}, 
#endregion  
 
[Switch${Force}, 
 
[Switch${NoClobber}, 
 
[ValidateSet('Unicode','UTF7','UTF8','ASCII','UTF32','BigEndianUnicode','Default','OEM')] 
[System.String] 
${Encoding}, 
 
[Parameter(ParameterSetName='Delimiter', Position=1)] 
[ValidateNotNull()] 
[System.Char] 
${Delimiter}, 
 
[Parameter(ParameterSetName='UseCulture')] 
[Switch${UseCulture}, 
 
[Alias('NTI')] 
[Switch${NoTypeInformation}) 
 
begin 
{ 
# This variable will tell us whether we actually need to append 
# to existing file 
$AppendMode = $false 
 
 try { 
  $outBuffer = $null 
  if ($PSBoundParameters.TryGetValue('OutBuffer', [ref]$outBuffer)) 
  { 
      $PSBoundParameters['OutBuffer'] = 1 
  } 
  $wrappedCmd = $ExecutionContext.InvokeCommand.GetCommand('Export-Csv', 
    [System.Management.Automation.CommandTypes]::Cmdlet) 
         
         
                #String variable to become the target command line 
                $scriptCmdPipeline = '' 
 
                # Add new parameter handling 
                #region Dmitry: Process and remove the Append parameter if it is present 
                if ($Append) { 
   
                                $PSBoundParameters.Remove('Append'| Out-Null 
     
  if ($Path) { 
   if (Test-Path $Path) {         
    # Need to construct new command line 
    $AppendMode = $true 
     
    if ($Encoding.Length -eq 0) { 
     # ASCII is default encoding for Export-CSV 
     $Encoding = 'ASCII' 
    } 
     
    # For Append we use ConvertTo-CSV instead of Export 
    $scriptCmdPipeline +'ConvertTo-Csv -NoTypeInformation ' 
     
    # Inherit other CSV convertion parameters 
    if ( $UseCulture ) { 
     $scriptCmdPipeline +' -UseCulture ' 
    } 
    if ( $Delimiter ) { 
     $scriptCmdPipeline +" -Delimiter '$Delimiter' " 
    }  
     
    # Skip the first line (the one with the property names)  
    $scriptCmdPipeline +' | Foreach-Object {$start=$true}' 
    $scriptCmdPipeline +'{if ($start) {$start=$false} else {$_}} ' 
     
    # Add file output 
    $scriptCmdPipeline +" | Out-File -FilePath '$Path' -Encoding '$Encoding' -Append " 
     
    if ($Force) { 
     $scriptCmdPipeline +' -Force' 
    } 
 
    if ($NoClobber) { 
     $scriptCmdPipeline +' -NoClobber' 
    }    
   } 
  } 
}  
   
 
   
 $scriptCmd = {& $wrappedCmd @PSBoundParameters } 
 
 if ( $AppendMode ) { 
  # redefine command line 
  $scriptCmd = $ExecutionContext.InvokeCommand.NewScriptBlock( 
      $scriptCmdPipeline 
    ) 
} else { 
  # execute Export-CSV as we got it because 
  # either -Append is missing or file does not exist 
  $scriptCmd = $ExecutionContext.InvokeCommand.NewScriptBlock( 
      [string]$scriptCmd 
    ) 
} 
 
# standard pipeline initialization 
$steppablePipeline = $scriptCmd.GetSteppablePipeline($myInvocation.CommandOrigin) 
$steppablePipeline.Begin($PSCmdlet) 
 
 } catch { 
   throw 
} 
     
} 
 
process 
{ 
  try { 
      $steppablePipeline.Process($_) 
  } catch { 
      throw 
  } 
} 
 
end 
{ 
  try { 
      $steppablePipeline.End() 
  } catch { 
      throw 
  } 
} 
 
} 
 
#Performance counters declaration 
 
function Get-CounterStats {  
param  
    (  
    [String]$ComputerName = $ENV:ComputerName 
     
    )  
 
$Object =@() 
 
 
$Counter = @"  
Processor(_total)\% processor time  
system\processor queue length 
Memory\Available MBytes  
Memory\% committed bytes in use 
PhysicalDisk(*)\Current Disk Queue Length  
PhysicalDisk(*)\Avg. Disk sec/Transfer  
PhysicalDisk(*)\% Disk Time  
PhysicalDisk(*)\Disk Read Bytes/sec  
PhysicalDisk(*)\Disk Write Bytes/sec  
Network Interface(*)\Bytes Total/sec  
"@  
 
        (Get-Counter -ComputerName $ComputerName -Counter (Convert-HString -HString $Counter)).counterSamples |   
        ForEach-Object {  
        $path = $_.path  
        New-Object PSObject -Property @{ 
        computerName=$ComputerName; 
        Counter        = ($path  -split "\\")[-2,-1] -join "-" ; 
        Item        = $_.InstanceName ; 
        Value = [Math]::Round($_.CookedValue,2)  
        datetime=(Get-Date -format "yyyy-MM-d hh:mm:ss") 
        }  
         
        } 
      
    
}  
 
#Collecting counter information for target servers 
 
foreach($server in $Servers) 
{ 
$d=Get-CounterStats -ComputerName $server |Select-Object computerName,Counter,Item,Value,datetime 
$d |Export-CsvFile $outfile  -Append -NoTypeInformation 
 
} 
 
#End of Script
 

Output:-


 

 

 

 


Comments

Leave a comment on the original post [sqlpowershell.wordpress.com, opens in a new window]

Loading comments...