Need one script to export User, Email, licenses used, MFA type, and Sign in status.

Steel Contributor

Dec 04, 2021

Okay, its too late for me rn
I dont have a premium plan so I cant test the part with last login and MFA.
Try it and give a feedback:
should look like this. Used the following articles:

https://techcommunity.microsoft.com/t5/azure/manage-licenses-with-powershell-in-azure-active-directory/m-p/2627903

https://www.higginson.org/powershell-getting-all-azure-ad-user-ids-last-login-date-and-time/

<#
Connect-AzAccount
Connect-AzureAD
Connect-ExcangeONline
Connect-MsolService
Import-Module -Name AzureADPreview
#>

Import-Module -Name AzureADPreview

$AzureADSubscribedSku = "O365_BUSINESS_ESSENTIALS"



Get-AzureAdUser | ForEach { $licensed=$False ; For ($i=0; $i -le ($_.AssignedLicenses | Measure).Count ; $i++)`
{ If( [string]::IsNullOrEmpty( $_.AssignedLicenses[$i].SkuId ) -ne $True) { $licensed=$true } } ; If( $licensed -eq $true)`
{ Write-Host $_.UserPrincipalName, $_.Mail, $_.AssignedLicenses.SkuId, $_.StrongAuthenticationRequirements.State} } | export-csv C:\users\Schnittlauch\desktop\exports.csv -Delimiter ';'




$Cred = Get-Credential
Connect-MsolService -Credential $Cred
Connect-AzureAD -Credential $Cred

$Users = Get-MsolUser -all
$Headers = "DisplayName`tUserPrincipalName`tLicense`tLastLogon" >>C:\Temp\Users.txt
ForEach ($User in $Users)
{
$UPN = $User.UserPrincipalName
$LoginTime = Get-AzureAdAuditSigninLogs -top 1 -filter "userprincipalname eq '$UPN'" | select CreatedDateTime
$NewLine = $User.DisplayName + "`t" + $User.UserPrincipalName + "`t" + $User.Licenses.AccountSkuId + "`t" + $LoginTime.CreatedDateTime
$NewLine >>C:\Temp\Users.txt
}

Jallcock

Copper Contributor

Dec 06, 2021

Thanks you are a life saver.

I have tried it but it erroring out. The account need MFA as it was required on all accounts as per the security consultant. I looks like the Connect-AzureAD isn't pulling for MFA and isn't logging.

This is the error.

Get-AzureAdUser : You must call the Connect-AzureAD cmdlet before calling any other cmdlets.
At line:15 char:1
+ Get-AzureAdUser | ForEach { $licensed=$False ; For ($i=0; $i -le ($_. ...
+ ~~~~~~~~~~~~~~~
+ CategoryInfo : NotSpecified: (:) [Get-AzureADUser], AadNeedAuthenticationException
+ FullyQualifiedErrorId : Microsoft.Open.Azure.AD.CommonLibrary.AadNeedAuthenticationException,Microsoft.Open.AzureAD16.PowerShell.GetUser

cmdlet Get-Credential at command pipeline position 1
Supply values for the following parameters:
Connect-AzureAD : One or more errors occurred.: AADSTS50076: Due to a configuration change made by your administrator, or because you moved to a new
location, you must use multi-factor authentication to access '00000002-0000-0000-c000-000000000000'.
Trace ID: 826c877b-dcd4-4ceb-96db-0fb64586a300
Correlation ID: 6db0dcc8-5d72-41da-931c-2c8a2f571c81
Timestamp: 2021-12-06 13:31:09Z
At line:24 char:1
+ Connect-AzureAD -Credential $Cred
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : AuthenticationError: (:) [Connect-AzureAD], AadAuthenticationFailedException
+ FullyQualifiedErrorId : Connect-AzureAD,Microsoft.Open.Azure.AD.CommonLibrary.ConnectAzureAD

Connect-AzureAD : One or more errors occurred.
At line:24 char:1
+ Connect-AzureAD -Credential $Cred
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : AuthenticationError: (:) [Connect-AzureAD], AggregateException
+ FullyQualifiedErrorId : Connect-AzureAD,Microsoft.Open.Azure.AD.CommonLibrary.ConnectAzureAD

Connect-AzureAD : AADSTS50076: Due to a configuration change made by your administrator, or because you moved to a new location, you must use
multi-factor authentication to access '00000002-0000-0000-c000-000000000000'.
Trace ID: 826c877b-dcd4-4ceb-96db-0fb64586a300
Correlation ID: 6db0dcc8-5d72-41da-931c-2c8a2f571c81
Timestamp: 2021-12-06 13:31:09Z
At line:24 char:1
+ Connect-AzureAD -Credential $Cred
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : AuthenticationError: (:) [Connect-AzureAD], AdalClaimChallengeException
+ FullyQualifiedErrorId : Connect-AzureAD,Microsoft.Open.Azure.AD.CommonLibrary.ConnectAzureAD

Connect-AzureAD : Response status code does not indicate success: 400 (BadRequest).
At line:24 char:1
+ Connect-AzureAD -Credential $Cred
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : AuthenticationError: (:) [Connect-AzureAD], HttpRequestException
+ FullyQualifiedErrorId : Connect-AzureAD,Microsoft.Open.Azure.AD.CommonLibrary.ConnectAzureAD

Connect-AzureAD : {"error":"interaction_required","error_description":"AADSTS50076: Due to a configuration change made by your administrator, or because
you moved to a new location, you must use multi-factor authentication to access '00000002-0000-0000-c000-000000000000'.\r\nTrace ID:
826c877b-dcd4-4ceb-96db-0fb64586a300\r\nCorrelation ID: 6db0dcc8-5d72-41da-931c-2c8a2f571c81\r\nTimestamp: 2021-12-06
13:31:09Z","error_codes":[50076],"timestamp":"2021-12-06 13:31:09Z","trace_id":"826c877b-dcd4-4ceb-96db-0fb64586a300","correlation_id":"6db0dcc8-5d72-41da
-931c-2c8a2f571c81","error_uri":"https://login.microsoftonline.com/error?code=50076","suberror":"basic_action"}: Unknown error
At line:24 char:1
+ Connect-AzureAD -Credential $Cred
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : AuthenticationError: (:) [Connect-AzureAD], AdalException
+ FullyQualifiedErrorId : Connect-AzureAD,Microsoft.Open.Azure.AD.CommonLibrary.ConnectAzureAD

Connect-AzureAD : One or more errors occurred.: AADSTS50076: Due to a configuration change made by your administrator, or because you moved to a new
location, you must use multi-factor authentication to access '00000002-0000-0000-c000-000000000000'.
Trace ID: 826c877b-dcd4-4ceb-96db-0fb64586a300
Correlation ID: 6db0dcc8-5d72-41da-931c-2c8a2f571c81
Timestamp: 2021-12-06 13:31:09Z
At line:24 char:1
+ Connect-AzureAD -Credential $Cred
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : NotSpecified: (:) [Connect-AzureAD], AadAuthenticationFailedException
+ FullyQualifiedErrorId : Microsoft.Open.Azure.AD.CommonLibrary.AadAuthenticationFailedException,Microsoft.Open.Azure.AD.CommonLibrary.ConnectAzureAD

Get-AzureAdAuditSigninLogs : You must call the Connect-AzureAD cmdlet before calling any other cmdlets.
At line:31 char:14
+ ... LoginTime = Get-AzureAdAuditSigninLogs -top 1 -filter "userprincipaln ...
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : NotSpecified: (:) [Get-AzureADAuditSignInLogs], AadNeedAuthenticationException
+ FullyQualifiedErrorId : Microsoft.Open.Azure.AD.CommonLibrary.AadNeedAuthenticationException,Microsoft.Open.MSGraphBeta.PowerShell.GetAuditSignInLo
gs

Forum Discussion

Need one script to export User, Email, licenses used, MFA type, and Sign in status.

Resources