Monthly Archives: January 2016

Create AD Users with help from Azure Automation and SharePoint Online

As a follow up to one of my earlier posts where I Create AD Users with SharePoint Online as frontend, I now wanted to share an extension of this solution where we will utilize Azure Automation with a Hybrid Worker to do the heavy lifting.
So basically, instead of having a script as a scheduled task reading the SharePoint list and perform tasks based on status, we will now have a SharePoint workflow to initiate our runbooks through an Azure Automation Webhook.
I also want to thank Anders Bengtsson for his quite similiar post that finally made me finish this one up. 🙂

The post will cover the following steps

  • Create an Azure Automation runbook with a WebHook that executes the runbook on a Hybrid Worker.
  • Create a basic SharePoint workflow in SharePoint Designer with approval steps prior creating the user.
  • End user experience.

I assume that you already have the following in place.

I will use Azure PowerShell create and publish the runbook.

1. First we need to create a credential asset that will be used to write back information to our list.

#Login to Azure 
#Define ResourceGroup and Automation Account to be used used for with Automation cmdlets
$PSDefaultParameterValues = @{
    '*-AzureRmAutomation*:ResourceGroupName' = 'ResourceGroup'
    '*-AzureRmAutomation*:AutomationAccountName' = 'MyAutomationAccount'
#Create credential object with the SPO service account
$SPOCreds = Get-Credential 
#Create credential asset in Azure Automation
$SPOCredsHt = @{
    Name = 'SPOAdmin'
    Description = 'SharePoint Online Service Account for user onboarding'
    Value = $SPOCreds
New-AzureRmAutomationCredential @SPOCredsHt

2. Next up we need to create the actual runbook. Since we are doing everything with PowerShell, we need to save the script to a file and import it to Azure Automation. My example runbook (CreateADUserfromSPO.ps1) can be found below.

$SPORunbookHt = @{
    Path = 'C:\CreateADUserfromSPO.ps1'
    Name = 'Create-ADUsersFromSPO'
    Type = 'PowerShell'
    Published = $true
Import-AzureRmAutomationRunbook @SPORunbookHt 


param ( 

#SharePoint Connection Info
$SPCredentials = Get-AutomationPSCredential -Name 'SPOAdmin'
$SPConnection = @{
    SharePointSiteURL = ""
    Username = $SPCredentials.Username
    Password = $SPCredentials.GetNetworkCredential().Password
#Create an object from the webhook request
$UserData = ConvertFrom-Json -InputObject $WebhookData.RequestBody

$TargetOU = "OU=Users,OU=ACME,DC=corp,DC=acme,DC=com"
$ListName = "UserOnboarding"
$PasswordLength = "12"
$UPNSuffix = ''
$FirstName = $UserData.FirstName
$LastName = $UserData.LastName
$Title = $UserData.Title
$Manager = $UserData.Manager

#region functions
function Convert-ToLatinCharacters {
function Get-JDDNFromUPN {
    param (
        [ValidateScript({Get-ADUser -Filter {UserprincipalName -eq $_}})] 
        $ADUser = Get-ADUser -Filter {UserprincipalName -eq $UserPrincipalName} -ErrorAction stop
        return $ADUser.distinguishedname
function New-JDSamAccountName {
    param (
        [parameter(Mandatory=$false)][int]$FirstNameCharCount = 3,
        [parameter(Mandatory=$false)][int]$LastNameCharCount = 3
    #Construct the base sAMAccountName
    $BaseSam = "{0}{1}" -f (Convert-ToLatinCharacters $FirstName).Substring(0,$FirstNameCharCount),(Convert-ToLatinCharacters $LastName).Substring(0,$LastNameCharCount)
    #Add a number until you find a free sAMAccountName
    if (Get-ADUser -Filter {samaccountname -eq $BaseSam} -ErrorAction SilentlyContinue) {
        $index = 0
        do {
            $sAMAccountName = "{0}{1}" -f $BaseSam.ToLower(),$index
        } until (-not(Get-ADUser -Filter {samaccountname -eq $sAMAccountName } -ErrorAction SilentlyContinue))
    } else {
        $sAMAccountName = $BaseSam.tolower()
    return $sAMAccountName
function New-JDUPNAndMail {
    param (
    #Construct the base userPrincipalName
    $BaseUPN = "{0}.{1}@{2}" -f (Convert-ToLatinCharacters $FirstName).replace(' ','.').tolower(),(Convert-ToLatinCharacters $LastName).replace(' ','.').tolower(),$UPNSuffix
    if (Get-ADUser -Filter {userprincipalname -eq $BaseUPN} -ErrorAction SilentlyContinue) {
        $index = 0
        do {
            $UserPrincipalName = "{0}{1}@{2}" -f $BaseUPN.Split("@")[0],$index,$UPNSuffix
        } until (-not(Get-ADUser -Filter {userprincipalname -eq $UserPrincipalName} -ErrorAction SilentlyContinue))
    } else {
        $UserPrincipalName = $BaseUPN
    return $UserPrincipalName
function New-JDADUser {
    param (
        [Parameter(Mandatory=$true)][int]$PasswordLength = 12
     #Generate a password
     $Password = [System.Web.Security.Membership]::GeneratePassword($PasswordLength,2)
     #Construct the user HT
     $ADUserHt = @{
        GivenName = $FirstName
        SurName = $LastName
        ChangePasswordAtLogon = $true
        EmailAddress = $UserPrincipalName
        UserPrincipalName = $UserPrincipalName
        sAMAccountName = $sAMAccountName
        Title = $Title
        Name = "$FirstName $LastName ($sAMAccountName)"
        Displayname = "$FirstName $LastName"
        Manager = $Manager
        Path = $OU
        AccountPassword = (ConvertTo-SecureString -String $Password -AsPlainText -Force)
        Enabled = $true
        OtherAttribute = @{proxyAddresses = "SMTP:$UserPrincipalName"}
     try {
        #Create the user and return a custom object
        New-ADUser @ADUserHt -ErrorAction Stop 
        Write-Verbose "Successfully created the user $($ADUserHt.Name)"
        [pscustomobject] @{
            sAMAccountName = $ADUserHt.sAMAccountName 
            UserPrincipalName = $ADUserHt.UserPrincipalName 
            Password = $Password
     } catch {
        Write-Warning "Error creating the user $($ADUserHt.Name) `r`n$_"
#endregion functions
    try {
        $sAMAccountName = New-JDSamAccountName -FirstName $Firstname -LastName $LastName
        $UPNandMail = New-JDUPNAndMail -FirstName $Firstname -LastName $LastName -UPNSuffix $UPNSuffix
        $ManagerDN = Get-JDDNFromUPN -UserPrincipalName $Manager
        #Create the user in Active Directory
        $NewAdUserHt = @{
            FirstName = $Firstname
            LastName = $LastName
            Manager = $ManagerDN
            sAMAccountName = $sAMAccountName
            UserPrincipalName = $UPNandMail
            Title = $Title
            OU = $TargetOU
            PasswordLength = $PasswordLength
        Write-Output $NewAdUserHt
        $ADUser = New-JDADUser @NewAdUserHt -ErrorAction Stop
        $UpdateHt = @{
            Status = 'Created'
            UserName = $ADUser.sAMAccountName 
            EmailAddress = $ADUser.UserPrincipalName 
        Update-SPOListItem -SPOConnection $SPConnection -ListItemID $UserData.ID -ListFieldsValues $UpdateHt -ListName $ListName
    } catch {
         $UpdateHt = @{
            Status = 'Error'
        Update-SPOListItem -SPOConnection $SPConnection -ListItemID $UserData.ID -ListFieldsValues $UpdateHt -ListName $ListName
        Write-Warning $_

3. Last step is to create the Webhook that will be used when triggering the runbook from the SharePoint workflow. As notified when running the cmdlet, save the WebhookURI, you will need it later and cannot see it more than on Webhook creation. I have set the Webhook expirytime to 2 years, change it according to your needs.

$SPOWebHookHt = @{
    Name = 'SPOWorkflow'
    RunbookName = 'Create-ADUsersFromSPO'
    IsEnabled = $true
    ExpiryTime = (Get-Date).AddDays(730)
New-AzureRmAutomationWebhook @SPOWebHookHt


1. Open SharePoint Designer and open your website containing the custom list.
2. Add a new List Workflow to your site and associate the workflow with your custom list.
3. Click on the workflow and edit the Start Options to kick of the workflow when a new item has been created.
4. Edit and customize the workflow to fit your needs. In my case, I will have one stage for manager approval and one stage for executing the automation runbook.

Manager approval stage
a. Create a “Start a task process action” to submit the request to the manager.
b. Create a condition to Execute the runbook if the request was approved.

Execute Azure Automation Runbook stage
a. First build a dictionary with the data you want to pass to the automation webhook.
b. Insert a “Call HTTP Web Service” action and configure it to POST the earlier created dictionary to the Webhook url you saved creating the Webhook.
c. Our last step before ending the workflow will be to write back the response from the webhook to the SharePoint list item. This makes it easier for us to track if the task was properly submitted to Azure Automation. 2016-01-09_17-15-46
5. Check the workflow for errors before saving and publishing. This is my finished workflow before publishing it.

First, we create a new item in the SharePoint list to start the process.2016-01-09_19-04-18

The manager approval process sends an email to the new users manager as we stated in the workflow.

After the manager has approved the request, the workflow will kick off the runbook, create the user in Active Directory and write back the UserName/EmailAddress and status of the newly created user to the list. In this example, a password is generated but not distributed to anyone.

My hope is that you with this post have got some inspiration on what you can achieve with Azure Automation in combination with SharePoint (Online or On-Prem). Sky is the limit!



Recursively enumerate Azure AD Group members with PowerShell

Many of the scripts used to assign licenses for Azure AD / Office 365 users are utilizing groups to assign the licenses. On the topic I have received quite a lot requests on nested group support, which is not possible with Get-MsolGroupMember as of now.

To solve this problem, I have built a simple function canned Get-JDMSOLGroupMember. It checks if a group consists of other groups, and loops through them until all group members of all groups have been enumerated. In the end it will sort the members so that no duplicate member records are returned. It will only return users, not groups.

See the screenshot below on how it works:


function Get-JDMsolGroupMember { 
    The function enumerates Azure AD Group members with the support for nested groups.
    Get-JDMsolGroupMember 6d34ab03-301c-4f3a-8436-98f873ec121a
    Get-JDMsolGroupMember -ObjectId  6d34ab03-301c-4f3a-8436-98f873ec121a -Recursive
    Get-MsolGroup -SearchString "Office 365 E5" | Get-JDMsolGroupMember -Recursive
    Author   : Johan Dahlbom, johan[at]
    Blog     : 
    The script are provided “AS IS” with no guarantees, no warranties, and it confer no rights.

        [Parameter(Mandatory=$true, ValueFromPipeline=$true,ValueFromPipelineByPropertyName=$true,Position=0)]
        [ValidateScript({Get-MsolGroup -ObjectId $_})]
    begin {
        $MSOLAccountSku = Get-MsolAccountSku -ErrorAction Ignore -WarningAction Ignore
        if (-not($MSOLAccountSku)) {
            throw "Not connected to Azure AD, run Connect-MsolService"
    process {
        Write-Verbose -Message "Enumerating group members in group $ObjectId"
        $UserMembers = Get-MsolGroupMember -GroupObjectId $ObjectId -MemberObjectTypes User -All
        if ($PSBoundParameters['Recursive']) {
            $GroupsMembers = Get-MsolGroupMember -GroupObjectId $ObjectId -MemberObjectTypes Group -All
            if ($GroupsMembers) {
                Write-Verbose -Message "$ObjectId have $($GroupsMembers.count) group(s) as members, enumerating..."
                $GroupsMembers | ForEach-Object -Process {
                    Write-Verbose "Enumerating nested group $($_.Displayname) ($($_.ObjectId))"
                    $UserMembers += Get-JDMsolGroupMember -Recursive -ObjectId $_.ObjectId 
        Write-Output ($UserMembers | Sort-Object -Property EmailAddress -Unique) 
    end {

As usual, let me know if you find issues or have any other suggestions.



License your Office 365/Azure AD users with Azure Automation

A while ago I wrote a post regarding schedule your Office 365 automation jobs in Azure Automation. At that time, the Azure AD PowerShell module did not support Azure Automation due to the Online Services Sign in Assistant dependency.

The preview Azure AD PowerShell module released in september do however use ADAL for authentication which solves this problem.
This is great news for all currently running licensing scripts in regular servers as scheduled tasks!

Since many probably will move from their scheduled tasks to Automation for their licensing scripts, I thought it was a good idea sharing on how to make the switch. I will use the “new” Azure portal and assume that you already have an Automation account.

1. Since the MSOnline module is not included by default in Azure Automation you need to download and install the module on your computer. When that is done you need to add the module folders (MSOnline and MSOnlineExtended) to a zip file each. The easiest way to do that is of course with PowerShell 🙂
The following code assumes that you have installed the modules in the default location and the zip files will be put on your desktop.

Add-Type -AssemblyName ''

Get-ChildItem -Path $PSHOME\Modules\MSonline* | ForEach-Object -Process {
    $Source = $_.FullName
    $Destination = '{0}\Desktop\{1}.zip' -f $env:USERPROFILE,$_.Name  
    [Io.compression.zipfile]::CreateFromDirectory($Source, $Destination) 

2. Upload the zipped modules files as assets to your automation account.
3. Now add your license service account as an asset to your automation account.
If you haven’t created one you can use the below PowerShell lines to do it. Note that the account only needs to have the “User management administrator” role.

#Create the service account
New-MsolUser -UserPrincipalName `
             -DisplayName "LicenseRobot" `
             -FirstName "License" `
             -LastName "Robot" `
             -Password 'password' `
             -ForceChangePassword $false `
             -PasswordNeverExpires $true 
#Add the user as an "User Account Administrator"
Add-MsolRoleMember -RoleName "User Account Administrator" `
                   -RoleMemberEmailAddress ""

4. Create your runbook and import the script. In this case I am using a slight modified version of this script, which you will find if you expand the script below below. This version are using the GroupObjectGuid to identify the groups instead of the DisplayName. Since the script is using Write-Output and Write-Warning for logging, Azure Automation will automatically handle basic logging.


#Connect to Azure AD
$Credentials = Get-AutomationPSCredential -Name 'LicenseRobot'
Connect-MsolService -Credential $Credentials

$Licenses = @{
                 'E1' = @{ 
                          LicenseSKU = 'tenant:STANDARDPACK'
                          Group = 'cb41a390-4312-4ecf-896e-086f64652690'
                          DisabledPlans = "SHAREPOINTSTANDARD"
                 'E3' = @{ 
                          LicenseSKU = 'tenant:ENTERPRISEPACK'
                          Group = '4549bd70-232a-4b71-8afe-4d431e5464ae'
                          DisabledPlans = "SHAREPOINTENTERPRISE","SHAREPOINTWAC"
                 'EMS' = @{
                         LicenseSKU = 'tenant:EMS'
                         Group = '9abc1736-049b-4c97-9fb6-8b6b02f9e7d5'
$UsageLocation = 'SE'
#Get all currently licensed users and put them in a custom object
$LicensedUserDetails = Get-MsolUser -All -Synchronized | Where-Object {$_.IsLicensed -eq 'True'} | ForEach-Object {
                UserPrincipalName = $_.UserPrincipalName
                License = $_.Licenses.AccountSkuId
                DisabledPlans = $_.Licenses.Servicestatus | Where-Object {$_.Provisioningstatus -contains "Disabled"}
#Create array for users to change or delete
$UsersToChangeOrDelete = @()
foreach ($license in $Licenses.Keys) {
    #Get current group name and ObjectID from Hashtable
    $GroupID = $Licenses[$license].Group
    $AccountSKU = Get-MsolAccountSku | Where-Object {$_.AccountSKUID -eq $Licenses[$license].LicenseSKU}
    $DisabledPlans =  $licenses[$license].DisabledPlans
    Write-Output "Checking for unlicensed $license users in group $GroupID..."
    #Get all members of the group in current scope
    $GroupMembers = ''
    $GroupMembers = (Get-MsolGroupMember -GroupObjectId $GroupID -All).EmailAddress
    #Get all already licensed users in current scope
    $ActiveUsers = ($LicensedUserDetails | Where-Object {$_.License -eq $licenses[$license].LicenseSKU}).UserPrincipalName 
    $UsersToHandle = ''
    $UsersToAdd = ''

    if ($GroupMembers) {  
        if ($ActiveUsers) {  
            #Compare $Groupmembers and $Activeusers
            #Users which are in the group but not licensed, will be added
            #Users licensed, but not, will be evaluated for deletion or change of license 
            $UsersToHandle = Compare-Object -ReferenceObject $GroupMembers -DifferenceObject $ActiveUsers -ErrorAction SilentlyContinue -WarningAction SilentlyContinue
            $UsersToAdd = ($UsersToHandle | Where-Object {$_.SideIndicator -eq '<='}).InputObject
            $UsersToChangeOrDelete += ($UsersToHandle | Where-Object {$_.SideIndicator -eq '=>'}).InputObject  
        } else {  
            #No licenses currently assigned for the license in scope, assign licenses to all group members.
            $UsersToAdd = $GroupMembers
    } else {  
        Write-Warning  "Group $GroupID is empty - will process removal or move of all users with license $($AccountSKU.AccountSkuId)"
        #If no users are a member in the group, add them for deletion or change of license.
        $UsersToChangeOrDelete += $ActiveUsers
    if ($UsersToAdd -match "[.]") {
        foreach ($User in $UsersToAdd){
            #Process all users for license assignment, if not already licensed with the SKU in order.  
            $MsolUser = Get-MsolUser -UserPrincipalName $User
            #Assign licenses for users
            if ($MsolUser.Licenses.AccountSkuId -notcontains $AccountSku.AccountSkuId) {
                try {  
                    #Assign UsageLocation and License.
                    $LicenseAssignmentHt = @{
                        UserPrincipalname = $User
                        AddLicenses = $AccountSKU.AccountSkuId
                    #Set custom license options to not enable SharePoint / OneDrive by Default
                    if ($DisabledPlans) {
                        $LicenseOptions = New-MsolLicenseOptions -AccountSkuId $AccountSKU.AccountSkuId -DisabledPlans $DisabledPlans
                        $LicenseAssignmentHt["LicenseOptions"] = $LicenseOptions
                    Set-MsolUser -UserPrincipalName $user -UsageLocation $UsageLocation -ErrorAction Stop -WarningAction Stop
                    Set-MsolUserLicense @LicenseAssignmentHt -ErrorAction Stop -WarningAction Stop
                    Write-Output "SUCCESS: Licensed $User with $license"
                } catch {  
                    Write-Warning "Error when licensing $User$_"
#Process users for change or deletion
if ($UsersToChangeOrDelete -ne $null) {
        foreach ($User in $UsersToChangeOrDelete) {
          if ($user -ne $null) {
            #Fetch users old license for later usage
            $OldLicense = ($LicensedUserDetails | Where-Object {$_.UserPrincipalName -eq $User}).License
             #Loop through to check if the user group assignment has been changed, and put the old and the new license in a custom object.
             #Only one license group per user is currently supported.
             $ChangeLicense = $Licenses.Keys | ForEach-Object {
                  $GroupID = $Licenses[$_].Group
                  if (Get-MsolGroupMember -All -GroupObjectId $GroupID | Where-Object {$_.EmailAddress -eq $User}) {
                        OldLicense = $OldLicense
                        NewLicense = $Licenses[$_].LicenseSKU
              if ($ChangeLicense) {
                    #The user were assigned to another group, switch license to the new one.
                    try {  
                          Set-MsolUserLicense -UserPrincipalName $User -RemoveLicenses $ChangeLicense.OldLicense -AddLicenses $ChangeLicense.NewLicense -ErrorAction Stop -WarningAction Stop
                          Write-Output "SUCCESS: Changed license for user $User from $($ChangeLicense.OldLicense) to $($ChangeLicense.NewLicense)"
                    } catch {  
                          Write-Warning "Error when changing license on $User`r`n$_"
              } else {  
                    #The user is no longer a member of any license group, remove license
                    Write-Warning "$User is not a member of any group, license will be removed... "
                    try {  
                          Set-MsolUserLicense -UserPrincipalName $User -RemoveLicenses $OldLicense -ErrorAction Stop -WarningAction Stop
                          Write-Output "SUCCESS: Removed $OldLicense for $User"
                    } catch {  
                          Write-Warning "Error when removing license on user`r`n$_"

5. Verify that the runbook is working as intended by starting it in the test pane. 2016-01-03_14-54-40
Make sure to solve eventual errors before you publish and schedule the runbook. The most common error is login errors for the automation service account.
6. Publish and schedule the runbook. In my case I will run it one time every hour.

You are now all set and can hopefully disable a scheduled task in your on premises environment. If thinking about running this in production, keep in mind that the PowerShell module is still in preview (I haven’t experienced any problems, but…).

Let me know if you have any questions or suggestions, and happy automation!