I was planning to continue the topic of workspace migration to a different region, but the next article in the series will contain some PowerShell script. That’s why we will start with brief introduction to this tool.
What is PowerShell
# Filter tasks where CPU usage is greater than 100s
Get-Process | Where-Object { $_.CPU -gt 100 }How to install PowerShell
- Stable release – Current release that contains latest (tested) features and bug fixes. It gets frequent updates and has shorter lifespan (I will explain what it means below).
- LTS release – Long Term Support release is not updated with latest features that frequently and is supported for longer time than stable release (3 years vs 18 months).
If you use for example Azure Automation and create Runbooks with PowerShell script, you must decide what will be the PowerShell version used to run them. If version is no longer supported – your scripts will fail. That is why it is important for you decide what’s more important. Access to latest features or having your scripts operation for longer period of time. For me I go with LTS and that’s my recommendation for any Power BI Admin tasks. The reason I am going through this is because it’s tempting to always pick the highest version available, but in this case it’s not necessarily the best choice.
Once you have your PowerShell installed, to be able to work with Power BI Cmdlets you must install specific packages in PowerShell. You can refer again to Microsoft Documentation to see the list of all available modules. We have modules dedicated to Amin tasks, to Capacities management, Reports, etc. However, one module is enough to install all of them. Please, bear in mind that you must open PowerShell as Admin to install anything on your machine. You can open PowerShell and type following command:
Install-Module -Name MicrosoftPowerBIMgmt -Scope CurrentUserIt may happen that you will not be allowed to install any modules, even as Admin. This may happen due to specific Execution Policies in your system. In this case, you will see an error like this:
Execution Policies help to protect you from untrusted code. However, in this particular case it’s safe to run the module installation. To bypass this setup, you can run the following commands:
# Get your current ExecutionPolicy
$originalPolicy = Get-ExecutionPolicy
# Alternatively, you can display it in consolder, just uncomment the line below:
# Write-Host "Original Execution Policy: $originalPolicy"
# We set the new policy as Unrestricted. "Process" scope means that this will be applicable only to the current session of PowerShell.
Set-ExecutionPolicy Unrestricted -Scope Process -Force
# Install Power BI Module
Install-Module -Name MicrosoftPowerBIMgmt -Scope CurrentUser
# If you want to bring back the previous ExecutionPolicy still in the current session, uncomment the line below:
# Set-ExecutionPolicy $originalPolicy -Scope Process -ForceOne more important thing to mention when it comes to Power BI Cmdlets is authentication. PowerShell must be aware of your access rights in Power BI Service, that’s why to run any command you must first login using: Connect-PowerBIServiceAccount command.
With this setup you should be good to go. Now, let’s see different options you have to run the PowerShell scripts.
PowerShell IDEs
- PowerShell Console
- PowerShell ISE
PowerShell ISE (Integrated Scripting Environment) is already a better choice than standard console. It allows to easier handle multiline scripts, allows to quicky access all available cmdlets and has IntelliSense support. But still, this is not the best choice as it’s even no longer developed now. This is why Microsoft recommends using PowerShell extension for Visual Studio Code.
Here is detailed documentation of the extension and its features. Among them, the most important are:
- Syntax highlighting
- Re-usable code snippets (own, built-in or from marketplace)
- Find Reference of your variables, classes, etc.
- Go to definition of your variables, classes, etc.
- Ritch debugging capabilities
- Run only selected code in terminal
- and many more
For those of your who love standard PowerShell ISE experience, VS Code extension even has the ISE based theme available for you 🙂
Not to mention that whatever code you are going to develop, you would probably like to store it in GitHub or Azure DevOps, and VS Code is just perfect for this as well.
Once we have everything we need, let’s look at the PowerShell syntax!
Objects, Properties and Methods
- Properties – attributes of an object (like Name, Id)
- Methods – actions you can perform on your object
# Opens Web Browser to login to Power BI Service
Connect-PowerBIServiceAccount
# Get available properties and methods for Get-PowerBICapacity object
# Input is formatted as a table
Get-PowerBICapacity | Get-Member | Format-Table -AutoSizeThis brings the following result:
Here we can clearly see what are the available properties and methods for the given object. On top of that, at the end of each property you may have noticed additional info in curly brackets: {get; set;}. This indicates if specific property is available only for read, write or both operations. If you need more details on specific method, you must slightly modify the code to ask for that method: Get-PowerBICapacity | Get-Member -Name “Equals” | Format-List. If find list is more convenient formatting in this case. It will provide us the following details:
In the Definition we see what type of value is returned (in this case it’s Boolean) and what type of argument it requires (in this case it’s another object to compare). It would be amazing if this kind of documentation would be supported by at least one example, but it’s still better than nothing.
Variables
# Declare your variables:
$FirstName = "Pawel"
$LastName = "Wrona"
# Use variables:
$FullName = "$FirstName $LastName" # Concatenation
# Display variable:
$FullNameWhen variable is assigned, PowerShell assumes proper data type, and usually this assumption is correct. You can run the code below to see if the data types displayed for each of the 3 variables is as per your assumption. Alternatively, the code below shows alternative approach, where data type is explicitly defined. Please, note that we are using new cmdlet here to display data types: Write-Host. This allows us to display text in terminal, and we can use different color coding. This could prove useful when you develop larger script, and you would like to “communicate” with your script by displaying some text in terminal. You can use the color coding to differentiate the types of output between information, alert, success or fail messages.
# Declare variables
$Name = "Pawel"
$Age = 38
$IsAdmin = $true
# Print data types in terminal using different colors
Write-Host "Name variable type: $($Name.GetType().Name)" -ForegroundColor Green
Write-Host "Age variable type: $($Age.GetType().Name)" -ForegroundColor Yellow
Write-Host "IsAdmin variable type: $($IsAdmin.GetType().Name)" -ForegroundColor Red
# Alternatively explicitly define data types
[string]$Name = "Pawel"
[int]$Age = 38
[bool]$IsAdmin = $trueVariables can be used also to assign objects. In our previous example we used cmdlet Get-PowerBICapacity to further get the list of available properties and methods. Below example shows exactly the same procedure but first assigns the Capacities object to a variable.
# Assign output object to $Capacities variable
$Capacities = Get-PowerBICapacity
# Use object
$Capacities | Get-Member | Format-Table -AutoSizeOperators
- Comparison operators:
- -eq: Equal to
- -ne: Not equal to
- -gt: Greater than
- -lt: Less than
- -ge: Great than or equal to
- -le: Less than or equal to
- Logical operators:
- -and: Returns true if both conditions are True
- -or: Returns true if at least one condition is True
- -xor: Returns true when one conditions is True and the other is False
- -not: Inverts the Boolean value
# Equal to / Not equal to
5 -eq 5 # True
5 -ne 5 # False
# Grater than / Less than
3 -gt 2 # True
3 -lt 2 # False
# Grater than or equal to / Less than or equal to
3 -ge 3 # True
3 -le 5 # True
# AND, OR, XOR, NOT
$true -and $true # True
$true -or $false # True
$true -xor $false # True
-not ($true) # FalseConditional statements
$IsAdmin = $true
$IsNice = $true
# IF
if ($IsAdmin -eq $true){
Write-Host "Pawel is an Admin."
}
# IF-ELSE
if ($IsAdmin -eq $true){
Write-Host "Pawel is an Admin."
}
else{
Write-Host "Pawel is not an Admin."
}
# IF-ELSEIF-ELSE
if ($IsAdmin -eq $true){
Write-Host "Pawel is an Admin."
}
elseif ($IsNice = $true) {
Write-Host "Pawel is not Admin, but at least he is nice."
}
else{
Write-Host "Pawel is not an Admin and he is rude."
}Loops
- for – you decide how many times loop will be executed
- foreach – specific case of for loop, which iterates through a collection of items (lists, arrays)
- while – runs a block of code while specified condition is true
- do while – similar as while, but guarantees that code will be executed at least once
- do until – reversed while, but runs the code until specified condition is true
- foreach-object – runs through all items in given object
# FOR LOOP
# First argument is initial state
# Second argument is a condition to stop the loop. Last execution is when i = 5
# Thrid argument is an increment logic for your control parameter: after each loop value of i increase by 1
for ($i=1; $i -le 5; $i++){
Write-Host $i
}
# FOREACH LOOP
# Requires a collection to iterate through
# Starts with first item in collection and ends up with last one
# Current Item name can be anything you want (here it's simply $number)
$numbers = @(1, 2, 3, 4, 5)
foreach ($number in $numbers){
Write-Host $number
}If you run these two loops, you will quickly notice that they produce exact same output. Let’s look at the last for loop variant – foreach-object. Why is this one important? It allows us to work with collections of items returned as output of cmdlets, by just using the “pipe” symbol (more on that in the next section). Looking at our example of foreach loop and collection of numbers, we can run the loop using foreach-object like this:
$Numbers = @(1, 2, 3, 4, 5)
# $_ stands for current item in the loop
$Numbers | ForEach-Object{
Write-Host $_
}Unlike in foreach loop, where we needed to give the name for the current item in the loop ($number in $numbers), with foreach-object we can’t do this. Therefore, we deal here with “$_” which is called a pipeline variable or automatic variable. When foreach-object is used, PowerShell automatically assigns the current item in the loop to that variable. We can use it as any other item in standard collection and do the “dot walk” by adding methods and properties to the expression:
# Calling this cmdlet will display following values in terminal:
# String, Int32, Boolean
$Items = @("Pawel", 38, $true)
$Items | ForEach-Object{
Write-Host $_.GetType().Name
}In PowerShell you will always work with objects, that’s why cmdlets like “Foreach-Object” or “Where-Object” are very important, as they allow to leverage the flagship feature of PowerShell which is Object-Based Pipeline Processing.
Pipeline Processing
# Step 1: Get all processes
$allProcesses = Get-Process
# Step 2: Convert to an array
$processArray = @()
foreach ($proc in $allProcesses) {
$processArray += $proc
}
# Step 3: Sort the array
for ($i = 0; $i -lt $processArray.Count; $i++) {
for ($j = $i + 1; $j -lt $processArray.Count; $j++) {
if ($processArray[$i].CPU -lt $processArray[$j].CPU) {
$temp = $processArray[$i]
$processArray[$i] = $processArray[$j]
$processArray[$j] = $temp
}
}
}
# Step 4: Select first 5 processes
$topProcesses = @()
for ($k = 0; $k -lt 5 -and $k -lt $processArray.Count; $k++) {
$topProcesses += $processArray[$k]
}
# Step 5: Display results
foreach ($process in $topProcesses) {
Write-Output "Process: $($process.Name) | CPU: $($process.CPU)"
}Don’t focus now on the code and don’t worry if you don’t understand it. The purpose here is to show how long this code is when it’s written without pipeline processing, and how crisp it can be when pipeline processing is used:
Get-Process | Sort-Object CPU -Descending | Select-Object -First 5 Name, CPUYou must admit that the difference is spectacular. Starting point is similar, we Get-Processes as step 1. Step 2 is not needed here, as we can directly sort the objects returned by Get-Processes (Step 3). Step 4 and Step 5 are done together by using Select-Object cmdlet. Instead of sorting operation and keeping only first 5, we could filter the collection using Where-Object to return only those items, where CPU Consumptions is higher than 1500 seconds:
Get-Process | Where-Object { $_.CPU -gt 1500 } | Select-Object Name, CPUWhere-Object is also an iterator just as Foreach-Object, and we can see it thanks to the current item operator: $_. We expand the objects property – CPU, we use the comparison operator (-gt), pipelines and object selection. With this single line of code, we used almost what we’ve learned today.
