Please help on speed up my code

DenisG · September 8, 2023, 5:02pm

Hello forum helper!
I have created a powershell with obtain some info from AzureAD User but it seems to run slowly.
I would like help to speed up this code. I have 80000 users and takes more or less 3 hours to run
Thank you in advance

#Gettting the User from the AAD
$all = get-azureaduser -All $true
$all | foreach-object {
    $user = $_
    #Expanding only the Extenstion Attributes related to the user and converting the Dictionary to Custom Object so that keys can be accessed through the dot (.) operator
    $Extension_Attributes = New-Object Psobject -Property $user.ExtensionProperty
    #Combining the required attributes from the user object and extension_attributes to A single object

    $u_properties = [pscustomobject] @{
    "ObjectID"= $user.ObjectId
    "Status" = $user.AccountEnabled
    "DisplayName" = $user.DisplayName
    "UserPrincipalName" = $user.UserPrincipalName
    "State" = $user.State
    "Country" = $user.Country
    "Company" = $user.CompanyName
    "City" = $user.City
    "EmployeeType" = $Extension_Attributes.extension_051b51b4f48a430aaeaac8d3f42b050a_employeeType
    "Mail" = $user.Mail
    "UsageCountry" = $user.UsageLocation
    "onPremisesDistinguishedName" = $Extension_Attributes.onPremisesDistinguishedName
    "Created" = $Extension_Attributes.createdDateTime
    "CompanyCode" = Get-ADUser -identity $Extension_Attributes.onPremisesDistinguishedName -Properties * | Select -Property extensionAttribute1, extensionAttribute11, extensionAttribute14
    "ManagerID" = Get-AzureADUserManager -ObjectId $user.ObjectId | Select -ExpandProperty ObjectId
    }
#Exporting the object to a file in an append fashoin
$u_properties | Export-Csv -Path C:\Users\xxxxxx\Documents\PowerBI\Users.csv -Append -NoTypeInformation -Encoding UTF8
}

Olaf · September 8, 2023, 7:46pm

DenisG,
Welcome to the forum.

I’d recommend 3 or 4 improvments on your code:

You query all Azure users and save them in a variable. But then you use the pipeline to iterate over them. I’d use a foreach loop.
You query your AD for each individual user inside your loop. I’d query all needed users before, save the result in a variable or a hashtable and use that inside the loop.
On top of that you query your AD with the parameter -Properties *. I wouldn’t do that. Provide only the properties for the parameter -Properties you need for your query.
You query your Azure AD for each individual user inside your loop. I’d query all needed users before, save the result in a variable or a hashtable and use that inside the loop.
You open, write and close your CSV file for each individual user inside your loop. I’d recommend to collect the results of your loop in a veriable and export this result at the end to a CSV file.

DenisG · September 9, 2023, 4:46pm

Hi Olaf,
Ok, I tried a full day to update my code and don’t really how to proceed but I already solved some issues.
For example, from Get-ADUser I obtain all info included the Extension Attributes so I think it is faster to use ADUser and not AzureADUser.
But I need to obtain the AzureADUser ObjectId when the user is sync with AAD

So, I can use :

$allAADUsers = get-azureADuser -all $true |Select OnPremisesSecurityIdentifier, ObjectId,UserPrincipalName
$AllADUsers  = Get-ADUser -Filter * -Properties GivenName,sn,DisplayName,CN,Name,extensionAttribute14,UserPrincipalName,StreetAddress,PostalCode,City,co,Country,State,MobilePhone,OfficePhone,Fax,Company,extensionAttribute11,Department,DepartmentNumber,Division,Organization,Title,Manager,DistinguishedName,employeeType,Enabled,extensionAttribute1,modifyTimeStamp

Now, I would like to find a way to join in the same file all info from ADUser + ‘ObjectId’ from AAD when the field ‘SID’ from ADuser = ‘OnPremisesSecurityIdentifier’ from AADUser

For example,
SID can be: S-1-5-21-8915387-163657461-1939875897-90558
OnPremisesSecurityIdentifier: S-1-5-21-8915387-163657461-1939875897-90558

If the user is not sync, I need to keep it in the list at the end without ObjectID assigned of course
PLease can you halp me on that?

Olaf · September 9, 2023, 7:47pm

That’s great to hear.

But in the end you need both, don’t you? So you have to query both in advance.

That’s what I meant with …

Again …

Since you did not provide any sample data to play with I had to come up with my own …

$ADUserList = @'
Name,Surname,GUID
John,Lennon,cbfd9c99-1f57-4568-b7d4-dde02fa3e793
George,Harrison,4365c83f-c567-43a2-9f65-20af7160aeed
Paul,McCartney,2c0fbd85-0f20-4a08-8593-565e89b8b750
Ringo,Starr,53efbdd4-573b-4ee7-a768-4bdc01d3b1a1
'@ |
ConvertFrom-Csv

$AADUserList = @'
UPN,AZ_GUID
John.Lennon@beatles.co.uk,cbfd9c99-1f57-4568-b7d4-dde02fa3e793
George.Harrison@beatles.co.uk,4365c83f-c567-43a2-9f65-20af7160aeed
Mick.Jagger@RollingStones.co.uk,02468be2-08e2-4cc7-aa26-e0a1d80e84b1
Ringo.Starr@beatles.co.uk,53efbdd4-573b-4ee7-a768-4bdc01d3b1a1
'@ |
ConvertFrom-Csv

The output of both looks like this:

PS C:\_Sample> $ADUserList

Name   Surname   GUID
----   -------   ----
John   Lennon    cbfd9c99-1f57-4568-b7d4-dde02fa3e793
George Harrison  4365c83f-c567-43a2-9f65-20af7160aeed
Paul   McCartney 2c0fbd85-0f20-4a08-8593-565e89b8b750
Ringo  Starr     53efbdd4-573b-4ee7-a768-4bdc01d3b1a1

PS C:\_Sample> $AADUserList

UPN                             AZ_GUID
---                             -------
John.Lennon@beatles.co.uk       cbfd9c99-1f57-4568-b7d4-dde02fa3e793
George.Harrison@beatles.co.uk   4365c83f-c567-43a2-9f65-20af7160aeed
Mick.Jagger@RollingStones.co.uk 02468be2-08e2-4cc7-aa26-e0a1d80e84b1
Ringo.Starr@beatles.co.uk       53efbdd4-573b-4ee7-a768-4bdc01d3b1a1

Now we need one of the list as hashtable to be able to use it as lookup table.

$LookUpHashTable = 
    $AADUserList | 
        Group-Object -Property AZ_GUID -AsHashTable

Now we iterate over the other list and use the hastable to get the missing property if it’s available.

foreach ($ADUser in $ADUserList) {
    [PSCustomObject]@{
        Name    = $ADUser.Name
        Surname = $ADUser.Surname
        UPN     = $LookUpHashTable[$ADUser.GUID].UPN
    }
}

The output looks like this:

Name   Surname   UPN
----   -------   ---
John   Lennon    John.Lennon@beatles.co.uk
George Harrison  George.Harrison@beatles.co.uk
Paul   McCartney 
Ringo  Starr     Ringo.Starr@beatles.co.uk

As an aside …

You don’t need the Select-Object. There’s no benefit in limitting the output to the provided 3 properties.

DenisG:

$AllADUsers  = Get-ADUser -Filter * -Properties GivenName,sn,DisplayName,CN,Name,extensionAttribute14,UserPrincipalName,StreetAddress,PostalCode,City,co,Country,State,MobilePhone,OfficePhone,Fax,Company,extensionAttribute11,Department,DepartmentNumber,Division,Organization,Title,Manager,DistinguishedName,employeeType,Enabled,extensionAttribute1,modifyTimeStamp

If you have such a big AD I’d urgently recommend using a -Searchbase.

Do you really need all these properties?

BTW: You don’t need to specify DistinguishedName, Enabled, GivenName, Name, ObjectClass, ObjectGUID, SamAccountName, SID, Surname and UserPrincipalName since these properties belong to the subset Get-ADUser returns by default.

DenisG · September 10, 2023, 1:19pm

Hi Olaf,
Thanks for your detailed feedback. I’m trying to apply your code to mine which become:

$AADUserList = get-azureADuser -ObjectId "d_G@mycompany.com" |Select OnPremisesSecurityIdentifier, ObjectId,UserPrincipalName | ConvertFrom-csv
$ADUserList  = Get-ADUser -Identity "CN=xxxxxx,OU=Internal Accounts,OU=P35 Accounts,OU=Users,OU=Accounts,DC=MyCompany,DC=net" -Properties sn,DisplayName,CN,extensionAttribute14,StreetAddress,PostalCode,City,co,Country,State,MobilePhone,OfficePhone,Fax,Company,extensionAttribute11,Department,DepartmentNumber,Division,Organization,Title,Manager,employeeType,extensionAttribute1,modifyTimeStamp | ConvertFrom-Csv

$LookUpHashTable = 
    $AADUserList | 
        Group-Object -Property OnPremisesSecurityIdentifier -AsHashTable

foreach ($ADUser in $ADUserList) {
    [PSCustomObject]@{
        Name    = $ADUser.Name
        Surname = $ADUser.Surname
        UPN     = $LookUpHashTable[$ADUser.SID].UPN
    }
}

Unfortunately, I receive this error message :

ConvertFrom-Csv : Cannot validate argument on parameter 'InputObject'. The argument is null, empty, or an element of the argument collection contains a null 
value. Supply a collection that does not contain any null values and then try the command again.
At line:5 char:397
+ ... er,employeeType,extensionAttribute1,modifyTimeStamp | ConvertFrom-Csv
+                                                           ~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidData: (CN=FRSC9001,OU=...=danfoss,DC=net:PSObject) [ConvertFrom-Csv], ParameterBindingValidationException
    + FullyQualifiedErrorId : ParameterArgumentValidationError,Microsoft.PowerShell.Commands.ConvertFromCsvCommand

Of course, some fields from AAD or AD user are empty … so how can I solve it?

Olaf · September 10, 2023, 1:41pm

Again … you should omit the Select-Object … You don’t need it.

And the ConvertFrom-Csv is wrong. That’s why you get an error!! Why did you add it? Are you guessing? Don’t you wonder why an error happens when it happens?

You should start learning how to debug your own code.

DenisG:

$ADUserList  = 
    Get-ADUser -Identity "CN=xxxxxx,OU=Internal Accounts,OU=P35 Accounts,OU=Users,OU=Accounts,DC=MyCompany,DC=net" -Properties sn,DisplayName,CN,extensionAttribute14,StreetAddress,PostalCode,City,co,Country,State,MobilePhone,OfficePhone,Fax,Company,extensionAttribute11,Department,DepartmentNumber,Division,Organization,Title,Manager,employeeType,extensionAttribute1,modifyTimeStamp | 
    ConvertFrom-Csv

You are querying ONE SINGLE AD-User!! Why?
And why did you add the ConvertFrom-Csv again?

If you add it because you’ve seen it in my code suggestion than think about why I added it, please. In my code suggestion I have to use sample data from CSV. You on the other hand get your data from an AD or AzureAD. So you don’t need to convert them. They are already proper PowerShell object.

Why do you actually need to optain the UPN of the users from Azure? They have a UPN in your AD, don’t they?

DenisG · September 10, 2023, 4:09pm

Hi Olaf,
Thank you for your patience … I’m beginner in Powershell … I developped in lot of Basic languages but learner in Powershell.
I tested on 1 user to speed-up my tests.
OK, I will try to progress on that

Olaf · September 10, 2023, 6:40pm

We all started once …

What basic languages? I’m concerned that PowerShell is actually one of most basic languages when it’s about to learn it.

Hmmm … while I can follow your thought I’d recommend to use a few more test objects.
When you collected your data anyway with the queries …

$SearchBase = 
    "OU=Internal Accounts,OU=P35 Accounts,OU=Users,OU=Accounts,DC=MyCompany,DC=net"
$Properties = @(
    'sn'
    'DisplayName'
    'CN'
    'extensionAttribute14'
    'StreetAddress'
    'PostalCode'
    'City'
    'co'
    'Country'
    'State'
    'MobilePhone'
    'OfficePhone'
    'Fax'
    'Company'
    'extensionAttribute11'
    'Department'
    'DepartmentNumber'
    'Division'
    'Organization'
    'Title'
    'Manager'
    'employeeType'
    'extensionAttribute1'
    'modifyTimeStamp'
)

$ADUserList = 
    Get-ADUser -Filter * -SearchBase $SearchBase -Properties $Properties
$AADUserList = 
    Get-AzureADUser -All $true

You can easily limit the use of them with a Select-Object …

$ADUserList | 
    Select-Object -First 100
$AADUserList | 
    Select-Object -First 100

You just have to make sure that you have enough objects to match in your selection.

If you assign this output to some intermediate variables you can play with them until you’re satisfied with the results without the need to query your AD and your AzureAD again and again.

DenisG · September 12, 2023, 8:08am

Hi Olaf,
Thanks for your last code which help me a lot.

I added -filter * for get-ADUser else it won’t work.
I added | Select OnPremisesSecurityIdentifier else it is not included by default in the list

$ADUserList = Get-ADUser -filter * -SearchBase SearchBase -Properties $Properties
$AADUserList = Get-AzureADUser -All $true |Select OnPremisesSecurityIdentifier

Please find the full code here:

$output = @()


$Path = $env:userprofile\Documents\

$SearchBase = 
    "OU=Internal Accounts,OU=P35 Accounts,OU=Users,OU=Accounts,DC=danfoss,DC=net"

$Properties = @(
    'sn'
    'DisplayName'
    'CN'
    'extensionAttribute14'
    'StreetAddress'
    'PostalCode'
    'City'
    'co'
    'Country'
    'State'
    'MobilePhone'
    'OfficePhone'
    'Fax'
    'Company'
    'extensionAttribute11'
    'Department'
    'DepartmentNumber'
    'Division'
    'Organization'
    'Title'
    'Manager'
    'employeeType'
    'extensionAttribute1'
    'modifyTimeStamp'
)

$ADUserList = Get-ADUser -filter * -SearchBase SearchBase -Properties $Properties
$AADUserList = Get-AzureADUser -All $true |Select OnPremisesSecurityIdentifier

$LookUpHashTable = 
    $AADUserList | 
        Group-Object -Property userPrincipalName -AsHashTable -asString

foreach ($ADUser in $ADUserList) {
    [PSCustomObject]@{
        SID     = $LookUpHashTable[$ADUser.userPrincipalName].OnPremisesSecurityIdentifier
        Name    = $ADUser.Name
        Surname = $ADUser.Surname
        sn = $ADuser.sn
        DisplayName = $ADuser.DisplayName
        CN = $ADuser.CN
        extensionAttribute14  = $ADuser.extensionAttribute14
        StreetAddress  = $ADuser.StreetAddress
        PostalCode =  $ADuser.PostalCode
        City = $ADuser.City
        co = $ADuser.co
        Country = $ADuser.Country
        State = $ADuser.State
        MobilePhone = $ADuser.MobilePhone
        OfficePhone = $ADuser.OfficePhone
        Fax = $ADuser.Fax 
        Company = $ADuser.Company
        extensionAttribute11 = $ADuser.extensionAttribute11
        Department = $ADuser.Department
        DepartmentNumber = $ADuser.DepartmentNumber
        Division = $ADuser.Division
        Organization = $ADuser.Organization
        Title = $ADuser.Title
        Manager = $ADuser.Manager
        employeeType = $ADuser.employeeType
        extensionAttribute1 = $ADuser.extensionAttribute1
        modifyTimeStamp = $ADuser.modifyTimeStamp
    }
    $output += $ADUser 
}
$output | export-csv -Path $Path -NoTypeInformation -Encoding UTF8

if ((Get-Item $path).Length -gt 0) {
  Write-Host "Report finished and saved in $path" -ForegroundColor Green

  # Open the CSV file
  Invoke-Item $Path

}else{
  Write-Host "Failed to create report" -ForegroundColor Red
}

To extract data from ADUser and AADuser is now faster … but unfortunately, the foreach loop takes 1 hour (around 84000 users)
Certainely, I, again made something wrong … Please, can you have a look and say me where I can improve the loop?
I know, I ask a lot from your time and I’m grateful for that.
I need all fileds from ADUsers and add SID from AADUsers
Thanks in advance

Olaf · September 12, 2023, 9:57am

Sorry. Of course you’re right. My mistake. I corrected my code suggestion above.

That’s actually technicly impossible because with Select-Object you limit the output to the provided properties. They have to be there before then.

You have to use quotes for strings. And it should be a complete file path. With a file name and extension if you want to use it for Export-Csv

Please wipe this syntax for expanding arrays completely from your brain. It’s wrong for 99.9999% of the cases. And it is slow.

Try this:

$Path = "$env:userprofile\Documents\AllUsersList_$(Get-Date -Format 'yyyyMMdd-HHmmss').csv"

$SearchBase = 
"OU=Internal Accounts,OU=P35 Accounts,OU=Users,OU=Accounts,DC=danfoss,DC=net"

$Properties = @(
    'sn'
    'DisplayName'
    'CN'
    'extensionAttribute14'
    'StreetAddress'
    'PostalCode'
    'City'
    'co'
    'Country'
    'State'
    'MobilePhone'
    'OfficePhone'
    'Fax'
    'Company'
    'extensionAttribute11'
    'Department'
    'DepartmentNumber'
    'Division'
    'Organization'
    'Title'
    'Manager'
    'employeeType'
    'extensionAttribute1'
    'modifyTimeStamp'
)

$ADUserList = 
    Get-ADUser -Filter * -SearchBase $SearchBase -Properties $Properties
$AADUserList = 
    Get-AzureADUser -All $true 

$LookUpHashTable = 
$AADUserList | 
Group-Object -Property userPrincipalName -AsHashTable -asString

$ResultList =
foreach ($ADUser in $ADUserList) {
    [PSCustomObject]@{
        SID                  = $LookUpHashTable[$ADUser.userPrincipalName].OnPremisesSecurityIdentifier
        Name                 = $ADUser.Name
        Surname              = $ADUser.Surname
        sn                   = $ADuser.sn
        DisplayName          = $ADuser.DisplayName
        CN                   = $ADuser.CN
        extensionAttribute14 = $ADuser.extensionAttribute14
        StreetAddress        = $ADuser.StreetAddress
        PostalCode           = $ADuser.PostalCode
        City                 = $ADuser.City
        co                   = $ADuser.co
        Country              = $ADuser.Country
        State                = $ADuser.State
        MobilePhone          = $ADuser.MobilePhone
        OfficePhone          = $ADuser.OfficePhone
        Fax                  = $ADuser.Fax 
        Company              = $ADuser.Company
        extensionAttribute11 = $ADuser.extensionAttribute11
        Department           = $ADuser.Department
        DepartmentNumber     = $ADuser.DepartmentNumber
        Division             = $ADuser.Division
        Organization         = $ADuser.Organization
        Title                = $ADuser.Title
        Manager              = $ADuser.Manager
        employeeType         = $ADuser.employeeType
        extensionAttribute1  = $ADuser.extensionAttribute1
        modifyTimeStamp      = $ADuser.modifyTimeStamp
    }
}
$ResultList | 
    Export-Csv -Path $Path -Encoding UTF8 -NoTypeInformation

Olaf · September 12, 2023, 12:04pm

Since you marked my last code suggestion as solution … I am curious … how long does the code run now to complete the task?

DenisG · September 12, 2023, 12:48pm

Hi Olaf,
Thank you very much for your patience and your great help. I learned a lot with your help
It works like a charme now and the full process takes only 50 minutes globally … I accepted your solution because first, you used lot of time to help me and also because I think we cannot reduce more the time used for this process

dotnVo · September 13, 2023, 1:07pm

One other thing that may help, look into using Microsoft Graph PowerShell SDK instead of the Azure AD module for querying users in Azure . The graph SDK is much faster in my experience.

DenisG · September 14, 2023, 6:21am

Hi dotnVo, I’m using MSGraph for querying Intune PC List so yes, I can useit for AzureADUser. DO you have an example?

dotnVo · September 14, 2023, 5:13pm

I use the PowerShell SDK so after connecting using Connect-MgGraph you can use Get-MgUser -All I believe to get all users. Depending on the properties you need from the azure side, you may need to modify that command though, as some of those properties may not return by default that do in the Get-AzureADUser command. However, it appears you are only concerned with the UPN so default output should be ok.