Press "Enter" to skip to content

Category: Powershell

Using Visual Studio Code As A Powershell IDE

Published 2017-08-30 by Kevin Feasel

Mike Robbins has a new video up:

If you follow me on Twitter, then I’m sure you’re aware that I’ve been using nothing but VS Code (Visual Studio Code) as a replacement for the PowerShell ISE (Integrated Scripting Environment) for the past couple of weeks and while I had tried it in the past, I didn’t previously think it was ready for prime time. That’s now changed with all of the updates and work that has gone into it. From what I’ve found, it works fairly well flawlessly so I’ve created a short and simple video to help others get VS Code installed and configured as a replacement for the PowerShell ISE.

Click through for some interesting comments, especially one from Mike Fal.

Comments closed

Handling Sensitive Data With Powershell

Published 2017-08-23 by Kevin Feasel

Adam Bertram shows off the data protection API in Powershell:

We need to automatically read this information without our intervention. This is where you sometimes see people get lazy and add passwords in plain text into the script. That’s a big no-no, and there’s a better way.

 PowerShell has native support for something called the data protection API (DPAPI). DPAPI is a built-in way Windows users can use certificates to encrypt and decrypt information on the fly which is perfect for PowerShell scripting. No username and password required. We simply need a certificate installed which can be self-signed.

This is about much more than storing and decoding passwords, so check it out.

Comments closed

Powershell: Text Search In A Table Value

Published 2017-08-21 by Kevin Feasel

Shane O’Neill clarifies a misunderstanding in Powershell:

and you are running the following PowerShell command to check if the results contain a value…

1
2
3
$String = "abc"
$Array = @(Invoke-Sqlcmd -ServerInstance "SQLServer" -Database "Database" -Query "SELECT code FROM dbo.users")
$Array.Contains($string)

It will return FALSE.

Now we know that the FALSE is false because we know that the string is in there!
This code is proven to work with arrays as stated here by the “Hey, Scripting Guy!”s so this was getting filed under “WTF PowerShell”

It’s a good post, so check it out and remember that DataRows aren’t strings.

Comments closed

Basic Powershell Regex

Published 2017-08-15 by Kevin Feasel

Adam Bertram shows how to use regular expressions for pattern matching in Powershell:

However, regex has traditionally been a topic that most IT pros shy away from when they first see how it works. Admittedly, regex does take a bit of getting used to, and you’re still probably going to have to do some Googling every time you need to use it. But learning how PowerShell integrates regex into its language is a skill that’s much easier learned and one that will come in handy often.

First of all, when someone says “working with regex,” that can mean a lot of things so let’s break it down a little bit. Regex is a method of string matching. Its sole purpose is to match and parse strings from within other strings. This can mean a lot of things and PowerShell allows you to do just about anything here, particularly as it has the full power of the .NET Framework. For our purposes, let’s investigate a few ways PowerShell allows you to match strings and how to parse strings with regex and PowerShell.

Read on for a couple sample scenarios.

Comments closed

Substrings: Powershell Versus T-SQL

Published 2017-08-14 by Kevin Feasel

Shane O’Neill contrasts the SUBSTRING function in T-SQL with Powershell’s Substring method:

The main difference that I can see when using SUBSTRING() in SQL Server versus in PowerShell is that SQL Server is very forgiving.

If you have a string that is 20 characters longs and you ask for everything from the 5th character to the 100th character, SQL Server is going to look at this, see that the string does not go to the 100th character, and just give you everything that it can.

It’s a small difference but an important one.

Comments closed

Automating Installation Of SQL Server

Published 2017-08-08 by Kevin Feasel

Nate Johnson has a script he uses to automate installation of SQL Server on a new server:

We can then use this file in the ConfigurationFile argument of setup.exe from the SQL Server install media.  To put a little more color on that: the .ini file is really just a collection of command-line arguments to setup.exe​; you could also list them all out in-line, but that would be tedious and silly.  Here’s a couple major selling points of creating your own config file:

  1. Slipstream updates (SP’s, CU’s), instead of having it go out to MSFT update servers (or *aghast* sticking with the original RTM bits, you heathen you!)

  2. Specify drive letters / default file locations: sure, this may be considered old-hat if you’re running super slick storage, but I still find it makes management a bit easier if I know where my MDFs, LDFs, TempDB, & backups will always be.

  3. Take advantage of 2016’s better TempDB setup options (# files, size & growth)

Read the whole thing.

Comments closed

Measure-Object Differences

Published 2017-08-03 by Kevin Feasel

Klaas Vandenberghe notes a discrepancy in how Measure-Object works, based on syntax:

So, is the -InputObject parameter broken? Not really, but we need to be aware of the logic behind it.
Maybe we are used to work with -ComputerName for a lot of cmdlets and functions, and we rely blindly upon the ability of the command to handle whatever collection we provide. We know this executes the action we chose, like Get-DbaSqlService or Get-DbaOperatingSystemor whatever, separately to every computer in the collection. The collection is ‘fold out’, ‘unpacked’, ‘split’.

-InputObject doesn’t do that! Is that wrong? Not necessarily, it may be a choice of design to have possibilities to enquire the properties of the collection itself and those of the ‘members’ or ‘children’. It’s just a surprise the behaviour differs from pipeline input to parameter input.

Something to keep in mind when writing Powershell scripts.

Comments closed

Powershell To Copy Reporting Services Subscriptions

Published 2017-07-28 by Kevin Feasel

Claudio Silva has a new contribution to the Reporting Services Powershell module:

If we take a look to the “New Subscription” form, we will discover about a dozen of fields that need to be configured. Doing this by hand can make you want to pull your hairs, also the probability of error is huge, even with copy & paste.
Who wants to do copy & paste of dozens of fields between reports? I know who doesn’t – me 🙂

Click through to learn more about Claudio’s cmdlets for getting, setting, and removing Reporting Services subscriptions.

Comments closed

ConvertTo-HTML Tips

Published 2017-07-27 by Kevin Feasel

Jeff Hicks shows off some of the niceties of Powershell’s ConvertTo-HTML cmdlet:

This is because Convertto-Html, like Export-CSV and Export-Clixml, take the entire object. This is not just the default result you see on the screen. Remember, everything will be treated as a string. In my example, if I want a similar HTML file, I will have to recreate the output with Select-Object. This might require piping the original result to Get-Member to discover the “real” property names.

It won’t output beautiful results, but with the appropriate CSS theming, you can generate good internal reports.

Comments closed