Category Archives: Powershell

Deploying a service principal to (CSP) child tenants

Cloud Solution Providers, or sometimes other types of Managed Service Providers often have to manage a large number of tenants. Ideally, they do their ‘Infrastructure As Code’.

Using various API’s to manage tenants is best done using a Service Principal instead of a user (MFA, lifecycle, etc).

Recently, I was tasked to provide a deployment method of a Service Principal (multi-tenant) to all child tenants of an MSP, including programmatically granting various Graph API permissions. The Graph endpoint for this (oauth2PermissionGrants) is still in Beta, but the other methods I wrote about in the past are not as reliable so we’re using the Beta endpoint.

The linked example script creates an SPN and grants AuditLog.Read.All. If you’re an MSP/CSP, you’ll probably want to capture the tenant ID’s you’re installing into, so you can easily administer these tenants centrally using your main multi-tenant SPN.

Moving forwards, you won’t need an admin user / service account in the tenants you manage anymore, at least for the API’s that support SPN’s.

Note: to completely remove module dependencies / login, check my independent token function.

Upserting Data to Azure SQL DB using PowerShell

For a project involving Azure Security I needed to store fairly large amounts of data in an Azure PaaS database using PowerShell.

If a row already exists, I want to do an UPDATE command, otherwise an INSERT command, also known as an UPSERT in SQL.

It should also use parameters to avoid issues with quotes in fields, and should convert PowerShell null’s/empty objects to the SQL equivalent.

The following function is what resulted, it only supports a single WHERE clause, but should be easy to enhance for those looking to improve 🙂

function invoke-sqlUpsert{
        [Array]$values, # example: @(@{"column"="deviceId";"value"="123415";"dataType"=[Data.SQLDBType]::NVarChar})
        [PSObject]$primaryKey, # example: @{"column"="deviceId";"value"="123415";"dataType"=[Data.SQLDBType]::NVarChar}

    $sqlQuery = "BEGIN TRANSACTION;
    UPDATE $tableName
        SET "
    for($i = 0;$i -lt $values.Count;$i++){
        $sqlQuery = "$sqlQuery$($values[$i].column)=@$($values[$i].column)"
        if($i -lt $values.Count-1){
            $sqlQuery = "$sqlQuery,"
    $sqlQuery = "$($sqlQuery)
        WHERE $($primaryKey.column) = @$($primaryKey.column);
    IF @@ROWCOUNT = 0
            INSERT INTO $tableName ($($primaryKey.column),"
    for($i = 0;$i -lt $values.Count;$i++){
        $sqlQuery = "$sqlQuery$($values[$i].column)"
        if($i -lt $values.Count-1){
            $sqlQuery = "$sqlQuery,"
    $sqlQuery = "$($sqlQuery)
            VALUES (@$($primaryKey.column),"
    for($i = 0;$i -lt $values.Count;$i++){
        $sqlQuery = "$($sqlQuery)@$($values[$i].column)"
        if($i -lt $values.Count-1){
            $sqlQuery = "$sqlQuery,"
    $sqlQuery = "$($sqlQuery));

    $sqlCmd=new-object system.Data.SqlClient.SqlCommand($sqlQuery, $sqlConn)
    $sqlCmd.Parameters.Add((New-OBJECT DATA.SQLClient.SQLParameter("@$($primaryKey.column)",$primaryKey.dataType))) | OUT-NULL
    $sqlCmd.Parameters[0].Value = $primaryKey.value
    for($i = 0;$i -lt $values.Count;$i++){
        $sqlCmd.Parameters.Add((New-OBJECT DATA.SQLClient.SQLParameter("@$($values[$i].column)",$values[$i].dataType))) | OUT-NULL
        $sqlCmd.Parameters[$i+1].Value = $values[$i].value

    for($i=0;$i -lt $sqlCmd.Parameters.count;$i++){
        if($sqlCmd.Parameters[$i].Value -eq $null){
            $sqlCmd.Parameters[$i].Value = [System.DBNull]::Value
    if($sqlCmd.ExecuteNonQuery() -ne 1){
        Throw $_

An example of how to connect from an Azure Function before using this command:

using namespace System.Data.SqlClient
using namespace System.Net
$msi_authenticationResult = Invoke-RestMethod -Method Get -Headers @{'Secret' = $env:MSI_SECRET} -Uri ($env:MSI_ENDPOINT +'?resource=')
$sqlConn = New-Object System.Data.SqlClient.SqlConnection
$sqlConn.ConnectionString = "Data Source =; Initial Catalog = yourdatabase"
$sqlConn.AccessToken = $msi_authenticationResult.access_token

Self scheduling cleanup job for MEM kiosk machines

Normally I’d recommend using the Unified Write Filter in Windows 10 to keep Kiosk machines in a semi-decent state.

For a customer that did not have this luxury, I wrote a tiny self-scheduling PowerShell script that will run as SYSTEM and clean up any of the specified folders in any of the user profiles on the machine.

This example can be used for many purposes to drop a script and maintain a scheduled task. Redeploying it will overwrite the dropped script and scheduled task as per the new config.

Git link or direct code here:

#Module name:      Invoke-wipeSpecifiedProfileFolders
#Author:           Jos Lieben
#Author Blog:
#Date:             18-12-2020
#License:          Free to use and modify non-commercially, leave headers intact. For commercial use, contact me
#Purpose:          Delete all files in the specified folder names in all user profiles on the machine, self-installs as a scheduled task
#Setup:            Deploy to machines, in system context
#Requirements:     Windows 10 build 1803 or higher

$folderWipeList = "Downloads,Network Shortcuts,Temp,Documents" #comma seperated list of folders to wipe

$desiredScriptFolder = Join-Path $env:ProgramData -ChildPath ""
$desiredScriptPath = Join-Path $desiredScriptFolder -ChildPath "Invoke-wipeSpecifiedProfileFolders.ps1"
    New-Item -Path $desiredScriptFolder -Type Directory -Force
Start-Transcript -Path (Join-Path $desiredScriptFolder -ChildPath "\folderWiperInstaller.log")

Write-Output "Configuring scheduled task..."

$taskname = "Invoke-wipeSpecifiedProfileFolders"
$taskdescription = "Delete all files in the specified folder names in all user profiles on the machine"
$action = New-ScheduledTaskAction -Execute 'Powershell.exe' -Argument "-NoProfile -WindowStyle Hidden -NonInteractive -ExecutionPolicy ByPass -File `"$desiredScriptPath`""
$triggers =  @()
$triggers += (New-ScheduledTaskTrigger -AtStartup)
$triggers += (New-ScheduledTaskTrigger -Daily -At 23:00)
$settings = New-ScheduledTaskSettingsSet -ExecutionTimeLimit (New-TimeSpan -Minutes 5) -RestartCount 3 -RestartInterval (New-TimeSpan -Minutes 1)
$task = Register-ScheduledTask -Action $action -Trigger $triggers -TaskName $taskname -Description $taskdescription -Settings $settings -User "System" -Force -RunLevel Highest

Write-Output "task info: "
Write-Output $task

Write-Output "Writing script file to local disk..."

$scriptContent = "
Start-Transcript -Path (Join-Path $desiredScriptFolder -ChildPath `"\folderWiper.log`")
`$folderWipeList = `"$folderwipeList`"
`$folderWipeList = `$folderwipeList.Split(`",`")
Get-ChildItem 'HKLM:\Software\Microsoft\Windows NT\CurrentVersion\ProfileList' | ForEach-Object {
    `$rootPath =  `$_.GetValue('ProfileImagePath') 
    Write-Output `"Parsing folders in `$rootPath`"
    `$childItems = `$Null
    `$childItems = Get-ChildItem -Path `$rootPath -Directory -ErrorAction SilentlyContinue -Recurse -Force | where{`$folderWipeList -contains `$_.BaseName}
    	foreach(`$folder in `$childItems){
    		Write-Output `"Wiping matched folder: `$(`$folder.FullName)`"
    		Get-ChildItem -Path `$folder.FullName -Force -ErrorAction SilentlyContinue | Remove-Item -Force -ErrorAction SilentlyContinue -Recurse -Confirm:`$False


Set-Content -Value $scriptContent -Path $desiredScriptPath -Force -Confirm:$False

Write-Output "Starting script as task for the first time..."

Start-ScheduledTask -InputObject $task

Write-Output "Install script has finished running"


Grouping devices in MDATP based on registered users

Microsoft Defender Advanced Threat Protection seems to be becoming the defacto leader in the A/V industry, at least when Windows is concerned, but other OS’es seem to be following quickly 🙂

At one of my international customers, many different locations and departments exist and we’d like to group devices in MDATP based on their primary user so we can assigned different administrators automatically, and apply different web filtering policies.

MDATP has the following options available for grouping:

These membership rules don’t say anything about the user, and the machine domains are all cloud native (no hybrid joins). So we need to use Tags to gain flexible targeting in MDATP.

The following PowerShell script can be scheduled as an Azure Runbook to automatically tag all your MDATP devices based on the ‘Company’ attribute of the device’s primary user. It could also be modified easily to e.g. parse a user’s group membership or UPN’s domain.

If you have a lot of devices, it may take a while for the first run (beyond Azure Automation limits), in that case run it locally first and then schedule it.

OnedriveMapper 4.04 with auto reconnect

Version 4.04 OnedriveMapper now automatically reconnects drives (of any type) when the cookie expires. No more ‘broken’ mappings! The script is smart enough to detect if its just a connectivity issue (= do nothing) or an actual drive issue.

All improvements since 4.00:

  1. Auto Remap (automatically reconnect disconnected drives)
  2. Block the IE firstrun wizard properly
  3. Bugfix: properly handle existing shortcuts instead of throwing an error
  4. Increase Converged Drive (single mapping with sub-mappings) reliability
  5. Better cleanup of existing mappings
  6. Always force the ‘keep me signed in’ option
  7. Support for root-level mappings