Is your Hybrid Exchange Server SAFE or unknowingly EXPOSED?

Hello and happy 2022! 🥳 I hope the year has started off as well as it possibly can for you (aside from the holiday ending a bit too early of course!)… 🍺😭 …right back to work!

Now, is your Exchange environment safe!!?? Really? Is it?

I love this utility called ‘SMTP Diag Tool’. It’s great for seeing whether you can connect directly to port 25 of an Exchange server. In most cases, if you can, you can send unauthenticated email to accepted domains within the organisation, because that’s how email used to work right?

SMTP Diag Tool

Before we had a mail filtering service in front of Exchange, email was sent directly between servers (in fact if Exchange was configured to use SpamHaus and SpamCop lookups, it did a pretty good job itself!). But I digress…

In many cases lately I have found I can connect directly to, and send unchecked email to Exchange servers, most often using ‘mail’ or ‘webmail’ hostnames. This method bypasses MX records, mail filtering services and also tells you probably have port 443 wide open as well. Clients are shocked – bypassing my mail filter – really? Yep! Here are the scenarios:

  1. You have a mail filtering service, but nobody set up the firewall or Exchange rules to restrict inbound email to the services sending IP Addresses (applies to on-premises and 365).
  2. You have a Hybrid Exchange configuration, some or all mailboxes are in the cloud but you still have port 25 and 443 open to your on-premises server.
  3. And a less serious case but a definite gap to plug – you are cloud only or hybrid, but have not configured rules to stop other tenants from emailing you directly (this would need to be a targeted attack, but I’m sure the attackers will automate the creation of that attack in future if not already).

To fix this there are several options; the more configured the better!

  1. The best step to take is restricting incoming traffic at the firewall. If you can’t use ‘Internet databases’ as firewall objects, throw away your firewall and get a suitable FortiGate model.
  2. Use the ‘Microsoft-Azure’ and ‘Microsoft-Outlook’ objects to restrict incoming (and outbound if you can) 25 traffic to Microsoft’s servers (if you are hybrid and using Defender mail filtering) OR by restricting to your mail filtering service’s IP range for port 25 (if you are on-premises, or routing through it).
  3. Use the Exchange Hybrid Agent! Re-run the wizard and choose the Agent option (you may need to install it manually first if running 2010). This allows you to close port 443 inbound entirely, by performing free / busy lookups and mailbox moves through an Azure App Proxy.
  4. For port 443, you can also use an Azure Application Proxy to act as a gateway to your environment if you are not Hybrid and it must be contactable from the internet (IMO Everyone should be using this for internet facing services – then you can close all inbound ports on your firewall and let Microsoft do the work!! (security team = ✅)
  5. If you do have inbound 443, use IIS with the URL Rewrite module to block access to any virtual directories that are not required (you’re not still using ActiveSync I hope!!?).
  6. You can also modify the Default Receive Connector in Exchange only to accept from a filtering service, or 365, but this is harder to maintain and shouldn’t be needed if the firewall is configured correctly.
  7. For the cloud-only and Hybrid scenarios, make sure you have implemented the Exchange rules to ensure only mail using your MX record will be delivered, ensuring it traverses the Mail Filtering protection of Exchange Online: Advanced Office 365 Routing: Locking Down Exchange On-Premises when MX points to Office 365 – Microsoft Tech Community.

I also recommend performing the following tasks to ensure maximum security for your environment, first focusing on Hybrid Exchange seeing as that is the most common scenario.

  • AD Connect – if it’s been around for a while, make sure it is now on a 2016/2019 server with TLS 1.2 enabled. You must have these OS to install the latest version. Also make sure it is configured for Hybrid Exchange while you are there.
  • Hopefully you are on Exchange 2016 (since that is free for Hybrid and 2019 is not), if not plan and get it done ASAP. Don’t use 2 vCPU and 8GB memory for your Exchange Hybrid server, what are we, skinflints? 4 vCPU and 16GB are recommended and should be easily achievable in any environment. If not then I cry for you… 😭😭
  • Use Azure Automation with Server Update Management to automatically patch your on-premises servers, even if you don’t have Azure yet this is worth enabling it for… WSUS – Yuuuuk! 🤮🤮

Until next time – chur chur from Simon!

Loading

Runbook: Sync Shared Mailbox accounts with an Azure AD Group

Hey! I hope you are well.. 🤘 🙂 🤘. This script was a result of the following ponderings:

  • How to monitor and manage the deletion of Blocked (Disabled) and Guest accounts in Azure AD.
  • I have a Dynamic group for ‘Blocked (Disabled) users’, but members include valid Shared Mailbox accounts.
  • What about Guest users… should I just leave them? 🤣

Noooo, I shouldn’t… paying monthly subscriptions it’s important to stay on top of user account maintenance. There are some reports and sorting you can do, and Power BI, Graph etc, but I wanted to script something!! In my usual non-perfect PowerShell way of course, but hey it gets the job done.

The Guest users are easy to group using a Azure AD Dynamic Security group with this Rule Syntax:

(user.userType -eq "Guest") and (user.accountEnabled -eq true)

Sweet! My Blocked (or Disabled) users group is Dynamic as well, using this syntax:

(user.accountEnabled -ne true) and (user.surname -ne "Shared_Mailbox")

I’ve only just added (user.surname -ne “Shared_Mailbox”) – the script sets that attribute when it adds an account to the Shared Mailbox group, so that the accounts are excluded from the Dynamic Bocked Users group. Cool now I can actually review the blocked users knowing my Shared Mailbox accounts are safe!

I could also use Conditional Access policies to increase the security of those accounts!

Here is the script… you need to have an Automation account with credential set up (this can be a Synced AD or cloud account that has ‘Exchange Recipient’ and ‘Group Administrator’ roles assigned. Make sure you have imported the AzureAD and ExchangeOnlineManagement modules into the Automation account, and have created the Azure AD Group (set the group to ‘Assigned’ membership rather than ‘Dynamic’). From Azure AD navigate to Groups, search for your group and click on it. You will be able to copy the Object ID from here:

Enter that for $sharedmailboxgroupid and the ‘Name’ of your Automation account credential as $runbookcredentialname. That’s it – give it a good testing and whack eem into production mate!

(NB – following all relevant change control precedures of course!)

See below the code for how the output looks in the Runbook logs… great for troubleshooting!

See ya! 🍺

# use TLS 1.2
[Net.ServicePointManager]::SecurityProtocol = [Net.SecurityProtocolType]::Tls12

# Sync shared mailbox accounts with an Azure AD group - Simon Burbery - November 2021
# Update variables with the name of your runbook credential and the Azure AD Object ID displayed on the groups overview page in the AAD portal.

# set variables
$runbookcredentialname = 'svc_runbookcredential'
$sharedmailboxgroupid = '12345678-abcd-4321-0987-665544332211'

# get credential for connections
Try { 
    $CredAzure = Get-AutomationPSCredential -Name $runbookcredentialname
}
        Catch {
            Write-Error "Failed to get credential!"
            Exit
        }   
Write-Output "Get automation credential - Success"

# connect Azure AD
Try {
    Connect-AzureAD -Credential $CredAzure | Out-Null
}
        Catch {    
            Write-Error "Failed to connect to Azure AD!"
            Exit
        }
Write-Output "Connect to Azure AD - Success"

# connect EOL
Try {
    Connect-ExchangeOnline -Credential $CredAzure
}
        Catch {    
            Write-Error "Failed to connect to EOL!"
            Exit
        }
Write-Output "Connect to EOL - Success"

# get group name
$groupname = (Get-AzureADGroup -ObjectId $sharedmailboxgroupid).DisplayName

# get all shared mailboxes and group members
Write-Output "Enumerating Shared Mailbox accounts and $groupname membership..."
Try {
    $sharedmailboxaccounts = Get-Mailbox -ResultSize Unlimited | Where-Object { $_.RecipientTypeDetails -eq 'SharedMailbox' } | select ExternalDirectoryObjectID,UserPrincipalName
    $currentgroupmembers = Get-AzureADGroupMember -All $true -ObjectId $sharedmailboxgroupid | select ObjectID,UserPrincipalName
}
        Catch {    
            Write-Error "Failed to enumerate Shared Mailbox accounts or $groupname membership!"
            Exit
        }
Write-Output "Enumerate Shared Mailbox accounts and $groupname membership - Success"

# remove any members that are no longer shared mailboxes
Write-Output "Verify $groupname membership..."
Try {
    foreach ( $groupmember in $currentgroupmembers ) {
        $groupmemberid = $groupmember.ObjectID
        $groupmemberupn = $groupmember.UserPrincipalName
        $checkmember = ( $sharedmailboxaccounts.ExternalDirectoryObjectId -contains $groupmemberid )
            If ( $checkmember -ne 'True' ) {
                Write-Output "Shared Mailbox not found - removing $groupmemberupn from $groupname..."
                Remove-AzureADGroupMember -ObjectId $sharedmailboxgroupid -MemberId $groupmemberid
                Set-AzureADUser -ObjectId $groupmemberid -Surname 'Disabled User'
            }
                Else {
                    Write-Output "Shared Mailbox found - skipping $groupmemberupn"
                }
    }
}
        Catch {    
            Write-Error "Error while removing accounts from group!"
            Exit
        }
Write-Output "Verify $groupname membership - Success"

# add new shared mailbox accounts to Azure AD group
Write-Output "Checking for new Shared Mailboxes..."
Try {
    foreach ( $sharedmailboxaccount in $sharedmailboxaccounts ) {
        $sharedmailboxaccountid = $sharedmailboxaccount.ExternalDirectoryObjectId
        $sharedmailboxaccountupn = $sharedmailboxaccount.UserPrincipalName
        $checkmembersm = ( $currentgroupmembers.ObjectID -contains $sharedmailboxaccountid )
            If ( $checkmembersm -ne 'True' ) {
                Write-Output "New Shared Mailbox - adding $sharedmailboxaccountupn to $groupname..."
                Add-AzureADGroupMember -ObjectId $sharedmailboxgroupid -RefObjectId $sharedmailboxaccountid
                Set-AzureADUser -ObjectId $sharedmailboxaccountid -Surname 'Shared_Mailbox'
            }
                else {
                    Write-Output "Skipping Shared Mailbox $sharedmailboxaccountupn"
                }
    }
}
        Catch {    
            Write-Error "Error while adding accounts to group!"
            Exit
        }
Write-Output "Check for new Shared Mailboxes - Success"

# clean up
Disconnect-ExchangeOnline -Confirm:$false
Disconnect-AzureAD -Confirm:$false

# end

And here is what I really like about using Runbooks – the output from the script is available to go back and look at when failures occur etc. Nice!

Loading

Remotely trigger delta AD Connect sync!

How often do you RDP to the AD Connect server to run a Delta Sync?

Yes I know, quite often right? And that is only once you find out which server it is running on. Especially If you are in new environments a lot or someone moved it since last time… sheesh thanks for telling us Dave!! 😭🤣

This script can be run from any Windows 10, 2016 or later endpoint… it will attempt to get the servername from AD then connect remotely and run a delta sync (we do some checks and have some messaging if things fail).

🙂👍 🙂

NOTE: Update 13/12/21 – when finding the AD Connect server, if you’ve already had more than one and someone hasn’t deleted the old computer account, both names will be returned causing the script to fail. Just run that bit of code first and delete any old accounts from the domain (or just replace the code with the server name)

First thing we do is run the following commands in an elevated PowerShell prompt to add the AD PowerShell module:

Install-PackageProvider Nuget -Force #justbecause

For Windows 10/11:
Add-WindowsCapability –online –Name Rsat.ActiveDirectory.DS-LDS.Tools~~~~0.0.1.0

For Windows 2016/2019:
Install-WindowsFeature RSAT-AD-PowerShell -Confirm:$false

Next, let’s make this easy to run with elevated rights by copying the script text into notepad and saving it into the c:\_scripts folder as “Force AD Connect Sync.ps1”

Then create a “Force AD Connect Sync.cmd” on your desktop with the following in it:

start powershell.exe -ExecutionPolicy Bypass -File "c:\_scripts\Force AD Connect Sync.ps1"

Now we can right-click on the cmd file and click ‘Run as Administrator”. Does the trick and time is life!

To find the server we use a method from easy365manager, and the link for enabling remoting is from faqforge. Thanks peoples!

Easy365manager:
https://www.easy365manager.com/how-to-identify-your-azure-ad-connect-server/

Faqforge:
https://www.faqforge.com/windows/create-powershell-session-remote-computer/

Here is the script – let me know if it worked or if it sucked and how you made it better! Until next time! Cheers, Simon 🍺 …oh and PS – if you want a great rundown on AD Connect, check out Adam’s post:

https://adamtheautomator.com/azure-ad-connect/#Install_Azure_AD_Connect

# force a delta sync to Azure AD

# load AD module
Try {
    Import-Module ActiveDirectory
}
    Catch {
        Write-Warning "Encountered a problem importing AD module."
        Write-Host
        Read-Host "Press Enter to exit..."
        Exit
    }
Write-Host -ForegroundColor Green "AD module loaded successfully."
Write-Host

Try {
    $ADConnectServer = Get-ADUser -LDAPFilter "(description=*configured to synchronize to tenant*)" -Properties description | % { $_.description.SubString(142, $_.description.IndexOf(" ", 142) - 142)}
}
    Catch {
        Write-Warning "Encountered a problem obtaining name of AD Connect server."
        Write-Host
        Read-Host "Press Enter to exit..."
        Exit
    }

Write-Host -ForegroundColor Green "Found AD Connect server $ADConnectServer!  Testing connection..."
Write-Host

Try {
    $session = New-PSSession -ComputerName $ADConnectServer -Authentication Default
    Enter-PSSession $ADConnectServer
}
    Catch {
        Write-Warning "Cannot connect to $ADConnectServer, please check remote connectivity." 
        Write-Warning "ref - https://www.faqforge.com/windows/create-powershell-session-remote-computer/"
        Write-Host
        Read-Host "Press Enter to exit..."
        Exit
    }

Write-Host -ForegroundColor Green "Connected to $ADConnectServer - Forcing a delta sync... one moment!"
Write-Host

Try {
    Start-ADSyncSyncCycle -PolicyType Delta
}
    Catch {
        Write-Warning "The command failed - either a sync is already in progress," 
        Write-Warning "or you are not a member of the 'ADSyncAdmins' group on the AD Connect server."
        Write-Host
        Read-Host "Press Enter to exit..."
        Exit
    }

Write-Host -ForegroundColor Green "Sync started successfully!"
Write-Host
Read-Host "Press Enter to exit..."

# clean up
Exit-PSSession
Remove-PSSession $session

Loading

Remove proxy address for specific domain from Exchange Distribution groups

This script can be run after connecting to Exchange Online or on-premises environment. Replace “porkchops.com” with the suffix you want to remove 👍

Thanks to me mate Sailesh who loooves his porkchops!! 🤣🤣

# Remove proxy address for "porkchops.com" from Exchange Distribution groups

$domainname = "porkchops.com"

$groups = Get-DistributionGroup -Resultsize unlimited | where {$_.EmailAddresses -like "*$domainname*"} 

foreach ($group in $groups) {  

    $groupidentity = $group.identity
    $addresstoremove = $group.Alias+"@$domainname"

    Set-DistributionGroup $groupidentity -EmailAddresses @{remove=$addresstoremove}
}

# End

Loading

How to restrict partner access to Azure and 365

Most customers have accepted at least one, if not multiple invitations from Microsoft partners to provide licensing or support services. What they often dont know is that by default this allows the partner to assign full administrative access to any of it’s staff, to perform tasks in the customers 365 / Azure tenant. It’s an ‘all or nothing’ configuration which is, and should be of concern to many customers who read the fine print of the invitation they are accepting.

The recent hack on a large distributor highlights how dangerous leaving this ‘as is’ can be:

Mega-distie SYNNEX attacked and Microsoft cloud accounts it tends tampered • The Register

Microsoft are developing the Lighthouse solution to allow us to use more detailed permissions for support. But it’s not there yet, so I started testing another solution to ths problem.

Turns out you can do most things using B2B guest access and client targeted URLs (appending the clients custom domain to the admin URL) as below:

Helpdesk staff use:
Azure AD – https://aad.portal.azure.com/customer.com
Exchange – https://outlook.office365.com/ecp/@customer.onmicrosoft.com

Admin staff use:
Azure – https://portal.azure.com/customer.com
Exchange – https://outlook.office365.com/ecp/@customer.onmicrosoft.com
SharePoint – https://customer-admin.sharepoint.com
Intune – https://endpoint.microsoft.com/customer.com  
Security Center – https://securitycenter.windows.com/?tid=customer_tenant_id

Unfortunately this doesn’t seem to apply for the 365 Admin Center (please comment if you found a way to do it!), which is where you would want Helpdesk staff to be performing User / Exchange tasks, rather than jumping between Azure AD and Exchange portals. But, at least it works, and achieves the goal for security conscious customers who are hesitant to accept the partner invitation.

Here is the process:

1. Customer accepts MSP invitation

2. Customer removes the admin / helpdesk agent privileges from the partners area of the customers portal (this keeps the association and you can still procure licensing for them, but removes the default partner permissions)

3. Create two or more groups in customers Azure AD; one for your Helpdesk and one for Admins (with assign roles to group enabled)

4. Assign the roles to the Helpdesk group (change to fit your needs or use custom roles):
User Administrator
Exchange Recipient Administrator

5. Assign roles to the Admin group as required (you can use more admin groups to assign roles to different support groups if required):

Intune Administrator
Authentication Administrator
Exchange Administrator
User Administrator
Guest Inviter
Application Administrator
Compliance Administrator
Global Reader
Conditional Access Administrator
Cloud App Security Administrator
License Administrator
Azure AD Joined Device Local Administrator
Groups Administrator
SharePoint Administrator
Privileged Role Administrator
Azure Information Protection Administrator
Security Administrator

6. Assign Azure subscription roles to the Admin groups as required:
Contributor

7. You can also use these groups to assign permissions to certain Azure objects, using the IAM blade under the resource

8. Use ‘Bulk Invite’ in customers Azure AD users blade to invite all your support staff to the customer tenant as guests

9. Add the invited guest accounts to the groups you created as required

After a support staff member accepts the invitation, they can open the URLs mentioned above using their standard user account to perform tasks in a customer tenant.

Not perfect, but it does work and avoids using generic credentials to perform tasks in a customer tenant.

Loading

Exchange Online – set default Retention Policy if null

# EDIT # I have updated this script due to an issue where multiple mailboxes are matched due to similar names, this line below with $mailboxes variable piped to the Set command uses Display Name for Identity which may not be unique. Script is updated to loop through the mailboxes using UPN for the Set command. Cheers! 🍺

$mailboxes | Set-Mailbox -RetentionPolicy $defaultpolicy.Name

I’ve come across several clients lately who are migrating to or have migrated to Exchange Online, and find some users have no retention policy set. This script can be scheduled in an Azure runbook to find enabled users with no policy and set it to the default policy. Replace ‘svc-runbookcred’ with your runbook credential name. Easily modified to connect to on premise Exchange; if you need any help just add a comment below! 🙂

# use TLS 1.2
[Net.ServicePointManager]::SecurityProtocol = [Net.SecurityProtocolType]::Tls12

# specify runbook credential name
$runbookcredential = 'svc-runbookcred'

# get credential for eol connection
Try { 
    $CredAzure = Get-AutomationPSCredential -Name $runbookcredential
}
        Catch {
            Write-Error "Failed to get credential!"
            Exit
        }   
Write-Output "Get automation credential - Success"

# connect eol
Try {
    Connect-ExchangeOnline -Credential $CredAzure
}
        Catch {    
            Write-Error "Failed to connect to MSOnline!"
            Exit
        }
Write-Output "Connect to EOL - Success"

# get default policy from org settings
Try {
    $defaultpolicy = Get-RetentionPolicy | Where-Object { $_.IsDefault -eq $true }
}
        Catch {    
            Write-Error "Failed to get default policy!"
            Exit
        }
Write-Output "Get default policy - Success"

# find enabled mailboxes with no policy set
Try {
    $mailboxes = Get-Mailbox -ResultSize Unlimited -Filter { ( RecipientTypeDetails -eq 'UserMailbox' ) -and ( ExchangeUserAccountControl -ne 'AccountDisabled') } | Where-Object { $_.RetentionPolicy -eq $null }
}
        Catch {    
            Write-Error "Failed to get mailboxes!"
            Exit
        }
Write-Output "Get mailboxes - Success"

# set to default policy
Try {
    foreach ($mailbox in $mailboxes) {
        Set-Mailbox -Identity $mailbox.UserPrincipalName -RetentionPolicy $defaultpolicy.Name
    }
}
        Catch {    
            Write-Error "Failed to set policy!"
            Exit
        }
Write-Output "Set default policy - Success"

# end

Loading

Azure Runbook – enable Exchange Online Litigation Hold

This script will connect to Exchange Online and enable litigation hold for all enabled users. Errors due to not having the appropriate license are ignored. Litigation hold can be enabled for users licensed with Business Premium, EOL Plan 2 or the Mailbox Archive add-on. Replace ‘svc-runbookcred’ with your runbook credential name. You can schedule to run nightly to pick up new users as they are added. If you like the script, made it cooler or need some help, please add a comment below! 🙂

# use TLS 1.2
[Net.ServicePointManager]::SecurityProtocol = [Net.SecurityProtocolType]::Tls12

# specify runbook credential name
$runbookcredential = 'svc-runbookcred'

# get credential for eol connection
Try { 
    $CredAzure = Get-AutomationPSCredential -Name $runbookcredential

}
        Catch {
            Write-Error "Failed to get credential!"
            Exit
        }   
Write-Output "Get automation credential - Success"

# connect eol
Try {
    Connect-ExchangeOnline -Credential $CredAzure
}
        Catch {    
            Write-Error "Failed to connect to MSOnline!"
            Exit
        }
Write-Output "Connect to EOL - Success"

# get user mailboxes
Try {
    $mailboxes = Get-Mailbox -ResultSize Unlimited -Filter { ( RecipientTypeDetails -eq 'UserMailbox' ) -and ( ExchangeUserAccountControl -ne 'AccountDisabled') } | Where-Object {$_.LitigationHoldEnabled -ne $true}
}
        Catch {    
            Write-Error "Failed to get user mailboxes!"
            Exit
        }
Write-Output "Get user mailboxes - Success"

# enable litigation hold
Try {
    $mailboxes | Set-Mailbox -LitigationHoldEnabled $true -ErrorAction Ignore
}
        Catch {    
            Write-Error "Failed to enable litigation hold!"
            Exit
        }
Write-Output "Enable litigation hold - Success"

Loading

Azure Runbook – Licensing Alert

I created this script for a client that wanted to know when they had no available licenses for any SKU.  I’m sure they will add this to the portal soon (?)

The goal is simple – if my consumed no. of licenses = available licenses for any given SKU, send an email to me and my CSP so I can replenish before it becomes a problem.  Easily modified to alert at any number of remaining available licenses. e.g. to alert when there are 5 available licenses change ($_.ConsumedUnits -eq $_.ActiveUnits) to ($_.ConsumedUnits -eq $_.ActiveUnits-5).

The script is written to run as an Azure PowerShell Runbook, which allows use of a credential stored in the automation account, as well as using output to have some nice text show up in the portal logs.  I’m assuming you have set this stuff up already (if you haven’t, google it and get it sorted =). I’ll do a post soon on how to do it but it is not too difficult.

Azure blocks outbound connections on port 25, so no going there!  But aha, they do allow secure port 587.  So I use a ‘soon to be’ deprecated command called send-mailmessage to send the email using a free SendGrid account (no cost for 100 emails per month) which is plenty enough for this solution.

Disclaimer ## as I was testing the script, I noticed Azure now has a ‘SendGrid solution’ where you can sign up to SendGrid free from within the Azure portal – awesome!  Shame I missed it… if I update to using that method I will update this post =).  My understanding is that you could sign up for that, then use it by calling a ‘playbook’ from the automation script.

Here is the script (replace $smtppswd with your sendgrid API key SG.xxxxxx, replace $runbookcredentialname with your runbook cred name, replace $mailfrom and $mailto).  Also, since there may be 0 available licenses or 1000 freely available licenses, by default I’m only considering available license values >1 <500.  Change to suit your needs!

If you have any problems or made the script cooler (like sending the info in an HTML table) please add a comment below! 🙂

# use TLS 1.2
[Net.ServicePointManager]::SecurityProtocol = [Net.SecurityProtocolType]::Tls12

# check for any licenses out of stock and send a notification - Simon Burbery - August 2021

# create credential for sending email via SendGrid
$smtpuser = 'apikey'
$smtppswd = ConvertTo-SecureString -String 'SG.xxxxxxx' -AsPlainText -Force
$CredSMTP = New-Object -TypeName System.Management.Automation.PSCredential -ArgumentList $SMTPuser, $SMTPpswd

# set variables
$runbookcredentialname = 'svc_runbookaccount'
$mailfrom = 'Azure License Notifcation <sendmail@place.co.nz>'
$mailto = @("<admin@place.co.nz>", "<azurealerts@place.co.nz>")
$mailsubject = 'Warning - out of licenses!'
$mailbody = 'Availability of one or more of your license SKUs has reached zero:'
$mailserver = 'smtp.sendgrid.net'
$mailport = '587'
$mailcredential = $CredSMTP

# get credential for msol connection
Try { 
    $CredAzure = Get-AutomationPSCredential -Name $runbookcredentialname
}
        Catch {
            Write-Error "Failed to get credential!"
            Exit
        }   
Write-Output "Get automation credential - Success"

# connect msol
Try {
    Connect-MsolService -Credential $CredAzure
}
        Catch {    
            Write-Error "Failed to connect to MSOnline - check credential!"
            Exit
        }
Write-Output "Connect to MSOL - Success"

# license check
$skucheck = Get-MsolAccountSku  | Where-Object { ($_.ActiveUnits -gt 0) -and ($_.ActiveUnits -lt 500) -and ($_.ConsumedUnits -eq $_.ActiveUnits) }

# email body format
$mailbodyfinal = $mailbody,$skucheck | Out-String -Width 500

# send notification
If ( $skucheck -ne $null ) {
    $MailParameters = @{
        From = $mailfrom
        To = $mailto
        Subject = $mailsubject
        Body = $mailbodyfinal
        SmtpServer = $mailserver
        Port = $mailport
        Credential = $CredSMTP
        UseSsl = $true
        }
        Send-MailMessage @MailParameters
            If  ($? -ne $true) { 
    Write-Error "Failed to send email notification!" 
    }
                Else {
                Write-Output "Send email notification - Success"
            }       
}
    Else {
        Write-Output "No licensing issues detected"
    }

# end

Loading

Azure AD – export groups and members to CSV

UPDATE Feb ’23 – David made me do it – well, he didn’t make me at all really, but I did it anyway 🙂. Check out this new post which uses AzAD and AzureAD cmdlets to get the groups and members email, UPN and ObjectID (catering for different member types and groups with no members):

Azure AD – export groups and members #2

UPDATE June ’22 – for on-premises AD check out Active Directory – export groups and members (with email addresses).

# export azure ad groups and members to csv (also output empty groups with 'No Members' value) 
# assumes existing connection to Azure AD using Connect-AzureAD (or use a runbook)

$allgroups = Get-AzureADGroup -All $true | select ObjectId,DisplayName

$result = foreach ( $group in $allgroups ) {

    $hash = @{GroupName=$group.DisplayName;Member=''}
    $groupid = $group.ObjectId
    
    if ( $members = Get-AzureADGroupMember -ObjectId $groupid ) {
            
            foreach ( $member in $members ) {

                $hash.Member = $member.DisplayName
                New-Object psObject -Property $hash
            }
            }
    else
        {
        $displayname = "No Members"
        $hash.Member = $displayname
        New-Object psObject -Property $hash
        }
}

$result | Export-Csv -Path C:\temp\AzureADGroups.csv -NoTypeInformation

# End

PowerShell get azure ad group members export to csv

export azure ad group members to csv PowerShell

PowerShell export azure ad user group membership to csv

Loading

Export Active Directory groups and members to CSV file

UPDATE 2022-04-02 – if you would like email addresses with the output, check out my new post at: https://www.howdoiuseacomputer.com/index.php/2022/04/02/export-active-directory-groups-and-members-to-a-csv-file-with-email-addresses/

# export active directory groups and members to csv (also output empty groups with 'No Members' value)
# assumes run on domain controller or import of ActiveDirectory module

$allgroups = Get-ADGroup -Filter *

$result = foreach ( $group in $allgroups ) {

    $hash = @{GroupName=$group.SamAccountName;Member=''}
    $groupid = $group.distinguishedname
    
    if ( $members = Get-ADGroupMember $groupid ) {
            
            foreach ( $member in $members ) {

                $hash.Member = $member.Name
                New-Object psObject -Property $hash
            }
    }
    else {
        $displayname = "No Members"
        $hash.Member = $displayname
        New-Object psObject -Property $hash
    }
}

$result | Export-Csv -Path C:\temp\ActiveDirectoryGroupsAndMembers.csv -NoTypeInformation

# End

Loading