Forum Discussion
Jallcock
Dec 01, 2021Copper Contributor
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