I use a MSGraph PowerShell script to extract certain information from our users' O365 accounts. The script runs fine for the first dozen users or so, then seems to stall for a minute or few then resume normally for the next dozen or so users and so on. I'd like to know if there's a way to speed up the execution. The execution speed is fine until extracting the SignInActivity. That's where things get bogged down. The problem lines are below.
@{Label = "Last Sign On"; Expression = {
#Expression variables
$GUID = $_.Id
$GetSignOn = Get-MgUser -UserId $GUID -Property SignInActivity | `
Select-Object {$_.SignInActivity} -ExpandProperty SignInActivity | Select-Object LastSignInDateTime
#Extract desired data from array
$LastSignOn = $GetSignOn.LastSignInDateTime -split " "
$LastSignOn[0]
}
I've removed the SignInActivity request from the script and it runs without pauses and at a decent pace. Clarifying the question, Can I speed up the execution of the portion of the script which pulls users' SignInActivity and if so, how? The full code is below.
#Connect to Microsoft Graph with Required Scopes
Connect-MgGraph -Scopes "User.Read.All","Directory.Read.All","AuditLog.Read.All"
#Switch to Beta version to access Last Sign On Properties
Select-MgProfile beta
#DateStamp and Path for Output file
$LogDate = Get-Date -Format "dd-MMM-yyyy"
$Path = "C:\Scripts\MSGraph\ADUsers\CTS Licensed Users_$LogDate.csv"
#Define Properties for UserReport
$Properties = @(
'GivenName', 'DisplayName', 'JobTitle', 'UserPrincipalName', 'CompanyName', 'Department', 'MobilePhone', 'BusinessPhones', 'OfficeLocation', `
'StreetAddress', 'City', 'State', 'OtherMails', 'EmployeeId', 'Manager', 'CreatedDateTime', 'EmployeeHireDate', 'Id', 'SignInActivity', 'AssignedLicenses'
)
#Define Filter Variables for Users with Paid O365 Subscription
$StdLicense = "f245ecc8-75af-4f8e-b61f-27d8114de5f3"
$PremLicense = "cbdc14ab-d96c-4c30-b9f4-6ada7cdc1d46"
#Define Filter Variable to exclude IT Helpdesk
$IT = "IT Helpdesk"
#Structure Filter as a Script Block
$UserReport = Get-MgUser -Filter "AccountEnabled eq true" -Property $Properties | Where-Object {
( ($_.AssignedLicenses.SkuId -eq $StdLicense) -or ($_.AssignedLicenses.SkuId -eq $PremLicense) -and ($_.DisplayName -ne $IT ) )
}
Write-Host -f Yellow "PowerShell is Preparing:"
Write-Host -f Yellow "CTS Licensed User Report"
#Build Hashtable and Populate Properties
$UserReport | Sort-Object GivenName | Select-Object `
@{Label = "Full Name"; Expression = { $_.DisplayName } },
@{Label = "Job Title"; Expression = { $_.JobTitle } },
@{Label = "Company Email"; Expression = { $_.UserPrincipalName } },
@{Label = "Company"; Expression = { $_.CompanyName } },
@{Label = "Department"; Expression = { $_.Department } },
@{Label = "Mobile Phone"; Expression = { $_.MobilePhone } },
@{Label = "Office Phone"; Expression = { $_.BusinessPhones } },
@{Label = "Office Address"; Expression = { $_.OfficeLocation } },
@{Label = "Employee Street"; Expression = { $_.StreetAddress } },
@{Label = "Employee City"; Expression = { $_.City } },
@{Label = "Employee State"; Expression = { $_.State } },
@{Label = "Personal Email"; Expression = { $_.OtherMails } },
@{Label = "EIN"; Expression = { $_.EmployeeId } },
@{Label = "Manager Name"; Expression = {
#Expression variables
$UserId = $_.UserPrincipalName
$ManagerArry = Get-MgUser -UserId $UserId -ExpandProperty Manager | `
Select-Object {$_.Manager.AdditionalProperties.displayName}
#Extract desired data from array
$splitName = $ManagerArry -Split "="
$ManagerName = $splitName -split "}"
$ManagerName[1]
} },
@{Label = "Manager Email"; Expression = {
#Expression variables
$UserId = $_.UserPrincipalName
$MailArry = Get-MgUser -UserId $UserId -ExpandProperty Manager | `
Select-Object {$_.Manager.AdditionalProperties.userPrincipalName}
#Extract desired data from array
$splitMail = $MailArry -Split "="
$ManagerMail = $splitMail -Split"}"
$ManagerMail[1]
} },
@{Label = "Account Created"; Expression = {
#Expression variables
$CreatedOn = $_.CreatedDateTime
#Extract desired data from array
$SplitTime = $CreatedOn -Split " "
$CreatedDate = $SplitTime -Split "="
$CreatedDate[0] } },
@{Label = "Hire Date"; Expression = {
#Expression variables
$HiredOn = $_.EmployeeHireDate
#Extract desired data from array
$NoHireTime = $HiredOn -Split " "
$HireDate = $NoHireTime -Split "="
$HireDate[0] } },
@{Label = "License"; Expression = {
#Expression variables
$UserId = $_.UserPrincipalName
$GetLicenses = Get-MgUserLicenseDetail -UserId $UserId | Select-Object SkuPartNumber
$GetLicenses.SkuPartNumber -join ','
} },
@{Label = "Last Sign On"; Expression = {
#Indicate Progress
Write-Host "Exporting User Details for"$_.DisplayName
#Expression variables
$GUID = $_.Id
$GetSignOn = Get-MgUser -UserId $GUID -Property SignInActivity | `
Select-Object {$_.SignInActivity} -ExpandProperty SignInActivity | Select-Object LastSignInDateTime
#Extract desired data from array
$LastSignOn = $GetSignOn.LastSignInDateTime -split " "
$LastSignOn[0]
}
} | Export-Csv -Path $Path -NoTypeInformation
Write-Host -f Yellow "Success!"
Write-Host -f Yellow "Report Saved to:" $Path
NOTE
Since I see that you have Select-MgProfile beta in your code, most likely you're using an old version of this Module, in latest version the cmdlets targeting beta and v1.0 endpoints have been divided into 2 different Modules, now for beta you would use Get-MgBetaUser without a need for Select-MgProfile.
Consider updating your Module with:
Update-Module Microsoft.Graph
And installing the beta version with:
Install-Module -Name Microsoft.Graph.Beta
There is a lot that can be improved from your current code, the main issues are many Graph calls for the same user to get information you already have, i.e. Get-MgUser -UserId $UserId -ExpandProperty Manager is being called twice, to get the Manager's displayName and mail when the API call could be just one. The other inefficiency comes when calling Get-MgUserLicenseDetail for each user when what you could do instead is create a map of all SkuID and their corresponding SkuPartNumber using Get-MgSubscredSku -All.
Filtering all users with:
Where-Object {
$_.AssignedLicenses.SkuId -eq $StdLicense -or
$_.AssignedLicenses.SkuId -eq $PremLicense -and
$_.DisplayName -ne $IT
}
Can be replaced by filtering server side, with the OData filter, i.e.:
displayName ne '$IT' and
(assignedLicenses/any(e:e/skuId eq $StdLicense) or
assignedLicenses/any(e:e/skuId eq $PremLicense))
Finally, the use of Select-Object and its horrible syntax for calculated properties makes the code unreadable, its recommended to use [pscustomobject] instead.
The final code becomes:
# definitions of `$Properties`, `$StdLicense`, `$premLicense` and others
# remains the same here
$skuMap = @{}
Get-MgSubscribedSku -All | ForEach-Object {
$skuMap[$_.SkuId] = $_.SkuPartNumber
}
$getMgUserSplat = @{
Filter = -join @(
"accountEnabled eq true and " # All Enabled user accounts
"displayName ne '$IT' and " # Where their displayName is not `$IT`
"(assignedLicenses/any(e:e/skuId eq $StdLicense) or " # and any of their assigned licenses
"assignedLicenses/any(e:e/skuId eq $PremLicense))" # is either `$StdLicense` or `$PremLicense`
)
Property = $Properties
ConsistencyLevel = 'eventual'
CountVariable = 'count'
All = $true
}
Get-MgUser @getMgUserSplat | ForEach-Object {
$managerEmail = $managerName = $null
$manager = (Get-MgUserManager -UserId $_.Id -ErrorAction SilentlyContinue).AdditionalProperties
if ($manager) {
$managerName = $manager['displayName']
$managerEmail = $manager['mail']
}
[pscustomobject]@{
'Full Name' = $_.DisplayName
'Job Title' = $_.JobTitle
'Company Email' = $_.UserPrincipalName
'Company' = $_.CompanyName
'Department' = $_.Department
'Mobile Phone' = $_.MobilePhone
'Office Phone' = $_.BusinessPhones -join ', '
'Office Address' = $_.OfficeLocation
'Employee Street' = $_.StreetAddress
'Employee City' = $_.City
'Employee State' = $_.State
'Personal Email' = $_.OtherMails -join ', '
'EIN' = $_.EmployeeId
'Manager Name' = $managerName
'Manager Email' = $managerEmail
'Account Created' = $_.CreatedDateTime
'Hire Date' = $_.EmployeeHireDate
'License' = $skuMap[$_.AssignedLicenses.SkuId] -join ', '
'Last Sign On' = $_.SignInActivity.LastSignInDateTime
}
} | Export-Csv $Path -NoTypeInformation