Press "Enter" to skip to content

Category: Powershell

Compare-Object And Null Values

Published 2018-01-10 by Kevin Feasel

Mike Robbins notes that the Compare-Object cmdlet in Powershell does not like to handle null values:

I thought I’d run into a bug with the Compare-Object cmdlet in PowerShell version 5.1 earlier today.

Compare-Object : Cannot bind argument to parameter ‘ReferenceObject’ because it is null.
At line:1 char:33
+ Compare-Object -ReferenceObject $DriveLetters -DifferenceObject $Driv …
+ ~~~~~~~~~~~~~
+ CategoryInfo : InvalidData: (:) [Compare-Object], ParameterBindingValidationException
+ FullyQualifiedErrorId : ParameterArgumentValidationErrorNullNotAllowed,Microsoft.PowerShell.Commands.CompareObjectCommand

Read on for the description of the problem as well as the solution.

Comments closed

Generating Passwords In T-SQL And Powershell

Published 2018-01-04 by Kevin Feasel

Dave Mason shares a couple methods for generating good passwords:

There’s really nothing special there. On line 7 I specify how many characters long I want the password to be. I can run the code as many times as needed, or put it in a UDF or a loop if I want to get fancy.

Recently, though, I had the need to generate passwords outside of a T-SQL environment. I immediately went looking in the .NET Framework, and none to my surprise, I found something: the function System.Web.Security.Membership.GeneratePassword(). I did some initial testing in C#, then decided to proceed with a PowerShell scrip

Click through for the scripts.

Comments closed

DBA Morning Health Checks

Published 2018-01-03 by Kevin Feasel

Patrick Keisler has a process which walks through some morning health checks for SQL Server instances:

A few weeks ago, a customer asked me to help develop a way to improve their morning routine of checking the health of each SQL Server. This daily task would typically take about an hour for the DBA to complete. The solution I provided him, reduced that task to under one minute.

The DBA needed me to answer these questions for each SQL Server:

1.       What is the uptime of each SQL Server?

2.       What is the status of each database?

3.       What is the status of each Availability Group?

4.       What is the backup status of each database?

5.       What is the available disk space?

6.       Are there any SQL Agent failed jobs in the last 24 hours?

7.       What errors appeared in the SQL errorlog in the last 24 hours?

In addition, the customer asked to display the results using the typical stop light colors (red, yellow, and green) to represent the condition of each server.

Click through for more details, as well as a link to Patrick’s GitHub repo which hosts the script.

Comments closed

Tracking Best Practices With dbatools And Pester

Published 2017-12-22 by Kevin Feasel

Jason Squires has an example of combining dbatools with Pester to build out SQL Server instance quality reports:

With what I have learned from Pester, it seems to be best to test each command on its own. That got me to thinking, what if I walk into a large environment and I want to see an enterprise summary of best practices? I started working on a few different versions with the ultimate goal of using dbatools function Get-DbaRegisteredServer. I have included a few examples of Test functions that include best practices, as well as some default database best practices all from dbatools.

In my first run, I wanted to make sure that I can pass multiple servers with multiple tests and came up with the below script. You will notice the top section that uses the Get-Dba functions, I had to supply my own values of the properties I am testing. In the bottom section, you will notice that dbatools Test-Dba functions have the IsBestPractice property already and that is all you have to test against.

Read on for a sample of what you can do.

Comments closed

Error Handling In Powershell In SQL Agent Jobs

Published 2017-12-20 by Kevin Feasel

Ben Miller has a couple of tips when executing Powershell within SQL Agent jobs:

What you don’t see is the way you have the job step succeed or fail. When using most commands in modules, you may find that errors still cause the step to fail because of the way they report the failure (some kind of throw or a Stop condition outside your control). So if you want things to fail that normally would show red on the screen but things would continue, remember that the default ErrorAction is Continue, so even though you get an error, PowerShell will just continue.

Read on for more.

Comments closed

DataRow To JSON With Powershell

Published 2017-12-19 by Kevin Feasel

Rob Sewell shows how to convert a .NET DataRow into its JSON form using Powershell:

I wanted to be able to Mock $variable. I wrapped the code above in a function, let’s call it Run-Query

Which meant that I could easily separate it for mocking in my test. I ran the code and investigated the $variable variable to ensure it had what I wanted for my test and then decided to convert it into JSON using ConvertTo-Json

Read on to see the fun mess that ConvertTo-Json made and then Rob’s simplification.

Comments closed

String Formatting With Powershell

Published 2017-12-14 by Kevin Feasel

Thomas Raynor provides a quick tip for string formatting in Powershell:

And, for some reason, instead of the default output which is formatted like a table, I want output presented like this.

This is a silly example, but notice that even though there are extensions of varying length (.ps1 and .dll are four characters including the dot, and .xlsx is five), all of the “file extension: <number>” is aligned.

How’d I do that?

Read on to learn how.

Comments closed

What’s In Your Powershell Profile?

Published 2017-12-05 by Kevin Feasel

Shane O’Neill wants to know what’s in your Powershell profile:

This brings me back to the main point. My profile does 3 things

  1. Changes the default colour of error and warning messages,
  2. Sets an alias for notepad to “n” since I use it so much Set-Alias -Name n -Value notepad , and
  3. loads up the dbatools prompt

I don’t import any modules because the two that I use the most are updated so frequently plus I’m currently using PowerShell 5 so they get automatically loaded when I type in one of their commands.

If you don’t already have a profile, read on and see how you can do it.  And over-do it if you’re not careful.

Comments closed