Forum Discussion

Jallcock's avatar
Jallcock
Copper Contributor
Dec 01, 2021

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!

  • Schnittlauch's avatar
    Schnittlauch
    Steel Contributor

    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-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's avatar
      Jallcock
      Copper Contributor
      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

Resources