If you are working interactively with PowerShell, It might be unhandy to define all the filtering options with Where-Object. I will show you how to filter for PowerShell objects the traditional way and doing it with Out-Gridview.
Filter for PowerShell objects – the traditional way with Where-Object
If we are looking forward to filter in PowerShell, we normaly make use of Where-Object this. In my example I am looking forward for all proccesses, which contain the name msedge and the value for Handles is greater than 292. My query would look like this:
The corporate design covers besides colors and fonts a logo, which is located in the upper left corner of the SharePoint site collection. If you want to automate your SharePoint intranet, you might want, that your corporate design is applied to your SharePoint Sites automatically. Changing Change SharePoint site collection logos can be done in various ways. I want to show you how you can change your SharePoint site collection logo in the GUI and with PowerShell.
Change SharePoint site collection logo in the GUI
As one can see, the logo is displayed with the initials of the SharePoint site:
So if we want to change the logo, we have to open the SharePoint Site settings -> Site information:
Change SharePoint site collection logo from local File
You can change your SharePoint site collection logo from a local file, like your file system. You cannot use this method easily in azure automation, since you got no “local client”.
Did you know that you can copy the path with CTRL + Right mouse button?
After doing this, the logo changes:
Change SharePoint site collection logo from Library
Now it gets interesting. This approach let’s you choose a logo from a SharePoint site library, doesn’t matter wether you choose your current site or another site. This is especially interesting for Azure Automation, since you got no easy local file access. You could create a storage account and access the local file over a blob storage. Since I got an easier way for azure automation, I will describe the easy way.
I have located a photo image in the library, which we will use as the logo.
A page consists of multiple webparts. With the webparts you can refer to other lists, libraries and apps within a page. You can add webparts SharePoint Pages manually and programmatically to a page. Adding webparts to single sites, can be done straight forwards in the edit section of the pages. If you want to do it in multiple sites and want to ensure, that the pages do look identical in terms of the structure, add webparts to SharePoint Pages with PowerShell! In this post I will show you how to do add webparts to pages manually and with PowerShell.
Add Webparts to SharePoint Pages manually
If you want to add webparts to SharePoint pages manually, you have to edit the page by clicking on edit.
Now you can add the webpart to areas, where you get displayed a red line with a plus:
Like here
or here:
After clicing on the red cross, you have to choose the webpart and can add. In my example, I want to display a user.
Add the webpart name and set a person.
If you want to display the changes to all users, click on publish (1) , otherwise click save as draft (2), so only you can see the changes.
I have published the page, so every user can now see my change:
Add Webparts to SharePoint pages with PowerShell
Recommendations
Use a code editor, which can format JSON properly – otherwise your code will look like a mess. I would recommend Visual Studio Code
Refresh the $Page variable fore each change you make on your page – Otherwise you will experience, that the homepage will be messed up
Building webpart Components
In the first step, I would recommend to design the webpart in the worbench page. You can find the workbench page of your site, by calling following URL:
Sometimes the use of PNP.PowerShell might not be sufficient. I encountered this experience, when I wanted to find out the usage of all sites. The Graph API provides methods, which you can use in your PowerShell Scripts. So in my example I wanted to get unused Sites with PowerShell. If you want to make use of it, you have to register an enterprise application and afterwards you can retrieve the information with an HTTP-Webrequest. In the following I will show you step by step how to access your SharePoint tenant with Graph API in PowerShell.
Considerations – Find the right Graph API Method
The Graph API has multiple methods, which we can use to analyze and change the content of our M365 services. In order to find the right method for your plan, check folllowing resources to see what the Graph API is capable of Microsoft Graph REST API v1.0 reference – Microsoft Graph v1.0 | Microsoft Docs. Based on the needed methods, you have to set up your enterprise application.
Let’s assume, that you want to see the site usage of all sites in your tenant. In order to do this, you have to make use of following API method:
GET /reports/getSharePointSiteUsageDetail(period='{period_value}’) GET /reports/getSharePointSiteUsageDetail(date={date_value})
This API requires following permissions. We will consider them in this article. I want to analyze the sharepoint usage and want to update it to a list afterwards, that’s why I will make use of Application – Reports.Read.All
Give your application a name, click on Accounts in this organizational directory only, select mobile as platform, after that click on register.
Take a note of the Application (client) ID, you will need it to authenticate against the Graph API.
Grant API Permissions for App Registration
After creating the app, we have to give it the permissions, which we have defined in the first step.
Click on Microsoft Graph.
Grant it Application permissions
Now you have to select the permissions, for which you want to use the Graph API. I just need the information for Reports.Read.All. If you don’t know which permission to take, check the considerations part of this post.
As you can see, the permission is not granted for this tenant.
Create Client Secret for App Registration
In order to authenticate to the Graph API in PowerShell, you have to create a client secret.
Click on Certificates & secrets and then on New client secret
Set a Description and define when it will be expiring. I would recommend to give it a description, which you can recognize, for what it will be used in future. I have set 24 months, because I want to make use it in an automation, which should run for a long term. When finished, click Add.
Take Note of the value! You wont see it again, if you leave the site.
Consent the Requested permissions for App Registration
Caution: You have to consent the created application with the global administrator role.
As you can see the permissions, which we have configured, are showing up:
Since you have not set a redirect url, you will encounter this issue, which you can ignore.
Check Permission consent
You can check that the permission is granted, if you see the green check marks.
Script To Acess SharePoint via the Graph API (PowerShell)
The script contains two parts. The first part is about authentication and the second is about getting the data provided.
Authentication
I am making use of a credential export to be sure, that nobody steals the credentials, when it is in plain text. If you don’t know how to, check out: Use credentials in PowerShell – SPO Scripts
Function Export-CredentialFile
{
param(
[Parameter(Mandatory=$true,Position=0)]
$Username,
[Parameter(Mandatory=$true,Position=1)]
$Path
)
While ($Path -eq "")
{
$Path = Read-Host "The path does not exist. Where should the credentials be exported to?"
}
$ParentPath = Split-Path $Path
If ((Test-Path $ParentPath) -eq $false)
{
New-Item -ItemType Directory -Path $ParentPath
}
$Credential = Get-Credential($Username)
$Credential | Export-Clixml -Path $Path
Return $Credential
}
Function Import-CredentialFile ($Path)
{
if (! (Test-Path $Path))
{
Write-Host "Could not find the credential object at $Path. Please export your credentials first"
Export-CredentialFile
}
Import-Clixml -Path $Path
}
$AppId = '949710fd-8d80-48ee-8c1b-a6f5e9e32be3'
$CredentialPath = "C:\temp\$AppId.key"
Export-CredentialFile -Username $AppId -Path $CredentialPath
After doing this, we notice, that the file with the app id as name, has an encrypted password. So we splitted credentials from script to increase the security. This credential file can only be used on the machine and with the user, who has created it.
If we run follwing script afterwards, we will notice, that the $AuthorizationRequest will show us a token with an bearer token.
Now that we got the access token, we can connect to SharePoint Online with following script. You can use the uris (methods), defined in Microsoft docs.
You can get the site usage with following uri “https://graph.microsoft.com/beta/reports/getSharePointSiteUsageDetail(period='{D90}’)?`$format=application/json”. The number next to the D means the amount of days. So for my example it shows the usage of all sites for the last 90 days. You can replace D90 with D7, D30, and D180.
With this script you can get the site usage for the last 90 days:
If you want to make use of the script, you have to change the parameters $GraphUrl and $AppID.
Param(
$AppId = '949710fd-8d80-48ee-8c1b-a6f5e9e32be3',
$GraphUrl = "https://login.microsoftonline.com/devmodernworkplace.onmicrosoft.com/oauth2/v2.0/token",
$Scope = "https://graph.microsoft.com/.default",
$Uri = "https://graph.microsoft.com/beta/reports/getOffice365GroupsActivityDetail`(`period=`'`D90`'`)?`$format=application/json",
)
Function Export-CredentialFile
{
param(
[Parameter(Mandatory=$true,Position=0)]
$Username,
[Parameter(Mandatory=$true,Position=1)]
$Path
)
While ($Path -eq "")
{
$Path = Read-Host "The path does not exist. Where should the credentials be exported to?"
}
$ParentPath = Split-Path $Path
If ((Test-Path $ParentPath) -eq $false)
{
New-Item -ItemType Directory -Path $ParentPath
}
$Credential = Get-Credential($Username)
$Credential | Export-Clixml -Path $Path
Return $Credential
}
Function Import-CredentialFile ($Path)
{
if (! (Test-Path $Path))
{
Write-Host "Could not find the credential object at $Path. Please export your credentials first"
Export-CredentialFile
}
Import-Clixml -Path $Path
}
$CredentialPath = "C:\temp\$AppId.key"
Export-CredentialFile -Username $AppId -Path $CredentialPath
$AppCredential = Import-CredentialFile -Path $CredentialPath
$Body = @{
client_id = $AppCredential.UserName
client_secret = $AppCredential.GetNetworkCredential().password
scope = $Scope
grant_type = 'client_credentials'
}
$AuthorizationRequest = Invoke-RestMethod -Uri $GraphUrl -Method 'post' -Body $Body
$Header = @{Authorization = "$($AuthorizationRequest.token_type) $($AuthorizationRequest.access_token)"}
$SitesRequest = Invoke-RestMethod -Uri $Uri -Method 'get' -Headers $Header
$SitesRequest.value | Out-GridView -PassThru
Conclusio
In this article you saw how to find the right permission for the enterprise application, which you need to access the SharePoint via the Graph API with PowerShell. After doing this, you can authenticate and analyze the data.
Credentials are necessary, if you want to access systems or APIs. Credentials can be used interactively and within a script. If you want to use credentials in PowerShell to automate processes, you might have to export your credentials and import them within your automation solution.
In this article I will show you a few ways to make use of credentials, how to export and import credential objects and security considerations.
Functionality
You export the credentials with PowerShell the context the user whith which you are running the cmdlets, also the machine will be considered. So if you copy the credential file to another machine, it won’t work with the same user and you have to re-export the credential file.
Security considerations
Every initialization of credentials (interactive or automated) will lead to a prompt like this. When you export credentials with PowerShell consider following advice:
Be sure, that you do this close to your authentication and do not enter your credentials on client/ servers, which are not trusted.
Why?
You might think this is safe. If we try to read the password, it looks like this:
But there is still a way to read the password:
The risk will be there, when you enter your credentials to a shell, which you are not owner of.
Recommendation
If you have to enter it in a Shell, which you do not controll, ensure, that the credential object will be initialized like this $Credential = $null
If possible, try to run your scripts within windows authentication. In PNP.PowerShell it looks like this:
But be sure to initialize the $Credential afterwards, so the potential security vector is as small as possible when you export credentials with PowerShell.
LINQ stands for Language Integrated Query and if you use LINQ in PowerShell it might boost the performance of your scripts. In this article, I’ll show you how to use LINQ in PowerShell for comparing arrays.
In this article, I will show you except and intersect of LINQ. There are many more methods. If you are interested in other methods, check out the official documentation of Microsoft.
Concept of using LINQ in PowerShell
We have three amounts red, orange and yellow. The aim is to only get a specific amount e.g. only the red portion.
$Red = @( "A", "B", "C")
$Yellow = @("C","D","E")
We also can do this for integer values.
$Red = @( 1..5)
$Yellow = @(4..10)
Using LINQ for Strings in arrays
I will show you how to use LINQ for string in this chapter. We will cover the methods except and intersect.
String – Get the red amount
In order to get the red amount, you have to do following:
We all can Imagine the scenario. You create sites in sharepoint and now you want to edit multiple sites afterwards with PowerShell. In order to be safe, we have to check, wether an connection exists and if yes to disconnect the current connection to have a clean processing of the sites. In this article I want to show you how can achieve dealing with existing SharePoint connections. If you don’t know how to connect to SharePoint Online, check the article.
Symptoms – How I tried it first
I tried to check the connection by a normal if query, but as you can see it throws everytime an error, so the script will be halted under normal circumstances. Changing the ErroActionPreference is something you could do for sure, but I would not recommend it, if you want to handle other upcomming potential errors of the API. So as you can see dealing with existing SharePoint connections in terms of checking, wether an connection exists, is not that easy.
if ((Get-PnPConnection) )
{
Write-Host "Connection found"
}
Get-PnPConnection : The current connection holds no SharePoint context. Please use one of the Connect-PnPOnline commands which uses the -Url argument to connect.
In Zeile:2 Zeichen:6
+ if ((Get-PnPConnection) )
+ ~~~~~~~~~~~~~~~~~
+ CategoryInfo : NotSpecified: (:) [Get-PnPConnection], InvalidOperationException
+ FullyQualifiedErrorId : System.InvalidOperationException,PnP.PowerShell.Commands.Base.GetPnPConnection
PS H:>>
if ((Get-PnPConnection) -ne $Null )
{
Write-Host "Connection found"
}
Get-PnPConnection : The current connection holds no SharePoint context. Please use one of the Connect-PnPOnline commands which uses the -Url argument to connect.
In Zeile:2 Zeichen:6
+ if ((Get-PnPConnection) -ne $Null )
+ ~~~~~~~~~~~~~~~~~
+ CategoryInfo : NotSpecified: (:) [Get-PnPConnection], InvalidOperationException
+ FullyQualifiedErrorId : System.InvalidOperationException,PnP.PowerShell.Commands.Base.GetPnPConnection
PS H:>>
if ((Get-PnPConnection|out-null) -ne $Null )
{
Write-Host "Connection found"
}
Get-PnPConnection : The current connection holds no SharePoint context. Please use one of the Connect-PnPOnline commands which uses the -Url argument to connect.
In Zeile:2 Zeichen:6
+ if ((Get-PnPConnection|out-null) -ne $Null )
+ ~~~~~~~~~~~~~~~~~
+ CategoryInfo : NotSpecified: (:) [Get-PnPConnection], InvalidOperationException
+ FullyQualifiedErrorId : System.InvalidOperationException,PnP.PowerShell.Commands.Base.GetPnPConnection
PS H:>>
if ((Get-PnPConnection -ErrorAction SilentlyContinue) -ne $Null )
{
Write-Host "Connection found"
}
Get-PnPConnection : The current connection holds no SharePoint context. Please use one of the Connect-PnPOnline commands which uses the -Url argument to connect.
In Zeile:2 Zeichen:6
+ if ((Get-PnPConnection -ErrorAction SilentlyContinue) -ne $Null )
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : NotSpecified: (:) [Get-PnPConnection], InvalidOperationException
+ FullyQualifiedErrorId : System.InvalidOperationException,PnP.PowerShell.Commands.Base.GetPnPConnection
Solutions
Try Catch Solution
In order to handle this situation, you have to catchup the error.
The snippet tries to check wether there is an connection and if there is one, it will proceed and disconnect it. After disconnecting it I have set the variable $connection to $null, so I can process it later on.
BONUS 1: Invoke-PNPConnection with Credential object (No MFA enforcement)
A function which handles the whole procedure of cutting of the connection and reconnecting, makes the handling easier. In this case I have added an additional check of the contents of lists, because sometimes you do connect, but experience that the webserver is not ready yet – basically you get an 403 FORBIDDEN message in PowerShell.
NOTE: This will only work If your user has no MFA enforcement. If you have MFA enabled, I have another function for you.
Most of our times, we just need just a bunch of items, to export them or to change their values. This post should help you to show, how to handle filtering for SharePoint items. Besides filtering for SharePoint items with Where-Object, you can also make use of CAML (Collaborative Application Markup Language), which lets you get only the items, you need. It might increase the performance of your queries, when you are dealing with large amounts of data.
Where are the items, which I am looking for?
Preqrequistes
If we want to achieve filtering for SharePoint items, with a CAML query, we have to fulfill following prerequisites:
Permissions to access the list
Installed Module PNP.Powershell. If you don’t know how to, check the post.
Connection to the site via PNP.PowerShell. If you don’t know how to, check the post.
Considerations
You should take care of the case sensitivity of operands and column names
You should take care of the <view> part. Sometimes it is needed, sometimes not – so I would rely on the examples.
Query Schema
A query is structured like this
"<View><Query><Where><LOGICAL OPERATOR><FieldRef Name='INTERNAL NAME OF COLUMN'/><Value Type='VALUE TYPE'>VALUE</Value></LOGICAL OPERATOR></Where></Query></View>"
You can find the internal name of columns in two ways:
In order to filter by query paramter, you have to define a filter query, depending on your datatype (string, integer, boolean..) you have to choose a different query value type.
My blog would not keep it’s promise, If you would not find examples, which give you a fast way to adapt the scripts, so here we go!
In my example, I am using my demo opportunities list. I have marked the names of the columns, the value types, the operands and the actual values bold. Mostly I am using the logical operator “eq”, but I think if you got the basic concept of this, you can adapt it to your solution easily and if not, we will find a way together.
Example for boolean
If you want to find items with TRUE values, you have to enter 1. For FALSE values, you have to make use of 0.
Get-PnPListItem : Mindestens ein Feld ist nicht richtig installiert. Wechseln Sie zur Listeneinstellungsseite, um diese Felder zu löschen.
In Zeile:1 Zeichen:1
+ Get-PnPListItem -List "Opportunities" -Query "<View><Query><Where><Gt ...
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : WriteError: (:) [Get-PnPListItem], ServerException
+ FullyQualifiedErrorId : EXCEPTION,PnP.PowerShell.Commands.Lists.GetListItem
Error message in english
Get-PnPListItem : One or more field types are not installed properly. Go to the list settings page to delete these fields.
In Zeile:1 Zeichen:1
+ Get-PnPListItem -List "Opportunities" -Query "<View><Query><Where><Gt ...
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : WriteError: (:) [Get-PnPListItem], ServerException
+ FullyQualifiedErrorId : EXCEPTION,PnP.PowerShell.Commands.Lists.GetListItem
Cause
You did not care of the case sensitivity of the column name
