PowerShell 4.0 PipelineVariable, With or Without, I can´t live…..Without you.

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.

Keith Hill – PowerShell V4 – PipelineVariable Common Parameter

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

Posted in Algo que Esqueci de Categorizar | Leave a comment

PASS PowerShell VC Meeting of March with Kendal Van Dyke

SQL AGENT JOB STATUS REPORTING WITH WINDOWS POWERSHELL

A critical, yet tedious, task DBAs perform on a daily basis is making sure SQL Agent jobs have run properly. Manually checking each instance is slow and doesn’t scale beyond a handful of servers. Agent alerts are helpful but can easily get lost and forgotten in your inbox; Monitoring tools make things a little easier – provided every one of your instances is monitored. What we really just need is a simple and efficient way to see the status of jobs across all instances. Windows PowerShell is a perfect fit for the task, and in this session you’ll learn how to automate building a daily report so that you can see what’s happening with SQL Agent jobs in one place.

Kendal Van Dyke is a practiced IT professional with over a decade of experience in SQL Server development and administration. He is currently a principal consultant with UpSearch SQL, where he helps companies keep their SQL Servers running in high gear. Kendal is a Microsoft MVP for SQL Server, a Microsoft Certified Professional in SQL Server 2008 and 2012, and president of MagicPASS (South Orlando’s PASS Chapter). He also serves as a Regional Mentor for the US Southeast Region of PASS.

 

Link to Register :

http://powershell.sqlpass.org/

Posted in Algo que Esqueci de Categorizar | Leave a comment

Some reasons to Darth Vader being a badass….

1 – Interview with the new System Administrator :

What? what you mean “I prefer next, next, finish”

powershell1

2 – After a nice vacation at Korriban (homeworld of Sith), Vader returns to Death Star and get the reports of the SQL Server servers :

Emperor :

“Vader, our new DBA wrote a cool backup process..humm..mmmm..on each server”

powershell2

3 – No Comments

“I agree, you never will spend, again, half of the morning checking jobs that failed. Apology accepted”

powershell5

4 – Checking the time frame of the Death Star employees

“WTF ? Is it true that you spent 1 hour manually repeating the same process on each server ?”

powersghell6

 

Dont be a “next, next, finish IT Professional”. Use the Force, or as it is known in the Galaxy, PowerShell :)

Posted in Algo que Esqueci de Categorizar | Leave a comment

Being notified when a child background job is done

PowerShell is so cool that you can be notified when a background job is done. I will not show how to do that, because you can find an excellent explanation about that in this blog post from my good friend Boe Prox .

PowerShell and Events: Object Events

My point is , have you ever tried to use –asjob on Invoke-command ? It will run the scriptblock in a background job. cool !!! But, if you pass more than one server to run this process, lets say 4 servers,  it will create to you one job “Father” and 3 others child jobs. That father  job will  control and report the success of failure of the entire process (all the child jobs)

In PowerShell 2.0  you need to expand the property childjobs to know them. To know what jobs are child  I will create a simples process to sleep the job in a random count. I am using more than one server (in my case the same server more than one time)

If we use the PowerShell 2.0 and get the object $job we will see only one job. The father one :

$job = Invoke-Command {start-sleep (get-random (1..500))} -AsJob -computername 'Vader','Vader','Vader'

$job = Invoke-Command {start-sleep (get-random (1..500))} -AsJob -computername 'Vader','Vader','Vader' Get-Job $job.id

The result will be :

image 

As we create the job for 3 servers, to check the other ones we need to expand the property childjobs :

$job = Invoke-Command {start-sleep (get-random (1..500))} -AsJob -computername 'Vader','Vader','Vader' Get-Job $job.id | select -ExpandProperty childjobs

image

 

Now imagine that you are dealing with a lot of long-running process in background jobs in different servers. How can I  check the child jobs status ?(to check how it is going the process in each server)  ?

PowerShell 3.0,  2 new parameters was added. They are -IncludeChildJob –ChildJobState. Lets take a look in the –IncludeChildJob

If I run the same command and now get the job but with –IncludeChildJob  parameter :

$job = Invoke-Command {start-sleep (get-random (1..500))} -AsJob -computername 'Vader','Vader','Vader' Get-Job $job.id -IncludeChildJob

image

Cool !! Backing to our example, I have a lot of long-running process in background jobs in different servers. Now I want to know when they finished. I can use the example that I mentioned in the beginning of this post, but it will notify you only when all the child jobs finished because the event is subscribe to the father job. I want to be notifed for each child job in the process. Let´s say that I want to know when he process in each server finished.  The parameter –ChildJobState only will show for me the jobs with the specified state. I will not be notified.

Well, the idea is the same to the father job, I just need to go in each child job and register an event for each one :)

$job = Invoke-Command {start-sleep (get-random (1..500))} -AsJob -computername 'Vader','Vader','Vader' Get-Job $job.id -IncludeChildJob | % { Register-ObjectEvent -InputObject $_ -EventName StateChanged -MessageData $_.Id -SourceIdentifier "Job.Monitor$($_.id)" -Action { write-host "Job $($event.sender.id) has changed from $($event.SourceEventArgs.PreviousJobStateInfo.State) to $($event.SourceEventArgs.JobStateInfo.state)" -ForegroundColor Yellow if ($event.SourceEventArgs.JobStateInfo.state -eq 'Completed') { $eventSubscriber | Unregister-Event $eventSubscriber.Action | Remove-Job } } }

image

if you are using PowerShell 4.0, you can use the new foreach method :)

$job = Invoke-Command {start-sleep (get-random (1..500))} -AsJob -computername 'Vader','Vader','Vader' (Get-Job $job.id -IncludeChildJob).foreach({ Register-ObjectEvent -InputObject $_ -EventName StateChanged -MessageData $_.Id -SourceIdentifier "Job.Monitor$($_.id)" -Action { write-host "Job $($event.sender.id) has changed from $($event.SourceEventArgs.PreviousJobStateInfo.State) to $($event.SourceEventArgs.JobStateInfo.state)" -ForegroundColor Yellow if ($event.SourceEventArgs.JobStateInfo.state -eq 'Completed') { $eventSubscriber | Unregister-Event $eventSubscriber.Action | Remove-Job } } })

Or you can do the same in the PowerShell 2.0, but expanding the property childjobs :

$job = Invoke-Command {start-sleep (get-random (1..500))} -AsJob -computername 'Vader','Vader','Vader' Get-Job $job.id | select -ExpandProperty childjobs | % { Register-ObjectEvent -InputObject $_ -EventName StateChanged -MessageData $_.Id -SourceIdentifier "Job.Monitor$($_.id)" -Action { write-host "Job $($event.sender.id) has changed from $($event.SourceEventArgs.PreviousJobStateInfo.State) to $($event.SourceEventArgs.JobStateInfo.state)" -ForegroundColor Yellow if ($event.SourceEventArgs.JobStateInfo.state -eq 'Completed') { $eventSubscriber | Unregister-Event $eventSubscriber.Action | Remove-Job } } }

Yes..I am using write-host. In this case as I just wanto to show in the screen, I dont need any output piped .

You can change the notification to send email, speak..whatever. It is up to you :)

Posted in Algo que Esqueci de Categorizar | 2 Comments

It’s just a matter of respect – at least thank your references.

I am doing this post and to be honest I guess that I dont need to write a blog post about this subject , since in my idea  we are professional and at least we have commom sense.

I am seeing lately an increase in the number of blog posts copied and pasted(that´s Including my local community – PowerShell or SQL Server). Sometimes some words are changed , but the main core you can “fell” that was copied.

I, and I am speaking only for me, I dont mind  if someone copy my content.One way or another it is some kind of respect about me and my work. If someone had the hard work to copy and paste my content, read it , and change the words to not looks like copied, at least some respect for me that person have.

But a LOT of other #sql and #powershell friends does not think like me, and they are completely rigth.  If you have a blog and it is active, lets see some points :

- You need to know about what you are blogging .That´s implies to study a LOT and in many many times miss your time with your family, friends and having fun.

- We are bloggers but also we are workers. Yes..you can think “I see that dumbass all the time in the twitter or facebook. He lives for blogging” Well, sorry to say that, but all the people that I know and are active bloggers are also excellent professionals and work for some company (can be their companys, but are companys). That means that they have to work at least 6/8 hours per day and then, only then , they will study and blogging.

- We do that at least because we love to share and learn. Nobody wants to be better than another. There are spots for everybody and every content.

- We are providing to you free information and in many times we give to you our personal emails and you can contact us directely to share ideas. It means more time after the regular time to our daily job.

- To finish, be a good guy does not mean we are stupid. We know when someone wants to take adavantage of our “good will”, and if we didnt say that it is not because we are stupid nerds. It is because , at least in my case and I am speaking again only for me, I still believe in the soul of the people. When I no longer believe in people, It is because I do not believe in myself anymore. Oh… then actually I have to stop.

Dont be a jerk. Be a good guy. You can Impress other people not only for your knowledge but also for your humility. Thank your references in your posts.

Or at least mention them. I am not speaking for me. As I said, I dont mind. But my friends yes..they mind about that. And as we are a family, #sqlfamily, we care about each other

Again…dont be a jerk.It’s just a matter of respect – at least thank your references.

If you dont have respect for other people, these other people will not have for you. Simple Newton system..action/reaction.

:)

Posted in Algo que Esqueci de Categorizar | 4 Comments

Treinamento de PowerShell 3.0 fundamentals – Março

Pessoal, vamos ter outra turma de PowerShell 3.0 fundamentals aos sabados, iniciando dia 8 de março e indo para os 3 sabados subsequentes, das 8:00 as 12:00 da manhã totalizando 16 horas.

O treinamento é online, via livemeeting.

Quem tiver interesse na ementa, valores etc, me manda um email – laertesqldba@outlook.com

:)

Posted in Algo que Esqueci de Categorizar | Leave a comment

Avoiding the PowerShell Gotchas with Don Jones – PASS PowerShell VC

“Join Windows PowerShell MVP Don Jones for a fun- and fact-filled session on “PowerShell Gotchas.” These are the tips and tricks that are tough to tease out on your own, but once you conquer them the shell will be friendlier and even more powerful. If you’re a PowerShell novice, this will keep you out of trouble; if you’re an accomplished expert, this will help you help others achieve the same lofty heights. Bring your questions and have them speedily answered by one of the industry’s most well-known PowerShell educators, who’s launching a limited series of public classes throughout the US (http://events.concentratedtech.com for locations or to be notified when a class is scheduled near you). Don will also be appearing at the second-ever PowerShell Summit (http://powershell.org/wp/summit), to be held in April 2014 right in Microsoft’s hometown. You’re also welcome to ask Don questions about the Summit, which still has a limited number of attendee slots available. Start 2014 off right by connecting with PowerShell community, overcoming “the gotchas,” and amping up your PowerShell sk1llz!”

About Don Jones

With more than two decades years of IT experience, Don Jones is one of the world’s leading experts on the Microsoft business technology platform. He’s the author of more than 45 books, including Learn Windows PowerShell in a Month of Lunches, Windows Administrator’s Scripting Toolkit, VBScript WMI and ADSI Unleashed, Special Edition Using Commerce Server 2002, Definitive Guide to SQL Server Performance Optimization, and many more. Don’s devotion to technology education culminated in The Nine Principles of Immediately Effective Design, an instructional design book that outlines Don’s bestselling approach.

Wed, Feb 19 2014 12:00 (GMT-05:00) Eastern Time (US & Canada)

Link to register : http://powershell.sqlpass.org/Home.aspx?EventID=1041

Posted in Algo que Esqueci de Categorizar | Leave a comment