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

Occasional Contributor

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!

2 Replies

@Jallcock 

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-directo...

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
}

 

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