Blog

View licensed and unlicensed Microsoft 365 users.

### To view the list of all disabled licensed user accounts in your organization, run the following command:
Get-AzureAdUser -All $true -Filter "AccountEnabled eq false" | 
ForEach { $licensed=$False ; For ($i=0; $i -le ($_.AssignedLicenses | 
Measure).Count ; $i++) { If( [string]::IsNullOrEmpty( $_.AssignedLicenses[$i].SkuId ) -ne $True) { $licensed=$true } } ; If( $licensed -eq $true) { Write-Host $_.UserPrincipalName} }

### To view the list of all unlicensed user accounts in your organization, run the following command:
Get-AzureAdUser | 
ForEach{ $licensed=$False ; For ($i=0; $i -le ($_.AssignedLicenses | 
Measure).Count ; $i++) { If( [string]::IsNullOrEmpty( $_.AssignedLicenses[$i].SkuId ) -ne $True) { $licensed=$true } } ; If( $licensed -eq $false) { Write-Host $_.UserPrincipalName} }

### To view the list of all licensed user accounts in your organization run the following command:
Get-AzureAdUser | 
ForEach { $licensed=$False ; For ($i=0; $i -le ($_.AssignedLicenses | 
Measure).Count ; $i++) { If( [string]::IsNullOrEmpty( $_.AssignedLicenses[$i].SkuId ) -ne $True) { $licensed=$true } } ; If( $licensed -eq $true) { Write-Host $_.UserPrincipalName} }

User case

Is it possible to create a list of licensed users who have not or never logged in in the past 90 days?

First step - Create an app registration

Go to the Azure Portal > App registrations and create an app registration with the following permissions:

Copy the following values and paste them in the Powershell Script.

• ApplicationID --> $ApplicationID
• Directory (tenant) ID --> $TenantDomainName

Create a secret key, copy the value and paste it into the script:

• $AccessSecret

$ApplicationID = "-----"
$TenantDomainName = ""
$AccessSecret ="-----"
$Body = @{
Grant_Type = "client_credentials"
Scope = "https://graph.microsoft.com/.default"
client_Id = $ApplicationID
Client_Secret = $AccessSecret
}
$ConnectGraph = Invoke-RestMethod -Uri "https://login.microsoftonline.com/$TenantDomainName/oauth2/v2.0/token" -Method POST -Body $Body
$AccessToken = $ConnectGraph.access_token

#Form request headers with the acquired $AccessToken
$headers = @{'Content-Type'="applicationjson";'Authorization'="Bearer $AccessToken"}

#This request get users list with signInActivity.
$ApiUrl = "https://graph.microsoft.com/beta/users?`$select=displayName,userPrincipalName,onPremisesSamAccountName,Mail,signInActivity,userType,assignedLicenses&`$top=999"

$Result = @()
While ($ApiUrl -ne $Null) #Perform pagination if next page link (odata.nextlink) returned.
{
$Response = Invoke-RestMethod -Method GET -Uri $ApiUrl -ContentType "applicationjson" -Headers $headers
if($Response.value)
{
$Users = $Response.value
ForEach($User in $Users)
{

$Result += New-Object PSObject -property $([ordered]@{ 
DisplayName = $User.DisplayName
onPremisesSamAccountName = $User.onPremisesSamAccountName
UserPrincipalName = $User.userPrincipalName
Email = $User.Mail
LastSignInDateTime = if($User.signInActivity.lastSignInDateTime) { [DateTime]$User.signInActivity.lastSignInDateTime } Else {$null}
LastNonInteractiveSignInDateTime = if($User.signInActivity.lastNonInteractiveSignInDateTime) { [DateTime]$User.signInActivity.lastNonInteractiveSignInDateTime } Else { $null }
IsLicensed = if ($User.assignedLicenses.Count -ne 0) { $true } else { $false }
IsGuestUser = if ($User.userType -eq 'Guest') { $true } else { $false }
})
}

}
$ApiUrl=$Response.'@odata.nextlink'
}

$DaysInactive = 90
$dateTime = (Get-Date).Adddays(-($DaysInactive))
$Result | 
Where-Object { $_.LastSignInDateTime -eq $Null -OR $_.LastSignInDateTime -le $dateTime } | 
Where-Object { $_.IsLicensed -eq $true } |
#Where-Object { $_.LastSignInDateTime -ne $null } |
#Where-Object { ($_.UserPrincipalName -notlike "*adm*") -or ($_.UserPrincipalName -notlike "*svc*")} #-or ($_.UserPrincipalName -notlike "*test*") -or ($_.UserPrincipalName -notlike "*sync*") } |
Export-CSV "C:TempLastLoginDateReport.CSV" -NoTypeInformation -Encoding UTF8

Write-host "Create CSV file"
Start-Sleep -Seconds 5

 

The Microsoft Azure REST APIs for application insight can help you to delete / remove / purge data stored in application insights based on filters you specify. This API (MS docs) permits you to purge data in an Application Insights component by applying a set of user-defined filters.

First step:
To use the REST API you need to authenticate by using Azure Active Directory OAuth2. To do that, you need to register an application into your Azure AD:

When the app is created, create a key for the application registration.
Select your AD application, click on Certificates and Secrets and then create a new client secret. Save the value, because you will need it later.

Next step:
You need to assign a role to your app in order to access the Application Insights resource.
Select the Application Insight resource, click on Access Control (IAM) and then add a role assignment:

 

Create a new role assignment with Data Purger as the role and select your app registration (Created in the first step; in my case PureAppInsights):

 

After the role assignment the setup for authorization is finished.

We are ready to build the powershell script.

Go to the registered app to get theapplication(client) ID and Directory (tenant) ID.

Go t

 

## AUTHENTICATION PART ##
# Import the AzureRM.Profile module #
Import-Module AzureRM.Profile
# Application (Client) ID of the registered app #
$appId = "APPLICATION_ID_REGISTERED_APP"
# Client secret value for the app registration #
$key = "CLIENT_SECRET_REGISTERED_APP"
# Azure Active Directory tenant ID #
$tenantId = "AZURE_AD_TENANT_ID"
# Azure Subscription ID #
$subscriptionId = "SUBSCRIPTION_ID"
# Resource group of the Application Insight Recource #
$resourceGroupName = "APPINSIGHTS_RESOURCEGROUP_NAME"
# Name of the Application Insight instance #
$resourceName = "APPINSIGHTS_RESOURCE_NAME"
# Create the authentication URL #
$authUrl = "https://login.windows.net/${tenantId}"
# Get the authentication context #
$AuthContext = [Microsoft.IdentityModel.Clients.ActiveDirectory.AuthenticationContext]$authUrl
# Build the credential object #
$credentials = New-Object Microsoft.IdentityModel.Clients.ActiveDirectory.ClientCredential -ArgumentList $appId,$key
# Get the token form AAD #
$result = $AuthContext.AcquireToken("https://management.core.windows.net/",$credentials)
# Build the authorization header JSON object #
$authHeader = @{
'Content-Type'='application/json'
'Authorization'=$result.CreateAuthorizationHeader()
}
## END AUTHENTICATION PART ##
## PURGE DATA PART ##
# Creates the API URI #
$URI = "https://management.azure.com/subscriptions/${subscriptionId}/resourceGroups/${resourceGroupName}/providers/Microsoft.Insights/components/${resourceName}/purge?api-version=2015-05-01"

# ***Choose which table you want to purge and enter a date when you want to purge #
$body = @"
{
   "table": "traces",
   "filters": [
     {
       "column": "timestamp",
       "operator": "<",
       "value": "2022-02-02T00:00:00.000"
     }
   ]
}
"@
# Invoke the REST API to purge the data on the Application Insights recource you have chosen #
$purgeID=Invoke-RestMethod -Uri $URI -Method POST -Body $body -Headers $authHeader
Start-Sleep -Seconds 5
# Show the purge ID #
Write-Host "Purge ID" $purgeID.operationId
## END PURGE DATA PART ##
## GET PURGE STATUS PART ##
# Create the API URI to get the purge status #
$purgeURI="https://management.azure.com/subscriptions/$subscriptionId/resourceGroups/${resourceGroupName}/providers/Microsoft.Insights/components/${resourceName}/operations/$($purgeID.operationId)?api-version=2015-05-01"
Invoke-RestMethod -Uri $purgeURI -Method GET -Headers $authHeader
## END GET PURGE STATUS PART ##

Run the script and you'll get the following response.

 

 

 

A purge task of Application Insights data is not immediatly executed. You can see the status of the task by sending a GET request as follows:

Invoke-RestMethod -Uri $uri -Method GET -Headers $authHeader

You also can check the activity log of the App Insights resource to check if the purge action is started:

 

 

 

If you check the status a day later, you will get the result below.

***Choose the table you want to purge

Add to:

"table": "tablename"

<#
Author: Kevin Pas
Date: 16-10-2020
Title: Add multiple users to WVD App Group
Description: With the script below you can read a txt file with the email addresses of the people who need to be added to the app group.

#>

$UserNames = Get-Content "C:\Temp\Users.txt"
$TenantName = "contoso"
$HostPoolName = "contosoHostPool"
$AppGroupName = "contosoAppGroupName"
$GetCurrentUsers = (Get-RdsAppGroupUser -TenantName $TenantName -HostPoolName $HostPoolName -AppGroupName $AppGroupName).UserPrincipalName

ForEach ($User in $UserNames) {
if($GetCurrentUsers -eq $User){

Write-Host "$User is already member of the app group"

}
else{
Write-Host "$User is added to the app group"
Add-RdsAppGroupUser -TenantName $TenantName -HostPoolName $HostPoolName -AppGroupName $AppGroupName -UserPrincipalName $user
}
}
Write-Host "`nThe persons below are now members of the app group"
Get-RdsAppGroupUser -TenantName $TenantName -HostPoolName $HostPoolName -AppGroupName $AppGroupName | ft UserPrincipalName

Met dit script kun je een backup maken van de Azure SQL databases. Onderstaand script is gemaakt voor een Octopus deploy. Octopus Deploy is een tool voor het geautomatiseerd deployen van software.

Pas de variabele aan met de juiste gegevens.

 

$ResourceGroupName = $OctopusParameters['ResourceGroupName']
$SqlServerName = $OctopusParameters['SqlServerName']
$SqlServerPassword = $OctopusParameters['SqlServerPassword']
$SqlServerUser = $OctopusParameters['SqlServerUser']
$StorageAccount = $OctopusParameters['StorageAccount']
$StorageKey = $OctopusParameters['StorageKey']
$StorageKeytype = $OctopusParameters['StorageAccessKey']
$StorageContainerName = $OctopusParameters['StorageContainerName']
$BacpacUri = $OctopusParameters['BacpacUri']
$DatabaseName = $OctopusParameters['DatabaseName']

# Check if Windows Azure Powershell is avaiable
try{
Import-Module Azure -ErrorAction Stop
}catch{
throw "Windows Azure Powershell not found! Please make sure to install them from http://www.windowsazure.com/en-us/downloads/#cmd-line-tools"
}

$dateTime = get-date -Format "_yyyyMMd_HHmmss"
$blobName = "Deployment-Backup/Database/$DatabaseName$dateTime.bacpac"
Write-Host "Using blobName: $blobName"

# Create Database Connection
$securedPassword = ConvertTo-SecureString -String $SqlServerPassword -asPlainText -Force
$serverCredential = new-object System.Management.Automation.PSCredential($SqlServerUser, $securedPassword)
#$databaseContext = New-AzureSqlDatabaseServerContext -ServerName $SqlServerName -Credential $serverCredential

# Create Storage Connection
#$storageContext = New-AzureStorageContext -StorageAccountName $StorageAccount -StorageAccountKey $StorageKey

#Initiate the Export
#Start-AzureSqlDatabaseExport -StorageContext $storageContext -SqlConnectionContext $databaseContext -BlobName $blobName -DatabaseName $DatabaseName -StorageContainerName $StorageContainerName

$exportRequest = New-AzureRmSqlDatabaseExport –ResourceGroupName $ResourceGroupName –ServerName $SqlServerName `
–DatabaseName $DatabaseName –StorageKeytype $StorageKeytype –StorageKey $StorageKey -StorageUri $BacpacUri/$blobName `
–AdministratorLogin $SqlServerUser –AdministratorLoginPassword $securedPassword

# Check status of the export
Start-Sleep -s 10
for($counter = 1; $counter -le 10; $counter++)
{
Start-Sleep -s 10
Get-AzureRmSqlDatabaseImportExportStatus -OperationStatusLink $exportRequest.OperationStatusLink
"Loop number $counter"
}
#Get-AzureRmSqlDatabaseImportExportStatus -OperationStatusLink $exportRequest.OperationStatusLink

Op dit moment is er nog geen optie om Azure notificaties te sturen wanneer een backup niet of wel heeft gelopen. Ik heb hier een Powershell script voor geschreven. Dit script gaan we aftrappen via de Azure Automation feature in de Subscription waar de Web App zich bevindt.

Wat heb je nodig?

• Azure Subscription (activate your MSDN subscriber benefits or sign up for a free account)
• Azure Automation account (Create automation account)
• Een Sendgrid account(gratis mailprovider - Install via Azure Portal - Browse - SendGrid Accounts - Meer info)

---

Check Backup Powershell script

Ga naar het "Edit Powershell Runbook" venster.

Line 26: Kies bij "-To" een emailadres waar de mail naar verstuurd moet worden - Kies bij "-From" een logisch emailadres.### Storage information

---

$StorageKey = "Key1 van het storageaccount"
$StorageAccountName = "Storageaccount waar de backup wordt gemaakt"
$Container = "Container waar de backup geplaatst wordt"
$context = New-AzureStorageContext -StorageAccountName $StorageAccountName -StorageAccountKey $StorageKey = ""
$FromEmail = ""
$ToEmail = ""

 
### Sendgrid information
$SendgridPw = ConvertTo-SecureString "Sendgrid Password" -AsPlainText -Force
$SendgridCreds = New-Object System.Management.Automation.PSCredential ("Sendgrid Account", $SendgridPw)
 
 
### Body of the email
$body = "Deze mail wordt automatisch verstuurd via een script dat de backup checkt<br>"
$body += "Om 22:00 wordt SQL en de website gebackupt. Het script checkt of er een backup file in de storage is geplaatst.<br>"
### Make a list with the name of the file and last modified date
$body += (Get-AzureStorageBlob -Container $Container -blob *.* -Context $context) | Select-Object Name, LastModified | ConvertTo-Html
 
 
### Web App backup is running on 22:00. This scripts runs on 23:30. Send a mail when the last file is older than 2h
if (Get-AzureStorageBlob -Container $Container -blob *.* -Context $context | Where-Object { $_.LastModified -gt ((Get-Date).AddHours(-2)) })
### Check if the backup file is in the blob
{Write-Output "Backup file is in the blob"}
else {
### Backup from today is not found in the blob.. send an email..
Write-Output "Sending Mail..."
Send-MailMessage -From $FromEmail -To $ToEmail -Subject "Check Azure Backup" -Body $body -BodyAsHtml -SmtpServer smtp.sendgrid.net -Credential $SendgridCreds
Write-Output "Mail is send!"
}

---

Klik op "Publish" als je klaar bent met editen. Wil je nog testen? Klik dan op "Test Pane"

Het script moet elke dag runnen dus er moet een schedule gekoppeld worden.

Klik op "Schedules" - "Add a schedule" - "Link a schedule to your runbook" - "Create a new schedule" - Vul de velden in.

Regex gebruik ik vooral bij iis rewrite en redirects. Er zijn tig mogelijkheden hoe je een redirect of rewrite op bouwt. Als geheugensteun maak ik regelmatig gebruik van deze pagina. Om te controleren of een Regex valide is gebruik ik deze pagina.

New Relic is application performance management tool waarmee je de performance van een website/applicatie kan monitoren. New Relic zorgt voor een helder inzicht in hoe jouw website presteert en waar zich eventuele problemen bevinden en gaan bevinden. Ikzelf gebruik het vaak om te troubleshooten en om te kijken waar je de performance van een website kunt verbeteren.