I am writing my new Simple-Talk article of the series The Posh DBA. This one is how to properly use Get-Counter to gathering Performance Counters (windows/SQL/.net..etc) and save to a csv or a SQL Server Table. While writing , I came across an cool example of how to use the new common parameter.
In this article, I am showing that you dont need to know .net classes or even have a intermediate knowldge of PowerShell to write your script to collect the counters. Why ?
Because we are using only the native cmdlets of PowerShell 3.0 and with a script code level 100, I will show to you how to write a process level 400, as collect the data for several servers in asynchronous mode and in background or even better, scheduling the entire process using PowerShell 3.0 Scheduled Jobs. I ´ve submitted a session showing all this process to SQLBITS and SQLPASS as well. Fingers crossed
Welll, but this post is about the new PowerShell 4.0 common parameter pipelinevariable. My fellow PowerShell MVP and guru Keith Hill already wrote about it and his EXCELLENT post is plenty of examples. You should read it before this one, because I am only showing a practical example that I am using.
Generally speaking, this new common parameter allows you to store in a variable the current pipeline object to use in the further pipeline process. Why that is so F Awesome Amazing Cool ? Just because I dont need to use , as in my example, a Foreach-Object just to create the variable to store the current pipeline object.
Lets see my code without PipelineVariable :
Get-Counter -ListSet 'LogicalDisk','SQLServer:Buffer Manager','SQLServer:Memory Manager' |
ForEach-Object {
$CounterCategory = $_
$CounterCategory |
Select-Object -ExpandProperty Counter |
ForEach-Object {
$CounterName = $_
$CounterName |
Where-Object {$CounterName -match '(sec/Transfer|Avg. Disk Queue Length|Buffer Cache|CheckPoint|Target Server|Total)'} |
Select-Object @{E={$CounterCategory.CounterSetName};N="CounterSetName"},
@{E={$CounterCategory.Description};N="Description"},
@{E={$CounterName};N="Counter"}
}
} |
Export-csv C:\temp\BaselineCounters.csv -NoClobber -NoTypeInformation
In this example I need to store the first pipeline to $CounterCategory and the second one in $CounterName variables , but I need to use Foreach-Objects , to allow me to use the information for these objects in the further pipeline with Select-Object. If I dont do that I dont have the information of my first and second pipeline objects 😦
With the New Super UItra Yo-Bro PipelineVariable common parameter that is possible and I dont need to use Foreach-Objects, because the current pipeline object is stored in the variable defined in the pipelinevariable parameter 🙂 :
Get-Counter -ListSet 'LogicalDisk','SQLServer:Buffer Manager','SQLServer:Memory Manager' -PipelineVariable CounterCategory |
Select-Object -ExpandProperty Counter -PipelineVariable CounterName |
Where-Object {$CounterName -match '(sec/Transfer|Avg. Disk Queue Length|Buffer Cache|CheckPoint|Target Server|Total)'} |
Select-Object @{E={$CounterCategory.CounterSetName};N="CounterSetName"},
@{E={$CounterCategory.Description};N="Description"},
@{E={$CounterName};N="Counter"} |
Export-csv C:\temp\BaselineCounters.csv -NoClobber -NoTypeInformation -Append
PipelineVariable,
With or without you
With or without you
I can’t live
……. without you