Forum Discussion
Need one script to export User, Email, licenses used, MFA type, and Sign in status.
So let me first say thanks for working with me as I am new to powershell. With that I have found and used three different scripts to get this info and have three different .csv files that I can then combine. But I am wanting to know if I can get them to run as one script to output on .csv to save time as I have to do this twice month for the next 12 months. Each script pulls from a different powershell connection so I don't know if this will work as I haven't found a way to do it. This environment is O365 with no local servers.
These are the scripts I have.
Licenses used:
#Using this script administrator can identify all licensed users with their assigned licenses, services, and its status.
Param
(
[Parameter(Mandatory = $false)]
[string]$UserNamesFile
)
Function Get_UsersLicenseInfo
{
$LicensePlanWithEnabledService=""
$FriendlyNameOfLicensePlanWithService=""
$upn=$_.userprincipalname
$Country=$_.Country
if([string]$Country -eq "")
{
$Country="-"
}
Write-Progress -Activity "`n Exported user count:$LicensedUserCount "`n"Currently Processing:$upn"
#Get all asssigned SKU for current user
$Skus=$_.licenses.accountSKUId
$LicenseCount=$skus.count
$count=0
#Loop through each SKUid
foreach($Sku in $Skus) #License loop
{
#Convert Skuid to friendly name
$LicenseItem= $Sku -Split ":" | Select-Object -Last 1
$EasyName=$FriendlyNameHash[$LicenseItem]
if(!($EasyName))
{$NamePrint=$LicenseItem}
else
{$NamePrint=$EasyName}
#Get all services for current SKUId
$Services=$_.licenses[$count].ServiceStatus
if(($Count -gt 0) -and ($count -lt $LicenseCount))
{
$LicensePlanWithEnabledService=$LicensePlanWithEnabledService+","
$FriendlyNameOfLicensePlanWithService=$FriendlyNameOfLicensePlanWithService+","
}
$DisabledServiceCount = 0
$EnabledServiceCount=0
$serviceExceptDisabled=""
$FriendlyNameOfServiceExceptDisabled=""
foreach($Service in $Services) #Service loop
{
$flag=0
$ServiceName=$Service.ServicePlan.ServiceName
if($service.ProvisioningStatus -eq "Disabled")
{
$DisabledServiceCount++
}
else
{
$EnabledServiceCount++
if($EnabledServiceCount -ne 1)
{
$serviceExceptDisabled =$serviceExceptDisabled+","
}
$serviceExceptDisabled =$serviceExceptDisabled+$ServiceName
$flag=1
}
#Convert ServiceName to friendly name
for($i=0;$i -lt $ServiceArray.length;$i +=2)
{
$ServiceFriendlyName = $ServiceName
$Condition = $ServiceName -Match $ServiceArray[$i]
if($Condition -eq "True")
{
$ServiceFriendlyName=$ServiceArray[$i+1]
break
}
}
if($flag -eq 1)
{
if($EnabledServiceCount -ne 1)
{
$FriendlyNameOfServiceExceptDisabled =$FriendlyNameOfServiceExceptDisabled+","
}
$FriendlyNameOfServiceExceptDisabled =$FriendlyNameOfServiceExceptDisabled+$ServiceFriendlyName
}
#Store Service and its status in Hash table
$Result = @{'DisplayName'=$_.Displayname;'UserPrinciPalName'=$upn;'LicensePlan'=$Licenseitem;'FriendlyNameofLicensePlan'=$nameprint;'ServiceName'=$service.ServicePlan.ServiceName;
'FriendlyNameofServiceName'=$serviceFriendlyName;'ProvisioningStatus'=$service.ProvisioningStatus}
$Results = New-Object PSObject -Property $Result
$Results |select-object DisplayName,UserPrinciPalName,LicensePlan,FriendlyNameofLicensePlan,ServiceName,FriendlyNameofServiceName,
ProvisioningStatus | Export-Csv -Path $ExportCSV -Notype -Append
}
if($Disabledservicecount -eq 0)
{
$serviceExceptDisabled ="All services"
$FriendlyNameOfServiceExceptDisabled="All services"
}
$LicensePlanWithEnabledService=$LicensePlanWithEnabledService + $Licenseitem +"[" +$serviceExceptDisabled +"]"
$FriendlyNameOfLicensePlanWithService=$FriendlyNameOfLicensePlanWithService+ $NamePrint + "[" + $FriendlyNameOfServiceExceptDisabled +"]"
#Increment SKUid count
$count++
}
$Output=@{'Displayname'=$_.Displayname;'UserPrincipalName'=$upn;Country=$Country;'LicensePlanWithEnabledService'=$LicensePlanWithEnabledService;
'FriendlyNameOfLicensePlanAndEnabledService'=$FriendlyNameOfLicensePlanWithService}
$Outputs= New-Object PSObject -Property $output
$Outputs | Select-Object Displayname,userprincipalname,Country,LicensePlanWithEnabledService,FriendlyNameOfLicensePlanAndEnabledService | Export-Csv -path $ExportSimpleCSV -NoTypeInformation -Append
}
Function main()
{
#Clean up session
Get-PSSession | Remove-PSSession
#Connect AzureAD from PowerShell
Connect-MsolService
#Set output file
$ExportCSV=".\DetailedO365UserLicenseReport_$((Get-Date -format yyyy-MMM-dd-ddd` hh-mm` tt).ToString()).csv"
$ExportSimpleCSV=".\SimpleO365UserLicenseReport_$((Get-Date -format yyyy-MMM-dd-ddd` hh-mm` tt).ToString()).csv"
#FriendlyName list for license plan and service
$FriendlyNameHash=Get-Content -Raw -Path .\LicenseFriendlyName.txt -ErrorAction Stop | ConvertFrom-StringData
$ServiceArray=Get-Content -Path .\ServiceFriendlyName.txt -ErrorAction Stop
#Hash table declaration
$Result=""
$Results=@()
$output=""
$outputs=@()
#Get licensed user
$LicensedUserCount=0
#Check for input file/Get users from input file
if([string]$UserNamesFile -ne "")
{
#We have an input file, read it into memory
$UserNames=@()
$UserNames=Import-Csv -Header "DisplayName" $UserNamesFile
$userNames
foreach($item in $UserNames)
{
Get-MsolUser -UserPrincipalName $item.displayname | where{$_.islicensed -eq "true"} | Foreach{
Get_UsersLicenseInfo
$LicensedUserCount++}
}
}
#Get all licensed users
else
{
Get-MsolUser -All | where{$_.islicensed -eq "true"} | Foreach{
Get_UsersLicenseInfo
$LicensedUserCount++}
}
#Open output file after execution
Write-Host Detailed report available in: $ExportCSV
Write-host Simple report available in: $ExportSimpleCSV
$Prompt = New-Object -ComObject wscript.shell
$UserInput = $Prompt.popup("Do you want to open output files?",`
0,"Open Files",4)
If ($UserInput -eq 6)
{
Invoke-Item "$ExportCSV"
Invoke-Item "$ExportSimpleCSV"
}
}
. main
MFA:
Write-Host "Finding Azure Active Directory Accounts..."
$Users = Get-MsolUser -All | ? { $_.UserType -ne "Guest" }
$Report = [System.Collections.Generic.List[Object]]::new() # Create output file
Write-Host "Processing" $Users.Count "accounts..."
ForEach ($User in $Users) {
$MFAEnforced = $User.StrongAuthenticationRequirements.State
$MFAPhone = $User.StrongAuthenticationUserDetails.PhoneNumber
$DefaultMFAMethod = ($User.StrongAuthenticationMethods | ? { $_.IsDefault -eq "True" }).MethodType
If (($MFAEnforced -eq "Enforced") -or ($MFAEnforced -eq "Enabled")) {
Switch ($DefaultMFAMethod) {
"OneWaySMS" { $MethodUsed = "One-way SMS" }
"TwoWayVoiceMobile" { $MethodUsed = "Phone call verification" }
"PhoneAppOTP" { $MethodUsed = "Hardware token or authenticator app" }
"PhoneAppNotification" { $MethodUsed = "Authenticator app" }
}
}
Else {
$MFAEnforced = "Not Enabled"
$MethodUsed = "MFA Not Used"
}
$ReportLine = [PSCustomObject] @{
User = $User.UserPrincipalName
Name = $User.DisplayName
MFAUsed = $MFAEnforced
MFAMethod = $MethodUsed
PhoneNumber = $MFAPhone
}
$Report.Add($ReportLine)
}
Write-Host "Report is in c:\temp\MFAUsers.CSV"
$Report | Select User, Name, MFAUsed, MFAMethod, PhoneNumber | Sort Name | Out-GridView
$Report | Sort Name | Export-CSV -NoTypeInformation -Encoding UTF8 c:\temp\MFAUsers.csv
Sign in status:
$Groups = Get-UnifiedGroup -ResultSize 5$Groups | ForEach-Object {
$group = $_
Get-UnifiedGroupLinks -Identity $group.Name -LinkType Members -ResultSize Unlimited | ForEach-Object {
New-Object -TypeName PSObject -Property @{
Group = $group.DisplayName
Member = $_.Name
EmailAddress = $_.PrimarySMTPAddress
RecipientType= $_.RecipientType
Status = $_.SignInStatus
}}}
Once again thanks for the help!
- SchnittlauchSteel Contributor
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://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 }
- JallcockCopper ContributorThanks 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