Press "Enter" to skip to content

Category: Powershell

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

Learn Powershell with PSKoans

Published 2020-10-01 by Kevin Feasel

Mikey Bronowski shows off PSKoans:

Recently I have got a question about resources to learn PowerShell. There is plenty out there in the wild, but I came across an interesting module I would like to write today – PSKoans.

I’m a big fan of the koan strategy of learning. It ramps you up slowly and gives you plenty of code to help understand syntax and flow. The F# koans are fantastic, as are Python’s.

Comments closed

Using Pester to Test Cluster Resource Owners

Published 2020-09-30 by Kevin Feasel

Jess Pomfret has a check for who owns specific failover cluster resources:

If we are going to test that we’re in our expected configuration, we need to record what that configuration looks like.  I have a hard coded list of cluster names. However, you could easily pull them from a text file, or a database.  Once we have the list of clusters we can use Get-ClusterGroup to determine the cluster roles and their current owners.

To persist this owner information I’m using ConvertTo-Json and then outputting it to a file. This creates a file that can easily be read back into PowerShell as an object using ConvertFrom-Json.

It’s also probably worth mentioning that this ideal configuration can be stored in source control. That’ll keep the file safe and you can easily keep track of any changes that are made to it.

Read on for the full set of steps.

Comments closed