How to export owners, members and other details of a Microsoft Team based on user ownership status

Problem

You have been asked to provide a list of all Microsoft Teams that a user owns. The list should include the members of each Team, owners and Teams details. You are provided with a long list of users to run this task against.

Solution

An easy way to do this is by developing a PowerShell script. I had a need to provide these details during the course of a Teams to Teams migration activity. In the script below, these are the steps I applied.

  1. Import a list of user’s UPN from a csv file.
  2. Foreach user in the list, identify Teams ID they are a member of.
  3. Foreach identified Team, export all users in the Team, their roles, UPN, the Team ID, DisplayName of the user and the number of owners in the Team.
  4. Foreach Team associated with each of the users exported, verify if the user is an owner of the Team.
  5. If the user is an owner of the Team in question and the number of owners is greater than 1, export the DisplayName of the team, email address of the Team, Description, Visibility, MailNickName of the Team.
  6. If the number of owners in the Team is equal to 1, export the DisplayName of the team, email address of the Team, Description, Visibility, MailNickName of the Team.
  7. Combine both exports into a single list. This gives you a list of all the Teams.
  8. Using this list, verify the number of owners identified in each and generate another list indicating the ones that are owned specifically by the user in question or owned by multiple owners including the user in question. The final list should include the following details:
    • Team ID
    • Role of the user
    • Team DisplayName they below to
    • Team MailNickName
    • User UPN
    • User DisplayName
    • Team EmailAddress
    • Number Of Owners in the Team
  9. Finally, I found a little bug that I could not find a way to fix. This final list returns a blank user detail in the first index. I excluded that using the final if statement.

Full Script:

#You will need a connection to Microsoft Teams and Exchange online

$TeamsExport = @()
$Importfile = Import-csv "C:\Users.csv"
Foreach($DUser in $ImportFile)
{
$TeamID = @()
$TeamID = Get-Team -User $DUser.UPN | select groupID
$Teams = @()
$OwnedGroup = @()
foreach($Team in $TeamID) {
$OwnerCount = $Null
$OwnerCount = ((Get-Teamuser -GroupId $Team.GroupId).role -eq "owner").count
$OwnedGroup = Get-Teamuser -GroupId $Team.GroupId
$Count = @()
$Count = (Get-Teamuser -GroupId $Team.GroupId).count
Do{
$Teams += New-Object PSObject -property @{
Role = $OwnedGroup.Role[$Count]
TeamID = $Team.GroupId
User = $OwnedGroup.User[$Count]
Name = $OwnedGroup.Name[$Count]
UPN = $DUser.UPN
NumberOfOwners = $OwnerCount
}
$Count--
}
while($Count -gt 0)
}
Foreach($Group in $Teams){
If(($Group.Role -eq "Owner") -and ($Group.User -eq $Group.UPN) -and ($Group.NumberOfOwners -gt 1))
{
$GroupValue = $Null
$GroupValue = Get-Team -GroupId $Group.TeamID |select DisplayName,Description,Visibility,MailNickName
$TeamsExport += New-Object PSObject -property @{
GroupId = $Group.TeamID
OwnerCount = $Group.NumberOfOwners
OwnerDisplayName = $Group.Name
OwnerUPN = $Group.UPN
GroupDisplayName = $Groupvalue.DisplayName
Description = $GroupValue.Description
Visibility = $GroupValue.Visibility
MailNickName = $GroupValue.MailNickName
GroupEmailAddress = (Get-Unifiedgroup -Identity $Group.TeamID).primarysmtpaddress
}
}
elseif(($Group.Role -eq "Owner") -and ($Group.User -eq $Group.UPN) -and ($Group.NumberOfOwners -eq 1))
{
$GroupValue = $Null
$GroupValue = Get-Team -GroupId $Group.TeamID |select DisplayName,Description,Visibility,MailNickName
$TeamsExport += New-Object PSObject -property @{
GroupId = $Group.TeamID
OwnerCount = 1
OwnerDisplayName = $Group.Name
OwnerUPN = $Group.UPN
GroupDisplayName = $Groupvalue.DisplayName
Description = $GroupValue.Description
Visibility = $GroupValue.Visibility
MailNickName = $GroupValue.MailNickName
GroupEmailAddress = (Get-UnifiedGroup -Identity $Group.TeamID).primarysmtpaddress
}
}
}
}
$MemberExport = @()
Foreach($Item in $TeamsExport)
{
$GOwnerCount =$Null
$GOwnerCount = ((Get-Teamuser -GroupId $Item.GroupId).role -eq "owner").count
If($GOwnerCount -gt 1)
{
$MemberCount = $Null
$MemberCount = (Get-Teamuser -GroupId $Item.GroupId).count
$Membervalue = $Null
$Membervalue = Get-Teamuser -GroupId $Item.GroupId | select User, Role, Name
$TeamEmailAddress =$Null
$TeamEmailAddress = (Get-UnifiedGroup -Identity $Item.GroupId).primarysmtpaddress
Do{
$MemberExport += New-Object PSObject -property @{
TeamID = $Item.GroupId
Role = $Membervalue.Role[$MemberCount]
TeamDisplayName = $Item.GroupDisplayName
TeamMailNickName = $Item.MailNickName
MemberUPN = $Membervalue.User[$MemberCount]
MemberDisplayName = $Membervalue.Name[$MemberCount]
TeamEmailAddress = $TeamEmailAddress
NumberOfOwners = $GOwnerCount
}
$MemberCount--
}
while($MemberCount -gt 0)
}
elseif($GOwnerCount -eq 1)
{
$MemberCount = $Null
$MemberCount = (Get-Teamuser -GroupId $Item.GroupId).count
$Membervalue = $Null
$Membervalue = Get-Teamuser -GroupId $Item.GroupId | select User, Role, Name
$TeamEmailAddress =$Null
$TeamEmailAddress = (Get-UnifiedGroup -Identity $Item.GroupId).primarysmtpaddress
Do{
$MemberExport += New-Object PSObject -property @{
TeamID = $Item.GroupId
Role = $Membervalue.Role[$MemberCount]
TeamDisplayName = $Item.GroupDisplayName
TeamMailNickName = $Item.MailNickName
MemberUPN = $Membervalue.User[$MemberCount]
MemberDisplayName = $Membervalue.Name[$MemberCount]
TeamEmailAddress = $TeamEmailAddress
NumberOfOwners = 1
}
$MemberCount--
}
while($MemberCount -gt 0)
}
}
Write-host "Teams" -ForegroundColor Yellow
$TeamsExport
$MemberExportB = @()
Foreach($M in $MemberExport)
{If($M.MemberUPN -ne $Null)
{
$MemberExportB += New-Object PSObject -property @{
TeamID = $M.TeamID
Role = $M.Role
TeamDisplayName = $M.TeamDisplayName
TeamMailNickName = $M.TeamMailNickName
MemberUPN = $M.MemberUPN
MemberDisplayName = $M.MemberDisplayName
TeamEmailAddress = $M.TeamEmailAddress
NumberOfOwners = $M.NumberOfOwners}
}
}
Write-host "Members" -ForegroundColor Yellow
$MemberExportB

Sponsored Post Learn from the experts: Create a successful blog with our brand new courseThe WordPress.com Blog

WordPress.com is excited to announce our newest offering: a course just for beginning bloggers where you’ll learn everything you need to know about blogging from the most trusted experts in the industry. We have helped millions of blogs get up and running, we know what works, and we want you to to know everything we know. This course provides all the fundamental skills and inspiration you need to get your blog started, an interactive community forum, and content updated annually.

Automatically disable AD Connect scheduler and increase deletion threshold through a Self-Service App – reset to default and resume DirSync

Problem

Do you administer AD Connect and routinely have situations where other teams that manage Active Directly reach out to you to increase AD Connect threshold during a weekend activity? If you are in this boat, this automation is for you as this would avoid you having to be around to perform this task. This could also be used by anyone that manages both services as they could set this up one time without having to login to AD Connect server to perform this routine task.

Solution

Here, we have a PowerApps form with a start and end time fields for capturing requests that are placed by the Active Directory team. The form is essentially used to capture a planned activity with the scheduled start time and the end time. The entries are saved in a SharePoint list that is associated with the PowerApps form. A Powershell script routinely checks for a new entry and whenever there is an entry, the start time and end time is copied and used to initiate disabling of DirSync scheduler (as long as it is not busy) and then increase deletion threshold from the default value to 10,000 (you can set this to unlimited if you wish). The script continues to check for new requests and if an existing request is already in motion, it checks if the requested end time is due and then resumes DirSync approximately 30 minutes after the end time is due. When the objects have been processed and dirsync is no longer busy, it reverts the deletion threshold to the default value. Otherwise, if it finds more than one request, it checks if the duplicate request scheduled window is in conflict with the oldest request. If there is a conflict, it will delete the new request and inform the requestor to submit a new request. Otherwise, it skips the new request for later processing. It also sends an email to the AD Connect administrator whenever any of these actions are applied. Please find the entire Powershell script below. To complete the form section, you can learn how to integrate PowerApp forms with a Powershell script here.

Full Script: Updated 10/31/2020 – Only deletes duplicate entries that are in conflict with the oldest request scheduled activity window.

Remove object deletion threshold in Dirsync (AD Connect) and disable the scheduler - https://kdships.sharepoint.com/sites/kdships/Lists/ADConnect
 Set variable for MyUPN, MySAM, SiteURL and TeamListName
 $MyUPN = "XXXXXXX@kdships.com"
 $MySAM = $MyUPN.Split("@")[0]
 $SiteURL = "https://kdships.sharepoint.com/sites/Kdships"
 $TeamListName = "ADConnect"
 Set more variables
 $FailedtoProcess = @()
 $Today3 = Get-date
 $Today4 = $Today3.ToShortDateString()
 $Today5 = $Today4 -replace '/','_'
 Start logging
 Start-Transcript -Path "PATH to log file_$Today5.txt" -Append
 Write-host "Logging started: $Today3"
 Uncomment this after $PW value is set
 $Username = “loginaccount@kdships.com”
 Comment out this line after $Cred value is set.
 $Cred = Get-Credential loginaccount@kdships.com
 Comment out this line after $PW value is set
 $PWA = $Cred.Password | ConvertFrom-SecureString
 Copy the value in $PWA and paste here. Uncomment this after pasting $PWA value
 $PW = "PASTE $PWA value here" | ConvertTo-SecureString -Force
 Uncomment this after $PW value is set
 $UserCredential = New-Object System.Management.Automation.PSCredential -ArgumentList $Username,$PW
 Connect to Office 365 and PnPOnline
 Try{
 Connect to PnP Online and setting up TLS
 [Net.ServicePointManager]::SecurityProtocol = [Net.SecurityProtocolType]::Tls12 #May not be required in your environment
 Connect-PnPOnline -Url $SiteURL -Credentials $UserCredential
 Start-Sleep -Seconds 1
 }
 Catch{
 Write-host "Failed to connect to PnP. $_" -ForegroundColor Red
 Stop-Transcript
 Exit
 }
 Check status of ADConnect sync cycle
 If ((Get-ADSyncScheduler).SyncCycleInProgress -eq $False)
 {
 Capture list items and store them in TeamEntries
 function Convert-ToLocal
 {
  param(
   [parameter(Mandatory=$true)]
   [String] $CTime
   )
  $strCurrentTimeZone = (Get-WmiObject win32_timezone).StandardName
  $SystemTimeZone = [System.TimeZoneInfo]::FindSystemTimeZoneById($strCurrentTimeZone)
  $LocalTime = [System.TimeZoneInfo]::ConvertTimeFromUtc($CTime, $SystemTimeZone)
  Return $LocalTime
 }
 $TeamEntries = $Null
 $TeamEntries = Get-PnpListItem -List $TeamListName
 if($TeamEntries.count -gt 1)
 {
 Write-host "Multiple entries found. Identifying oldest entry" -ForegroundColor Yellow
 $TeamEntriesNew = $Null
 $TeamEntriesNew = $TeamEntries | Select -First 1
 foreach ($Entry in $TeamEntries)
     {
     Start-sleep -Seconds 10
     If($entry.Fieldvalues.ID -eq $TeamEntriesNew.ID)
     {
                 $RequestorEmail = $Null
                 $EntryID = $Null
                 $StartTime = $Null
                 $EndTime = $Null
                 $Created = $Null
                 $From = "TeamSenderEmail@kdships.com"
                 $Created = Convert-ToLocal -CTime $entry.Fieldvalues.Created
                 $StartTime = Convert-ToLocal -CTime $entry.Fieldvalues.TerminationStartTime
                 $EndTime = Convert-ToLocal -CTime $entry.Fieldvalues.TerminationEndTime
                 $EntryID = $entry.Fieldvalues.ID
                 $RequestorEmail = $entry.Fieldvalues.Author.Email
                 If($StartTime -gt (Get-date).AddDays(.082999))
                 {
                     Write-host "Start time is more than two hours away. EntryID: $EntryID. Will attempt this again in 10mins" -ForegroundColor Red
                     Stop-Transcript
                     Exit
                 }
                 elseif(($EndTime -lt (Get-date).AddDays(-.021000)) -and ((Get-ADSyncScheduler).SyncCycleEnabled -eq $false)) #Resume scheduler without reverting deletion threshold
                 {
                         #Set file variable
                         $RequestorEmail = $Null
                         $EntryID = $Null
                         $StartTime = $Null
                         $EndTime = $Null
                         $From = "TeamSenderEmail@kdships.com"
                         $StartTime = Convert-ToLocal -CTime $entry.Fieldvalues.TerminationStartTime
                         $EndTime = Convert-ToLocal -CTime $entry.Fieldvalues.TerminationEndTime
                         $EntryID = $entry.Fieldvalues.ID
                         $RequestorEmail = $entry.Fieldvalues.Author.Email
                             If((Get-ADSyncExportDeletionThreshold).ThresholdCount -gt 500)
                             {
                             #Re-enable the scheduler                                 Try                                 {                                 Set-ADSyncScheduler -SyncCycleEnabled $True                                 Start-sleep -S 3                                 Send-MailMessage -From $From -to "TeamEmail@kdships.com" -Subject "AD Connect - ADSync Scheduler has been re-enabled" -Body "AdSync scheduler has been re-enabled" -priority Normal -SmtpServer smtp.kdships.net                                 }                                 Catch                                 {                                 Write-host "Failed to renable the scheduler." -ForegroundColor Red                                 Send-MailMessage -From $From -to "TeamEmail@kdships.com" -Subject "Action Required: Failed to re-enable the scheduler" -Body "The scheduler failed to start while being re-enabled." -priority Normal -SmtpServer smtp.kdships.net                                 }                         }                         else                         {                         Write-host "AD Connect - Threshold was already reverted to default. Multiple entry block. EntryID: $EntryID" -ForegroundColor Yellow                         }             }             elseif(($EndTime -lt (Get-date).AddDays(-.021000)) -and ((Get-ADSyncScheduler).SyncCycleEnabled -eq $true) -and ((Get-ADSyncExportDeletionThreshold).ThresholdCount -gt 500) -and ((Get-ADSyncScheduler).SyncCycleInProgress -eq $false))             {                     #Set file variable                     $RequestorEmail = $Null                     $EntryID = $Null                     $StartTime = $Null                     $EndTime = $Null                     $From = "TeamSenderEmail@kdships.com"                     $StartTime = Convert-ToLocal -CTime $entry.Fieldvalues.TerminationStartTime                     $EndTime = Convert-ToLocal -CTime $entry.Fieldvalues.TerminationEndTime                     $EntryID = $entry.Fieldvalues.ID                     $RequestorEmail = $entry.Fieldvalues.Author.Email                                                    #Reset threshold and start the scheduler                                 Try                                 {                                 Enable-ADSyncExportDeletionThreshold -DeletionThreshold 500                                 Write-host "AD Connect - Threshold has been reverted to default. EntryID: $EntryID" -ForegroundColor Yellow                                 Start-sleep -S 5                                 Send-MailMessage -From $From -to "TeamEmail@kdships.com" -Subject "AD Connect - object deletion threshold reverted to default" -Body "Object deletion threshold has been reverted to default" -priority Normal -SmtpServer smtp.kdships.net                                 }                                 Catch                                 {                                 Write-host "Failed to reset threshold to default. Verify threshold and reset if not already." -ForegroundColor Red                                 Send-MailMessage -From $From -to "TeamEmail@kdships.com" -Subject "Action Required: Failed to start the scheduler" -Body "Object deletion threshold has been reverted to default. However, the scheduler failed to start. Verify threshold and reset if not already." -priority Normal -SmtpServer smtp.kdships.net                                 }                      #Remove entry from the sharepoint list using powerautoamte                     $UriTeam = "https://XXXXXXXX.logic.azure.com:443/workflows/XXXXXXXXXXXX/triggers/manual/paths/invoke?api-version=2016-06-01&sp=%2Ftriggers%2Fmanual%2Frun&sv=1.0&sig=-XXXXXXXXX"                     $BodyT = [ordered] @{                     EntryID = $EntryID                     } | ConvertTo-Json -Depth 10                     Invoke-RestMethod -Method POST -Uri $UriTeam -Body $BodyT -ContentType application/json             }             elseif(($StartTime -lt (Get-date).AddDays(.082999)) -and ((Get-ADSyncScheduler).SyncCycleInProgress -eq $False))  #If start time is about 2hrs away and sync is not running, check if scheduler is enabled, then disable it. Then check if threshold is at default, then increase it.             {                                 #Stop the scheduler                                 If((Get-ADSyncScheduler).SyncCycleEnabled -eq $true)                                 {  Try                                     {                                     Set-ADSyncScheduler -SyncCycleEnabled $false                                     Write-host "Scheduler paused." -ForegroundColor Green                                     }                                     Catch                                     {                                     Write-host "Failed to stop the scheduler. Will attempt this task again in 10mins" -ForegroundColor Red                                     Stop-Transcript                                     Exit                                     }                                 }                                 Else                                 {                                 Write-host "Scheduler is already paused." -ForegroundColor Yellow                                 }                             #Increase threshold                             If((Get-ADSyncExportDeletionThreshold).ThresholdCount -eq 500)                             {                             Enable-ADSyncExportDeletionThreshold -DeletionThreshold 10000                             Write-host "AD Connect - Threshold Increased. EntryID: $EntryID" -ForegroundColor Yellow                             Send-MailMessage -From $From -to "TeamEmail@kdships.com" -Subject "AD Connect object deletion threshold increased" -Body "Object deletion threshold increased" -priority Normal -SmtpServer smtp.kdships.net                             }                             else                             {                             Write-host "Threshold was already increased. EntryID: $EntryID" -ForegroundColor Yellow                             }                     }             else              {             Write-host "An entry is found, but no condition holds true. EntryID: $EntryID" -ForegroundColor Yellow             }   } elseif(($entry.Fieldvalues.ID -ne $TeamEntriesNew.ID) -and ((Convert-ToLocal -CTime $entry.Fieldvalues.TerminationStartTime) -lt (Convert-ToLocal -CTime $TeamEntriesNew.FieldValues.TerminationEndTime.AddDays(.021000)))) {     $EntryID = $Null     $EntryID = $entry.Fieldvalues.ID     $To = $Null     $To = $entry.Fieldvalues.Author.Email     $NewStartTime = $Null     $NewStartTime = Convert-ToLocal -CTime $TeamEntriesNew.FieldValues.TerminationEndTime.AddDays(.029000)     Write-host "Delete duplicate entry on SharePoint. Dup EntryID: $EntryID" -ForegroundColor Yellow             #Email status of the request through PowerAutomate to the requestor, email request details to your team and delete request from SharePoint             $UriTeam = "https://XXXXXXXXXXXXXX.logic.azure.com:443/workflows/XXXXXXXXXXXXXXXXXXX/triggers/manual/paths/invoke?api-version=2016-06-01&sp=%2Ftriggers%2Fmanual%2Frun&sv=1.0&sig=-XXXXXXXXXXXXXXXXXXXXXX"                     $BodyT = [ordered] @{                     EntryID = $EntryID                   } | ConvertTo-Json -Depth 10             Invoke-RestMethod -Method POST -Uri $UriTeam -Body $BodyT -ContentType application/json             Write-host "A conflict entry has been deleted. EntryID: $EntryID" -ForegroundColor Yellow             $Body =             "             Hello,             Your scheduled activity is in conflict with someone's activity. Please kindly enter a new request with a start time of $NewStartTime or greater.             This is an automated email. Please do not reply.             Thank you.             IT Team             "             Send-MailMessage -From $From -to $To -Subject "Re: Your request to Pause ADSync and Increase object deletion threshold" -Body $Body -priority Normal -SmtpServer smtp.kdships.net     } }
 }
 Elseif($TeamEntries.count -eq 1)
 {
                 $RequestorEmail = $Null
                 $EntryID = $Null
                 $StartTime = $Null
                 $EndTime = $Null
                 $Created = $Null
                 $From = "TeamSenderEmail@kdships.com"
                 $Created = Convert-ToLocal -CTime $TeamEntries.Fieldvalues.Created
                 $StartTime = Convert-ToLocal -CTime $TeamEntries.Fieldvalues.TerminationStartTime
                 $EndTime = Convert-ToLocal -CTime $TeamEntries.Fieldvalues.TerminationEndTime
                 $EntryID = $TeamEntries.Fieldvalues.ID
                 $RequestorEmail = $TeamEntries.Fieldvalues.Author.Email
                 If($StartTime -gt (Get-date).AddDays(.082999))
                 {
                         Write-host "Start time is more than two hours away. EntryID: $EntryID. Will attempt this again in 10mins" -ForegroundColor Red
                         Stop-Transcript
                         Exit
                 }
                 elseif(($EndTime -lt (Get-date).AddDays(-.021000)) -and ((Get-ADSyncScheduler).SyncCycleEnabled -eq $false)) #Resume scheduler without reverting deletion threshold
                 {
                         #Set file variable
                         $RequestorEmail = $Null
                         $EntryID = $Null
                         $StartTime = $Null
                         $EndTime = $Null
                         $From = "TeamSenderEmail@kdships.com"
                         $StartTime = Convert-ToLocal -CTime $TeamEntries.Fieldvalues.TerminationStartTime
                         $EndTime = Convert-ToLocal -CTime $TeamEntries.Fieldvalues.TerminationEndTime
                         $EntryID = $TeamEntries.Fieldvalues.ID
                         $RequestorEmail = $TeamEntries.Fieldvalues.Author.Email
                              If((Get-ADSyncExportDeletionThreshold).ThresholdCount -gt 500)
                              {                       
                                     #Re-enable the scheduler
                                     Try
                                     {
                                     Set-ADSyncScheduler -SyncCycleEnabled $True
                                     Start-sleep -S 3
                                     Send-MailMessage -From $From -to "TeamEmail@kdships.com" -Subject "AD Connect - ADSync Scheduler has been re-enabled" -Body "AdSync scheduler has been re-enabled" -priority Normal -SmtpServer smtp.kdships.net
                                     }
                                     Catch
                                     {
                                     Write-host "Failed to renable the scheduler." -ForegroundColor Red
                                     Send-MailMessage -From $From -to "TeamEmail@kdships.com" -Subject "Action Required: Failed to re-enable the scheduler" -Body "The scheduler failed to start while being re-enabled." -priority Normal -SmtpServer smtp.kdships.net
                                     }
                              }
                              else
                              {
                              Write-host "Threshold was already reset. $EntryID" -ForegroundColor Yellow
                              }
                 }
                 elseif(($EndTime -lt (Get-date).AddDays(-.021000)) -and ((Get-ADSyncScheduler).SyncCycleEnabled -eq $true) -and ((Get-ADSyncExportDeletionThreshold).ThresholdCount -gt 500) -and ((Get-ADSyncScheduler).SyncCycleInProgress -eq $false))
                 {
                         #Set file variable
                         $RequestorEmail = $Null
                         $EntryID = $Null
                         $StartTime = $Null
                         $EndTime = $Null
                         $From = "TeamSenderEmail@kdships.com"
                         $StartTime = Convert-ToLocal -CTime $TeamEntries.Fieldvalues.TerminationStartTime
                         $EndTime = Convert-ToLocal -CTime $TeamEntries.Fieldvalues.TerminationEndTime
                         $EntryID = $TeamEntries.Fieldvalues.ID
                         $RequestorEmail = $TeamEntries.Fieldvalues.Author.Email                   
                                     #Reset threshold limit and start the scheduler
                                     Try
                                     {
                                     Enable-ADSyncExportDeletionThreshold -DeletionThreshold 500
                                     Write-host "AD Connect - Threshold has been reverted to default. EntryID: $EntryID" -ForegroundColor Yellow
                                     Start-sleep -S 5
                                     Send-MailMessage -From $From -to "TeamEmail@kdships.com" -Subject "AD Connect - object deletion threshold reverted to default" -Body "Object deletion threshold has been reverted to default" -priority Normal -SmtpServer smtp.kdships.net
                                     }
                                     Catch
                                     {
                                     Write-host "Failed to reset threshold to default. Verify threshold limit and reset if not already." -ForegroundColor Red
                                     Send-MailMessage -From $From -to "TeamEmail@kdships.com" -Subject "Action Required: Failed to start the scheduler" -Body "Object deletion threshold limit has been reverted to default. However, the scheduler failed to start. Verify threshold limit and reset if not already." -priority Normal -SmtpServer smtp.kdships.net
                                     } 
                         #Remove entry from the sharepoint list using powerautoamte
                         $UriTeam = "https://XXXXXXXX.logic.azure.com:443/workflows/XXXXXXXXXXXX/triggers/manual/paths/invoke?api-version=2016-06-01&sp=%2Ftriggers%2Fmanual%2Frun&sv=1.0&sig=-XXXXXXXXXXXXXXX"
                         $BodyT = [ordered] @{
                         EntryID = $EntryID
                         } | ConvertTo-Json -Depth 10
                         Invoke-RestMethod -Method POST -Uri $UriTeam -Body $BodyT -ContentType application/json
                 }
                 elseif(($StartTime -lt (Get-date).AddDays(.082999)) -and ((Get-ADSyncScheduler).SyncCycleInProgress -eq $false)) #If start time is about 2hrs away and sync is not running, check if scheduler is enabled, then disable it. Then check if threshold is at default, then increase it.
                 {
                                 #Pause the scheduler
                                 If((Get-ADSyncScheduler).SyncCycleEnabled -eq $true)
                                  {  Try
                                     {
                                     Set-ADSyncScheduler -SyncCycleEnabled $false
                                     Write-host "Scheduler paused" -ForegroundColor Green
                                     }
                                     Catch
                                     {
                                     Write-host "Failed to pause the scheduler. Will attempt this again in 10mins" -ForegroundColor Red
                                     Stop-Transcript
                                     Exit
                                     }
                                  }
                                  Else
                                  {
                                  Write-host "Scheduler is already paused." -ForegroundColor Yellow
                                  }
                             #Increase threshold to 10,000
                             If((Get-ADSyncExportDeletionThreshold).ThresholdCount -eq 500)
                             {
                             Enable-ADSyncExportDeletionThreshold -DeletionThreshold 10000
                             Write-host "AD Connect - Threshold Increased. EntryID: $EntryID" -ForegroundColor Yellow
                             Send-MailMessage -From $From -to "TeamEmail@kdships.com" -Subject "AD Connect object deletion threshold increased" -Body "Object deletion threshold increased" -priority Normal -SmtpServer smtp.kdships.net
                             }
                             else
                             {
                             Write-host "Threshold was already increased. EntryID: $EntryID" -ForegroundColor Yellow
                             }
                     Write-host "" -ForegroundColor Yellow             }             else             {             Write-host "An entry is found, but no condition holds true. EntryID: $EntryID" -ForegroundColor Yellow             }
 }
 Else
 {
 Write-host "There is no request to process at this time" -ForegroundColor Yellow
 Send-MailMessage -From "TeamSenderEmail@kdships.com" -to "TeamEmail@kdships.com" -Subject "There is no request to process at this time" -Body "There is no request to process at this time" -priority Normal -SmtpServer smtp.kdships.net
 }
 Stop-Transcript
 }
 else
 {
 Write-host "Sync Cycle In Progress. Check back in 10 mins." -ForegroundColor Yellow
 Stop-Transcript
 }

Disclaimer: The information on this page is offered “as is” with no warranty. It is strongly recommended that you verify these claims before using it in a production environment.

Automatically alert your team when your tenant is running low or out of Office 365 license

Problem:

I would like to kick off this post with a question. Why would Microsoft create something so BB (big and beautiful) and forget to add an alert to indicate when a license SKU is running low? I have not seen any feature within Office 365 that sends one a notification when a tenant license SKU is low. If you have seen one, please let me know.

If you’ve read my previous post on how to automate Office 365 licensing, you may probably share the same frustrations most Office 365 admins and architects face on a daily basis. Another one of those is this very critical part of the platform, but yet very simple to implement. So, why would Microsoft skip this one? Anyway, enough of my ranting.

Solution:

Out of necessity, I went about trying to figure out how to create a solution for myself and my team. While writing the Powershell script that I described in my previous post, I came about the need to check for the number of available license SKU before assigning a license to a user object. After adding the script block and testing it out, I quickly noticed that I could also use it for another combo script that does more. I will discuss that in one of my subsequent posts.

For now, depending on how big or complicated your environment is, the alert script may require a little modification to meet your need. Our need was just as simple as “tell us when our license is low or out”. As the saying goes, necessity is the mother of invention.

$PaidUnitsE3 = Get-AzureADSubscribedSku -ObjectId "XXXXXX" | Select -Property Sku*,ConsumedUnits -ExpandProperty PrepaidUnits

$AvailableUnitsE3 = (($PaidUnitsE3.Enabled)-($PaidUnitsE3.ConsumedUnits))

The script simply checks for the number of E3 license unit that was procured by the tenant and assigns it to variable, $PaidUnitsE3. Then it checks for the number of consumed units, gets the difference and assigns it to another variable, $AvailableUnitsE3.

 if($AvailableUnitsE3 -lt 250 -and  $AvailableUnitsE3  -gt 0){

 $AvailableUnits += New-Object PSObject -property @{ 

                  E3 = "$AvailableUnitsE3"} 

Next, I used the if statement to verify if the available unit is less than “250” and greater than “0”. I added the latter to ensure this condition only applies while the available unit is not less than “0” otherwise, it would apply every time the count is 0, -1, -2, -3 and so on. If the condition holds true, it assigns the value in the $AvailableUnitsE3 variable to $AvailableUnits array. If it does not hold true, it skips this and moves to the next condition.

You declare this array like so: $AvailableUnits = @()  

Your elseif statement would look like this:

esleif ($AvailableUnitsE3 -lt 1 ){

 $AvailableUnits2 += New-Object PSObject -property @{ 

                  E3 = "$AvailableUnitsE3"}

The elseif statement checks for another possible condition. Like the previous if statement, it checks if $AvailableUnitsE3 is less than 1. If that holds true, it assigns the value in $AvailableUnitsE3 to the array, $AvailableUnits2.

You can replace the PowerAutomate sections with PowerShell Send-MailMessage cmdlet and use your internal mail relay to fire off the email. If you are interested in using PowerAutomate, you can learn more about that here.

Full Script:

# Notify IT when Office 365 license unit is low or out
# Author: Kdships
# If you have a different set of license SKUs, you can replace those accordingly
$AvailableUnits = $Null
$AvailableUnits2 = $Null
$AvailableUnits=@()
$AvailableUnits2=@()
$PaidUnitsE3 = Get-AzureADSubscribedSku -ObjectId "PASTE E3 SKU OBJECTID HERE" |Select -Property Sku*,ConsumedUnits -ExpandProperty PrepaidUnits
$AvailableUnitsE3 = $Null
$AvailableUnitsE3 = (($PaidUnitsE3.Enabled)-($PaidUnitsE3.ConsumedUnits))
$PaidUnitsF1 = Get-AzureADSubscribedSku -ObjectId "PASTE F1 SKU OBJECTID HERE" |Select -Property Sku*,ConsumedUnits -ExpandProperty PrepaidUnits
$AvailableUnitsF1 = $Null
$AvailableUnitsF1 = (($PaidUnitsF1.Enabled)-($PaidUnitsF1.ConsumedUnits))
$PaidUnitsExPlan2 = Get-AzureADSubscribedSku -ObjectId "PASTE Exchange Plan 2 SKU OBJECTID HERE" |Select -Property Sku*,ConsumedUnits -ExpandProperty PrepaidUnits
$AvailableUnitsExPlan2 = $Null
$AvailableUnitsExPlan2 = (($PaidUnitsExPlan2.Enabled)-($PaidUnitsExPlan2.ConsumedUnits))
if(($AvailableUnitsF1 -lt 250) -or ($AvailableUnitsE3 -lt 250) -or ($AvailableUnitsExPlan2 -lt 250))
 {
        if(($AvailableUnitsF1 -lt 1) -or ($AvailableUnitsE3 -lt 1) -or ($AvailableUnitsExPlan2 -lt 1))
        {
        $AvailableUnits2 += New-Object PSObject -property @{ 
                 E3 = "$AvailableUnitsE3"
                 F1 = "$AvailableUnitsF1"
                 ExP2 = "$AvailableUnitsExPlan2"
                 }
        #Send email to the team using PowerAutomate - Use can replce this with your internal mail relay
        $UriA = "Insert PowerAutomate HTTP Request URL here"
        $SupportEmail = "ITTeam@insterswap.com"
        $Out = ($AvailableUnits2 | ConvertTo-Html -Fragment) -join ""
        $tableHtml = $tableHTML -replace "<", "<"
        $tableHtml = $tableHTML -replace ">", ">"
        $tableHtml = $tableHTML -replace """, "`""
        $BodyA = [ordered] @{
            email   = $SupportEmail
            subject = "Action Required: Office 365 - One or more of your tenant license SKU dropped below 1!"
            body    = "$Out
            
            Sent by a trigger for 'CheckLicenseUnit_at_12PM' task on server: insert server name here"
          } | ConvertTo-Json -Depth 10
     
        Invoke-RestMethod -Method POST -Uri $UriA -Body $BodyA -ContentType application/json
        }
        elseif((($AvailableUnitsF1 -gt 100) -and ($AvailableUnitsF1 -lt 250)) -or (($AvailableUnitsE3 -gt 100) -and ($AvailableUnitsE3 -lt 250)) -or (($AvailableUnitsExPlan2 -gt 100) -and ($AvailableUnitsExPlan2 -lt 250)))
        {$AvailableUnits += New-Object PSObject -property @{ 
                 E3 = "$AvailableUnitsE3"
                 F1 = "$AvailableUnitsF1"
                 ExP2 = "$AvailableUnitsExPlan2"
                 }
        $Uri = "Insert PowerAutomate HTTP Request URL here"
        $SupportEmail = "ITTeam@insterswap.com"
        $Low = ($AvailableUnits | ConvertTo-Html -Fragment) -join ""
        $tableHtml = $tableHTML -replace "<", "<"
        $tableHtml = $tableHTML -replace ">", ">"
        $tableHtml = $tableHTML -replace """, "`""
         
        $Body = [ordered] @{
            email   = $SupportEmail
            subject = "Office 365 - One or more of your tenant license SKU dropped below 490"
            body    = "$Low
            
            Sent by a trigger for 'CheckLicenseUnit_at_12PM' task on server: insert server name here"
          } | ConvertTo-Json -Depth 10
     
        Invoke-RestMethod -Method POST -Uri $Uri -Body $Body -ContentType application/json 
        }
        if ((($AvailableUnitsF1 -gt 0) -and ($AvailableUnitsF1 -le 100)) -or (($AvailableUnitsE3 -gt 0) -and ($AvailableUnitsE3 -le 100)) -or (($AvailableUnitsExPlan2 -gt 0) -and ($AvailableUnitsExPlan2 -le 100)))
        {
            $AvailableUnits += New-Object PSObject -property @{
                 E3 = "$AvailableUnitsE3"
                 F1 = "$AvailableUnitsF1"
                 ExP2 = "$AvailableUnitsExPlan2"
                 }
        $Uri1 = "Insert PowerAutomate HTTP Request URL here"
        $SupportEmail = "ITTeam@insterswap.com"
        $Low1 = ($AvailableUnits | ConvertTo-Html -Fragment) -join ""
        $tableHtml = $tableHTML -replace "<", "<"
        $tableHtml = $tableHTML -replace ">", ">"
        $tableHtml = $tableHTML -replace """, "`""
         
        $Body1 = [ordered] @{
            email   = $SupportEmail
            subject = "Action Required: Office 365 - One or more of your tenant license SKU may run out soon"
            body    = "$Low1
            
            Sent by a trigger for 'CheckLicenseUnit_at_12PM' task on server: insert server name here"
          } | ConvertTo-Json -Depth 10
     
        Invoke-RestMethod -Method POST -Uri $Uri1 -Body $Body1 -ContentType application/json
        }
 }
 else
 {
 Write-host "We are good on license units"
 }

If you found this useful, please let us know by leaving a comment below.

Disclaimer: The script on this page is offered “as is” with no warranty. It is strongly recommended that you validate the script in an isolated test environment before using it in a production environment

Align Office 365 License Automation with Azure AD Connect Sync Cycle

Problem:

We had an Office 365 licensing script that was scheduled to run every hour. However, due to our high turnover rate, new hires are occasionally setup within a few hours of being hired. This had a negative impact on our Day One new hire experience as new objects were occasionally not licensed on time. Upon our investigation, we identified several problems. One was related to the dependency on other teams which I talked about in my debut article. Apparently, while our Azure AD Connect server is scheduled to sync every 2 hrs, occasionally, it would go a little over that due to a large number of object modifications that needs to be synced. When this occurs or when the IAM team provisions an object a few minutes just after a new sync has initiated, they would usually have to wait for that to complete and rolled into the next cycle. Often times, we would receive a ticket requesting to license a new hire object that is still pending. We know that there is nothing we can do, but explaining this to the other teams in a more convincing way or providing an automated means to inform them whenever a successful new sync cycle occurs was challenging.

Solution 1:

We discussed this as a team and agreed to update our licensing script to monitor the status of Azure AD Connect. Essentially, it would check the last successful sync cycle in Azure AD Connect before initiating the licensing script block and send an email notification to the team. It would also alert the team whenever it does not find a new sync log information or when last successful sync cycle is over 4 hrs old.

These are the three types of email alerts:

  • New sync cycle completed successfully | Last Export: 4 mins ago | Import/Sync/Export cycle completed (Delta). | License reconciliation script ran 0 Mins : 30 Secs ago.
  • No new sync information was found at this time
  • No new export – Last successful sync cycle: 4 hrs : 15 mins ago | Message: Import/Sync/Export cycle completed (Delta).
#Set variables
$Today1 = Get-date
$Today2 = $Today1.ToShortDateString()
$Today = $Today2 -replace '/','_'
$DirSync = $Null
$ErrorMessage = $Null
#Start logging
Start-Transcript -Path "Path to Log file" -Append
Write-host "Logging started: $Today1" -ForegroundColor Green
#Verify last sync cycle and run license reconciliation block
Try
{
$DirSync = Get-EventLog -LogName application | where {$_.InstanceId -eq 904} | where {$_.Message -like "*Import/Sync/Export cycle completed*"} | Select -First 1
    if($DirSync -eq $Null)
    {
    $DirSyncCount = 0
    }
    else
    {
    $DirSyncCount = 1
    }
}
Catch
{
$DirSyncCount = 2
$ErrorMessage = $_
#Write-host "Unable to fetch logs | Error Message: $_" -ForegroundColor Red
}
Finally
{
  If($DirSyncCount -eq 1)
  {
    If (($DirSync.TimeGenerated -gt (Get-date).adddays(-.0099)) -and ($DirSync.Timegenerated -lt (get-date)) -and ($DirSync.message -like "*Import/Sync/Export cycle completed (Delta)*"))
    {
        write-host "Just completed a new export" -ForegroundColor Green
        #Connect Office 365
        Connect-MsolService -Credential $UserCredential
        $LicenseOption = New-MsolLicenseOptions -AccountSkuId "company:ENTERPRISEPACK"
        Get-msoluser -LicenseReconciliationNeededOnly -all | Set-MsolUser -UsageLocation "GB"
        Get-msoluser -LicenseReconciliationNeededOnly -all | Set-MsolUserLicense -AddLicenses "company:ENTERPRISEPACK" -LicenseOptions $LicenseOption
        $New = Get-msoluser -LicenseReconciliationNeededOnly -all
        $New | Out-file "Path to exported unlicensed user objects file after each run"
        $Attachment = "Path to previously exported unlicensed user objects file"
        $LastSync = $DirSync.TimeGenerated
        $Message = $DirSync.message
        $Today3 = Get-date
        $Lastrun1 = $Today3 - $Today1
        $LastrunM = $Lastrun1.Minutes
        $LastrunS = $Lastrun1.Seconds
        $LastSync_Hour = $Today3 - $LastSync
        $minutesago = $LastSync_Hour.Minutes
        Try{
        Send-MailMessage -From "licensereconciliationscript@insterswap.com" -to "ITTeam@insterswap.com" -Subject "New sync cycle just completed successfully - License reconciliation script ran $LastrunM Mins : $LastrunS Secs ago" -Body "New sync cycle completed successfully | Last Export: $minutesago mins ago | $Message | License reconciliation script ran $LastrunM Mins : $LastrunS Secs ago. License Pending - Please find attached" -priority low -attachments $Attachment -SmtpServer emailrelay.insterswap.com
        }
        Catch{
        Write-host "Unable to connect to remote server | Error Message: $_" -ForegroundColor Red
        Start-Sleep -Seconds 120
        Send-MailMessage -From "licensereconciliationscript@insterswap.com" -to "ITTeam@insterswap.com" -Subject "New sync cycle just completed successfully - License reconciliation script ran $LastrunM Mins : $LastrunS Secs ago" -Body "New sync cycle completed successfully | Last Export: $minutesago mins ago | $Message | License reconciliation script ran $LastrunM Mins : $LastrunS Secs ago. License Pending - Please find attached" -priority low -attachments $Attachment -SmtpServer emailrelay.insterswap.com
        }
        $Today4 = Get-date
        Write-host "Logging stopped: $Today4 | Last Export: $minutesago mins ago | $Message | License reconciliation script ran $LastrunM Mins : $LastrunS Secs ago" -ForegroundColor Green
        Stop-Transcript
}
    else
    {
        $LastSync = $DirSync.TimeGenerated
        $Message = $DirSync.message
        $Today1 = Get-date
        $LastSync_Hour = $Today1 - $LastSync
        $Hoursago = $LastSync_Hour.Hours
        $minutesago = $LastSync_Hour.Minutes
        write-host "No new export - Last successful sync cycle: $Hoursago h : $minutesago mins ago | Message: $Message" -ForegroundColor Yellow
        if($Hoursago -ge 4)
        {
            Try{
            Send-MailMessage -From "licensereconciliationscript@insterswap.com" -to "ITTeam@insterswap.com" -Subject "No new export in over $Hoursago hrs" -Body "No new export - Last successful sync cycle: $Hoursago hrs : $minutesago mins ago | Message: $Message" -priority high -SmtpServer emailrelay.insterswap.com
                }
                Catch{
                Write-host "Unable to connect to remote server | Error Message: $_" -ForegroundColor Red
                Send-MailMessage -From "licensereconciliationscript@insterswap.com" -to "ITTeam@insterswap.com" -Subject "No new export in over $Hoursago hrs" -Body "No new export - Last successful sync cycle: $Hoursago hrs : $minutesago mins ago | Message: $Message" -priority high -SmtpServer emailrelay.insterswap.com
                }
        }
        $Today4 = Get-date
        Write-host "Logging stopped: $Today4" -ForegroundColor Green
        Stop-Transcript
        }
  }
  elseif($DirSyncCount -eq 0)
  {
        If(($DirSync.TimeGenerated -gt (Get-date).adddays(-.015)) -and ($DirSync.Timegenerated -lt (get-date)) -and ($DirSync.message -like "*Import/Sync/Export cycle completed (Delta)*"))
        {
        write-host "Just completed a new export" -ForegroundColor Green
        #Connect Office 365
        Connect-MsolService -Credential $UserCredential
        $LicenseOption = New-MsolLicenseOptions -AccountSkuId "company:ENTERPRISEPACK"
        Get-msoluser -LicenseReconciliationNeededOnly -all | Set-MsolUser -UsageLocation "US"
        Get-msoluser -LicenseReconciliationNeededOnly -all | Set-MsolUserLicense -AddLicenses "company:ENTERPRISEPACK" -LicenseOptions $LicenseOption
        $New = Get-msoluser -LicenseReconciliationNeededOnly -all
        $New | Out-file "Path to exported unlicensed user objects file after each run"
        $Attachment = "Path to previously exported unlicensed user objects file"
        $LastSync = $DirSync.TimeGenerated
        $Message = $DirSync.message
        $Today3 = Get-date
        $Lastrun1 = $Today3 - $Today1
        $LastrunM = $Lastrun1.Minutes
        $LastrunS = $Lastrun1.Seconds
        $LastSync_Hour = $Today3 - $LastSync
        $minutesago = $LastSync_Hour.Minutes
        Try{
        Send-MailMessage -From "licensereconciliationscript@insterswap.com" -to "ITTeam@insterswap.com" -Subject "New sync cycle just completed successfully - License reconciliation script ran $LastrunM Mins : $LastrunS Secs ago" -Body "New sync cycle completed successfully | Last Export: $minutesago mins ago | $Message | License reconciliation script ran $LastrunM Mins : $LastrunS Secs ago. License Pending - Please find attached" -priority low -attachments $Attachment -SmtpServer emailrelay.insterswap.com
        }
        Catch{
        Write-host "Unable to connect to remote server | Error Message: $_" -ForegroundColor Red
        Start-Sleep -Seconds 120
        Send-MailMessage -From "licensereconciliationscript@insterswap.com" -to "ITTeam@insterswap.com" -Subject "New sync cycle just completed successfully - License reconciliation script ran $LastrunM Mins : $LastrunS Secs ago" -Body "New sync cycle completed successfully | Last Export: $minutesago mins ago | $Message | License reconciliation script ran $LastrunM Mins : $LastrunS Secs ago. License Pending - Please find attached" -priority low -attachments $Attachment -SmtpServer emailrelay.insterswap.com
        }
        $Today4 = Get-date
        Write-host "Logging stopped: $Today4 | Last Export: $minutesago mins ago | $Message | License reconciliation script ran $LastrunM Mins : $LastrunS Secs ago" -ForegroundColor Green
        Stop-Transcript
        }
        else
        {
        Try{
        write-host "No new sync information was found at this time" -ForegroundColor Yellow
        Send-MailMessage -From "licensereconciliationscript@insterswap.com" -to "ITTeam@insterswap.com" -Subject "No new sync information was found at this time" -Body "No new sync information was found at this time" -priority high -SmtpServer emailrelay.insterswap.com
        }
        Catch{
        write-host "Unable to connect to remote server | Error Message: $_" -ForegroundColor Red
        Start-Sleep -Seconds 120
        Send-MailMessage -From "licensereconciliationscript@insterswap.com" -to "ITTeam@insterswap.com" -Subject "No new sync information was found at this time" -Body "No new sync information was found at this time" -priority high -SmtpServer emailrelay.insterswap.com
        }
        $Today4 = Get-date
        Write-host "Logging stopped: $Today4" -ForegroundColor Green
        Stop-Transcript
        }
  }
  elseif($DirSyncCount -eq 2)
  {
    write-host "Unable to fetch logs | Error Message: $ErrorMessage" -ForegroundColor Yellow
        Try{
        Send-MailMessage -From "licensereconciliationscript@insterswap.com" -to "ITTeam@insterswap.com" -Subject "Unable to fetch logs" -Body "Unable to fetch logs | Error Message: $_" -priority high -SmtpServer emailrelay.insterswap.com
        }
        Catch{
        write-host "Unable to connect to remote server | Error Message: $_" -ForegroundColor Red
        Start-Sleep -Seconds 120
        Send-MailMessage -From "licensereconciliationscript@insterswap.com" -to "ITTeam@insterswap.com" -Subject "Unable to fetch logs" -Body "Unable to fetch logs" -priority high -SmtpServer emailrelay.insterswap.com
        }
        $Today4 = Get-date
        Write-host "Logging stopped: $Today4" -ForegroundColor Green
        Stop-Transcript
  }
}
#Delete all old log files that are older than 7 days
$Path = "Path to your logs root folder"
$Dayscount = "-7"
$Deletedate = (Get-date).AddDays($Dayscount)
Get-ChildItem $Path | Where-Object { $_.LastWriteTime -lt $Deletedate } | Remove-Item -Force

Solution 2:

This is a lot more straight to the point as the last sync time is fetched directly from Office 365. The only caveat here is that your local server timezone may not be the same as your tenant time zone. If this is the case in your environment, you would have to adjust the Get-Date value on every DST clock shift (daylight). If your tenant time zone is the same as the server you are running the script on, you do not need to adjust the Get-Date value. In my case, I needed to convert UTC to CST. You can modify this section of the script to do this automatically. If timezone conversion is not required in your case, modify this script, by replacing:

#$DirSync = (Get-MsolCompanyInformation).LastDirSyncTime.adddays(-.25)

and

$DirSync = (Get-MsolCompanyInformation).LastDirSyncTime.adddays(-.20833)

With this:

$DirSync = (Get-MsolCompanyInformation).LastDirSyncTime

Full Script:

#Set variables
$Today1 = (Get-date).ToShortDateString()
$Today = $Today1 -replace '/','_'
#Start logging
Start-Transcript -Path "path to log.txt file" -Append
Write-host "Logging started at:" (Get-date) -ForegroundColor Green
#Verify last sync cycle and run license reconciliation block if a new sync time is detected
    #$DirSync = (Get-MsolCompanyInformation).LastDirSyncTime.adddays(-.25) # UTC -6 Swap every DST clock shift
    $DirSync = (Get-MsolCompanyInformation).LastDirSyncTime.adddays(-.20833) # UTC -5 Swap every DST clock shift
    $DirSyncTime = $DirSync.ToShorttimeString()
    $NewDirSyncTime = $DirSyncTime -replace '[\W]', ''
    $LastDirSyncTime = Import-csv "Path to LastDirSyncTime_$Today.csv file" #Previous Sync Times
    $LastSync_Hour = (Get-date) - $DirSync; $minutesago = $LastSync_Hour.Minutes; $Hoursago = $LastSync_Hour.Hours
    If($LastDirSyncTime.LastDirSyncTime -contains $NewDirSyncTime)
    {
     Write-host "No new export - Last successful sync time: $DirSyncTime" -ForegroundColor Yellow
    }
    else
    {
    $NewDirSyncTimeExport = @()
    $NewDirSyncTimeExport += New-Object PSObject -property @{ 
                            LastDirSyncTime = $NewDirSyncTime}
    $NewDirSyncTimeExport |  Export-csv "Path to LastDirSyncTime_$Today.csv file" -Append -NoTypeInfo
        #Insert your licensing script here
        $LicenseReconciliationNeededOnly = Get-msoluser -LicenseReconciliationNeededOnly -all
        $LastLicenseRun = (Get-date).ToShorttimeString()
    Write-host "New sync cycle completed successfully at $DirSyncTime - License reconciliation script ran at $LastLicenseRun" -ForegroundColor Green
    Try{
       Send-MailMessage -From "INSERTFROMEMAILADDRESS" -to "INSERTYOUREMAILHERE" -Subject "New sync cycle just completed successfully at $DirSyncTime - License reconciliation script ran at $LastLicenseRun" -Body "New sync cycle completed successfully at $DirSyncTime - License reconciliation script ran at $LastLicenseRun." -priority low -SmtpServer emailrelay.insterswap.com
       }
    Catch
    {
    Write-host "Unable to connect to remote mail server | Error Message: $_" -ForegroundColor Red
    }
    }
If($Hoursago -gt 4)
{
        Try{
       Send-MailMessage -From "INSERTFROMEMAILADDRESS" -to "INSERTYOUREMAILHERE" -Subject "No export in over $Hoursago hrs : $minutesago min(s)" -Body "No export in over $Hoursago hrs : $minutesago min(s) - Licensing script has not run since $LastLicenseRun." -priority high -SmtpServer emailrelay.insterswap.com
       }
    Catch
    {
    Write-host "Unable to connect to remote mail server | Error Message: $_" -ForegroundColor Red
    }
}
Write-host "Logging stopped at:"(Get-date) -ForegroundColor Green
#Stop-Transcript
#Delete all old log files that are older than 7 days
$Path = "Path to your logs root folder"
$Dayscount = "-7"
$Deletedate = (Get-date).AddDays($Dayscount)
Get-ChildItem $Path | Where-Object { $_.LastWriteTime -lt $Deletedate } | Remove-Item -Force  

If you found this helpful, please leave a feedback in the comment section below.

Disclaimer: The script on this page is offered “as is” with no warranty. It is strongly recommended that you validate the script in an isolated test environment before using it in a production environment

How to send an email and delete a SharePoint list item through a Powershell script using PowerAutomate

Problem:

While developing a solution for Microsoft Teams, I needed to establish a process that triggers two things within the Powershell script. When a request has been processed, either successfully or not, it should send an email to either the end user or our IT team and then delete the SharePoint list item.

Solution:

Requirements:

  • Powershell script
  • PowerAutomate

When creating your PowerAutomate flow process, the first item is the HTTP request. I used a template that works just fine for this purpose. As you can see below, the request Body JSON Schema holds the object type with my Entry ID under the properties. It does not matter which property comes first. But I have my Entry ID showing up first here which represents the SharePoint list ID. This will be used to identify the list item when I send the “Delete item” action. The type here could be an integer or a string. It depends on what was assigned to the variable in the script. In my case, I used an integer to remain consistent with the value type of the list ID.

Fig 1. HTTP Request – Entry ID

In the next figure, we introduce the Email property which would hold the email address of the user. We also introduce the Subject property and EmailBody property. These are all obviously using the string type.

Fig 2. HTTP Request – Email

Next, we introduce the firstname property which would hold the user’s first name. All the properties discussed so far are all listed within the same JSON. The next action is the standard send email template. Once that is added and populated with the JSON properties, save the flow and copy the HTTP POST URL and paste in the script as shown below.

Fig 3. HTTP Request – FirstName
Fig 4. Send Email

Finally, you would add the delete item action using the EntryID value that was captured in your JSON.

Fig 5. Delete List Item

The final PowerAutomate structure would look like this:

Fig 6. All three actions

When an item is processed and an Invoke-RestMethod request is triggered by the script, the data is captured in PowerAutomate and consumed in the send email and delete item actions.

The script would look like this:

#Send status of the request to the user and delete the SharePoint list item
                    $Uri = "Insert the HTTP POST URL HERE"
                            $Body = [ordered] @{
                            EntryID = $EntryID
                            Email   = $RequestorsEmail
                            Subject = "[$Name] Team has been successfully created"
                            EmailBody    = "[$Name] Team has been created. It should now be visible within your Teams client. 
                    
                            Team Name: $Name. 
            
                            Privacy Preference: $VisibilityType.
                    
                            If you have any questions, please reach out to IT team."
                            FirstName = "Hi $GivenName"
                          } | ConvertTo-Json -Depth 10
                    Invoke-RestMethod -Method POST -Uri $Uri -Body $Body -ContentType application/json

Alternatively, this could have been done strictly with Powershell only, but I chose to use this options for several reasons. For instance, it gives me more control and flexibility when I need to enrich the email body to meet various conditions. This method is actually faster when running the script as it executes only one Powershell command and moves on to other tasks, while PowerAutomate does the rest in the background. And finally, I get to use one of the newest solutions out there. I hope this was helpful. If you wish to see how this was used extensively, please read this article: How to develop Microsoft Teams Self-Service App to fix the duplicate display name limitation in the Teams Client

How to integrate a Microsoft PowerApps form with a Powershell script

Problem:

If you have been wondering how to use a PowerApps form to feed data into a Powershell script for an automated task, you would find this article very useful. Again, this was another challenge that I had at my workplace. This started off when I developed a script for an Office 365 self-service licensing platform. I will discuss that in great detail in another blog article. After I did that, I thought about developing another one for Microsoft Teams self-service request app.

However, this form has more fields due to the data that is required to provision MS Teams using Powershell.

New-Team -MailNickname $TeamsName -Displayname $Name -Visibility $VisibilityType -Description $Description

Originally, I needed a way to capture the MailNickName, DisplayName, Visibility and Description. I also wanted to capture a “yes” or “no” response when asked if they would like to merge the new Team with an existing Office 365 group. This brings the number of fields to six. Actually, five fields as the MailNickName would essentially be the same as the DisplayName, but without the spaces.

Here are my fields:

  • Display Name
  • Visibility
  • Description
  • Would you like to merge the team with an existing Office 365 group?
  • Please provide the name of the Office 365 group (if your answer is “no” in the previous field, this field will not be shown)

Solution

To get started, I provisioned a SharePoint list and named the columns to correspond with these fields. You can find more information on how to create a SharePoint list here. At that page, you would also find links to how to create a column and change the list view. Below is the final look of my SharePoint list.

Create SharePoint List and Columns

The columns were configured as follows:

  • Name – Single line of text (required field) – This was formally named Title as it is the default Title column. I renamed it to Name. But when you pull it up in Powershell, you will use the default name, “Title”.
  • VisibilityType – Choice (required field) – Choice options:

Private – Only Team owners can add member
Public – Anyone in your organization can join

  • Description – Multiple lines of text (required field)
  • MergeWithOffice365Group – Choice (required field) – Choice options:

Yes

No

  • Office365GroupNameorEmail – Single line of text (set as not a required field)

Building the PowerApps form from this point on is pretty straightforward. In PowerApps, the field names will be changed to reflect what you want the users to see when completing the form. For now, the field names used here are for easy identification within the Powershell script. Otherwise, it may become difficult identifying the columns when fed into Powershell. Hence we have them in one word naming convention. We start by clicking customize forms as shown below.

Customize SharePoint List

This opens up PowerApps showing the default mobile device portrait layout. If you are building this for use on computers, you would configure the layout by switching it to landscape. To change that, go to file>settings, under screen size+orientation, select landscape and click apply.

Click back to go back to the form. If you zoom in, you would notice the fields /columns that we created a while ago on SharePoint. This is where you would modify the fields and the entire form to your reference.

After modifying the form, you can publish the form by going to file>save>publish and eventually share the form URL. All data entered into the form are stored in the SharePoint list.

This is the completed form.

Now, the final part of the work is how to fetch data from the SharePoint list and setting up the variables that will be used to provision MS Teams.

First, you would assign values to variables for both the site url and the list name:

$SiteURL = "https://companyname.sharepoint.com/sites/sitename"
$TeamListName = "TeamsSelfServiceRequest"

Next, connect to SharePoint online:

$UserCredential = Get-Credential
Connect-PnPOnline -Url $SiteURL -Credentials $UserCredential

Next, connect to MS Teams using the same credential:

#Import Teams module and connect to Teams
Import-module -Name MicrosoftTeams
Connect-MicrosoftTeams -Credential $UserCredential

Now, fetch data from the SharePoint list and set the variables for your columns:

#Fetch list items and store them in $Entries
$Entries = $Null
$Entries = Get-PnpListItem -List $TeamListName
#Loop through each item in $Entries and store values in new variables
foreach ($Entry in $Entries)
{
$Name = $entry.Fieldvalues.Title
$VisibilityType = $entry.Fieldvalues.VisibilityType
$Description = $entry.Fieldvalues.Description
$RequestorsEmail = $entry.Fieldvalues.Author.Email
$MergewithO365Group = $entry.Fieldvalues.MergewithOffice365Group
$O365GroupNameorEmail = $entry.Fieldvalues.Office365GroupNameorEmail
}

Finally, you now have your data in Powershell for further consumption. If you wish to explore how to develop the full script for fetching, processing requests for Teams creation and deleting the list item in SharePoint when it’s no longer needed, please read this article: How to develop Microsoft teams self-service app to fix the duplicate display name limitation in the teams client . If you found this article helpful, please share and let us know your thoughts in the comments below.

Disclaimer: The information on this page is offered “as is” with no warranty. It is strongly recommended that you verify these claims before using it in a production environment.

Develop a Microsoft Teams Self-Service App as a solution to the Teams Client duplicate display name issue

Problem:

As of this writing, the native Teams client and WebApp creates Teams without verifying if a the display name is already in use by an existing Team. This can be problematic as when an end user searches for a team, they could be presented with multiple Teams sharing the same display name. For IT support, when troubleshooting a team with a display name that was provided by an end user, if they have multiple teams using the same display name, they would have to do some extra work to identify the actual team in question.

Solution:

This Powershell script was developed for Teams self-service app. Requests are stored in a SharePoint list through PowerApps and processed in Powershell. PowerAutomate formats and sends email messages, and then deletes SharePoint list items when a request has been processed.

Benefits:

  • Eliminates duplicate display name limitation on the Teams client.
  • Ensures that the Team email address is unique without random numbers appended to it.
  • Informs the user when a name is already in use and provides the name of the existing Team owner. This gives the requestor an opportunity to negotiate name swap with the existing owner.
  • Blocks restricted words from being used. More restricted words can be added in the csv file.
  • Gives administrators more control over how teams are created. The script can be modified to include unique naming conventions.
Fig 1. PowerApps form

You can learn more about how to integrate PowerApps form and SharePoint with this script here.

Fig 1. All three actions

You can learn more on how to send an email and delete a SharePoint list item through a Powershell script using PowerAutomate here.

When you are ready to deploy the app, grant users non-owners permission to the Powerapps form. On SharePoint, grant your users reader access to the SharePoint site and contribute permission to the SharePoint list. To process user requests, you schedule the script to run every 5 to 10 min interval. Mine runs every 5 mins.

If you have any questions or need help setting this up, please leave your comment below or contact me directly here.

Full script:

# Teams self-service request App script
# Author: kdships
# Note: The reason for the multiple start-sleep entries is due to a known feedback failures from Microsoft. 
# Adding some delays helped fix the issue. Microsoft have been informed about the PS bug.
# Remember to assign your credentials to $UserCredential
$SiteURL = "https://XXXXXXX.sharepoint.com/sites/XXXXXXXXXX"
$TeamListName = "XXXXXXXXXX"

#Set more variables
$FailedtoProvision = @()
$Today3 = Get-date
$Today4 = $Today3.ToShortDateString()
$Today5 = $Today4 -replace '/','_'
Write-host "Logging started: $Today3"

####Connect to site via PnP Online
Connect-PnPOnline -Url $SiteURL -Credentials $UserCredential -Verbose
Start-Sleep -Seconds 1

####Connect to AzureAD
Connect-AzureAD -Credential $UserCredential -Verbose
Start-Sleep -Seconds 1

####Connect to MS Teams
Import-module -Name MicrosoftTeams #-Verbose
#Start-Sleep -Seconds 1
Connect-MicrosoftTeams -Credential $UserCredential -Verbose
Start-Sleep -Seconds 1

#Capture list items and store in ListEntries
$ListEntries = @()
$Entries = $Null
$AllGroups = $Null
$Entries = Get-PnpListItem -List $TeamListName
if($Entries.count -gt 0)
{
$Session = New-PSSession -ConfigurationName Microsoft.Exchange -ConnectionUri https://outlook.office365.com/powershell-liveid/ -Credential $UserCredential -Authentication Basic -AllowRedirection
Import-PSSession $Session -AllowClobber -Verbose
$Blockedwords = $Null #New
$Blockedwords = import-csv "<Insert FilePath to blockedwords here_Header is BlockedWords>"
foreach ($Entry in $Entries)
{
#Set variable
$VisibilityType = $Null
$Description = $Null
$RequestorsEmail = $Null
$MergewithO365Group = $Null
$O365GroupNameorEmail = $Null
$EntryID = $Null
$TeamsName = $Null
$GivenName = $Null
$Array = $Null #New
$WordCheck = 0 #New
$word = $Null #New
$UPN = $Null
$Name = $Null
$Name0 = $Null
$Name = $entry.Fieldvalues.Title
$Name0 = $Name -replace '[\W]', ''
$VisibilityType = $entry.Fieldvalues.VisibilityType
$Description = $entry.Fieldvalues.Description
$RequestorsEmail = $entry.Fieldvalues.Author.Email
$MergewithO365Group = $entry.Fieldvalues.MergewithO365Group
$O365GroupNameorEmail = $entry.Fieldvalues.O365GroupNameorEmail
$EntryID = $entry.Fieldvalues.ID
$UPN = (Get-mailbox -identity $RequestorsEmail).userprincipalname
$GivenName = (Get-AzureADUser -ObjectId $UPN).GivenName
$TeamsName = $Name0
$Array = $Name.Split(" ") #New
Foreach($word in $Array)
{
  $Wordvalue = $Null
  $Wordvalue = $word -replace ' ', ''
  if ($Blockedwords.blockedwords -contains $Wordvalue)
  {
  $WordCheck++
  }
}
    If($WordCheck -eq 0)
    {
    #Email status of the request to the requestor
    $UriT = $Null
    $StatusValue = $Null
    $EntryIDNull = $EntryID
    $EntryIDNull = $Null
    $UriTNote = "<Insert Invoke PowerAutomate URL Path HERE>"
    [int]$StatusValueNote = "1"
            $BodyTNote = [ordered] @{
            EntryID = $EntryIDNull
            email   = $RequestorsEmail
            checkvalue = $StatusValue
            subject = "Your Microsoft Team request is being processed"
            bodyvalue    = "Your Microsoft Team request is being processed.
            
            Team Name: $Name. 
            
            Privacy Preference: $VisibilityType."
            firstname = "Hi $GivenName"
          } | ConvertTo-Json -Depth 10
    Invoke-RestMethod -Method POST -Uri $UriTNote -Body $BodyTNote -ContentType application/json
    if($MergewithO365Group -eq "Yes")
    {
    Start-Sleep -Seconds 3
    #Email status of the request to the requestor, email request details to IT team and delete request from SharePoint
    $UriT = $Null
    $StatusValue = $Null
    $UriT = "Insert Invoke PowerAutomate URL Path HERE"
    [int]$StatusValue = "1"
            $BodyT = [ordered] @{
            EntryID = $EntryID
            email   = $RequestorsEmail
            checkvalue = $StatusValue
            subject = "Your Team provisioning request has been received"
            bodyvalue    = "Your request to merge '$O365GroupNameorEmail' Office 365 Group with your Microsoft Team, $Name, has been assigned to IT team. A member of the team will be in touch as soon as the merge is complete."
            firstname = "Hi $GivenName"
          } | ConvertTo-Json -Depth 10
    Invoke-RestMethod -Method POST -Uri $UriT -Body $BodyT -ContentType application/json
    $Subject = "Action Required: A request to create Teams with an O365 group merge"
    $Body = $Null
    $Body = "Hi Team,
    
    Please create MS Team on my behalf and merge it with my existing O365 Group. Please find details below.
    Team Name: $Name
    Team NickName/Alias: $TeamsName
    Visibility: $VisibilityType
    Description: $Description
    Existing O365 Group: [$O365GroupNameorEmail]
    
    If you have any questions, please let me know.
    
    Thank you
    
    $GivenName
    
    $RequestorsEmail
    $UPN
    
    Sent by a trigger for 'TeamsProvisioningSelfServiceApp' on behalf of $UPN"
    Send-MailMessage -From $RequestorsEmail -to "XXXXXXXX" -Subject $Subject -Body $Body -priority High -SmtpServer XXXXXX
    Write-host "Team and Group merge request for $TeamsName by $UPN has been sent to the IT team" -ForegroundColor Green
    }
    elseif ($MergewithO365Group -eq "No")
    {
    #Check if this Team already exist
    $Check1 = $Null
    $Check2 = $Null
    $Check3 = $Null
    $Check4 = $Null
    $Check1 = Get-Recipient -Identity $TeamsName
    Start-Sleep -Seconds 3
    $Check2 = Get-Recipient -Identity ($TeamsName + '@kdships.com')
    Start-Sleep -Seconds 3
    $Check3 = Get-Recipient -Identity ($TeamsName + '@kdships.onmicrosoft.com')
    Start-Sleep -Seconds 3
    $Check4 = Get-Recipient -Identity "$Name"
    if (($Check1 -eq $Null) -and ($Check2 -eq $Null) -and ($Check3 -eq $Null) -and ($Check4 -eq $Null))
    {
    Start-Sleep -Seconds 10
                    If($VisibilityType -eq "Private - Only Team owners can add members")
                    {
                $VisibilityType = "Private"
                }
                    else
                    {
                $VisibilityType = "Public"
                }
        Try {
            #Create Team
            $ReadyTeam = $Null
            $RunCheck = 0
            $ReadyTeam = New-Team -MailNickname $TeamsName -displayname $Name -Visibility $VisibilityType -Description $Description
            $RunCheck = 1
            Start-Sleep -Seconds 10
            }
        Catch {
                #Email status of the request to the requestor, email request details to IT team and delete request from SharePoint
                $UriT = $Null
                $StatusValue = $Null
                $UriT = "https://prod-121.REGION.logic.azure.com:443/workflows/FLOWPATHID"
                [int]$StatusValue = "3"
                        $BodyT = [ordered] @{
                        EntryID = $EntryID
                        email   = $RequestorsEmail
                        checkvalue = $StatusValue
                        subject = "Your Team request is pending - $Name"
                        bodyvalue    = "Your Team provisioning request is pending. A member of the IT team will be in touch as soon as it is rectified."
                        firstname = "Hi $GivenName"
                      } | ConvertTo-Json -Depth 10
                Invoke-RestMethod -Method POST -Uri $UriT -Body $BodyT -ContentType application/json
                $Subject = "Action Required: Failed to create Team - $Name"
                $Body = $Null
                $Body = "Hi Team,
    
                MS Teams provisioning self-service app failed to create this Team. Using the details below, please kindly retry creatng the Team through the self-service app and update the ownership. Remember to remove yourself as the owner.
    
                Self-Service App Link: https://apps.powerapps.com/play/APPID
                Team Display Name: $Name
                Team NickName/Alias: $TeamsName
                Visibility preference: $VisibilityType
                Description: $Description
                Requestor's Email Address: $RequestorsEmail
                Requestor's UPN: $UPN
                Reason for the failure: $_
    
                Sent by a trigger for 'TeamsProvisioningSelfServiceApp' task"
                Send-MailMessage -From "TeamsSelfServiceApp@kdships.com" -to "kdships@ticketingserviceemail.com" -Subject $Subject -Body $Body -priority normal -SmtpServer email.kdships.com
                }
        If($RunCheck -eq 1)
        {       #Add the requestor as owner of the Team and remove the service account
                Start-Sleep -Seconds 13
                Try{
                $RunCheckB = 0
                Add-TeamUser -GroupId $ReadyTeam.GroupId -User $UPN -Role Owner
                $RunCheckB = 1
                Start-Sleep -Seconds 3
                #Email status of the request to the requestor, email request details to IT team and delete request from SharePoint
                    $UriT = $Null
                    $StatusValue = $Null
                    $UriT = "https://prod-121.REGION.logic.azure.com:443/workflows/FLOWPATHID"
                    [int]$StatusValue = "2"
                            $BodyT = [ordered] @{
                            EntryID = $EntryID
                            email   = $RequestorsEmail
                            checkvalue = $StatusValue
                            subject = "[$Name] Team has been successfully created"
                            bodyvalue    = "[$Name] Team has been created. Shortly, it will be visible within your Teams client. If you do not find it, please scroll to the bottom of your Teams list.
                    
                            Team Name: $Name. 
            
                            Privacy Preference: $VisibilityType.
                    
                            If you have any questions, please reach out to IT team."
                            firstname = "Hi $GivenName"
                          } | ConvertTo-Json -Depth 10
                    Invoke-RestMethod -Method POST -Uri $UriT -Body $BodyT -ContentType application/json
                    Write-host "$TeamsName was successfully created for $UPN" -ForegroundColor Green
                    Send-MailMessage -From "TeamsSelfServiceApp@kdships.com" -to "kdships@kdships.com" -Subject "New Teams Request by $UPN" -Body "$TeamsName - New Teams Request by $RequestorsEmail | $UPN" -priority Low -SmtpServer email.kdships.com
                }
                Catch{
                #Email status of the request to the requestor, email request details to IT team and delete request from SharePoint
                    $UriT = $Null
                    $StatusValue = $Null
                    $UriT = "https://prod-121.REGION.logic.azure.com:443/workflows/FLOWPATHID"
                    [int]$StatusValue = "2"
                            $BodyT = [ordered] @{
                            EntryID = $EntryID
                            email   = $RequestorsEmail
                            checkvalue = $StatusValue
                            subject = "[$Name] Team has been successfully created but pending one final step"
                            bodyvalue    = "[$Name] Team has been created. We are applying finishing touches and would let you know as soon as it is ready.
                    
                            Team Name: $Name. 
            
                            Privacy Preference: $VisibilityType.
                    
                            If you have any questions, please reach out to IT team."
                            firstname = "Hi $GivenName"
                          } | ConvertTo-Json -Depth 10
                    Invoke-RestMethod -Method POST -Uri $UriT -Body $BodyT -ContentType application/json
                $Subject = "Action Required: Failed to create Team owner"
                $Body = $Null
                $Body = "Hi Team,
    
                MS Teams provisioning self-service app failed to add the owner to the team. Using the details below, kindly add the owner through Teams admin portal or through the Client. Please remember to remove yourself and Collab service account from the Team.
    
                Team Display Name: $Name
                Requestor's Email Address: $RequestorsEmail
                Requestor's UPN: $UPN
                Reason for the failure: $_
    
                Sent by a trigger for 'TeamsProvisioningSelfServiceApp' task"
                Send-MailMessage -From "TeamsSelfServiceApp@kdships.com" -to "kdships@ticketingserviceemail.com" -Subject $Subject -Body $Body -priority normal -SmtpServer email.kdships.com
                Send-MailMessage -From "TeamsSelfServiceApp@kdships.com" -to "kdships@kdships.com" -Subject $Subject -Body $Body -priority High -SmtpServer email.kdships.com
                Write-host "Adding owner to $Name failed - Owner: $UPN - Reason: $_" -ForegroundColor Red
                }
                Finally{
                If($RunCheckB -eq 1){
                Start-Sleep -Seconds 3
                Remove-TeamUser -GroupId $ReadyTeam.GroupId -User serviceaccount@kdships.com -Role Owner
                Start-Sleep -Seconds 3
                Remove-TeamUser -GroupId $ReadyTeam.GroupId -User serviceaccount@kdships.com}
                }
        }
        elseif($RunCheck -eq 0)
        {
        Send-MailMessage -From "TeamsSelfServiceApp@kdships.com" -to "kdships@kdships.com" -Subject $Subject -Body $Body -priority High -SmtpServer email.kdships.com
        Write-host "Failed to complete processing Team for $UPN. Reason: $_" -ForegroundColor Red
        }             
    }
    elseif (($Check1 -ne $Null) -or ($Check2 -ne $Null) -or ($Check3 -ne $Null) -or ($Check4 -ne $Null))
    {
    #Check who owns the team
    $Owners = 0
    $Owners = (Get-UnifiedGroup -Identity "$Name").ManagedBy
        If($Owners.count -gt 0)
                {
                #Email status of the request to the requestor and delete request from SharePoint
                $UriT = $Null
                $StatusValue = $Null
                $UriT = "https://prod-121.REGION.logic.azure.com:443/workflows/FLOWPATHID"
                [int]$StatusValue = "4"
                        $BodyT = [ordered] @{
                        EntryID = $EntryID
                        email   = $RequestorsEmail
                        checkvalue = $StatusValue
                        subject = "Failed to create $Name Team - Reason: Name Conflict"
                        bodyvalue    = "
                        Your Team request failed due to a conflict with an existing Team or Group. The name you provided is already in use by: $Owners. 
            
                        Please change the name and try again."
                        firstname = "Hi $GivenName"
                      } | ConvertTo-Json -Depth 10
                Invoke-RestMethod -Method POST -Uri $UriT -Body $BodyT -ContentType application/json
                }
                else
                {
                #Email status of the request to the requestor and delete request from SharePoint
                $UriT = $Null
                $StatusValue = $Null
                $UriT = "https://prod-121.REGION.logic.azure.com:443/workflows/FLOWPATHID"
                [int]$StatusValue = "4"
                        $BodyT = [ordered] @{
                        EntryID = $EntryID
                        email   = $RequestorsEmail
                        checkvalue = $StatusValue
                        subject = "Failed to create $Name Team - Reason: Name Conflict"
                        bodyvalue    = "
                        Your Team request failed due to a conflict with an existing object. Please change the name and try again."
                        firstname = "Hi $GivenName"
                      } | ConvertTo-Json -Depth 10
                Invoke-RestMethod -Method POST -Uri $UriT -Body $BodyT -ContentType application/json
                }
    Write-host "Failed to create Team due to a name conflict - $Name" -ForegroundColor Red
    }
}
}
    else
    {
    #Email status of the request to the requestor and delete request from SharePoint
    $UriT = $Null
    $StatusValue = $Null
    $UriT = "<Insert Invoke PowerAutomate URL Path HERE>"
    [int]$StatusValue = "4"
            $BodyT = [ordered] @{
            EntryID = $EntryID
            email   = $RequestorsEmail
            checkvalue = $StatusValue
            subject = "Failed to create $Name Team - Reason: Restricted keyword"
            bodyvalue    = "
            Your Team request ($Name) failed due to a restricted keyword. Please change the name and try again." 
            firstname = "Hi $GivenName"
          } | ConvertTo-Json -Depth 10
    Invoke-RestMethod -Method POST -Uri $UriT -Body $BodyT -ContentType application/json
    Write-host "Failed to create Team due to a restricted keyword -$Name" -ForegroundColor Red
    }
}
}
elseif ($Entries.count -eq 0)
{
Write-host "No pending request"
}
$Today3 = Get-date
Write-host "Logging stopped: $Today3" 
#Stop-Transcript
# Delete all Files older than 7 day(s) in XXXX
$Path = "PATH TO LOG FILE"
$Dayscount = "-7"
$Deletedate = $Today3.AddDays($Dayscount)
Get-ChildItem $Path | Where-Object { $_.LastWriteTime -lt $Deletedate } | Remove-Item

Github repo: https://github.com/kdships/MicrosoftTeams. If you found this article helpful, please share and leave a feedback comment below. If you need help with developing a similar script, click here.

Disclaimer: The script on this page is offered “as is” with no warranty. It is strongly recommended that you validate the script in an isolated test environment before using it in a production environment.

Reduce IT cost by integrating Office 365 user licensing automation with HR and IAM New Hire provisioning processes

This is my first attempt at blogging some of the unique problems that I have encountered at my consulting job. I will go by the simple “problem & solution” structure and hope to present both in a meaningful and straightforward fashion. If you’re like me, you would agree that often times making extra effort in solving problems, brings out the best in us. Now let’s get started with my first article!

Problem

A couple of years ago, I joined a team that overlooks the entire Office 365 cloud and on-premises infrastructure. We obviously had our niche and shared some other responsibilities with other teams. This is expected in every large environment such as ours. The downside is usually when we have to depend on other teams in order to complete some of ours. And this was the root cause of the challenges we had. It probably would have been a better experience if the other team completed their tasks consistently, accurately with minimum amount of errors.

The first problem that I noticed was the lack of a consistent and error free new hire provisioning process. Provisioning of duplicate accounts had become a norm and affected almost every other process along that path. Licensing Office 365 user objects and placing objects in an unlicensed state was one of the processes that had the most impact. We had a Powershell script that regularly runs in the background and would automatically assigned E3 license to any user object that is synced to Azure AD. This worked, but was basic as it does not have any criteria for licensing a user object other than the default minimum requirements set by Microsoft. It does not also have a way to identify who should have E3 or E1 license. The team had to periodically downgrade users from E3 to E1 when a list is sent in by those that care to help.

The team embarked on series of license reclamation activities, day in, day out. This brought some sanity to some extent and helped avoid procuring more license from Microsoft. But, in spite of this, the nightmare went on for months.

One of the underlying problems that made this difficult, was the lack of a unique identifier for users that require certain type of license SKU to ensure licenses are issued to users in line with their roles and responsibilities. These challenges, overtime, created a huge “user license crisis”. A nightmare, to say the least. The organization had lost thousands of dollars through the years trying to keep up with these challenges as user turnover rate continued to increase. Occasionally, the tenant would have a good number of available license SKU. But many times, the tenant would go out of license, and fall within the over issued state while hundreds of terminated and some duplicate user objects remained licensed. Many of these objects would remain in AD active state, disabled state or deleted state until they are purged and unlicensed at the default 30th day in Azure AD recycle bin.

It was also impossible to keep track of the number of licenses that are consumed by each operating company. All the available reporting tools (third-party and Office 365 reporting) could not help in providing an accurate data to justify what was consumed by each operating company. One of the reasons for this was due to the inconsistencies that was present in the AD environment. For instance, each operating company had a unique number which was also stamped on objects that are associated with the company. But there were instances where some of these attributes were either null, incorrect (meant for another operating company) or invalid altogether. Email domain, job title, UPN suffix etc. were all inconsistent across the board.

The team embarked on series of license reclamation activities, day in, day out. This brought some sanity to some extent and helped avoid procuring more license from Microsoft. But, in spite of this, the nightmare went on for months. In an attempt to save the day, occasionally, I would intentionally bring up the topic in our daily stand-up meeting to draw the attention of everyone. Fortunately, after series of discussions with the team, it was agreed that we would explore some third-party solutions while I review a Microsoft feature known as Azure Group based licensing. We reviewed two well known third-party vendor products in the market, but the prices were not reasonable as the license cost is calculated based on the number of users on the tenant. Customers would have to spend additional $8 to $28 per user, per month to take advantage of the tool. Now, multiply that by 60,000 users, by 12 months. You do the math.

With regards to Azure Group based licensing, I had a session with some Microsoft representatives to get more insight into what dynamic groups could do and also answer some questions that I had about automating some activities. Unfortunately, Microsoft dynamic group is designed, such that, admins cannot add objects directly to the group. Objects are dynamically added or removed from the group based on object attribute values. Rules are used to set the required criteria. Hence, the name, Azure dynamic group. This obviously would not suffice for the company due to all of the inconsistencies that I described earlier. To make matters worse, since the environment is hybrid, object attributes would have to sync to Azure AD before they are processed and added to the dynamic group. The average wait time between when an object attribute is modified in AD and when it is synced to Azure AD is approximately 2 hrs on a delta sync cycle. In smaller environments, this value may be lower, but 2 hrs was not an acceptable one for us. The AD environment has a lot of inconsistencies that were not cleaned up before moving to Office 365. As a result, the only workaround was to manipulate AD Connect by adding custom rules to ensure that only objects that met certain criteria are synced. This obviously created a new problem, which was the extended sync time (that’s a whole new subject for another day).

We considered using Azure basic group which allows adding users directly to an AD synced licensed security group. But this also had to deal with the bottleneck of our Azure AD Connect 2 to 4 hr sync time.

Solution

Pondering on a few starting points that my boss had shared with me, I eventually came up with a very simple process of merging all the loose ends while using an AD security group, an Azure AD basic security group and some Powershell scripting.

#Verify desktopProfile true value

if (($CleanUser.DesktopProfile_AzureAD -ne “E1”) -and ($CleanUser.DesktopProfile_AzureAD -ne “E3”)){

Write-host “A required value is missing for “$CleanUser.DisplayName””

$ADValue2 = Get-aduser -identity $SAMValue -Properties *|Select-object Userprincipalname,extensionAttribute7,Name,Mail,DesktopProfile

$OpCoID2 = $ADValue2.extensionAttribute7

$CustomAttribute7_Office365_2 = (Get-mailbox $ADValue2.UserPrincipalName).CustomAttribute7

Excerpt from the script

This came about when I considered creating a temporary location or a method to identity new hires within AD. There was “hired date” attribute that I could have used or simply add a custom attribute, but I was not confident in following that path as my team would have to rely, yet again, on other teams to keep those values accurate, consistent and up-to-date. Instead, I used the idea of a waiting room or what you would call a reception hall. If I could have a waiting room in AD where I can easily find all new hires in the fastest way possible, I could meet up with them and take them to the various offices or operating companies where they would eventually reside. You get the picture.

Essentially, all that I needed was an AD group that does not need to be synced to Azure AD. Then, develop a script to fetch users from the group based on certain criteria that must be met and then add them directly to a cloud only Azure AD basic security licensed group. I could also set a minimum criteria to be met to avoid attempting to license user objects that have not met the minimum requirements.

This removed the huge gap that previously existed between when an object is provisioned and when it is licensed.

I knew I had to engage with other teams to make this possible. So, I setup a meeting with the HR team and the IAM team. Luckily, they embraced the idea and agreed to create a field in the HR system and IAM system to set a distinction between both license SKUs. A hiring manager makes this call while completing the form and this is in turn passed through to the AD object attribute with either E3 or E1 value. Azure AD Connect syncs both the object and attribute as part of the user provisioning process. Basically, both teams are responsible for fetching the values from the HR system and passing it to AD. As long as this was automated and the field in the hiring request form is made mandatory, the values will always flow to AD.

At this point, all I had to do is have the script on standby, waiting for new user objects in the waiting room (the non-synced AD security group). As soon as a user is added to the group, the script processes the object to validate if it meets all the criteria or minimum criteria, and then adds the object to the appropriate cloud only Azure AD basic licensed security group. In this case, we had E3 group and E1 group. This removed the huge gap that previously existed between when an object is provisioned and when it is licensed. As long as the object had successfully synced to Azure AD at the initial AD object provisioning process, there was no need to wait for the attribute that holds the license value to sync before being licensed.

I added an email notification feature that sends email using Microsoft PowerAutomate. This sends email to the appropriate team that fails to complete or update any of the required or less required object attributes. An email is sent each time an object is processed, before and after being licensed, until the missing or invalid attribute is fixed by the appropriate team. To avoid processing an already licensed object, an object is removed from the AD security group as soon as it meets all the required criteria.

elseif (($Unsynced.DesktopProfile_AD -eq “E1”) -or ($Unsynced.DesktopProfile_AD -eq “E3”)){

#Add to a licensed group if available license is greater than 10 and remove from AzureAD E1 group

if ($Unsynced.DesktopProfile_AD -eq “E3”){

if($AvailableUnitsE3_2 -gt “10”){

Excerpt from the script

Before an object is added to the cloud-only licensed group, the script would check for available SKU and when the count is running low or extremely low, it would send a notification to the license procurement team to take appropriate action. It also stops adding objects to the licensed groups when the count is equal to 10 E3 licenses and 5 E1 licenses. This essentially reserves 10 E3 and 5 E1 licenses and allows the team to manually prioritize license assignments until more licenses are procured.

Placing termed users in unlicensed state was easy from this point on. All we had to do was schedule another script to fetch all disabled and deleted users based on certain criteria and remove them from the cloud-only licensed groups.

If you found this article helpful, let us know your thoughts and please share with anyone that may find this useful. If you would like to have a free copy of the script or need help with developing one, drop me a line. Contact