Every Friday, your employees must fill out their calendars for the coming week with which customers they plan to work. This is so their manager has an overview of their workload the next week, and gave gives them more tasks if needed. I know some share their calendar, and their manager must browse through all calendars every week to extract the “data”. This can be automated very simply. The only thing your employees need to do is to put the # sign at the start of the calendar event subject for the customer they plan to work on. This way, we can filter out and get all events that are customer related for the coming week.
The script should ideally be run on Saturday/Sunday, as it checks one week ahead of the day its runs. To have it work you need to create an Azure App Registration with the User.Read.All, Calendar.Read.All and Directory.Read.All rights. You also need an application secret, tenant id, and client/app id.
The output can look like this. You also see it handles multiple customers in one day.
Here is what the calendar looks like:
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| $TenantId = "xxxxxxxx" | |
| $ClientID = "xxxxxxxxx" | |
| $ClientSecret = "xxxxxxx" | |
| function Get-MSGraphAppToken{ | |
| <# .SYNOPSIS | |
| Get an app based authentication token required for interacting with Microsoft Graph API | |
| .PARAMETER TenantID | |
| A tenant ID should be provided. | |
| .PARAMETER ClientID | |
| Application ID for an Azure AD application. Uses by default the Microsoft Intune PowerShell application ID. | |
| .PARAMETER ClientSecret | |
| Web application client secret. | |
| .EXAMPLE | |
| # Manually specify username and password to acquire an authentication token: | |
| Get-MSGraphAppToken -TenantID $TenantID -ClientID $ClientID -ClientSecert = $ClientSecret | |
| .NOTES | |
| Author: Jan Ketil Skanke | |
| Contact: @JankeSkanke | |
| Created: 2020-15-03 | |
| Updated: 2020-15-03 | |
| Version history: | |
| 1.0.0 - (2020-03-15) Function created | |
| #>[CmdletBinding()] | |
| param ( | |
| [parameter(Mandatory = $true, HelpMessage = "Your Azure AD Directory ID should be provided")] | |
| [ValidateNotNullOrEmpty()] | |
| [string]$TenantID, | |
| [parameter(Mandatory = $true, HelpMessage = "Application ID for an Azure AD application")] | |
| [ValidateNotNullOrEmpty()] | |
| [string]$ClientID, | |
| [parameter(Mandatory = $true, HelpMessage = "Azure AD Application Client Secret.")] | |
| [ValidateNotNullOrEmpty()] | |
| [string]$ClientSecret | |
| ) | |
| Process { | |
| $ErrorActionPreference = "Stop" | |
| # Construct URI | |
| $uri = "https://login.microsoftonline.com/$tenantId/oauth2/v2.0/token" | |
| # Construct Body | |
| $body = @{ | |
| client_id = $clientId | |
| scope = "https://graph.microsoft.com/.default" | |
| client_secret = $clientSecret | |
| grant_type = "client_credentials" | |
| } | |
| try { | |
| $MyTokenRequest = Invoke-WebRequest -Method Post -Uri $uri -ContentType "application/x-www-form-urlencoded" -Body $body -UseBasicParsing | |
| $MyToken =($MyTokenRequest.Content | ConvertFrom-Json).access_token | |
| If(!$MyToken){ | |
| Write-Warning "Failed to get Graph API access token!" | |
| Exit 1 | |
| } | |
| $MyHeader = @{"Authorization" = "Bearer $MyToken" } | |
| } | |
| catch [System.Exception] { | |
| Write-Warning "Failed to get Access Token, Error message: $($_.Exception.Message)"; break | |
| } | |
| return $MyHeader | |
| } | |
| } | |
| $Count=0 | |
| #Generate Graph API access token | |
| $global:Header = Get-MSGraphAppToken -TenantID $TenantId -ClientID $ClientID -ClientSecret $ClientSecret | |
| $Date = get-date -Format "yyyy-MM-dd" | |
| $currentUriUsers = "https://graph.microsoft.com/v1.0/users?`$select=displayName,mail,userPrincipalName,id,userType,assignedLicenses" | |
| #Get all entries in SharePoint list. | |
| $Users = while (-not [string]::IsNullOrEmpty($currentUriUsers)) { | |
| # API Call | |
| # Write-Host "`r`nQuerying $currentUri..." -ForegroundColor Yellow | |
| $apiCall = Invoke-WebRequest -Method "GET" -Uri $currentUriUsers -ContentType "application/json" -Headers $global:Header -ErrorAction Stop -UseBasicParsing | |
| $nextLink = $null | |
| $currentUriUsers = $null | |
| if ($apiCall.Content) { | |
| # Check if any data is left | |
| $nextLink = $apiCall.Content | ConvertFrom-Json | Select-Object '@odata.nextLink' | |
| $currentUriUser = $nextLink.'@odata.nextLink' | |
| $apiCall.Content | ConvertFrom-Json | |
| } | |
| } | |
| $Users = $Users.value | Where-Object{$_.assignedLicenses} | |
| $useractivityList = @() | |
| foreach($user in $users){ | |
| $currentUriUser = "https://graph.microsoft.com/v1.0/users/$($user.userprincipalname)/calendar/events?`$filter=start/dateTime ge '$($Date)T00:00:00.00Z'" | |
| #Get all entries in SharePoint list. | |
| $Events = while (-not [string]::IsNullOrEmpty($currentUriUser)) { | |
| # API Call | |
| # Write-Host "`r`nQuerying $currentUri..." -ForegroundColor Yellow | |
| $apiCall = Invoke-WebRequest -Method "GET" -Uri $currentUriUser -ContentType "application/json" -Headers $global:Header -ErrorAction Stop -UseBasicParsing | |
| $nextLink = $null | |
| $currentUriUser = $null | |
| if ($apiCall.Content) { | |
| # Check if any data is left | |
| $nextLink = $apiCall.Content | ConvertFrom-Json | Select-Object '@odata.nextLink' | |
| $currentUriUser = $nextLink.'@odata.nextLink' | |
| $apiCall.Content | ConvertFrom-Json | |
| } | |
| } | |
| $Events = $Events.value | Where-Object{$_.Subject -like "#*"} | |
| $Monday = @() | |
| $Tuesday = @() | |
| $Wednesday = @() | |
| $Thursday = @() | |
| $Friday = @() | |
| Foreach($Event in $Events){ | |
| $DayOfTheWeek = @() | |
| $DayOfTheWeek = (get-date $Event.start.dateTime).DayOfWeek | |
| if($DayOfTheWeek -like "Monday"){If(!$Monday){$Monday += "$($Event.subject)"}Else{$Monday += ", $($Event.subject)"}} | |
| if($DayOfTheWeek -like "Tuesday"){If(!$Tuesday){$Tuesday += "$($Event.subject)"}Else{$Tuesday += ", $($Event.subject)"}} | |
| if($DayOfTheWeek -like "Wednesday"){If(!$Wednesday){$Wednesday += "$($Event.subject)"}Else{$Wednesday += ", $($Event.subject)"}} | |
| if($DayOfTheWeek -like "Thursday"){If(!$Thursday){$Thursday += "$($Event.subject)"}Else{$Thursday += ", $($Event.subject)"}} | |
| if($DayOfTheWeek -like "Friday"){If(!$Friday){$Friday += "$($Event.subject)"}Else{$Friday += ", $($Event.subject)"}} | |
| } | |
| $useractivity = @() | |
| $useractivity = [PSCustomObject]@{ | |
| Name = $User.displayName | |
| Monday = $($Monday -join "").Replace("#","") | |
| Tuesday = $($Tuesday -join "").Replace("#","") | |
| Wednesday = $($Wednesday -join "").Replace("#","") | |
| Thursday = $($Thursday -join "").Replace("#","") | |
| Friday = $($Friday -join "").Replace("#","") | |
| } | |
| $useractivityList += $useractivity | |
| } | |
| $useractivityList |
A blog about automation and technologies in the cloud 