PowerShell

O365 – Determine Mailbox Folder Size from Exchange Online using PowerShell

by

I’m sure all Exchange Online environments have users who are data hoarders in their email environment. Most tenants have policies to limit how big the mailbox can be for various reasons. Users may come back with increasing mailbox sizes so they can hoard more of those outdated data. It is good practice to maintain the folder items. Get-MailboxFolderStatistics helps retrieve information about folders in mailboxes, including number and size of items in the folder and other information.

Before proceeding, make sure you are connected to Exchange Online module,

$credential = Get-Credential
Connect-ExchangeOnline -credential $credential

To determine a folders from a user’s mailbox, the folder size and the number of items in the folders,

$EmailId = Read-Host "Enter user's email address"
Get-MailboxFolderStatistics -Identity $EmailId | Select Name,FolderSize,ItemsinFolder

To determine individual folders and subfolder sizes of a specific user:

$EmailId = Read-Host "Enter user's email address"
Get-MailboxFolderStatistics -Identity $EmailId | Select Name,FolderAndSubfolderSize,ItemsInFolderAndSubfolders

To determine inbox folders statistics of a specific user:

$EmailId = Read-Host "Enter user's email address"
Get-MailboxFolderStatistics -Identity $EmailId -FolderScope Inbox | Format-Table Identity,ItemsInFolderAndSubfolders,FolderAndSubfolderSize -AutoSize

To determine and display inbox folder sizes for all mailboxes in the organization 

$All = Get-Mailbox -ResultSize Unlimited
$All | foreach {Get-MailboxFolderStatistics -Identity $_.Identity -FolderScope Inbox} | Select Identity,ItemsInFolderAndSubfolders,FolderAndSubfolderSize | ft -AutoSize

and export to CSV file:

$Allmbx = Get-Mailbox -ResultSize Unlimited
$Allmbx | foreach {Get-MailboxFolderStatistics -Identity $_.Identity -FolderScope Inbox | Select Identity,ItemsInFolderAndSubfolders,FolderAndSubfolderSize | Export-Csv "C:\tmp\Inbox_data.csv" -NoTypeInformation -Append}

Get-MailboxFolderStatistics returns IPM subtree folders. This folder structure consists of messages between recipients(Inbox, Sent Items). In the Exchange Online, the Non-IMP subtree is quite larger, as different O365 applications have been using mailboxes to store and process data. Teams, Delve, MyAnalytics, all have their own folders or folder trees inside the Non-IPM root.

To determine Non-IPM subtree folders and their sizes:

$EmailId = Read-Host "Enter user's email address"
Get-MailboxFolderStatistics -Identity $EmailId -FolderScope NonIpmRoot | Format-Table Identity,ItemsInFolderAndSubfolders,FolderAndSubfolderSize -AutoSize

Thank you for stopping by. ✌

Office 365 – License Reporting using PowerShell – Updated

by

Reporting on O365 licenses is crucial and is necessary to keep track. I’m sure just like me most administrators get asked to generate reports on how the current license state on the tenant. In this post, I have this script I’ve put together just for this purpose.

The O365 portal does provide few options to export the data but this script generates the below reports,

  • O365 license usage
  • All enabled and licensed users report
  • All unlicensed users report
  • All disabled and licensed users report

This report can also be scheduled to run if you already use a mechanism to store your credentials securely and pass it on to your PS scripts.

I use the ImportExcel PowerShell module for this script,

Install-Module -Name ImportExcel

Before proceeding further, you must first connect to your MS online service. To do so, run the cmdlet Connect-MsolService at the Windows PowerShell command prompt. You will then be prompted for your credentials.

$Msolcred = Get-credential
Connect-MsolService -Credential $MsolCred

$xlsxPath = ".\Office365LicenseReport_$((Get-Date).ToString("MMddyyyy")).xlsx"
$LicNames = Get-Content -raw ".\FriendlyLicenseName.txt" | ConvertFrom-StringData

Get-MsolAccountSku | foreach {
            $ActiveUnits = $_.ActiveUnits
            $ConsumedUnits = $_.ConsumedUnits
            $LicenseItem = $_.AccountSkuId -Split ":" | Select-Object -Last 1
            $FriendlyName = $LicNames[$LicenseItem]

            [PSCustomObject]@{
                    'License' = $FriendlyName;
                    'Active Units' = $ActiveUnits;
                    'Consumed Units' = $ConsumedUnits
            }
} | Export-Excel -Path $xlsxPath -WorksheetName "Licensed_State" -TableStyle Medium16 -AutoSize -Append

Get-MsolUser -All -EnabledFilter EnabledOnly | where {$_.IsLicensed -eq $true} | Select-Object -ExpandProperty Licenses DisplayName,UserPrincipalName,Title,Department,UsageLocation | Select DisplayName,UserPrincipalName,Title,Department,UsageLocation,AccountSkuId | foreach {
            $DisplayName = $_.DisplayName
            $UPN = $_.UserPrincipalName
            $JobTitle = $_.Title
            $Department = $_.Department
            $UsageLoc = $_.UsageLocation
            $LicenseItem = $_.AccountSkuId -Split ":" | Select-Object -Last 1
            $FriendlyName = $LicNames[$LicenseItem]

            [PSCustomObject]@{
                    'Display Name' = $DisplayName;
                    'User Principal Name' = $UPN;
                    'License Plans' = $FriendlyName;
                    'JobTitle' = $JobTitle;
                    'Department' = $Department;
                    'Usage Location' = $UsageLoc
            }
}  | Export-Excel -Path $xlsxPath -WorksheetName "Enabled_Licensed_Users" -TableStyle Medium16 -AutoSize -Append

Get-MsolUser -All -UnlicensedUsersOnly | foreach {
            $DisplayName = $_.DisplayName
            $UPN = $_.UserPrincipalName
            $JobTitle = $_.Title
            $Department = $_.Department

            [PSCustomObject]@{
                    'Display Name' = $DisplayName;
                    'User Principal Name' = $UPN;
                    'JobTitle' = $JobTitle;
                    'Department' = $Department
            }
} | Export-Excel -Path $xlsxPath -WorksheetName "UnLicensed_Users" -TableStyle Medium16 -AutoSize -Append

Get-MsolUser -All -EnabledFilter DisabledOnly | where {$_.IsLicensed -eq $true} | Select-Object -ExpandProperty Licenses DisplayName,UserPrincipalName,Title,Department,UsageLocation | Select DisplayName,UserPrincipalName,Title,Department,UsageLocation,AccountSkuId | foreach {
            $DisplayName = $_.DisplayName
            $UPN = $_.UserPrincipalName
            $JobTitle = $_.Title
            $Department = $_.Department
            $UsageLoc = $_.UsageLocation
            $LicenseItem = $_.AccountSkuId -Split ":" | Select-Object -Last 1
            $FriendlyName = $LicNames[$LicenseItem]

            [PSCustomObject]@{
                    'Display Name' = $DisplayName;
                    'User Principal Name' = $UPN;
                    'License Plans' = $FriendlyName;
                    'JobTitle' = $JobTitle;
                    'Department' = $Department;
                    'Usage Location' = $UsageLoc
            }
} | Export-Excel -Path $xlsxPath -WorksheetName "Disabled_Licensed_Users" -TableStyle Medium16 -AutoSize -Append

I used this link to create a file to do a lookup of the ID which is the output from the AccountSkuId and convert it into a friendly name. This list is subject to change but you can download the ‘FriendlyLicenseName.txt‘ from this below link.

FriendlyLicenseNameDownload

Place this file in the same location as you have the PS script or modify script accordingly.

I found it useful that this script’s output can be readily used to generate Pivot tables or charts. If you wish, you can also generate it straight from the script using -IncludePivotTable

Hope this script was helpful in determining the current license state in your O365 environment.

Thank you for stopping by. ✌

Azure AD – Create Security Groups and Add Members using PowerShell

by

Azure AD security groups can be used to manage member and computer access to shared resources for a group of users. A security group can have users, devices, groups and SPNs as its members. In this post, I’ll go over steps on how to create a Azure AD security group, add users to one group and also bulk import users to one group and multiple groups.

Make sure you are connected to Azure AD,

$credential = Get-Credential
Connect-AzureAD -credential $credential

To create an Azure AD security group:

$GroupName = Read-Host "Enter name for Azure AD group"
New-AzureADGroup -DisplayName $GroupName -MailEnabled $false -SecurityEnabled $true -MailNickName "NotSet"

To create multiple Azure AD security groups:

Note: csv file has name,description as column headers

$groups = import-csv "C:\tmp\AzureAD Groups\groups.csv"

Foreach($group in $groups) {
New-AzureADGroup -DisplayName $group.name -Description $group.description -MailEnabled $false -SecurityEnabled $true -MailNickName "NotSet"
}

To add an user and an owner to an existing group:

$Group = Read-Host "Enter name of Azure AD group"
$User = Read-Host "Enter username of user to be added"
$Owner = Read-Host "Enter username of group owner"

$GroupObj = Get-AzureADGroup -SearchString $Group
$UserObj = Get-AzureADUser -SearchString $User
$OwnerObj = Get-AzureADUser -SearchString $Owner

Add-AzureADGroupMember -ObjectId $GroupObj.ObjectId -RefObjectId $UserObj.ObjectId
Add-AzureADGroupOwner -ObjectId $GroupObj.ObjectId -RefObjectId $OwnerObj.ObjectId

To display members of a security group:

$Group = Read-Host "Enter name of Azure AD group to display members"
$GroupObj = Get-AzureADGroup -SearchString $Group
Get-AzureADGroupMember -ObjectId $GroupObj.ObjectId

To display owners of a security group:

$Group = Read-Host "Enter name of Azure AD group to display owner(s)"
$GroupObj = Get-AzureADGroup -SearchString $Group
Get-AzureADGroupOwner -ObjectId $GroupObj.ObjectId

To bulk add multiple users to a specific group:

Note: csv file has UserPrincipalName as column header

$Group = Read-Host "Enter name of Azure AD group to add users"
$GroupObj = Get-AzureADGroup -SearchString $Group
$Members = Import-csv "C:\tmp\GroupMembers.csv"
Foreach($Member in $Members) {

$User = $Member.UserPrincipalName
Write-Progress -Activity "Adding user.." -Status $User

Try {
$UserObj = Get-AzureADUser -SearchString $User
Add-AzureADGroupMember -ObjectId $GroupObj.ObjectId -RefObjectId $UserObj.ObjectId
}
catch {
Write-Host "Error occured while adding $User" -ForegroundColor Red
}
}

To bulk add multiple users to multiple groups. This below script checks to see if the user is already part of the Azure AD group and returns an error.

Note: csv file has UserPrincipalName,Group as column headers

$list = Import-csv "C:\tmp\UsersGroups.csv"
Foreach($entry in $list) {

$User = $entry.UserPrincipalName
$Group = $entry.Group
$UserObj = Get-AzureADUser -SearchString $User
$GroupObj = Get-AzureADGroup -SearchString $Group
$GroupMembers = (Get-AzureADGroupMember -ObjectId $GroupObj.ObjectId).UserPrincipalName

If ($User -notin $GroupMembers) {
    Add-AzureADGroupMember -ObjectId $GroupObj.ObjectId -RefObjectId $UserObj.ObjectId
    Write-Host "$User added to $Group" -ForegroundColor Green
    }
    else {
    Write-Host "$User already in $Group" -ForegroundColor Red
    }
}

Hope this was helpful for you in exploring Azure AD security groups.

Thank you for stopping by. ✌

Azure AD – Bulk Add Guest users using PowerShell

by

With Azure AD B2B collaboration, we can invite guest users into our tenant to allow them to access M365 apps and other Azure AD SSO integrated apps. There are multiple ways to invite external users into our Azure AD tenant. In this post, I’ll go through what I worked on recently to invite a bunch of external users in to one of the tenants I manage.

Make sure you are connected to Azure AD,

$credential = Get-Credential
Connect-AzureAD -credential $credential

To send an invite to an external user:

$GuestName = Read-Host "Enter name of user being invited"
$GuestEmail = Read-Host "Enter email address of user being invited"
New-AzureADMSInvitation -InvitedUserDisplayName $GuestName -InvitedUserEmailAddress $GuestEmail -SendInvitationMessage $True -InviteRedirectUrl "http://myapps.microsoft.com"

To bulk invite users from a csv file, this below script will work. This script checks to make sure if the user is already invited and also includes a custom message you can include in the invite.

How the csv file looks like,

$GuestUsers = Import-csv "C:\tmp\InviteGuestUsers\GuestUsers.csv"

$invitationBody = @"
Hi,

Please accept this invitation to join our organization.
If you have any questions, please contact our helpdesk at
support@internaldomain.com

"@

$invitemessage = @{customizedmessagebody = $invitationBody}

ForEach($GuestUser in $GuestUsers) {

$GuestUserEmail = $GuestUser.GuestEmail
$GuestUserName = $GuestUser.GuestName

    if ((Get-AzureADUser -SearchString $GuestUserEmail).Length -le 0) {

        $Invite = New-AzureADMSInvitation -InvitedUserDisplayName $GuestUserName -InvitedUserEmailAddress $GuestUserEmail -SendInvitationMessage $True -InvitedUserMessageInfo $invitemessage -InviteRedirectUrl "http://myapps.microsoft.com"
        Write-Host "Guest user $GuestUserName invited" -ForegroundColor Green
        } else {
        $status = Get-AzureADUser -filter "Mail eq '$GuestUserEmail'" | Select -Expandproperty UserState
        Write-Host "Guest user $GuestUserName already invited - Guest account exists in tenant and invite status is $status" -ForegroundColor Red
        }
}

To list all guest users in the tenant and their invitation acceptance status:

Get-AzureADUser -filter "UserType eq 'Guest'" | Select DisplayName,UserPrincipalName,UserState

or a filter can be applied to determine a specific user,

$guestuser = Read-Host "Enter the guest user email address to check invite status"
Get-AzureADUser -filter "Mail eq '$guestuser'" | Select DisplayName,UserPrincipalName,UserState

Hope this post helped you in external user invites.

Thank you for stopping by. ✌

O365 – Determine Licensed users

by

Who are the licensed users in our tenant and what licenses are assigned to them? This question comes up way too often in several scenarios and there are a few methods to determine this. I will go over those in this post. I’ve updated this post with some newer information about exporting from the admin portal when I learned them.

Using PowerShell

There are two versions of the PowerShell module that you can use to connect to Microsoft 365 and administer user accounts, groups, and licenses:

  • Microsoft Azure AD Module for Windows PowerShell, whose cmdlets include Msol in their name
  • Azure AD PowerShell for Graph, whose cmdlets include AzureAD in their name

Please make sure you have the MSOnline Module for PowerShell installed and loaded

The Get-MsolUser is a powerful cmdlet which provides a lot of details and I’m going to use it for determining the user’s license.

To connect to the service,

$credential = Get-Credential -credential "adminuser@tenant.onmicrosoft.com"
Connect-MsolService -credential $credential

To get all (licensed and unlicensed) users,

Get-MsolUser -All
Get-MsolUser output

To list only licensed users,

Get-MsolUser -All | Where {$_.isLicensed -eq $true}

To list unlicensed users,

Get-MsolUser -All -UnlicensedUsersOnly

To export all users to a csv with their user name, license status and license assigned,

Get-MsolUser -All | Where {$_.isLicensed -eq $true} | Select Displayname,userprincipalname,islicensed,{$_.Licenses.AccountSkuId} | Export-csv "C:\tmp\userlist.csv" -NoTypeInformation

The exported csv will look like this,

csv data

Using the O365 admin portal

Microsoft has enabled exporting licensing information from the portal.

  1. Login to O365 admin center
  2. Users –> Active Users
  3. Click Export Users
  4. Click Continue
admin portal licensing information

This is how the csv output looks like,

csv export from admin center

Thank you for stopping by. ✌