Help with Creating 365 Users and Altering UPN

SaraBowman 

Hi, Sara.

This scenario has a lot of depth and complexity to it - depending on how resilient you want the script to be.

I don't have the stamina to explore all the issues you can face in one pass, so I'll start with a simpler compromise first, focusing on the important logical design considerations and then we can iterate through other considerations after that if you have questions.

I started with your script and worked from there, but as you'll see, the resulting example below is notably different.

Design considerations

• You don't want your script to fall over if one or more columns do not have a value, or if the value is nothing but whitespace;
• You need to look at more than just userPrincipalName for conflicts. Focus on:
  • mail;
  • proxyAddresses;
  • userPrincipalName;
• While not required, it's prudent to keep consistency between userPrincipalName, mail and mailNickname;
• Your script should cope graciously with partial completion;
• Your script should run efficiently through minimising unnecessary calls to Graph;
• Use the least-permissive Graph API scope that will get the job done.

Script template

If you use something well-defined from a source of truth for MailNickname, such as an employeeId, studentId, etc., you should comment out or remove line 124.

#Connect to Microsoft Graph Powershell Module
Connect-MgGraph -Scopes "User.ReadWrite.All" -NoWelcome;

# Import the CSV file
$SourceFile = "D:\Data\Temp\Forum\forum.csv";
$LogFile = [string]::Concat($SourceFile, ".log");
$csvrecords = Import-Csv -Path $SourceFile; #Add our file here

# Create a numeric randomiser.
$Randomiser = [random]::new();

# Obtain the licence skuId once up front, as we're wasting time, money and service rate limits calling it repeatedly from within a loop.
$e3Sku = Get-MgSubscribedSku | Where-Object -Property "SkuPartNumber" -EQ -Value 'AAD_BASIC'; # SPE_E3

# Loop through each user in the CSV file
foreach ($csvrecord in $csvrecords)
{
    #region Basic quality check against the CSV userPrincipalName, where if it's invalid, we'll skip this record and continue to the next.
    if (
        [string]::IsNullOrWhiteSpace($csvrecord.UserPrincipalName) -or
        (2 -ne ($csvrecord.UserPrincipalName.Split("@")).Count)
    )
    {
        continue;
    }
    #endregion

    # Create a password profile featuring a randomised four-digit value.
    $PasswordProfile = @{
        Password = "Password$( $Randomiser.Next(9999).ToString('D4'))!"
    }

    #region Prepare a HashTable for splatting with New-MgUser.
    # Start with the well-defined attributes.
    $Parameters = @{
        AccountEnabled = $true;
        PasswordProfile = $PasswordProfile;
        UsageLocation = "US";
        UserPrincipalName = $csvrecord.UserPrincipalName;
    }

    # Then conditionally add additional parameters only if they are not null/empty/whitespace values.
    foreach ($Column in @("GivenName", "Department", "DisplayName", "JobTitle", "MailNickname", "Surname"))
    {
        # This only works because the CSV column names match the New-MgUser parameter names.
        if (-not [string]::IsNullOrWhiteSpace($csvrecord."$Column"))
        {
            $Parameters.Add($Column, $csvrecord."$Column");
        }
    }
    #endregion

    #region Fetch all potential matches in a single pass.
    $upnParts = $csvrecord.UserPrincipalName.Split("@");
    $upnPrefix = $upnParts[0];
    $upnDomain = $upnParts[1];
    $users = Get-MgUser -Filter "startsWith(userPrincipalName, '$upnPrefix')" -Property mail, proxyAddresses, userPrincipalName;
    #endregion

    #region Process the returned list of users, determining a new index - if relevant.
    if ($users)
    {
        # We're going to work off non-negative values indicating the current highest value.
        $maxIndex = -1;
        $newIndex = 0;
        
        foreach ($user in $users)
        {
            # Note: We're not going to explicitly perform a check against the mail attribute, as it's value implicitly features within the more comprehensive proxyAddresses attribute.

            #region Check against proxyAddresses, noting that proxyAddresses - like mail - is optional.
            if ($user.proxyAddresses.Count -gt 0)
            {
                foreach ($Entry in $user.proxyAddresses)
                {
                    # We're only interested in SMTP addresses, not X.500 or SIP (though if you wanted to get really robust, you'd probably bring SIP into scope).
                    if ($Entry.StartsWith("smtp:", [System.StringComparison]::OrdinalIgnoreCase))
                    {
                        # Strip off the smtp: prefix to ease subsequent comparisons.
                        $Address = $Entry.Substring(5);
                        $currentPrefix = $Address.Split("@")[0];

                        if ($upnPrefix -eq $currentPrefix)
                        {
                            if ($maxIndex -eq -1)
                            {
                                $maxIndex = 0;
                            }
                        }
                        elseif ([uint64]::TryParse($currentPrefix.Substring($upnPrefix.Length), [ref] $newIndex) -and ($newIndex -gt $maxIndex))
                        {
                            $maxIndex = $newIndex;
                        }
                    }
                }
            }
            #endregion

            #region Check against userPrincipalName.
            if ($user.userPrincipalName)
            {
                $currentPrefix = $user.userPrincipalName.Split("@")[0];

                if ($upnPrefix -eq $currentPrefix)
                {
                    if ($maxIndex -eq -1)
                    {
                        $maxIndex = 0;
                    }
                }
                elseif ([uint64]::TryParse($currentPrefix.Substring($upnPrefix.Length), [ref] $newIndex) -and ($newIndex -gt $maxIndex))
                {
                    $maxIndex = $newIndex;
                }
            }
            #endregion
        }

        if ($maxIndex -ge 0)
        {
            # Update the relevant parameters to leverage the provided userPrincipalName prefix post-fixed with [index + 1].
            $upnPrefix = [string]::Concat($upnPrefix, (1 + $maxIndex));

            $Parameters["MailNickname"] = $upnPrefix;
            $Parameters["UserPrincipalName"] = [string]::Concat($upnPrefix, "@", $upnDomain);
        }
    }
    #endregion

    #region Create the new user, set licencing, etc. and export the outcome.
    # Create a new output-friendly object.
    $Result = [PSCustomObject] @{
        status = "Failed";
        id = $null;
        csvUserPrincipalName = $csvrecord.UserPrincipalName;
        userPrincipalName = $Parameters["UserPrincipalName"];
        mailNickname = $Parameters["MailNickname"];
        defaultPassword = $PasswordProfile["Password"];     # This is not a good idea at all, but it's in line with your current approach. Having passwords in files, e-mails, etc. is inviting trouble.
    }

    try
    {
        # Create the user.
        $newUser = New-MgUser @Parameters -ErrorAction:Stop;
        $Result.status = "Created";
        $Result.id = $newUser.id;

        # Assign a license to the new user.
        if ($e3Sku.SkuId)
        {
            $null = Set-MgUserLicense -UserId $newUser.id -AddLicenses @{SkuId = $e3Sku.SkuId} -RemoveLicenses @() -ErrorAction:Stop;
            $Result.status = "Licenced";
        }
        else
        {
            $Result.status = "Skipped licencing";
        }

        # Final update to status indicating end-to-end completion.
        $Result.status = "Success";
    }
    catch
    {
        # We-re going to treat exceptions as non-terminating for this example, but I personally default to treating them as terminating.
        Write-Error -Exception ($_.Exception) -ErrorAction:Continue;
    }

    # Append the result to a CSV file.
    $Result | Export-Csv -Path $LogFile -Append -NoTypeInformation; #Add our file here
    #endregion
}

#Disconnect from MgGraph
Disconnect-MgGraph | Out-Null;

Examples

I've provided example output from two separate passes so you can see the impact on the log file as things like licence exhaustion lead to partial completion.

Source CSV data

Note: I'd replaced the "@" symbolc with "_at_", since these forums strip out the entire RFC822 value, otherwise.

First pass

Given the results are being piped to CSV, I haven't bothered sending the output to additional locations like the standard output stream. As there's no errors in this first pass, there's no screenshot from the execution of the script.

Log file

Verification to screen

Second pass

In this second pass, during the script execution, we see I've run out of licences to assign, leading to the partial completion scenario I mentioned in the design considerations.

In this case, the script is simple and there's only one partial success possibility, which is that the account was created but the licencing failed, which is what we see across all screens and the CSV log output. But if there more steps, this would become increasingly valuable when troubleshooting.

Log file

Note the "status" column indicates the last successful stage actioned.

Execution screen

Validation to screen

Let me know if you have any queries on particular lines/areas.

Cheers,

Lain