Press "Enter" to skip to content

Category: Powershell

Parsing Parameter Default Values in Powershell

Published 2020-10-19 by Kevin Feasel

Aaron Bertrand continues a series:

In part 1 and part 2 of this series, I introduced ParamParser: a PowerShell module that helps parse parameter information – including default values – from stored procedures and user-defined functions, because SQL Server isn’t going to do it for us.

In the first few iterations of the code, I simply had a .ps1 file that allowed you to paste one or more module bodies into a hard-coded $procedure variable.

Read on to see what’s new in the ParamParser repo.

Comments closed

Custom Formatting in Powershell

Published 2020-10-16 by Kevin Feasel

Jeffrey Hicks takes us through formatting in Powershell and uses Get-Process as an example:

One of the features I truly enjoy about PowerShell, is the ability to have it present information that I need in a form that I want. Here’s a good example. Running Get-Process is simple enough and the output is pretty complete. But one thing that would make it better for me, is that sometimes I want an easy way to see high-memory use properties. Yes, I can pipe Get-Process to Sort-Object and Where-Object. However, in this particular situation, what I really want is to see high-memory usage processes displayed in red. Maybe those that are getting close to my arbitrary limit I’d like to see in Yellow. This isn’t that difficult to achieve using ANSI escape sequences.

Click through to see how.

Comments closed

Data Masking Improvements in dbatools

Published 2020-10-15 by Kevin Feasel

Sander Stad walks us through some changes to the data masking algorithm in dbatools:

If you’ve used the data masking command in dbatools you’ve probably noticed that the PowerShell session becomes memory intensive when it has to handle larger tables with one or more unique indexes.

The reason that happens is that during the data masking process the command looks for any unique indexes in the table. If it finds a unique index it will create a unique row for all the columns in the unique index.

Read on to see how Sander handled this.

Comments closed

Updating Power BI Report Parameters via Powershell

Published 2020-10-14 by Kevin Feasel

Martin Schoombee gets off the beaten path:

We’ve only used the PowerShell cmdlets for Power BI so far in this series, but things are about to get interesting because there aren’t cmdlets available for everything you might want to do. One such thing is updating parameters, and we’re going to use the Power BI REST API (which the cmdlets use underneath the covers anyways) to achieve that.

REST APIs are usually a little tricky to deal with, especially the process of authentication. Fortunately there is an Invoke-PowerBIRestMethod cmdlet that makes it possible to use the API in PowerShell without the need to deal with some of the underlying complexities.

Read on to see how the whole process works.

Comments closed

Downloading Power BI Reports from a Workspace

Published 2020-10-13 by Kevin Feasel

Shabnam Watson has a helpful script for us:

You can use PowerShell to download all of your PBI reports in a workspace all at once without having to go through the PBI service UI one at a time. As an added bonus, you may notice that downloading a report with PowerShell is faster that downloading it through the PBI Service UI.

This script is useful for admins to take backups of reports deployed to PBI Service. It can be easily extended to loop through all/several workspaces. It is also useful for developers to take a backup of their report before publishing a new version.

Click through for the script.

2 Comments

Clone Logins and Users with dbatools

Published 2020-10-08 by Kevin Feasel

Jana Sattainathan takes us through a couple of DBA scenarios:

One of the more frequent requests that a SQL Server DBA receives is to “Clone a login” with all its permissions. For example a request could be

– Clone BILL_BLACK’s login and create a new login JACK_JOHNSON with exactly the same privileges in all databases.
– Clone AD group login BILLING_APP_ADMINS and create a login for new AD group BILLING_APP_CONSULTANTS with the same permissions as BILLING_APP_ADMINS in all databases

Read on to see how.

Comments closed

Creating a Power BI Workspace from Powershell

Published 2020-10-02 by Kevin Feasel

Martin Schoombee continues a series on automating Power BI deployments:

There are two things to keep in mind when creating workspaces in an automated fashion:

– The workspace may already exist. When you delete a workspace, it isn’t really deleted…only its status changes to “deleted”. That’s great if you want to restore a workspace you’ve accidentally deleted, but your code will have to account for it or it will fail.
– Whether you are creating or restoring a workspace, you have to provide an account to be the new administrator in the workspace. This ensures that the workspace is accessible, and by design a workspace cannot exist without an administrator.

Read on for the code.

Comments closed

Refresh a Dataset when Dataflow Refresh Completes

Published 2020-10-02 by Kevin Feasel

Matthew Roche points out an API update:

Back in August I highlighted the new dataflows PowerShell script repo on GitHub. These scripts provide an accelerated starting point to working with the dataflows REST APIs and to automate common dataflows tasks.

This week the dataflows team has released two new REST APIs for managing dataflows transactions (think “refresh”) and a new parameterized PowerShell script to make using the APIs easier.

Read on to see how you can use these new APIs to trigger a dataset refresh once a dataflow refresh has completed.

Comments closed

Color Combination Testing in Powershell

Published 2020-10-02 by Kevin Feasel

Jeffrey Hicks has a script to test out color combinations in Powershell:

A lot of my PowerShell work lately has involved color. I find myself using ANSI escape sequences quite often. I’m also playing with different color schemes in Windows Terminal. And I still on occasion find myself using Write-Host to display colorized messages. What has gotten trickier is that Windows Terminal schemes can redefine colors. What I am used to as Green may not actually be Green. I polished up a simple script, to display all possible combinations for the console colors.

Read on for the script, as well as some oddities with Windows Terminal.

Comments closed