SentryOne Team Blog (blogs.sentryone.com)

My First PowerShell : A Simple Story with Dramatic Flair

Recently, I needed an automated way to restart remote servers in my test environment for SentryOne. I knew that this sounded like a PowerShell-ish thing, but I lack what most people call experience when it comes to PowerShell. Maybe that's what everyone calls it?

First Attempt

First, I came across Restart-Computer -ComputerName $ComputerName -Credential $Credential -Force which seemed to be exactly what I needed.

Next, I opened PowerShell and gave it a try:

PowerShell Restart-Computer with Login Popup WindowPowerShell Restart-Computer with Login Popup Window

As shown above, this prompted for credentials to complete the task. Technically, this was what I wanted. However, I wanted to place Restart-Computer in a loop, therefore the popup asking for credentials wouldn't suffice.

Avoiding Popup for Credentials

I discovered that I could use a secure string for the credentials, however, none of the initial examples I found explained how to create the secure string. Way to make me feel even more like a foolish newbie. ;-) A little more searching and I was on my way.

Creating a Secure String

Read-Host "Enter Stuff" -AsSecureString | ConvertFrom-SecureString | Out-File "C:\PowerShell\mysecurestring.txt"

PowerShell Create a Secure StringPowerShell Create a Secure String

Voilà! A secure string is created from a string that totally isn't my password. Seriously, I'm not *that* trusting.

PowerShell the Secure StringPowerShell the Secure String

Using a Secure String

Now that I've created a secure string, I can use it in a credentials object to avoid the popup:

$username = "mconnors"
$password = cat C:\PowerShell\mysecurestring.txt | convertto-securestring
$cred = new-object -typename System.Management.Automation.PSCredential -argumentlist $username, $password
$strComputer = "OA-SQL-100"
Restart-Computer -ComputerName $strComputer -Authentication default -Credential $cred

Clearly this is something that looks more loop-friendly:

PowerShell Using the Secure StringPowerShell Using the Secure String

Now it was loop-ready and I wrapped it in a simple for-loop.

The PowerShell Loop
$username = "mconnors"
$password = cat C:\PowerShell\mysecurestring.txt | convertto-securestring
$cred = new-object -typename System.Management.Automation.PSCredential -argumentlist $username, $password
$strComputer = "OA-SQL-"
For ($intInc = 1; $intInc -le 100; $intInc++) {
$serverNameOrIp = $strComputer + $intInc
Write-Host $serverNameOrIp
Restart-Computer -ComputerName $serverNameOrIp -Authentication default -Credential $cred
}

One of the first problems I saw was the computer name was OA-SQL-1 instead of OA-SQL-001. I had a formatting issue to resolve. Padding the zeros into the formatting was easy enough:

$serverNameOrIp = $strComputer + "{0:000}" -f $intInc

Windows PowerShell ISE

Another problem was that pasting the loop into the PowerShell command window and hitting enter didn't look right. It wouldn't run, either. There were '>' for all new lines and it appeared to simply hang when I would execute it. A coworker asked where I was running it and suggested that I use the Windows PowerShell ISE window instead of the more old-school PowerShell command window. I tried that and my loop executed without requiring any changes. Of course, I'd nearly lost my mind at that point…

Output

Once it was working, I made some tweaks to output the computer name and timestamp so I could see the progress. I added a sleep timer so the host machine wouldn't flounder while restarting 100 VMs. I ran into some issues with the SQL Server service timing out on restart across a few random machines. Better to wait a little longer to know they're all safe than discover that during test execution.

As seen above, I was using Write-Host in my script, until I was advised against it. It turns out that Write-Host is kind of bad.

The Solution (so far)

The following script is what I'm excuting now for various ranges of servers. It gets the job done. Could it be improved? Probably. I still have much to learn about PowerShell.

$username = "mconnors"
$password = cat C:\PowerShell\mysecurestring.txt | convertto-securestring
$cred = new-object -typename System.Management.Automation.PSCredential -argumentlist $username, $password
$strComputer = "OA-SQL-"
For ($intInc = 1; $intInc -le 10; $intInc++) {
$serverNameOrIp = $strComputer + "{0:000}" -f $intInc
$CurrentDate = Get-Date
$CurrentDate = $CurrentDate.ToString('hh:mm:ss')
$restartString = '{0} {1}' -f ($serverNameOrIp,$CurrentDate)
Write-Output $restartString
Restart-Computer -ComputerName $serverNameOrIp -Authentication default -Credential $cred
Start-Sleep 30
}

Windows PowerShell ISE showing Restart-Computer LoopWindows PowerShell ISE showing Restart-Computer Loop

What I Learned

What I believed would be a quick foray into a line or two of PowerShell turned into a larger learning experience.

Things I Know Now

  • First, to use Windows PowerShell ISE instead of just PowerShell.
  • Second, how to store and access a secure string for credentials.
  • Formatting output, such as the padded 0s and desired date/time.
  • Speaking of output, why I should use Write-Output instead of Write-Host (thanks, Allen! (b|t)
  • Finally, PowerShell might *actually* be cool. I've been hearing that for years, but now I've experienced it!
  • What's Next

    So I have this small amount of PowerShell knowledge now. It's created intrigue for me. I'm sure it can make my life easier and that I can integrate it into Visual Studio Load and Performance Testing to automate a few more steps for me. I'll be sure to share anything interesting in the future. In addition, I recently met Warren Estes at SQL Saturday Boston and heard wonderful things about his presentation, Get-Session "Powershell 101", so now is the perfect time to ask him to speak at Maine PASS!

    9 replies on “My First PowerShell : A Simple Story with Dramatic Flair”

    9 Comments (Comments are now closed.)
    1. Great post Melissa!
      Congrats on drinking the Powershell cool aid!

      I'd love to speak if you have the openings, name the date and i'll be there.

    2. Nicely done, Melissa! Your colleague & our friend Allen White helped me this weekend on a related matter. He pointed me to your post. Like you, my PoSh-fu is not formidable. Yet I'm confident we can grow strong!

      BTW, SQLDBAwithABeard, thanks for the tips & pointers.

    3. Hi,

      Excellent start. Everyone has to start somewhere and it is fantastic that you have blogged about your experience so others will feel comfortable in exploring PowerShell

      For credentials you can use Export-Clixml and Import-Clixml as Jaap shows here http://www.jaapbrasser.com/quickly-and-securely-storing-your-credentials-powershell/

      If you need help or advice be sure to try the SQL community Slack Channel via https://sqlps.io/slack There is a #powershellhelp channel in there with folks keen to assist

      I would recommend that you use the ISE or VS Code and take advantage of the snippets available in ISE. Just hit CTRL + J to see them. I have a few snippets here you are welcome to use https://github.com/SQLDBAWithABeard/Functions/blob/master/Snippets%20List.ps1

      Last and not least it would be remiss of me as an Officer not to mention the Pass PowerShell Virtual Group!! Sessions every month and a youtube channel https://sqlps.io and https://sqlps.io/youtube There are other presenters around who would be glad to present to your UG. If you take remote presentations you could ask me

      Regards

      Rob

      PS PowerShell is wayyyy coool ;-)
      PPS Write-Host is evil

    4. Hi Melissa, enjoyed the post!

      Depending on your `$PSVersionTable.PSVersion` , you can use `Write-Information` if you have version 5.

      `help Write-Information -ShowWindow`

      I don't think anybody is going to complain about you using that! :)