Tag Archives: PowerShell

Getting Started with PowerShell Core on Linux and macOS

My newest course, Getting Started with PowerShell Core on Linux and macOS, is now live on Pluralsight! This course is my eighteenth in a long line of Pluralsight courses.

I begin the course explaining the difference between PowerShell for Windows (version 5.1) and the all-new PowerShell Core (version 6.2 was used for this course), which works not only on Windows but on Linux and macOS as well. I then show how to install PowerShell Core, along with a few other key components such as Visual Studio Code, on both Linux and macOS.

Not familiar with PowerShell? No problem! I quickly cover the basics of PowerShell including cmdlets, the use of the pipeline, how to write functions, and how to put those functions in reusable scripts.

As if that weren’t enough, I show how to do some “cool things” with PowerShell Core, including working with Docker containers, SQL Server, and Azure.

For the course, I primarily used Ubuntu 19.04 and macOS Mojave. The code was also tested on Ubuntu 18.04 LTS and 18.10, as well as macOS High Sierra. In addition, I tested the Linux installs on a variety of distributions including CentOS, Manjaro, and more. The samples include markdown files with information on how to install on these other distributions.

All of the samples are included in the downloadable components of the course on Pluralsight. New with this course I have the samples also available on my GitHub site. As I move into the future the GitHub codebase will be updated with new samples and information.

Also included in the samples are several markdown files that have additional information not included in the course, such as setting VSCode on Windows to use PowerShell Core instead of Windows PowerShell 5.1 as the default terminal.

While you are up on my GitHub site be sure to check out the full list of repositories, I have a lot of examples on it, including some from previous courses such as my recent Reporting Services course. (For a full list of my courses just check out the About ArcaneCode page on this site.)

Note the sample file on Pluralsight will remain static, so if someone watches the course their samples will reflect what is in the course. For the latest updated samples see the GitHub site referenced above.

What? You don’t have a Pluralsight subscription yet? Well, no worries dear reader, just email me, free @ arcanetc.com and I can send you a code good for 30 days with which you can watch all 18 of my courses, plus anyone else’s course at Pluralsight.

Downloading Files with PowerShell and Invoke-WebRequest

Last weekend I was at the Atlanta Code Camp, giving a presentation on PowerShell for Developers. One of the attendees emailed me, asking for a good example of being able to download multiple files from a website.

To do so, we’ll use the Invoke-WebRequest cmdlet. But first, we’ll setup a directory to hold our output. For all my demos I have a root folder, C:\PowerShell. For this article I’ve created a subfolder called Invoke-WebRequest-Demo. The first thing my code does is sets a variable to point to this folder, then changes the current location to it.

$dir = 'C:\PowerShell\Invoke-WebRequest-Demo'
Set-Location $dir

Next, we’ll need a variable to point to the root URL of the website we wish to download from. This will be everything except for the actual file name.  For this demo I will use files from the No Agenda Show podcast. Not only is it a cool podcast, but they have an unrestricted license for their material meaning I can freely reuse it for demo purposes. Each episode of the podcast has it’s own image, so we’ll download images from a few recent episodes.

After I place the base URL into a variable, I create an array, each item in the array having the name of a file to download.

$baseUrl = 'http://adam.curry.com/enc/'
$files = '1537127586.656_na-1069-art-feed.png',
          '1536868338.385_na-1068-art-feed.png',
          '1536264075.484_na-1066-art-feed.png'

Now that everything is setup, we use a simple foreach loop to iterate over the array and download each file via Invoke-WebRequest.

foreach ($file in $files)
{
   Write-Host "Downloading $file"
   $dlUrl = "$($baseUrl)$file"
   $dlPath = "$($dir)$file"
   Invoke-WebRequest $dlUrl -OutFile $dlPath
}

And that’s all there is to it. If you want to learn more about downloading files via the web, this code was extracted from my Testing PowerShell with Pester course on Pluralsight. In it I test a module which gets the RSS feed, then downloads images and audio files for the No Agenda show. Just go to the About Me link at the top and you’ll find a complete list of all my Pluralsight courses.

Speaking of Pester, you might also appreciate the introduction to Pester series of articles I’m currently authoring for Red Gate’s Simple Talk website. You can find a link via the same About Me page, or just jump directly there by going to http://arcanecode.red to see my articles.

Get Notified When Your Home Router IP Address Changes–With PowerShell!

Readers of my blog over may recall I’m an amateur (ham) radio operator (N4IXT that’s me!). One of my ham radio buddies has a cool radio that can be remotely controlled over the internet. With their software he just enters the IP address of his home (his home router that is, after we setup some port forwarding), and he can connect to and operate his radio remotely, make contacts around the world and more.

The tricky part of all this came in the sentence enters the IP address of his home…. His home router tends to change its address on the whim of his service provider. If the router reboots, it will definitely have a new address when it comes up. You may be thinking “well just get a static IP address”. Unfortunately, his internet service provider can’t give him this, something about him using a bonded pair of 10 mb lines to get 20 mb prevents it.

So how could he get notified when his IP address changes? Well I was fairly certain I could solve his problem with some simple PowerShell scripting!

I’ve now published the solution on my GitHub site:

https://github.com/arcanecode/PowerShell/tree/master/IPAddressMailer

Rather than going more in-depth here, the ReadMe has the full details describing the scripts that were created. In addition the code is heavily commented.

In addition to solving this particular problem, the code could be used as a template for:

  • Getting the IP Address of your home router
  • Sending emails via GMail
  • Setting up tasks in the Windows Task Scheduler

Hopefully other people with similar issues will find this little project as useful as my ham radio buddy has!

Azure PowerShell PlaybooK: Azure SQL–Now on Pluralsight!

My latest course is now available on Pluralsight! It’s the Azure PowerShell Playbook: Azure SQL. If you aren’t familiar with Pluralsight’s Playbook format, they are fast past courses that are almost 100% demo driven. They are meant to be consumed quickly, my course is just one hour and four minutes long. Great lunchtime viewing!

This course shows you how to use PowerShell to manage and migrate your on premises database up to Azure SQL. In brief, you’ll learn how to:

  • Create resource groups
  • Create and work with Storage Accounts
  • Create a SQL Server in Azure SQL
  • Package up your local database into a bacpac file
  • Import your bacpac file into a new Azure SQL database
  • Execute commands against your new Azure SQL database
  • Cleanup! I even show how to remove everything you’ve created, individually or as a whole

And all of this with PowerShell!

Additionally, I’ve included functions for just about everything listed, so (assuming your subscription gives you access to the samples) you’ll have a great starting point for your own library of cmdlets. (All the code for the functions appears on screen, so if you have to you could always pause and type it in.)

You can find my new course at:

https://www.pluralsight.com/courses/azure-powershell-sql-playbook

I also wrote an article for RedGate’s SimpleTalk website that aligns well with this course. I dive deeper into the restartability aspect of the way the functions were coded, something I couldn’t get deep into with the video course due to time constraints.

https://www.red-gate.com/simple-talk/sysadmin/powershell/powershell-functions-reusability-restartability-azure/

What’s that? Yes you in the back row, you say you don’t have a Pluralsight subscription? Well no worries, just email me, free@arcanetc.com and I’ll be glad to send you a code that will be good for 30 days at Pluralsight. During that time you can watch my courses, indeed you can watch any course at Pluralsight.

Creating Azure Resource Groups Safely With PowerShell

A few posts back I mentioned that I had become a SimpleTalk author, and that my first post for them was PowerShell Functions For Reusability and Restartability in Azure (read it at http://bit.ly/acred01)

I’ve created a companion video for it, which focuses on creating Resource Groups. You can see it below, or on YouTube at http://bit.ly/acredyt01.

As I mention in the video, you can find the code samples on my github site, at http://bit.ly/acredgit01

I’m Now a SimpleTalk Author!

RedGateSimpleTalkLogoVerticalAdding to my other activities, I’m now writing for SimpleTalk, RedGate’s community hub. My first article just went live.

PowerShell Functions for Reusability and Restartability in Azure is the title, in it I describe how to implement the concepts of reusability and restartability in PowerShell. The functions were written against the Azure platform, however the concepts are valid for any PowerShell implementation. You can read the full article at:

https://www.red-gate.com/simple-talk/sysadmin/powershell/powershell-functions-reusability-restartability-azure/

If you want to keep up with all my articles, I’ve setup a special URL which will jump you to my author page at SimpleTalk:

http://arcanecode.red

It’s small now, but expect it to grow quickly. Happy reading!

Eric Ligman’s FREE Microsoft eBook Giveaway–Revising the download script

Every year, Eric Ligman, director of Sales Excellence for Microsoft, creates a blogpost in which he gives away tons of FREE Microsoft eBooks. This year has 361 in the list.

You name it, it’s in the list. SQL Server, Azure, PowerShell, .NET, BizTalk, SharePoint, Windows Server, and more. You can find Eric’s post at:

https://blogs.msdn.microsoft.com/mssmallbiz/2017/07/11/largest-free-microsoft-ebook-giveaway-im-giving-away-millions-of-free-microsoft-ebooks-again-including-windows-10-office-365-office-2016-power-bi-azure-windows-8-1-office-2013-sharepo/#comments

While there are individual links to each file, what if you want every one of them? He explains on the post why he doesn’t provide a big zip file. He does, however, provide a PowerShell script (attributed to David Crosby) that will do the job.

However, I found some issues with the script. Not that it didn’t work, it did, but there were several things I felt could be done to improve it.

First, there was no progress message issued during the download. As a user, I had no idea which file I was on, so had no concept of how much longer it would take. Thus, I’ve added a little progress message.

I then thought “Hmm, what if my downloads were interrupted, I don’t want to have to start all over”. So, I added some code that sees if the file we’re downloading already exists. This way it won’t re-download a file it already has.

But then another problem arose. What if it had partially downloaded a file? Just checking the file names wouldn’t catch that. So I added further code to compare the file size at the source with the file size on disk. If different, then it will re-download.

So far so good, now it will skip the file only if the file name is already on the local disk, and the file sizes match.

I now encountered my next concern. Crappy internet. I live out in the country, and while I love my privacy and rural living, my internet sucks. It is prone to go down or drop packets. If it had issues during a  download I didn’t want it to crash, but instead go onto the next file.

Thus I added a try/catch error handler, which displays an error message and continues on.

At this point I thought I was done. Just I was about to call it finished though, a typical afternoon Alabama thunderstorm came up. Kaboom! House rattled and power blinked.

This presented my final concern, what if the power went out? I’d want to know where it got to with the downloads. So I added some further code such that when the downloading starts it creates a new log file and appends each message to it.

I realize some of you have superfast gigabit internet and will be able to download these almost instantly. (I hate you by the way. #jealous). Therefore I made logging optional, so it wouldn’t create an extra file if you didn’t want it. Just set the $log variable to $false, and it will skip logging.

So there you go, a revised download script that will handle stopping and restarting the script gracefully, will look for errors, and adds logging so you can track progress.

You’ll find the revised script on my GitHub site, in the PowerShell folder:

https://github.com/arcanecode/PowerShell

Just look for the file “Eric Ligmans Microsoft eBook Giveaway Revised Download Script.ps1

There’s also a readme style file by the same name, which echoes this blog post.

What happened to Save-AzureRmProfile?

I’ve been working a lot in the Azure PowerShell area of late. One thing I wanted to be able to do is have my scripts login automatically to Azure. In many examples the cmdlet Save-AzureRmProfile was used to save your Azure credentials, then later you could use Import-AzureRmProfile to import them.

But, when I attempted to run Save-AzureRmProfile I got the error ‘Save-AzureRmProfile is not recognized as the name of a cmdlet, function, script file, or operable program’.  Huh? I checked the docs, and it does include a listing for Save-AzureRmProfile.

https://docs.microsoft.com/en-us/powershell/module/azurerm.profile/save-azurermprofile?view=azurermps-3.8.0

This is a case of the PowerShell AzureRM module getting ahead of the docs. After beating my head against the wall, I found the cmdlets had been replaced with the new noun of AzureRmContext.

To use them, first login to Azure manually. Then, use the new Save-AzureRmContext to save your information to a file.


# Setup – First login manually per previous section
Add-AzureRmAccount

# Now save your context locally (Force will overwrite if there)
$path = "C:\Azure\PS\ProfileContext.ctx’
Save-AzureRmContext -Path $path -Force

Once that’s done, from then on you can use the Import-AzureRmContext to automate the login.


# Once the above two steps are done, you can simply import
$path = C:\Azure\PS\ProfileContext.ctx’
Import-AzureRmContext -Path $path

Be warned, this does present a security issue. If someone were to steal your context file, they could then login as you. You need to be sure your context file is stored in a safe location no one can get to.

IT ops and news talk–Episode 3 Secure that Jump Server

I owe everyone an apology, I missed blogging about this at the time it occurred. Last December I was a guest on Don Box’s podcast, “IT ops and news talk”. I appeared on Episode 3, Secure that Jump Server.

In the podcast we discuss the testing of PowerShell code with Pester. After that we got into an interesting discussion on the current state of DevOps. Give it a listen I think you’ll enjoy, it’s about half an hour in length. Don is a great interviewer it was a lot of fun.

As a follow on to the discussion, you might want to learn more about Pester. I have a complete course on the subject in my Pluralsight course Testing PowerShell with Pester. If you don’t know anything about PowerShell, but want to learn, then I’d suggest my Beginning PowerShell Scripting for Developers. Note that even though it says “…for Developers” we don’t mean programmers (although it could), instead it refers to people who wish to develop scripts in PowerShell.

What? What was that? You in the back row waving your hand? You say you don’t have a Pluralsight subscription? Hey, no problem. Just email me, free <at> arcanetc.com and I can send you a code good for 30 days of free access to Pluralsight, with which you can watch not just my courses, but any course from Pluralsight’s library of over 5,000 courses.

Zero to Hero with PowerShell and SQL Server–Precon at DevDataDay

{dev = data} DAY Birmingham 2016Are you looking to learn about PowerShell? Do you use SQL Server? Then boy have I got a bargain for you!

I’m doing a precon for our upcoming devdataday event. The precon will take place on Friday, August 19th, the day before devdataday.

This is designed to be a course for the PowerShell novice. No prior knowledge required! I’ll start the day with an introduction to PowerShell, basic usage and cmdlets. Next we’ll dive into programming scripts in PowerShell, starting with the basics of loops, conditional logic, and functions. We’ll move into advanced topics, including creating your own reusable modules and testing them.
The real meat of the day comes next, learning how to interact with SQL Server from PowerShell. Both the SQL Provider and SMO (SQL Management Objects) DLL library will be covered in full. Most importantly you’ll learn how to read the online SMO library documentation and how to convert the examples into PowerShell syntax. The day will culminate by building a reusable module for working with many SQL Server common tasks, such as T-SQL code generation, or performing health checks on your servers.
The day will wrap up with a look at Pester, the new open source PowerShell testing tool. You’ll see how to test all the code you generated during the day. Don’t be left behind, learn how to leverage the power of PowerShell in your SQL Server environment.

To register, just go to https://devdatadaypowershellprecon.eventbrite.com/

Updating and Sorting the Microsoft Word QuickStyle Gallery with PowerShell

 

Introduction

I’m currently working on my fifth book, this one is for the folks at PACKT Publishing. It’s still in the early stages so I can’t say too much, but if you like SQL Server Reporting Services, you’ll enjoy this book.

All book publishers use their own set of styles in Microsoft Word to allow them to control the layout of the book when it goes to print. There will be special styles for italics, bold, code listings, and more. The techniques here though are applicable to more than just book or magazine publishers.

You’re company may have a limited set of styles it approves for use in internal documents. Perhaps you need to adjust your set of QuickStyles based on what type of document you are authoring. One set for company memos, another for technical documentation, and yet another set of styles for love letters.

Using the PowerShell script in this post can make it easy for you to setup your QuickStyle gallery based on the type of document you are creating.

When working on the book it’s been convenient to clear out the list of built in styles in the QuickStyle gallery, and replace them with the ones I need for my publisher. Doing it manually though is a time consuming process.

Unfortunately I experienced further oddities. Every time I got a chapter back from my editor, my curated list of styles had been replaced. I don’t think it’s anything my editor did deliberately, she’s a really nice lady. I believe it’s some quirk of Word, related to the fact we appear to be on different versions. I knew I couldn’t keep sucking up time adding, removing, and sorting these each time I got a chapter back. I needed a way to automate.

I did some looking online, but couldn’t find a total solution that fit my needs. I found some VBA code for adding and removing styles from the QuickStyle gallery, but nothing on how to sort items in the gallery.

Being the geek I am means I love PowerShell. So I rolled up my sleeves and dove into the Microsoft Word object model. Specifically, I focused in on the Style object.

I’m a firm believer in the adage that you should completely understand any script you cut and paste off the web. So while I’ll place the complete script at the bottom of this post for easy copy / paste, I’ll step through it first to explain the various components.

Before I proceed, one important note: updating the QuickStyle gallery using the code below only updates one specific document. Other existing Word documents, or new documents you create, won’t be affected by this script.

1. Set a variable to hold the file name to update.

$wordFile = 'C:\Book\Chapter02.docx'

I start by setting a variable to hold the file name of the Word document I want to update. At some point I’ll probably turn this into a module and make this a parameter to the main routine, but for today this gets the job done and is easy for people who also want to use the script.

2. Back up the file.

$bakfile = $wordFile + '.bak'
Copy-Item -Path $wordFile -Destination $bakfile -Force

 

The next thing I do is make a backup. First, if something happens I want to be able to go back. Second, you should always backup before doing something like this, so making it built in prevents issues (as well as avoiding nasty comments on this post). Winking smile

3. Load an array with your desired styles.

$myStyles = @( 'Normal [PACKT]',
               'Numbered Bullet [PACKT]',
               'Screen Text [PACKT]',
               'Code In Text [PACKT]',
               'Code Within Bullets [PACKT]',
               'Code listing [PACKT]',
               'Italics [PACKT]',
               'Figure [PACKT]',
               'Chapterref [PACKT]',
               'Bold [PACKT]',
               'Heading 1,Heading 1 [PACKT]',
               'Heading 2,Heading 2 [PACKT]',
               'Heading 3,Heading 3 [PACKT]',
               'Tip [PACKT]',
               'Layout Information [PACKT]',
               'Figure Caption [PACKT]',
               'Part Heading [PACKT]'
             )

Next up, I load a list of the styles I want to place in my QuickStyles. Also, the array should have the styles in the order you wish them in the gallery. This list is specific to the styles I use most frequently on this project. To see the complete list of available styles within your document, open up Word and load your document. On the bottom right of the QuickStyle gallery is a little button that will bring up the style list.

SNAGHTML3fa2582

Once open, you’ll see a list of all the styles available in this document. Here’s the top of the list, scrolling down will reveal a LOT of styles.

image

The names that appear in here are the ones that you’d use in the array. Just type them into the array declaration exactly as they appear in the Styles list.

4. Load up style type enumerations.

# Load up the style type enumerations.
$wdStyleTypeParagraph = 1  # Paragraph style.
$wdStyleTypeCharacter = 2  # Body character style.
$wdStyleTypeTable = 3      # Table style.
$wdStyleTypeList = 4       # List style.

When we set the property to place something in the QuickStyles gallery, we have to be careful as only certain types of styles may appear in the QuickStyles area. We check this using a property of the Style object called Type, which has several enumerations. While I probably could have loaded the enumerations from the class, sometimes simple is good. Since the values for the enumerations were easily found in MSDN, I just replicated them as variables in PowerShell.

In this script we only use two of them, but for completeness I listed them all should I (or you) ever want to reuse this script as a basis for other Word work with PowerShell.

5. Create a new instance of Word.

$word = New-Object -ComObject Word.Application

The next step is pretty simple. All we do is create a new object of type Microsoft Word, and put a reference to it in the $word variable. Creating the new Word object is akin to opening up Word without a document in it.

6. Make Word visible.

$word.Visible = $true

This next step is optional. By default, when you create a new Word object it is not displayed to the user. If you are applying this to a whole batch of Word documents, I’d omit this step as it will slow down the process. For just one document though it’s not much of an impact. And to be honest, it’s pretty cool to watch Word as it removes then adds the styles.

7. Open the Word document to update.

$doc = $word.Documents.Open($wordFile)

This is probably an obvious step, but we need to load the document we want to update into our Word object. The Open method returns a new variable of type Document (MSDN). The $word variable represents Microsoft Word, the $doc variable represents the specific DOCX file (or DOC) that we want to update.

8. Reset all styles to remove them from the QuickStyles; additionally reset their priority.

foreach ($sty in $doc.Styles)
{
  # Only these two types can be QuickStyles
  if ( ($sty.Type -eq $wdStyleTypeCharacter) -or ($sty.Type -eq $wdStyleTypeParagraph) ) 
    { $sty.QuickStyle = $false }
  
  $sty.Priority = 100
}

This next section is important to understand. The document has a collection of Style objects, stored in the Styles collection. We start by looping over each Style in the document.

Each Style object has a property named QuickStyle, which has a value of true or false. This flag sets whether a style should appear in the QuickStyles gallery (true) or not (false). If you recall from the enumerations section above, there are some styles that cannot be in the QuickStyle area. If we were to attempt to set the QuickStyle property on these, we would get an error (even if setting it to false). Hence the need to check the Type property and only attempt to set the QuickStyle property for those Styles who can appear in the QuickStyle list.

The real challenge in terms of investigation came in ordering the styles within the QuickStyle area. To order them manually, start by clicking the lower right button on the styles dialog.

image

When the Manage Styles window appears, click on the Recommend tab.

image

 

To the left of each style name, you see a number or the word “last”. This number represents the order in which it will recommend styles to you. In other words, the order in which they will appear in the QuickStyles gallery.

With the buttons at the bottom you can rearrange the order in which they appear. Note, it is possible to have multiple styles with the same value. If that happens Word will arrange all styles with the same recommended value alphabetically. The word “last” simply tells Word to put these styles at the very end of the QuickStyles gallery.

It took a lot of research, but I finally figured out the “Recommended” value is contained in a property called “Priority” within the Style object. The word last is represented by the value 100, giving a valid range of 1 to 100 for Priorities.

In the above code I am setting all styles, even the styles built into Word, to 100 which is the equivalent of ‘last’. For my situation this is fine, as I’m only going to be using this specific set of styles in this specific document so I don’t really care about the built in ones. Even if I changed my mind later, all I would need to do is add the built in styles (or any new ones I create) to my array and rerun the script.

At this point then we’ve removed all styles from the QuickStyles gallery, as well as set their priorities to ‘last’. Now it’s time to add the styles we want and order them.

9. Add our desired styles to the QuickStyle gallery and order them.

$priority = 1
foreach ($mySty in $myStyles)
{ 
  # Setting to true will make the style appear in the QuickStyle gallery
  $doc.Styles($mySty).QuickStyle = $true
  # The priority is an integer which determines the sort order within the QS gallery
  $doc.Styles($mySty).Priority = $priority++
}

With this next code, we are going to add our list of styles as well as sort them. It starts by setting the starting point for our Priority (the order) to the value 1, our starting point.

We then enter a foreach loop, where we iterate over each style in the array we assembled back in step 3. Within the loop, we set the QuickStyle property to true. Naturally I’ve only placed style names into the array that are allowed to be in the Style gallery. If you have doubts you could add a check against the Type property, as we did in Step 8, before setting the QuickStyle property.

Note that the Styles collection allows us to address a style by passing in the name of the style. When you assemble the array then, it is important to match the name that appears in Word exactly so it can find it in the array.

Next up is setting the Priority. Again, the Priority is the order in which styles are displayed in the gallery. After assigning the value I use the ++ operator to increase the value for the next iteration of the loop.

Note that since I have far less than 100 items, I don’t have any error checking for exceeding the 100 mark. If you have more than 100 styles for your QuickStyles you’ll need to modify this code. Although quite frankly if you have more than 100 styles in your QuickStyles, it’s not really that quick and you should probably rethink your editing habits. Winking smile

10. Save your work.

$doc.Save()

As the next to the last step, we’ll have the script go ahead and save the document.

11. Close Word (optional).

$doc.Close()

If you modified this script to work in a big loop so you could batch updates, or perhaps you will just want to apply the fix and go on, you could have the script go ahead and close Word. On the other hand, if after applying the updates to the style gallery you’ll immediately want to start editing, just comment out this last step. 

Summary

A final note to close this out, I’ve tested the script with Word 2016 and PowerShell 5.0. From the documentation in MSDN the same techniques also apply to Word 2013. I’ve not tried it on previous versions of Word, but in theory it should work all the way back to the version in which the QuickStyle gallery was introduced. If you’ve tried it on an older version let us know the results by posting a comment below.

Additionally, while I’ve written this on PowerShell 5.0 I’m not doing anything new or unusual, it should work as far back as PowerShell 3.0.

Below is the script, complete with comments to act as a reminder of the explanations above. Just copy and paste, modify the $wordFile and the $myStyles array, and you should be good to go.

 


The Script

<#-----------------------------------------------------------------------------
  Updating and Sorting the Microsoft Word QuickStyle Gallery, ArcaneCode style!

  Author: Robert C. Cain | @ArcaneCode | arcane@arcanetc.com
          http://arcanecode.com
 
  This script is Copyright (c) 2016 Robert C. Cain. All rights reserved.
  No warranty or guarantee is implied or expressly granted. Use at your own
  risk. 

  This script may not be reproduced in whole or in part without the express
  written consent of the author. 
-----------------------------------------------------------------------------#>

# Set the path / file name of the file you want to update
$wordFile = 'C:\Book\Chapter02.docx'

# Make a backup before we do changes. Note if the BAK exists it will be overwritten
$bakfile = $wordFile + '.bak'
Copy-Item -Path $wordFile -Destination $bakfile -Force

# Create an array of the styles you wish, in the order you want 
# them to appear in the QuickStyle area 
$myStyles = @( 'Normal [PACKT]',
               'Numbered Bullet [PACKT]',
               'Screen Text [PACKT]',
               'Code In Text [PACKT]',
               'Code Within Bullets [PACKT]',
               'Code listing [PACKT]',
               'Italics [PACKT]',
               'Figure [PACKT]',
               'Chapterref [PACKT]',
               'Bold [PACKT]',
               'Heading 1,Heading 1 [PACKT]',
               'Heading 2,Heading 2 [PACKT]',
               'Heading 3,Heading 3 [PACKT]',
               'Tip [PACKT]',
               'Layout Information [PACKT]',
               'Figure Caption [PACKT]',
               'Part Heading [PACKT]'
             )

# Load up the style type enumerations.
$wdStyleTypeParagraph = 1  # Paragraph style.
$wdStyleTypeCharacter = 2  # Body character style.
$wdStyleTypeTable = 3      # Table style.
$wdStyleTypeList = 4       # List style.

# Create a new instance of Word
$word = New-Object -ComObject Word.Application

# This is optional, if set it will display Word and let you watch the fun
$word.Visible = $true

# Open the document you wish to reset the styles for
$doc = $word.Documents.Open($wordFile)

# First, reset all styles to not be Quick Styles, 
# and set the priorty to 100 (which will be 'last' in Word)
foreach ($sty in $doc.Styles)
{
  # Only these two types can be QuickStyles
  if ( ($sty.Type -eq $wdStyleTypeCharacter) -or ($sty.Type -eq $wdStyleTypeParagraph) ) 
    { $sty.QuickStyle = $false }
  
  $sty.Priority = 100
}

# Now set the styles like we want 'em!
$priority = 1
foreach ($mySty in $myStyles)
{ 
  # Setting to true will make the style appear in the QuickStyle gallery
  $doc.Styles($mySty).QuickStyle = $true
  # The priority is an integer which determines the sort order within the QS gallery
  $doc.Styles($mySty).Priority = $priority++
}

# Save the document. 
$doc.Save()

# Close up word (Optional, if you want to start editing 
# right away you can comment this out)
$doc.Close()

I have to give a shout out to the folks at Sapien for their PowerShell Studio tool. It has a great feature, Copy HTML, which takes pieces of PowerShell code, copies it to the clipboard and in the process adds the appropriate HTML tags to do the nice coloring.

SQL Saturday 498 Chattanooga

Do you like PowerShell? SQL Server? Are you anywhere close to Chattanooga TN? Then don’t miss this SQL Saturday, June 25th 2016.

I’m giving two sessions (yes two for the price of one!). The first session will be PowerShell 201. It covers advanced concepts you need to know, such as debugging, remoting, security, and code signing.

The second session will educate on using PowerShell with SQL Server. We’ll cover the use of the SQL Provider (SQLPS) as well as the more advanced SQL Management Object library (SMO).

You’ll find the full schedule here, as well as links to register, directions, and the like.

http://www.sqlsaturday.com/498/Sessions/Schedule.aspx 

Best of all, my demos are already uploaded, so you can download early and play along during the presentation!

I’ll mention Chattanooga is a great vacation town, so bring your whole family. There’s a huge aquarium, discovery museum, Lookout Mountain, and tons of attractions to keep them occupied while you’re having fun at SQL Saturday.

Testing PowerShell with Pester

My newest Pluralsight course is now live, Testing PowerShell with Pester! In this 4 hour course I walk through a complete introduction to Pester, to showing you how to both modify existing code as well as create a brand new module using Pester to guide development using the Test Driven Development methodology. You’ll find all the details at:

https://www.pluralsight.com/courses/powershell-testing-pester

Not as familiar as PowerShell as you’d like to be? I have several other courses at Pluralsight that may help, especially my Beginning PowerShell Scripting for Developers course. You can see my full catalog of courses at:

https://www.pluralsight.com/authors/robert-cain

What? You don’t have a Pluralsight subscription? No problem, just shoot me an email to free<at>arcanetc.com and I’ll send you a code good for a 30 day trial to Pluralsight, with which you can watch my courses, or any of the great courses by my fellow Pluralsight authors.

Much TODO with Sapien PowerShell Studio 2016 and Pester

I’ve been working a lot with Peter lately, and as I’ve blogged about before I use Sapien’s PowerShell Studio tool. The new 2016 version has an interesting new capability that while it may seem small, works extremely well when developing Pester tests.

When I create a Pester test, for example for a function, I outline all the things I need to test. Once I’ve created the list, I then go into my test script, and create It statements for each test.

Pester has a switch you can pass to It called –Pending. This will cause Pester to skip over the test. This allows you to stub out needed tests without them interfering with your code. Pester itself will even display pending tests in its output.

    It ‘is a test yet to be implemented’ –Pending {

    }

Knowing you have a test to implement, and being able to quickly find where that code is, are two different things. Tests can get rather long rather quickly. I also admit I don’t always develop the tests linearly. For example, I may develop part of a unit test, then copy that code lower in the script and adapt it for the acceptance test. Then go back up to the next It assertion I need to work on.

That’s where a nifty little feature in the new 2016 version of PowerShell Studio comes in handy. When you add a #TODO: statement (pound sign TODO followed by a colon), it will appear in the navigation drop down in the PowerShell Studio editor.

Here’s a little example. In the screen capture below, you see I have the first test, which has been implemented. Following are two more tests using the –Pending switch.

image

 

Here’s a slightly closer view. You can see the various #TODO: comments. When I click in the navigation drop down, these TODOs show up, allowing me to jump to them easily.

image

This has been surprisingly useful when working with Pester. While the example shown above seems trivial, some of my tests are hundreds of lines long. Being able to quickly navigate to tests I still need to implement keeps my workflow going ahead at a good pace, and keeps me from getting frustrated trying to find the next test I want to implement.

I also have to say the new dark color theme leaves me all warm and fuzzy inside. Smile

Presentation for the SQL PASS PowerShell Virtual User Group

Today (April 6, 2016) I was pleased to present “High Class PowerShell” to the SQL PASS PowerShell Virtual User Group. In it I covered the creation of custom objects and classes in PowerShell.

The first question everyone asks is “where’s the code”. I’ve pasted the code samples for the main demo at the bottom of this post. You can review it here, or copy – paste into your own ISE.

The closing code on calling classes created in PowerShell was discussed in these two blog posts, so I won’t repeat it here.

During the presentation, I mentioned this code is a small subset of my larger Pluralsight course, Beginning PowerShell Scripting for Developers. You’ll find it and my other courses here:

https://www.pluralsight.com/authors/robert-cain

Don’t have a Pluralsight subscription? No problem, Pluralsight has graciously provided me some trial codes I can share with you. These will allow you a one month, no obligation trial with which you can watch any of my courses, or indeed any course found on Pluralsight. Just email free@arcanetc.com to request your code. (If you didn’t get to see the presentation, no worries, you can still email us for the Pluralsight trial code!)

When the video of the presentation goes live I will have a follow up blog post, meanwhile, and without further ado, here is the code sample.

<#-----------------------------------------------------------------------------
  High Class PowerShell

  Author: Robert C. Cain | @ArcaneCode | arcanecode@gmail.com
          http://arcanecode.com
 
  This module is Copyright (c) 2016 Robert C. Cain. All rights reserved.
  The code herein is for demonstration purposes. No warranty or guarentee
  is implied or expressly granted. 
  This module may not be reproduced in whole or in part without the express
  written consent of the author. 
-----------------------------------------------------------------------------#>

#-----------------------------------------------------------------------------#
# Demo 0 -- Object Oriented Terminology
#-----------------------------------------------------------------------------#
<#
                                  class = blueprints
                             properties = describe
                    methods (functions) = actions
                                 object = house
             instantiating a new object = building a new house
each object is an instance of the class = each house is a copy of the blueprint


#>

#-----------------------------------------------------------------------------#
# Demo 1 -- Create a new object 
# This is the most common method of creating objects
#-----------------------------------------------------------------------------#

# The most basic way to create a custom object is to build a hash table with 
# the properties. Using [ordered] will ensure the properties remain in the
# desired order. 
$properties = [ordered]@{ Schema = $Schema
                          Table = $Table
                          Comment = $Comment
                        }

# Start by creating an object of type PSObject
$object = New-Object –TypeName PSObject -Property $properties

# Now you can access it's properties
$object.Schema = 'MySchema'
$object.Table = 'MyTable'
$object.Comment = 'MyComment'
$object


# Much of the time, the process of creating a custom object is wrapped in a 
# function to make it easier. 
function Create-Object ($Schema, $Table, $Comment)
{
  # Build a hash table with the properties
  $properties = [ordered]@{ Schema = $Schema
                            Table = $Table
                            Comment = $Comment
                          }

  # Start by creating an object of type PSObject
  $object = New-Object –TypeName PSObject -Property $properties

  # Return the newly created object
  return $object
}

$myObject = Create-Object -Schema "MySchema" -Table "MyTable" -Comment "MyComment"
$myObject

# Display in text. If you try to reference a property in text, it doesn't
# work quite right.
"My Schema = $myObject.Schema"

# Instead, wrap it in $() to access a property. This will force PowerShell to
# evaluate the object.property call as an expression, and return that to the
# string
"My Schema = $($myObject.Schema)"

# Changing properties is easy
$myObject.Schema = "New Schema"
$myObject.Comment = "New Comment"
$myObject


#-----------------------------------------------------------------------------#
# Demo 2 -- Create a new object by adding properties one at a time
# In the previous demo a property hash table was used to generate the object
# Behind the scenes it does the equivalent of what this function does
#-----------------------------------------------------------------------------#
function Create-Object ($Schema, $Table, $Comment)
{
  # Start by creating an object of type PSObject
  $object = New-Object –TypeName PSObject

  # Add-Member by passing in input object
  Add-Member -InputObject $object `
             –MemberType NoteProperty `
             –Name Schema `
             –Value $Schema

  # Alternate syntax, pipe the object as an input to Add-Member
  $object | Add-Member –MemberType NoteProperty `
                       –Name Table `
                       –Value $Table
  
  $object | Add-Member -MemberType NoteProperty `
                       -Name Comment `
                       -Value $Comment

  return $object
}

$myObject = Create-Object -Schema "MySchema" -Table "MyTable" -Comment "MyComment"
$myObject

# No difference in the way we display values or assign them
"My Schema = $($myObject.Schema)"

$myObject.Schema = "New Schema"
$myObject

# Show this is a PSCustomObject
$myObject.GetType()

# Each property has it's own .NET Data Type. 
# Note this is different from the Member Type
$myObject.Comment.GetType()

# To see the Member Types, pipe to Get-Member
$myObject | Get-Member 


#-----------------------------------------------------------------------------#
# Demo 3 -- Add alias for one of the properties
#-----------------------------------------------------------------------------#
Clear-Host

# Demo 3 -- Add alias so both Comment and Description reference the same thing
Add-Member -InputObject $myObject `
           -MemberType AliasProperty `
           -Name 'Description' `
           -Value 'Comment' `
           -PassThru  # Use passthru to see the new object

"Comment......: $($myObject.Comment)"
"Description..: $($myObject.Description)"

# Change the value
$myObject.Description = 'This is now a description'
"Comment......: $($myObject.Comment)"
"Description..: $($myObject.Description)"

# Demo 3 -- Add script block to object
Clear-Host
$block = { 
           $fqn = $this.Schema + '.' + $this.Table 
           return $fqn
         }

Add-Member -InputObject $myObject `
           -MemberType ScriptMethod `
           -Name 'FullyQualifiedName' `
           -Value $block `
           -PassThru

# Using Get-Member we can see the newly added script method
$myObject | Get-Member 

# When calling our new function, parens are very important, without them 
# PowerShell will just display the function
$myObject.FullyQualifiedName()  


#-----------------------------------------------------------------------------#
# Demo 4 -- Script block with parameters
#-----------------------------------------------------------------------------#
Clear-Host
$block = { 
           param ($DatabaseName)
           $fqn = "$DatabaseName.$($this.Schema).$($this.Table)"
           return $fqn
         }

Add-Member -InputObject $myObject `
           -MemberType ScriptMethod `
           -Name 'DatabaseQualifiedName' `
           -Value $block 

# Place any parameters within the parens
$myObject.DatabaseQualifiedName('MyDBName')  


#-----------------------------------------------------------------------------#
# Demo 5 -- Script Property
#-----------------------------------------------------------------------------#
# These are analogues to properties in C#, with a Getter and Setter function
Clear-Host

# Add a property we can work with
Add-Member -InputObject $myObject `
           –MemberType NoteProperty `
           –Name AuthorName `
           –Value 'No Author Name'

$myObject # Show the added property

# This defines the GET for this property
$getBlock = { return $this.AuthorName }

# This defines the SET. Adding a simple check for the name
$setBlock = { 
              param ( [string]$author )
                            
              if($author.Length -eq 0)
              { $author = 'Robert C. Cain, MVP' }
              
              $this.AuthorName = $author
            }

# Now add the custom Get/Set ScriptProperty to the member
Add-Member -InputObject $myObject `
           -MemberType ScriptProperty `
           -Name Author `
           -Value $getBlock `
           -SecondValue $setBlock

# Demo its use when passing as value
$myObject.Author = 'ArcaneCode'
"`$myObject.Author now equals $($myObject.Author )"

# Now pass in nothing to see the setter functionality kicking in
$myObject.Author = ''
$myObject.Author

# Unfortunately the original property is still available, and thus
# the custom get/set can be bypassed
$myObject.AuthorName = ''
$myObject.Author                       # Author reflects value of AuthorName

$myObject.Author = ''                  # Going thru scriptproperty sets correctly
$myObject.Author


#-----------------------------------------------------------------------------#
# Demo 6 -- Set default properties
# Note: Thanks to Poshoholic for his cool code sample, see it at:
# http://poshoholic.com/2008/07/05/essential-powershell-define-default-properties-for-custom-objects/
#-----------------------------------------------------------------------------#
Clear-Host

# When just running the object, it displays all properties
$myObject

# If you have a lot, this can get overwhelming. Instead you can define a
# default set to display.

# Define the property names in an array
$defaultProperties = 'Schema', 'Table', 'Comment', 'Author'

# Create a property set object and pass in the array 
$defaultPropertiesSet `
  = New-Object System.Management.Automation.PSPropertySet(`
      ‘DefaultDisplayPropertySet’ `
      ,[string[]]$defaultProperties `
      )

# Create a PS Member Info object from the previous property set object
$members `
  = [System.Management.Automation.PSMemberInfo[]]@($defaultPropertiesSet)

# Now add to the object
$myObject | Add-Member MemberSet PSStandardMembers $members

# Now the object will just display the default list in standard output
$myObject

# Little easier to read in a list
$myObject | Format-List

# To display all properties, pipe through format-list with wild card for property
$myObject | Format-List -Property *


#-----------------------------------------------------------------------------#
# Demo 7 - Create a class from .Net Code and call a static method
#-----------------------------------------------------------------------------#

# Load the contents of the file into a variable
$code = @"
using System;

public class MyObjectStatic
{   public static string composeFullName(string pSchema, string pTable)   {     string retVal = "";  // Using retVal for Write-Verbose purposes     retVal = pSchema + "." + pTable;     return retVal;   } // public static void composeFullName
} // class MyObjectStatic

"@

# Add a new type definition based on the code
Add-Type -TypeDefinition $code `
         -Language CSharpVersion3 

# Call the static method of the object
$mySchema = "dbo"
$myTable = "ArcaneCode"
$result = [MyObjectStatic]::composeFullName($mySchema, $myTable)
$result


#-----------------------------------------------------------------------------#
# Demo 8 - Instantiate an object from .Net Code in embedded code
#-----------------------------------------------------------------------------#

$code = @"
using System;

public class MyObjectEmbedded
{   public string SomeStringName;   public string composeFullName(string pSchema, string pTable)   {     string retVal = "";  // Using retVal for Write-Verbose purposes     retVal = pSchema + "." + pTable;     return retVal;   } // public string composeFullName
} // class MyObjectEmbedded

"@

# Add a new type definition based on the code
Add-Type -TypeDefinition $code `
         -Language CSharpVersion3 

# Instantiate a new version of the object
$result = New-Object -TypeName MyObjectEmbedded

# Set and display the property
$result.SomeStringName = "Temp"
$result.SomeStringName

# Call the method
$result.composeFullName($mySchema, $myTable)


#-----------------------------------------------------------------------------#
# Demo 9 - Create object from .Net Code in an external file
#-----------------------------------------------------------------------------#

# Path and File Name
$file = 'C:\PS\Classes and Modules\HighClassPowerShell.cs'

# Open the file in the ise to look at it
psedit $file

# Load the contents of the file into a variable
$code = Get-Content $file | Out-String

# Add a new type definition based on the code
Add-Type -TypeDefinition $code `
         -Language CSharpVersion3 

# Call the static method of the object
$mySchema = "dbo"
$myTable = "ArcaneCode"
$result = [MyObjectExternal]::composeFullName($mySchema, $myTable)
$result


#-----------------------------------------------------------------------------#
# Demo 10 - Add to an existing object
#-----------------------------------------------------------------------------#

# Going to add a script method and note property to the System.IO.FileInfo 
# objects returned by Get-ChildItem

# Define the custom script method
$script = { 
            $retValue = 'Unknown'

            switch ($this.Extension)
            {
              '.ps1'  { $retValue = 'Script' }
              '.psm1' { $retValue = 'Module' }
              '.psd1' { $retValue = 'Module Declration' }
              '.cs'   { $retValue = 'C# File'}
              default { $retValue = 'No Clue. Seriously stumped here.'}
            }

            return $retValue
          }

# Load a variable with a collection of file objects
Set-Location 'C:\PS\Classes and Modules'
$items = Get-ChildItem

$itemCount = 0
foreach($item in $items)
{
  # Add a note property, setting it to the current item counter
  $itemCount++
  $item | Add-Member –MemberType NoteProperty `
                     –Name ItemNumber `
                     –Value $itemCount

  # Add script property to the individual file object
  Add-Member -InputObject $item `
             -MemberType ScriptMethod `
             -Name 'ScriptType' `
             -Value $script 

  "$($item.ItemNumber): $($item.Name) = $($item.ScriptType())"
}

# Show our new note and script having been added to the FileInfo type
$items[0] | Get-Member


#-----------------------------------------------------------------------------#
# Demo 11 - Serializing an Object
#-----------------------------------------------------------------------------#

  # Create a simple object
  $mySiteProperties = [ordered]@{ 
                                  WebSite = 'ArcaneCode'
                                  URL = 'http://arcanecode.com'
                                  Twitter = '@ArcaneCode'
                                }

  # Convert it to an object
  $mySite = New-Object –TypeName PSObject -Property $mySiteProperties
  
  # Show the object
  $mySite
  
  # Save the object to a file and 
  $savedDataFile = 'C:\PS\Classes and Modules\mySite.xml'
  $mySite | Export-Clixml $savedDataFile
  psedit $savedDataFile 

  # Now grab the saved object and recreate in a different variable
  $newMySite = Import-Clixml $savedDataFile
  $newMySite


#region Enum

#-----------------------------------------------------------------------------#
# Show Enums in PS
#-----------------------------------------------------------------------------#

# Define the valid values for the enum
Enum MyTwitters
{
  ArcaneTC
  ArcaneCode
  N4IXT
}

# Note when typing the last : will trigger intellisense!
$tweet = [MyTwitters]::ArcaneCode 
$tweet

# See if they picked something valid
[enum]::IsDefined(([MyTwitters]), $tweet)

# Set it to something invalid and see if it passes as an enum
$tweet = 'Invalid'
[enum]::IsDefined(([MyTwitters]), $tweet)

#endregion Enum


#region Basic Class

#-----------------------------------------------------------------------------#
# Basic Class
#-----------------------------------------------------------------------------#
  
Class Twitterer
{
  # Create a property
  [string]$TwitterHandle
  
  # Create a property and set a default value
  [string]$Name = 'Robert C. Cain'

  # Function that returns a string
  [string] TwitterURL()
  {
    $url = "http://twitter.com/$($this.TwitterHandle)"
    return $url
  }

  # Function that has no return value
  [void] OpenTwitter()
  {
    Start-Process $this.TwitterURL()
  }

}

$twit = [Twitterer]::new()
$twit.GetType()

$twit.TwitterHandle = 'ArcaneCode'
$twit.TwitterHandle
  
# See default property value
$twit.Name

# Override default value
$twit.Name = 'Robert Cain'
$twit.Name

$myTwitter = $twit.TwitterURL()
$myTwitter

$twit.OpenTwitter()

#endregion Basic Class


#region Advanced Class

#-----------------------------------------------------------------------------#
# Advanced Class
#   Constructors
#   Overloaded Methods
#-----------------------------------------------------------------------------#

Class TwittererRedux
{
  # Default Constructor
  TwittererRedux ()
  {
  }

  # Constructor passing in Twitter Handle
  TwittererRedux ([string]$TwitterHandle)
  {
    $this.TwitterHandle = $TwitterHandle
  }

  # Constructor passing in Twitter Handle and Name
  TwittererRedux ([string]$TwitterHandle, [string]$Name)
  {
    $this.TwitterHandle = $TwitterHandle
    $this.Name = $Name
  }

  # Create a property
  [string]$TwitterHandle
  
  # Create a property and set a default value
  [string]$Name = 'Robert C. Cain'

  # Static Properties
  static [string] $Version = "2016.04.06.001"

  # Function that returns a string
  [string] TwitterURL()
  {
    $url = "http://twitter.com/$($this.TwitterHandle)"
    return $url
  }

  # Overloaded Function that returns a string
  [string] TwitterURL($twitterHandle)
  {
    $url = "http://twitter.com/$($twitterHandle)"
    return $url
  }

  # Function that has no return value
  [void] OpenTwitter()
  {
    Start-Process $this.TwitterURL()
  }

  # Can launch a twitter page without instantiating the class
  static [void] OpenTwitterPage([string] $TwitterHandle)
  {
    $url = "http://twitter.com/$($TwitterHandle)"
    Start-Process $url
  }

}

# Create a class using default constructor
$twitDefault = [TwittererRedux]::new()

# Display without assigning
"TwitterHandle = $($twitDefault.TwitterHandle)"

# Now assign and display again
$twitDefault.TwitterHandle = 'ArcaneTC'
"TwitterHandle = $($twitDefault.TwitterHandle)"

# Show version one of TwitterURL
"URL = $($twitDefault.TwitterURL())"

# Show overloaded version
"URL = $($twitDefault.TwitterURL('ArcaneCode'))"

# Create a new instance using the second constructor
$twitAdvanced = [TwittererRedux]::new('N4IXT')

# Display without assigning - Should have what was passed in constructor
"TwitterHandle = $($twitAdvanced.TwitterHandle)"

# Create yet another instance using the third constructor
$twitAdvanced2 = [TwittererRedux]::new('ArcaneCode', 'R Cain')
$twitAdvanced2.TwitterHandle
$twitAdvanced2.Name

# Static Value - Can be called without initializing the class
[TwittererRedux]::Version

# Use the static method
[TwittererRedux]::OpenTwitterPage('ArcaneTC')


#endregion Advanced Class