Press "Enter" to skip to content

Category: Powershell

Converting from XML in Powershell

Published 2021-06-09 by Kevin Feasel

Phil Factor has unleashed the full power of XML:

I want to convert reasonably small XML files to hash tables and PowerShell objects. PowerShell never had a ConvertFrom-XML Cmdlet because gulping a large XML file into a PowerShell data object is expensive in resources. It is the sheer time it takes to consume a large XML file. Instead, you have to use the XMLDocument object to navigate to the data you want or use an Xpath query. It is all well and good to handle XML in this way, but it is inconsistent to have no ConvertFrom-XML cmdlet. After all, there is a ConvertFrom cmdlet for CSV, JSON, and a variety of text-based data. It would be good to have one for XML as well. Usually, I just want to consume relatively small XML files and just pick out the data I want. I hoped that one that worked would turn up but somehow it never did. So I wrote my own.

Click through for that script, as well as some considerations about using it.

Comments closed

Querying AWS Athena via Powershell

Published 2021-06-08 by Kevin Feasel

Michael Bourgon needs to get some data out of S3:

I was running into issues with the Linked Server lopping off long JSON that I’m having to pull out from the raw files.  I can’t explain it – doesn’t appear to be SSMS.  See previous post

But I needed to automate this, rather than use SQL Workbench, save to “Excel” (it was XML), then opening it again and saving it so that instead of 250mb, it’s 30mb.  Runs against the previous month, one day at a time (walking the partitions), and then saves to a file.  You got your Athena, your ODBC, your Export-Excel…

Incidentally, that previous post was around trying to use a linked server to pull the data in via SQL Server.

Comments closed

Building a Powershell Script Template

Published 2021-06-07 by Kevin Feasel

Eitan Blumin has a template for us:

If at any point an automated script fails for some reason, or does not behave as expected, it would be invaluable to have it produce and retain historical logs that could later be investigated for clues as to what it did, and where and why it failed.

Powershell has a few useful cmdlets for this, capable of writing an Output to any sort of destination, such as a log file. For example, Out-File.

However, in my personal experience, nothing beats the level of verbosity offered by a special cmdlet called Start-Transcript.

Read on for an explanation for each part of the template, and then the template itself.

Comments closed

Finding Cmdlets in Powershell

Published 2021-05-28 by Kevin Feasel

Jack Vamvas helps us with cmdlet lookup:

Question: I need to interrogate the module for a Powershell cmdlet but I can’t find the actual physical location of the file on the host server.  I can execute the cmd through the Powershell command line OK , but can’t find the powershell cmd file. Is there a Powershell method of locating the Powershell module files?

Click through to see the answer.

Comments closed

Restoring Databases from Blob Storage Files

Published 2021-05-28 by Kevin Feasel

Stuart Moore talks us through a (rare) gap in dbatools:

In the comments here I was asked about using Restore-DbaDatabase when all you have is blobs in an Azure Storage account. This can be done, involves a couple of non dbatools steps.

Restore-DbaDatabase doesn’t natively ‘talk’ Azure, nor do any of the other dbatools commands. The reason for this is that we didn’t want to force dbatools users to have to install the AzureRM/Az powershell modules just to use our module. We’ve gone to a lot of effort to make sure that dbatools is acceptable to Security Admins and that it has a small(ish) footprint, and adding those large modules as prerequisites would have broken that.

Read on for how you can get around that.

Comments closed

Creating an Azure SQL Database from Powershell

Published 2021-05-26 by Kevin Feasel

Gijs Reijn shows how to automate the process of creating an Azure SQL Database using Powershell:

Before you can create an Azure SQL database, you must create an Azure SQL server to host it on. Assuming you’re already authenticated to Azure:

Open PowerShell on your local computer and create the Azure SQL server that will host the Azure SQL database.

The command below is creating an Azure SQL server called sqlestate in the prerequisite resource group with a SQL admin username of SqlAdministrator and a password of AVeryStrongP@ssword0. The command is saving the output of the New-AzSqlServer cmdlet to use attributes from the server created later.

Read on for the step-by-step breakdown and full script.

Comments closed

Searching through Stored Procedures using dbatools

Published 2021-05-26 by Kevin Feasel

Jess Pomfret has another way to search through stored procedure text:

When we’re looking for the command we need within dbatools to fulfil our needs I cannot recommend Find-DbaCommand highly enough.  This command will search all other commands for the pattern you pass in.  Today we know we want to find references in stored procedures so let’s see if there is a command that will help.

Seems like querying sys.sql_modules is a little easier, though if this is a step in a pipeline (such as finding old procedures based on some no-longer-appropriate code snippet and deploying new versions), this can be a good first step.

Comments closed

Understanding the PipelineVariable

Published 2021-05-25 by Kevin Feasel

Chad Baldwin shows off the PipelineVariable parameter:

PowerShell has been a daily tool for me for at least 5 or 6 years at this point, so when I learn something new that seems fairly useful I figure it’s probably worth writing about. These posts also help me remember because they force me to do more research into it than I normally would.

TIL (Today I Learned) about the -PipelineVariable parameter in PowerShell, known as a “Common Parameter”; which are automatically added by PowerShell to cmdlets that are decorated with the [cmdletbinding()] attribute.

This is by no means a “new” feature, -PipelineVariable was added as a common parameter in 2017 for version v4.0.

Read on for a clear explanation of how it works and where it can be useful.

Comments closed

Network Configuration with Powershell

Published 2021-05-25 by Kevin Feasel

Patrick Gruenauer shows how to configure network settings on a Windows machine using Powershell:

Today I would like to give a tutorial for a basic network configuration on Windows systems using PowerShell. We start with a fresh installation of a Windows operating system.

Read on for a fairly simple example, one which lays out the foundation for more complicated scenarios such as configuring networking settings for an Availability Group.

Comments closed