Simple ideas sometimes aren’t as simple to implement as the ask makes them sound. Got one such ask not too long ago.
I work for a global company that has offices is various regions across the world and sometimes in our busy schedules we forget about holidays and say things like .. “Okay.. I’ll see you Monday.. ” until someone reminds us.. “You mean Tuesday.. Monday is a holiday”. “Well why doesn’t my calendar already tell me so?”. Simple ask.. but now how do I make it happen? Here is an approach I used to make this happen (If you do this, make sure you test with a few accounts and gradually expand.. you are responsible for your own testing).
Pre-Requisites
- Azure Account with Blob Storage
- Azure Storage Explorer
- Active Directory Attribute reserved to track which users already have the calendar for the current year imported (you can choose to use another database alternatively, but it is easier to query which users don’t have the calendar imported)
- PST Files for each region (Only doing a single region in the script, but you can run it with a different list of Users + PST File each time). Avoid trying to do too much in a single script.
- Troubleshoot the failed jobs
This should give us a SAS URL that is valid till Dec 31st 2019, which is how long the calendar is valid. After that date the link will stop working, which is fine since the calendar has nothing of value past that date – we will bring in a new calendar each year. Anyone with the URL will be able to READ the PST file (no access to modify the PST), however it does not contain any sensitive data and is therefore safe to embed in scripts. Update the script with the URL, variables $st, $BLOBURI, $SignedURL and $year will need updating.
If you have used Microsoft Graph / Exchange REST or another mechanism to accomplish something similar, please do share. Here is the script
<span id="mce_SELREST_start" style="overflow:hidden;line-height:0;"></span>
#Holiday Calendar Import Script.. will find users matching a criteria in AD and import
$st = "?st=2018-xxxxxxxxxx"
$BlobURI = "https://example.blob.core.windows.net/myblob1/USHolidayCalendar.pst"
$SignedURL = "https://example.blob.core.windows.net/myblob1/USHolidayCalendar.pst?st=2018-10-12TZ&se=2020-01-01T1xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx"
$year = "2019"
$DebugPreference= 'Continue'
Function Get-MDY{
Get-Date -Format yyMMdd
}
Function Write-DebugLog{
PARAM(
[string]$Message,
[string]$file = "C:\scripts\holidaycalendar\importlog_$(Get-Mdy).log"
)
$dt = Get-date -Format u
"$dt $Message"| Out-File -FilePath $file -Append
Write-Debug "$dt $Message"
}
$resultCSV = "C:\scripts\holidaycalendar\jobresults_prod_$(Get-Mdy).csv "
#This account should have O365 privileges to create import jobs
$cred = Get-Credential -Credential "some.account@domain.com"
#Clear out any old sessions
Get-PSSession | Remove-PSSession
$session = New-PSSession -ConfigurationName Microsoft.Exchange -ConnectionURI https://ps.outlook.com/powershell/ -Credential $cred -Authentication Basic -AllowRedirection
Import-PSSession $session
#Get Employees in US
$arr=@()
$employeeBaseDN = "OU=Employees,DC=domain,DC=com"
$customAttr = extensionAttribute1
$emp = Get-ADUser -SearchBase -Filter `
{(co -eq 'US') -and (Enabled -eq $true) -and (EmailAddress -like '*') -and ((employeeType -eq 'Employee') -or (employeeType -eq 'Intern'))} `
-Properties EmailAddress, co, employeeType, extensionAttribute1 | where {$_.extensionAttribute1 -ne "$year"}
$arr = $emp
Write-Debuglog -Message "Starting Import for $($arr.count) Users"
#foreach US Employee Run Import
$count = 1
$sleepSecs = 30
$afterEvery = 50
foreach($obj in $arr){
$mbx = $obj.EmailAddress
#Throttle script speed.. sleep for $sleepSecs seconds after submitting $afterEvery jobs
if(($count % 50)-eq 0){
Start-Sleep -Seconds $sleepSecs
}
#If Email Address has value was found
if($mbx){
$jobname = "mailbox_calendar_import_$($obj.SamAccountName)"
Write-DebugLog -Message "Checking if we need to run $jobname for $mbx"
$jobItem = Get-MailboxImportRequest -name $jobname
#If the job exists in a failed state, remove it - because we are going to try again
if(Get-MailboxImportRequest -name $jobname){
Get-MailboxImportRequest | where {$_.name -eq $jobname -and $_.status -eq "Failed"} |
Remove-MailboxImportRequest -confirm:$false
}
if(-not ($jobItem | where {$_.Status -ne 'Failed'})){
Write-Debuglog -message "Starting $jobname"
if($jobItem){$jobItem | Remove-MailboxImportRequest -confirm:$false}
try{
$mailbox = Get-Mailbox $mbx
New-MailboxImportRequest -Name $jobname -Mailbox $($mailbox.Name) -BadItemLimit 250 `
-AzureBlobStorageAccountUri $BlobURI -AzureSharedAccessSignatureToken $st
$count++
} catch{
Write-DebugLog -Message "Couldn't create $jobname for $mbx"
}
}else{
#The job has already been run succesfully for this mailbox. Skip and mark the AD Attribute extensionAttribute1 as up to date for 2019.
Write-Debuglog -Message "$jobname already in progress or completed.. not starting"
}
}
else{
Write-DebugLog -Message "Mailbox not Found for $($obj.Name)"
}
}
#Check status of import jobs .. this can take several hours to complete... O365 will do finish it with low priority
#Just keep running this loop until it finishes
#Powershell connection times out at times need to update to re-establish if necessary
#Use Import-PSSession -AllowClobber to allow bringing the new session in
Get-MailboxImportRequest | where {$_.Name -match "mailbox_calendar_import" } | sort -Property Status | ogv
$pending = $true
#$VerbosePreference = 'Continue'
While($pending){
$jobs = Get-MailboxImportRequest | where {$_.Name -match "mailbox_calendar_import" }
$isItDoneYet = ($jobs | where {$_.status -ne "Completed" -and $_.status -ne "Failed"})
$inProgress = $jobs | where {$_.Name -match "mailbox_calendar_import" -and $_.status -eq "InProgress"}
$queued = $jobs | where {$_.Name -match "mailbox_calendar_import" -and $_.status -eq "Queued"}
$done = ($jobs | where {$_.Name -match "mailbox_calendar_import" -and $_.status -eq "Completed"})
$doneCount = ($done | Measure-Object).Count
if($isItDoneYet){
Write-Debuglog -Message "$doneCount Done. Still working on $($isItDoneYet.Count) requests: $(($inProgress| Measure-Object).count) are in Progress and $($queued.Count) are queued"
Start-Sleep -Seconds 300
} else{
Write-Debuglog -Message "All Done. Completed $doneCount requests"
$pending = $false
}
}
#Export Job Status details for
$jobs | export-csv -NoTypeInformation -path $resultCSV
#Foreach user where job is complete, lets go update AD and remove the job from O365
foreach($user in $arr){
$jobname = "mailbox_calendar_import_$($user.SamAccountName)"
$matchjob = $jobs | where {$_.name -eq $jobname -and ($_.status -eq 'Completed' -or $_.status -eq 'Failed')}
if($matchJob -and $($matchjob.Status -eq 'Completed')){
Write-Debuglog -Message "User: $($user.SamAccountName) Job: $jobname Status: $($matchjob.Status). Updating extensionAttribute1 in AD for $($user.SamAccountName)"
Set-ADUser -Identity $($user.SamAccountName) -Replace @{extensionAttribute1 = "$year"}
if(Remove-MailboxImportRequest -Identity $($matchjob.Identity) -Confirm:$false){
Set-ADUser -Identity $($user.SamAccountName) -Replace @{extensionAttribute1 = "$year"}
#Get-aduser $($user.SamAccountName) -Properties extensionAttribute1
}
} elseIf($matchJob -and $($matchjob.Status -eq 'Failed')){
Write-Debuglog -Message "User: $($user.SamAccountName) Job: $jobname Status: $($matchjob.Status)"
Write-Debuglog -Message "Exporting Diagnostic data to C:\scripts\holidaycalendar\logs\diagnostics\$($matchjob.Name)_$(Get-MDY).xml"
#Export failure results to file for diagnostics
$jobXML = Get-MailboxImportRequestStatistics -Identity $matchjob.identity
$jobXML | Export-Clixml "C:\scripts\holidaycalendar\logs\diagnostics\$($matchjob.Name)_$(Get-MDY).xml" -Force -Confirm:$false
Remove-MailboxImportRequest -Identity $($matchjob.Identity) -Confirm:$false
} else{
Write-Debuglog -Message "User: $($user.SamAccountName) Job: Not Found"
}
}
Relevant Security 