Email people via Office 365 from PowerShell when passwords about to expire

I have a demonstration environment where many users have accounts but they never logon to AD directly nor look at this demonstration email mailbox. They only use the environment via Azure AD where they logon at Azure AD via the replicated password hash. Because of this they don’t get password expiry notifications and continue to logon however if they try and access something that does hook into AD and not Azure AD the logon fails.

They wanted to be emailed of upcoming password expiry to their real-email. To accomplish this their real email was stored in extensionAttribute10. I didn’t use the proxyaddresses as this may have SIP information. This attribute could be easily set with:

$aduser | Set-Aduser -Replace @{extensionAttribute10=$AltEmail}

1	$aduser \| Set-Aduser -Replace @{extensionAttribute10=$AltEmail}

I had a mailbox for a core process I use. Now that user has no other rights so I placed the password in the script but that’s not ideal at all. If this was Azure Automation I could have used a credential object, I could have at least made the password harder to read by creating an encrypted version of the password and then storing that in the file (but its still reversible, just slightly harder to glance at!), e.g.

ConvertFrom-SecureString (ConvertTo-SecureString -AsPlainText -Force 'Password123') #this is run once to generate the value

$securepassword = ConvertTo-SecureString "<the huge value from previous command on one line>" #this is then in script going forward
$cred = new-object System.Management.Automation.PSCredential ("john@savilltech.com", $securepassword)

ConvertFrom-SecureString (ConvertTo-SecureString -AsPlainText -Force 'Password123') #this is run once to generate the value

$securepassword = ConvertTo-SecureString "<the huge value from previous command on one line>" #this is then in script going forward

$cred = new-object System.Management.Automation.PSCredential ("john@savilltech.com", $securepassword)

However the account can’t do anything except email and access to the script location was highly restricted so I left it as text which was also easier to demonstrate below however in my environment I used the alternate approach above just to make it a little harder to get the password on glance :-). Replace this with your own email and password.

The script looks for any password expiring in less than 10 days and emails a simple message. Customize as you like! It has a basic HTML block with a placeholder (MESSAGEHOLDER) that is replaced by a custom string for the user.

$CurrentDate = Get-Date
$Users = Get-ADUser -filter {Enabled -eq $True -and PasswordNeverExpires -eq $False} -Properties msDS-UserPasswordExpiryTimeComputed, PasswordLastSet, CannotChangePassword, extensionAttribute10

$fromEmail = 'fromuser@domain.com'
$fromPass = 'password'
$securePassword = ConvertTo-SecureString $fromPass -AsPlainText -Force
$emailcred = New-Object System.Management.Automation.PSCredential ($fromEmail, $securePassword)  
$SMTPServer = 'smtp.office365.com'
$SMTPPort = '587'

$PasswordNotifyDays = 10

$emailBody = @"
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta content="en-us" http-equiv="Content-Language" />
<meta content="text/html; charset=utf-8" http-equiv="Content-Type" />
</head>
<body>
<p>MESSAGEHOLDER</p>
<p>Please change password at <a href="https://myapps.microsoft.com">https://myapps.microsoft.com</a>.</p>
</body>
</html>
"@

foreach($user in $users)
{
    $userUPN = $user.DistinguishedName
    $userName = $user.Name

        $PasswordLastChanged = $user.PasswordLastSet
        $PasswordExpired = [datetime]::FromFileTime($user."msDS-UserPasswordExpiryTimeComputed")
        
        $PasswordTillExpired = ($PasswordExpired - $CurrentDate).Days
        if($PasswordTillExpired -lt $PasswordNotifyDays)
        {
            if($PasswordTillExpired -lt 0)
            {
                $message = "User $userName ($($user.UserPrincipalName)) password is expired ($PasswordTillExpired days past change time). Last changed $PasswordLastChanged"
            }
            else
            {
                $message = "User $userName ($($user.UserPrincipalName)) password expires in $PasswordTillExpired days. Last changed $PasswordLastChanged"
            }
            Write-Output $message
            $altemail = $user.extensionAttribute10
            if($altemail -ne $null)
            {
                write-output "   Alternate email found and message being sent to $altemail"
                Send-MailMessage -To $altemail -From $fromEmail -Subject "Password Expiry Notification - $($user.UserPrincipalName)" -BodyAsHtml $emailBody.Replace("MESSAGEHOLDER",$message) `
                    -SmtpServer $SMTPServer -Port $SMTPPort -UseSsl -Credential $emailcred
            }
        }
}

$CurrentDate = Get-Date

$Users = Get-ADUser -filter {Enabled -eq $True -and PasswordNeverExpires -eq $False} -Properties msDS-UserPasswordExpiryTimeComputed, PasswordLastSet, CannotChangePassword, extensionAttribute10

$fromEmail = 'fromuser@domain.com'

$fromPass = 'password'

$securePassword = ConvertTo-SecureString $fromPass -AsPlainText -Force

$emailcred = New-Object System.Management.Automation.PSCredential ($fromEmail, $securePassword)

$SMTPServer = 'smtp.office365.com'

$SMTPPort = '587'

$PasswordNotifyDays = 10

$emailBody = @"

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<head>

</head>

<body>

<p>MESSAGEHOLDER</p>

<p>Please change password at <a href="https://myapps.microsoft.com">https://myapps.microsoft.com</a>.</p>

</body>

</html>

foreach($user in $users)

{

$userUPN = $user.DistinguishedName

$userName = $user.Name

$PasswordLastChanged = $user.PasswordLastSet

$PasswordExpired = [datetime]::FromFileTime($user."msDS-UserPasswordExpiryTimeComputed")

$PasswordTillExpired = ($PasswordExpired - $CurrentDate).Days

if($PasswordTillExpired -lt $PasswordNotifyDays)

{

if($PasswordTillExpired -lt 0)

{

$message = "User $userName ($($user.UserPrincipalName)) password is expired ($PasswordTillExpired days past change time). Last changed $PasswordLastChanged"

}

else

{

$message = "User $userName ($($user.UserPrincipalName)) password expires in $PasswordTillExpired days. Last changed $PasswordLastChanged"

}

Write-Output $message

$altemail = $user.extensionAttribute10

if($altemail -ne $null)

{

write-output " Alternate email found and message being sent to $altemail"

Send-MailMessage -To $altemail -From $fromEmail -Subject "Password Expiry Notification - $($user.UserPrincipalName)" -BodyAsHtml $emailBody.Replace("MESSAGEHOLDER",$message) `

-SmtpServer $SMTPServer -Port $SMTPPort -UseSsl -Credential $emailcred

}

Have fun!

Add group members to another tenant via Azure AD B2B and PowerShell

I needed to add members of a number of groups from one Azure AD tenant to a group in another Azure AD tenant that would then be given access to a resource. The goal was to not require the users added to have to redeem the invite which is common when adding a B2B user. To do this the first step was a user invited via B2B the normal way, that user redeemed the invite and in this case was then made a global admin (although another option would have been to enable guests to invite guests). The key point was this user had the ability to invite people via B2B and could enumerate users in the invited Azure AD instance which would mean invites would not have to be redeemed.

My first version of the script was very simply however I soon realized I would have to rerun the script to add new users and so I enhanced it to extract the current members of the group, convert to regular email format (since when invite to Azure AD the users have @ replaced with _ and is put in a string with various components separated by a #). The script therefore extracts the first part and converts the _ back to a @. Then looks for only for people who are not already members.

In the script below replace the group names, Azure AD names and IDs to meet your requirements.

Import-Module AzureAD

$OneMTCTenantName = "sourceten.net"
$OneMTCTenantID = "ID1"
$OPCTenantName = "targetten.net"
$OPCTenantID = "ID2"

$AADGroupsToEnumerate = "SourceGroup1","SourceGroup2","SourceGroup3"
$AllMTCTeam = @()
$MTCTeamGroupName = "TargetGroup"

Connect-AzureAD -TenantId $OneMTCTenantID

foreach($AADGroupName in $AADGroupsToEnumerate)
{
    $AADGroup =Get-AzureADGroup -Filter "DisplayName eq '$AADGroupName'"
    $AADPeople = Get-AzureADGroupMember -ObjectId $AADGroup.ObjectId -All $true
    $AllMTCTeam += $AADPeople
}

Connect-AzureAD -TenantId $OPCTenantID

#$OPCOneMTCGroup = New-AzureADGroup -DisplayName $MTCTeamGroupName -Description "All members of the global OneMTC organization" `
#    -SecurityEnabled $true -MailEnabled $false -MailNickName "$MTCTeamGroupName"

$OPCOneMTCGroup = Get-AzureADGroup -SearchString "$MTCTeamGroupName" 

$OPCUsers = Get-AzureADUser -Filter "userType eq 'Guest'" -All $true | Select-Object -ExpandProperty UserPrincipalName
$OPCCleanUsers = @()
foreach($OPCUser in $OPCUsers)
{
    #Clean up to a real email address
    $tempemail = ($OPCUser.Split("#"))[0].Replace('_','@')
    #Write-Output "Converting $OPCUser to $tempemail"
    $OPCCleanUsers += $tempemail
}

foreach($MTCTeamMember in $AllMTCTeam)
{
    #Check if already a guest
    if($OPCCleanUsers -Contains $MTCTeamMember.UserPrincipalName)
    {
        Write-Output "User $($MTCTeamMember.UserPrincipalName) already added"
    }
    else
    {
        Write-Output "Adding $($MTCTeamMember.UserPrincipalName)"
        $AADB2BAdd = New-AzureADMSInvitation -InvitedUserEmailAddress "$($MTCTeamMember.UserPrincipalName)" -SendInvitationMessage $false `
            -InviteRedirectUrl "http://myapps.microsoft.com"
        Add-AzureADGroupMember -ObjectId $OPCOneMTCGroup.ObjectId -RefObjectId $AADB2BAdd.InvitedUser.Id
    }
}

Import-Module AzureAD

$OneMTCTenantName = "sourceten.net"

$OneMTCTenantID = "ID1"

$OPCTenantName = "targetten.net"

$OPCTenantID = "ID2"

$AADGroupsToEnumerate = "SourceGroup1","SourceGroup2","SourceGroup3"

$AllMTCTeam = @()

$MTCTeamGroupName = "TargetGroup"

Connect-AzureAD -TenantId $OneMTCTenantID

foreach($AADGroupName in $AADGroupsToEnumerate)

{

$AADGroup =Get-AzureADGroup -Filter "DisplayName eq '$AADGroupName'"

$AADPeople = Get-AzureADGroupMember -ObjectId $AADGroup.ObjectId -All $true

$AllMTCTeam += $AADPeople

}

Connect-AzureAD -TenantId $OPCTenantID

#$OPCOneMTCGroup = New-AzureADGroup -DisplayName $MTCTeamGroupName -Description "All members of the global OneMTC organization" `

# -SecurityEnabled $true -MailEnabled $false -MailNickName "$MTCTeamGroupName"

$OPCOneMTCGroup = Get-AzureADGroup -SearchString "$MTCTeamGroupName"

$OPCUsers = Get-AzureADUser -Filter "userType eq 'Guest'" -All $true | Select-Object -ExpandProperty UserPrincipalName

$OPCCleanUsers = @()

foreach($OPCUser in $OPCUsers)

{

#Clean up to a real email address

$tempemail = ($OPCUser.Split("#"))[0].Replace('_','@')

#Write-Output "Converting $OPCUser to $tempemail"

$OPCCleanUsers += $tempemail

}

foreach($MTCTeamMember in $AllMTCTeam)

{

#Check if already a guest

if($OPCCleanUsers -Contains $MTCTeamMember.UserPrincipalName)

{

Write-Output "User $($MTCTeamMember.UserPrincipalName) already added"

}

else

{

Write-Output "Adding $($MTCTeamMember.UserPrincipalName)"

$AADB2BAdd = New-AzureADMSInvitation -InvitedUserEmailAddress "$($MTCTeamMember.UserPrincipalName)" -SendInvitationMessage $false `

-InviteRedirectUrl "http://myapps.microsoft.com"

Add-AzureADGroupMember -ObjectId $OPCOneMTCGroup.ObjectId -RefObjectId $AADB2BAdd.InvitedUser.Id

}

Create AD sites through PowerShell

I recently needed to create an AD site for each MTC (an office), add the IP range assigned to that MTC (which was in a CSV file) and then associate the site with a site link for its region. This is so the Active Directory automatic site coverage feature will enable DCs to populate per-site DNS records for the MTCs ensuring authentication traffic uses the most optimal DC. The DCs are spread over four regional locations.

The CSV file simply had one or two second octet numbers for the /16 IP range associated with the MTC. The code therefore enumerates through each OU, checks to see if the MTC can be found in the CSV data for the IP ranges. Next if the site does not already exist it is created, added to its regional site link (based on the parent OU name and for NA if its East or West) and then the IP ranges for the MTC assigned.

$MTCIPConfigFile = "F:\PowerShell\basicmtcips.csv"
$MTCIPInfo = Import-Csv -Path $MTCIPConfigFile 

$RootDomain = "DC=onemtc,DC=net"
$TopLevelRegions = "APAC","EMEA","NA"

foreach($TopLevelRegion in $TopLevelRegions)
{
    $MTCOUs = Get-ADOrganizationalUnit -filter * -searchbase "OU=$TopLevelRegion,$RootDomain" -SearchScope OneLevel -Properties Name, Description  
    foreach($MTCOU in $MTCOUs)
    {
        $MTCCode = $MTCOU.Name
        $MTCCodeShort = $MTCOU.Name.Substring(3)
        #Find the MTC in the IP list
        $MTCIPrange = $null
        $MTCLocation = $null
        foreach($MTCInfo in $MTCIPInfo)
        {
            if($MTCInfo.Code -eq $MTCCode)
            {
                if($MTCInfo.LocalOctet -eq $MTCInfo.SharedOctet)
                {
                    $MTCIPrange = $MTCInfo.LocalOctet
                }
                else
                {
                    $MTCIPrange = $MTCInfo.LocalOctet,$MTCInfo.SharedOctet
                }
                $MTCLocation = $MTCInfo.Location
            }
        }
        Write-Output "MTC $MTCCodeShort processing in region $TopLevelRegion"
        if($MTCIPrange -ne $null)
        {
            $MTCADSiteName = "$TopLevelRegion$MTCCodeShort"
            $MTCADSiteSearch = $null
            try
            {
                $MTCADSiteSearch = Get-ADReplicationSite -Filter {Name -eq $MTCADSiteName}
            }
            catch {}

            if($MTCADSiteSearch -eq $null)
            {
                Write-Output "Site not found, creating"
                New-ADReplicationSite $MTCADSiteName

                #Add to site link
                switch ($TopLevelRegion)
                {
                    'NA' {if($MTCLocation -eq "W") {$siteLinkName = "NAMTCs-AzureWestUS"} else {$siteLinkName = "NAMTCs-AzureEastUS"}}
                    'APAC' {$siteLinkName = "APACMTCs-AzureSoutheastAsia"}
                    'EMEA' {$siteLinkName = "EMEAMTCs-AzureWestEurope"}
                    Default {$siteLinkName = "NAMTCs-AzureEastUS"}
                }
                Write-Output "Adding to site link $siteLinkName"
                Set-ADReplicationSiteLink $siteLinkName -SitesIncluded @{Add="$MTCADSiteName"}

                foreach($IPrange in $MTCIPrange)
                {
                    $IPCIDR = "10.$IPrange.0.0/16"
                    Write-Output "Assigning $IPCIDR to new site $MTCADSiteName"
                    New-ADReplicationSubnet -Name $IPCIDR -Site $MTCADSiteName -Location $TopLevelRegion
                }
            }
            else
            {
                Write-Output "Site exists"
            }
        }
        else
        {
            Write-Output "No IP configuration found, skipping"
        }
        Write-Output ""
    }
}

$MTCIPConfigFile = "F:\PowerShell\basicmtcips.csv"

$MTCIPInfo = Import-Csv -Path $MTCIPConfigFile

$RootDomain = "DC=onemtc,DC=net"

$TopLevelRegions = "APAC","EMEA","NA"

foreach($TopLevelRegion in $TopLevelRegions)

{

$MTCOUs = Get-ADOrganizationalUnit -filter * -searchbase "OU=$TopLevelRegion,$RootDomain" -SearchScope OneLevel -Properties Name, Description

foreach($MTCOU in $MTCOUs)

{

$MTCCode = $MTCOU.Name

$MTCCodeShort = $MTCOU.Name.Substring(3)

#Find the MTC in the IP list

$MTCIPrange = $null

$MTCLocation = $null

foreach($MTCInfo in $MTCIPInfo)

{

if($MTCInfo.Code -eq $MTCCode)

{

if($MTCInfo.LocalOctet -eq $MTCInfo.SharedOctet)

{

$MTCIPrange = $MTCInfo.LocalOctet

}

else

{

$MTCIPrange = $MTCInfo.LocalOctet,$MTCInfo.SharedOctet

}

$MTCLocation = $MTCInfo.Location

}

Write-Output "MTC $MTCCodeShort processing in region $TopLevelRegion"

if($MTCIPrange -ne $null)

{

$MTCADSiteName = "$TopLevelRegion$MTCCodeShort"

$MTCADSiteSearch = $null

try

{

$MTCADSiteSearch = Get-ADReplicationSite -Filter {Name -eq $MTCADSiteName}

}

catch {}

if($MTCADSiteSearch -eq $null)

{

Write-Output "Site not found, creating"

New-ADReplicationSite $MTCADSiteName

#Add to site link

switch ($TopLevelRegion)

{

'NA' {if($MTCLocation -eq "W") {$siteLinkName = "NAMTCs-AzureWestUS"} else {$siteLinkName = "NAMTCs-AzureEastUS"}}

'APAC' {$siteLinkName = "APACMTCs-AzureSoutheastAsia"}

'EMEA' {$siteLinkName = "EMEAMTCs-AzureWestEurope"}

Default {$siteLinkName = "NAMTCs-AzureEastUS"}

}

Write-Output "Adding to site link $siteLinkName"

Set-ADReplicationSiteLink $siteLinkName -SitesIncluded @{Add="$MTCADSiteName"}

foreach($IPrange in $MTCIPrange)

{

$IPCIDR = "10.$IPrange.0.0/16"

Write-Output "Assigning $IPCIDR to new site $MTCADSiteName"

New-ADReplicationSubnet -Name $IPCIDR -Site $MTCADSiteName -Location $TopLevelRegion

}

else

{

Write-Output "Site exists"

}

else

{

Write-Output "No IP configuration found, skipping"

}

Write-Output ""

}

Bulk created group policy objects with PowerShell

A lot of the work I do around Active Directory and Azure AD is for our OneMTC.net environment used by our global Microsoft Technology Centers. It is built around a number of region-based organizational units which then have child OUs for each MTC.

The requirement was to create a number of GPOs for each MTC which could then be modified by the local administrator of the MTC. To do this I created two template GPOs with most of the basic settings which I then just needed to copy to a new, per-MTC GPO instance then link to the GPO. This was very easy with PowerShell and the GroupPolicy module.

I also had already created the GPOs for a couple of MTCs so wanted to skip creating the objects for them. In the PowerShell below you can see I have a variable for the top-level of the MTC and then an array of the top level regional OUs. From there I have the names of the GPO templates and an array of the MTCs to skip. At that point I just enumerate for OUs, copy the GPOs and link the new per-instance GPO to the OU.

Import-Module GroupPolicy

$RootDomain = "DC=onemtc,DC=net"
$TopLevelRegions = "APAC","EMEA","NA"

$WSUSConfigTemplateName = "WSUS Configuration"
$AdminTemplateName = "MTC Admins Template"

$MTCsToSkip = "DAL","ATL","DEN","MPLS"

foreach($TopLevelRegion in $TopLevelRegions)
{
    $MTCOUs = Get-ADOrganizationalUnit -filter * -searchbase "OU=$TopLevelRegion,$RootDomain" -SearchScope OneLevel -Properties Name, Description  
    foreach($MTCOU in $MTCOUs)
    {
        $MTCCode = $MTCOU.Name.Substring(3)
        if($MTCsToSkip -notcontains $MTCCode)
        {
            Write-Output "Creating GPOs and linking for MTC $MTCCode"
            #Copy the two templates to the new names
            Copy-GPO -SourceName $AdminTemplateName -TargetName "$MTCCode Admins"
            Copy-GPO -SourceName $WSUSConfigTemplateName -TargetName "$MTCCode WSUS Config"

            #Link the GPOs to the OU
            New-GPLink -Name "$MTCCode Admins" -Target $MTCOU.DistinguishedName
            New-GPLink -Name "$MTCCode WSUS Config" -Target $MTCOU.DistinguishedName
        }
    }
}

Import-Module GroupPolicy

$RootDomain = "DC=onemtc,DC=net"

$TopLevelRegions = "APAC","EMEA","NA"

$WSUSConfigTemplateName = "WSUS Configuration"

$AdminTemplateName = "MTC Admins Template"

$MTCsToSkip = "DAL","ATL","DEN","MPLS"

foreach($TopLevelRegion in $TopLevelRegions)

{

$MTCOUs = Get-ADOrganizationalUnit -filter * -searchbase "OU=$TopLevelRegion,$RootDomain" -SearchScope OneLevel -Properties Name, Description

foreach($MTCOU in $MTCOUs)

{

$MTCCode = $MTCOU.Name.Substring(3)

if($MTCsToSkip -notcontains $MTCCode)

{

Write-Output "Creating GPOs and linking for MTC $MTCCode"

#Copy the two templates to the new names

Copy-GPO -SourceName $AdminTemplateName -TargetName "$MTCCode Admins"

Copy-GPO -SourceName $WSUSConfigTemplateName -TargetName "$MTCCode WSUS Config"

#Link the GPOs to the OU

New-GPLink -Name "$MTCCode Admins" -Target $MTCOU.DistinguishedName

New-GPLink -Name "$MTCCode WSUS Config" -Target $MTCOU.DistinguishedName

}

Delivering a Customizable, Graphical Insight into Azure VM Security, Health and Connectivity Using Several Azure Services Together

In this blog I want to walkthrough a solution I recently architected and implemented along with a two other MTC architects to deliver a solution we needed for two reasons:

To provide insight into the VMs hosted in Azure across the global Microsoft Technology Center environment
Showcase the use of some key Microsoft cloud technologies

The Requirement

The global MTC organization is made up of around 30 offices which each have several Azure subscriptions to host the projects they are working on and environments used in customer activities. Additionally, there are several global, shared Azure subscriptions that host core infrastructure and experiences. These subscriptions are tied to various Azure AD tenants depending on requirements. The primary subscription for each MTC also hosts a virtual network that is part of a global IP space that is connected via one of four regional ExpressRoute circuits to the MTC worldwide VPN that provides connectivity between all MTC offices.

While there is a standard governance and process guide each MTC has control of their own subscriptions and resources however from a central MTC organization perspective insight into several key factors was required.

Are the VMs registered with the central Log Analytics instance to report inventory and patch state. Log Analytics is part of the Operations Management Suite and is used to accept log information from almost any sort and then provides power analytical capabilities to use that information to provide insight into the environment. A number of solutions are included that provide visibility into best practices, patch status, anti-malware status and much more. For OS instance visibility Log Analytics uses the Microsoft Monitoring Agent (MMS) which is the same agent used by System Center Operations Manager.
What is the current patch status of the VM. This is provided by information to Log Analytics and to Azure Security Center if registered. Azure Security Center (ASC) provides a central security posture location for Azure resources including VM health, network health, storage health and more.
Is the VM connected to ExpressRoute. This can be found by checking the virtual network a VM is attached to and if that virtual network has an ExpressRoute Gateway connected.
Does the VM have a public IP and is it health. Public IP existence can be found through the properties of VM IP configurations and the health which is based on use of Network Security Groups to lock down communication through ASC.
Is the VM older than 30 days. Object creations are logged in Azure. By default, these are kept for 60 days which enables a search of the logs for the VM creation. If not found it would mean the VM is older than 60 days and if found the exact age can be determined. The age is useful as short-term VMs do not have the same levels of reporting requirements, i.e. does not have to be registered to OMS.

The insight into the health needed to be in the form to provide easy overall insight while allowing detail to be exposed through drilling down into the data.

The Solution

I started off crafting a solution in PowerShell through which I can access the full knowledge of the Azure Resource Manager via the AzureRM module and also other solutions such as Log Analytics, Azure Security Center and Azure Storage.

If you like to read the end of the book below is the final solution and what I will walk through is some of the detail you see in the picture.

The first challenge was the context to run the script under since multiple Azure AD tenants were utilized and I didn’t want to have to manage multiple credentials. Therefore, Azure AD B2B (business to business) was utilized. A single identity in the main Azure AD tenant was created and then a communication sent to each MTC to add that identity via Azure AD B2B to any local Azure AD tenant instances and then to give that account Read permissions to all subscriptions. This enabled a single credential to be used across every subscription, regardless of the Azure AD tenant the subscription was tied to. This same credential was also give rights to the Log Analytics instance all VMs reported to which enables queries to be run.

Now the access was available the next step was the actual PowerShell to gather the required information. A storage account was created that would be used to store the output of the execution which would be a basic execution report and two JSON files that contained custom objects representing the VM state and Azure subscription information.

The basic PowerShell flow is as follows:

Import the ASC and Log Analytics PowerShell modules
Access the credential that will be used
Connect to Azure using the credential
Store a list of every subscription associated to the credential in an array
Connect to the Azure Storage account to create a context for BLOB storage
Connect to the Log Analytics workspace and trigger two queries whose results were stored in two arrays
- List of all machines that report to the instance that are stored in Azure
- List of all machines that are missing patches that are stored in Azure

Create three files that contained todays date; log files, VM JSON files and subscription JSON file
Create two empty arrays that will store custom objects for VM state and subscription information
For every subscription perform the following:
- List the administrators and write to the log
- Retrieve the ASC status for the subscription and store in an array
- For every Resource Group
  - Find the virtual networks connected to ExpressRoute gateway and store in an array
  - For every VM in the Resource Group
    - Find the creation time by scanning the operational log of Azure. Save the creation time if found and if older than 30 days or report older than 30 days if no log found
    - For each NIC inspect the IP configurations
      - Is it connected to a virtual network that has ExpressRoute connectivity
      - Does it have a public IP address and if so what is the health of that public IP based on information previously saved from ASC
    - Is the VM registered in OMS
    - Is the VM missing patches based on information from OMS or ASC
    - Create a custom object using a hash table with all desired information about the VM and add to the VM object array
- Add a subscription information custom object to the subscription array
Upload the three data files generated to the Azure storage account as BLOBs

To actually run the PowerShell I used Azure Automation which not only provided a resilient engine to run the code but capabilities such as credentials which could securely store the identity that was used removing any need to hardcode it in the script itself. The schedule capability was used to trigger the runbook (the container for the PowerShell in Azure Automation) to right daily at 11pm.

At this point in an Azure Storage account was a report and two JSON files with one of them, the VM state JSON file, the most useful which enabled all information to be queried easily however the goal was to have it more easily digestible which meant PowerBI and ideally getting the data more easily available to everyone, e.g. Teams along with a notification that the nights execution was successful.

The solution was to use a Logic App (created by Ali Mazaheri, https://blogs.msdn.com/alimaz) which enables activities to be chained together using various connectors which include Azure Storage, Teams and SharePoint. The Logic App was designed with a recurrence trigger (but could also trigger based on object creations and other triggers) and to then perform the following:

List the blobs in the azurescan container (a container is like a folder in Azure Store)
For each object that is not empty
- Get the BLOB content
- Create a file containing that content in SharePoint
- Copy the BLOB to an archive BLOB
- Delete the original BLOB

Write a message to a team’s channel that the log migration was completed (Or send an email, notification to phone, etc.)

A great feature of Logic Apps is that they are implemented by adding the built-in connectors or your own API apps, Azure Functions and then graphically laying out the flow using conditions, branches and those connectors by passing output as an input for next connectors and in this case some custom expressions. Below is the key content of the Logic App (as an alternative we could have also used Azure Functions and EventGrid to achieve the same goal).

The final step was the Power BI portion to read in the file from SharePoint and provide a visualization of the data contained in the JSON. David Browne created this powerful dashboard that enabled various visualizations of the data and easy access to change the criteria of the data contained.

The Power BI Service can connect directly to SharePoint Online to read the files. Power Query in Power BI is used to identify the latest data files, convert them from JSON to a tabular format and to clean the data. The data is then loaded into an in-memory Tabular Model hosted by Power BI and configured for daily refresh.

Fix WSUS Console Crash

I recently deployed a new WSUS server on Windows Server 2016 but the console would crash, the WSUS engine had crashed and it turns out the problem is it runs out of memory. Make sure your WSUS server had at least 8GB of memory then perform the following:

Open IIS Manager
Select <server> – Application Pools
Right click on WsusPool and select Advanced Settings
Change the Recycling – Private Memory Limit (KB) from 1.4GB to around 4.8GB, e.g. 50331645
Click OK
Start or Recycle the WsusPool

Problem should be fixed!

Using the Azure PS Drive

If you leverage the Azure Cloud Shell in the Azure portal its a very convenient way to manage Azure resources using PowerShell and the CLI but you may have also noticed an actual Azure drive, i.e. Set-Location azure: and you can navigate around your Azure resources (this is actually the default location when the cloud shell opens). At the top level are subscriptions and you can then navigate to resource groups, VMs, WebApps and more.

The Azure drive is provided via the Simple Hierarchy in PowerShell (SHiPS) provider which you can see via Get-PSProvider.

The actual functionality is evolving, its a project on GitHub at https://github.com/PowerShell/SHiPS but this also means you can run this same provider outside of the Azure Cloud Shell.

You need to ensure you are running the latest version of the AzureRM module then download, install, add an Azure account and add the provider:

Update-Module AzureRM
Install-Module AzurePSDrive
Login-AzureRmAccount 
Import-Module AzurePSDrive
New-PSDrive -Name Azure -PSProvider SHiPS -root 'AzurePSDrive#Azure'

Update-Module AzureRM

Install-Module AzurePSDrive

Import-Module AzurePSDrive

New-PSDrive -Name Azure -PSProvider SHiPS -root 'AzurePSDrive#Azure'

You can now navigate to Azure: and enjoy the same feature as when in the Azure Cloud Shell.

Note this is completely different from the Azure Cloud Drive which is the persistent file storage you have in the Azure Cloud Shell that is backed by Azure Files and enables data to be saved and used between sessions. Use Get-CloudDrive to see the current configuration and if you wish to change it simply run Dismount-CloudDrive and then restart the shell and select Advanced options to customize the location.

Writing to files with Azure Automation

Azure Automation enables PowerShell (and more) to be executed as runbooks by runbook workers hosted in Azure. Additionally Azure Automation accounts bring capabilities such as credential objects to securely store credentials, variables, scheduling and more. When a runbook executes it runs in a temporary environment that does not have any persistent state and so if you want to work with files you need to save them somewhere, for example to an Azure storage account as a blob, before the runbook completes.

You can actually create and use files as normal using the default path within PowerShell during execution, just remember to save the files externally before the script completes.

For example create a file as usual:

$todaydate = Get-Date -Format MM-dd-yy 
$LogFull = "AzureScan-$todaydate.log" 
$LogItem = New-Item -ItemType File -Name $LogFull

"  Text to write" | Out-File -FilePath $LogFull -Append

$todaydate = Get-Date -Format MM-dd-yy

$LogFull = "AzureScan-$todaydate.log"

$LogItem = New-Item -ItemType File -Name $LogFull

" Text to write" | Out-File -FilePath $LogFull -Append

Then before ending the PowerShell, copy it to a blob (as an example storage place):

#Get key to storage account
$acctKey = (Get-AzureRmStorageAccountKey -Name onemtceastusfs -ResourceGroupName EastUS-Infra-RG).Value[0]

#Map to the reports BLOB context
$storageContext = New-AzureStorageContext -StorageAccountName "onemtceastusfs" -StorageAccountKey $acctKey

#Copy the file to the storage account
Set-AzureStorageBlobContent -File $LogFull -Container "azurescan" -BlobType "Block" -Context $storageContext -Verbose

#Get key to storage account

$acctKey = (Get-AzureRmStorageAccountKey -Name onemtceastusfs -ResourceGroupName EastUS-Infra-RG).Value[0]

#Map to the reports BLOB context

$storageContext = New-AzureStorageContext -StorageAccountName "onemtceastusfs" -StorageAccountKey $acctKey

#Copy the file to the storage account

Set-AzureStorageBlobContent -File $LogFull -Container "azurescan" -BlobType "Block" -Context $storageContext -Verbose

Easily create multiple subnets in an Azure Virtual Network

I recently needed to create a whole set of subnets in a large number of virtual networks of various sizes. I thought some variables would be a great way to quickly create the set of subnets in each virtual network which were each /20 networks in a shared class B IP which enabled 16 virtual networks per Class B IP space. The goal was to show that each subnet didn’t need to be a full class C (/24) in instead we could use smaller subnets based on the number of hosts actually required. I’ve included the comments which explains the subnets created and the number of hosts supported in each.

#10.x.y.0/26            Gateway subnet      255.255.255.192     reserved for gateway, no hosts
#10.x.y.64/26           Subnet1             255.255.255.192     59 usable
#10.x.y.128/26          Subnet2             255.255.255.192     59 usable
#10.x.y.192/26          Subnet3             255.255.255.192     59 usable
#10.x.y+1.0/25          Subnet4             255.255.255.128     123 usable
#10.x.y+1.128/25        Subnet5             255.255.255.128     123 usable
#10.x.y+2.0/24          Subnet6             255.255.255.0       251 usable
#10.x.y+3.0/27          Subnet7             255.255.255.224     27 usable
#10.x.y+3.32/27         Subnet8             255.255.255.224     27 usable
#10.x.y+3.64/27         Subnet9             255.255.255.224     27 usable
#10.x.y+3.96/27         Subnet10            255.255.255.224     27 usable
#10.x.y+3.128/26        Subnet11            255.255.255.192     59 usable
#10.x.y+3.192/26        Subnet12            255.255.255.192     59 usable
#
#As can be seen subnets can be a variety of sizes based on the number of IPs required in each. It is recommended to use the
#smallest subnet possible to optimize IP address space
#
#3rd octet increase by 16 for each virtual network

$BaseCIDR = "10.240.32.0/20" #This would change for each network
$IPSplit = $BaseCIDR.Split(".")
$First2Octet = "$($IPSplit[0]).$($IPSplit[1])"
$3rdOctet = [int]$IPSplit[2]

#Set the NSG, VNet and resource group names as variables $NSGName, $VNetName and $VNetRG

$NSG = Get-AzureRmNetworkSecurityGroup -Name $NSGName -ResourceGroupName $VNetRG
$VNet = Get-AzureRmVirtualNetwork -Name $VNetName -ResourceGroupName $VNetRG

$GWSubnet ="$First2Octet.$3rdOctet.0/26"
Write-Output "Creating GW Subnet: $GWSubnet"

$subnets = "$First2Octet.$3rdOctet.64/26","$First2Octet.$3rdOctet.128/26","$First2Octet.$3rdOctet.192/26","$First2Octet.$($3rdOctet+1).0/25",
    "$First2Octet.$($3rdOctet+1).128/25","$First2Octet.$($3rdOctet+2).0/24","$First2Octet.$($3rdOctet+3).0/27","$First2Octet.$($3rdOctet+3).32/27",
    "$First2Octet.$($3rdOctet+3).64/27","$First2Octet.$($3rdOctet+3).96/27","$First2Octet.$($3rdOctet+3).128/26","$First2Octet.$($3rdOctet+3).192/26"

$subnetno=1
foreach($subnet in $subnets)
{
    Write-Output "Creating subnet $subnet"
    Add-AzureRmVirtualNetworkSubnetConfig -VirtualNetwork $VNet -Name "Subnet$subnetno" `
        -AddressPrefix $subnet -NetworkSecurityGroup $NSG
    $subnetno++
}

Set-AzureRmVirtualNetwork -VirtualNetwork $VNet

Write-Output "A /24 gives 251 usable, a /25 gives 123 usable, /26 gives 59 usable and a /27 gives 27 usable"
Write-Output "These subnets are created as defaults and to give examples. You can create additional subnets up to $First2Octet.$($3rdOctet+15).x"

#10.x.y.0/26 Gateway subnet 255.255.255.192 reserved for gateway, no hosts

#10.x.y.64/26 Subnet1 255.255.255.192 59 usable

#10.x.y.128/26 Subnet2 255.255.255.192 59 usable

#10.x.y.192/26 Subnet3 255.255.255.192 59 usable

#10.x.y+1.0/25 Subnet4 255.255.255.128 123 usable

#10.x.y+1.128/25 Subnet5 255.255.255.128 123 usable

#10.x.y+2.0/24 Subnet6 255.255.255.0 251 usable

#10.x.y+3.0/27 Subnet7 255.255.255.224 27 usable

#10.x.y+3.32/27 Subnet8 255.255.255.224 27 usable

#10.x.y+3.64/27 Subnet9 255.255.255.224 27 usable

#10.x.y+3.96/27 Subnet10 255.255.255.224 27 usable

#10.x.y+3.128/26 Subnet11 255.255.255.192 59 usable

#10.x.y+3.192/26 Subnet12 255.255.255.192 59 usable

#As can be seen subnets can be a variety of sizes based on the number of IPs required in each. It is recommended to use the

#smallest subnet possible to optimize IP address space

#3rd octet increase by 16 for each virtual network

$BaseCIDR = "10.240.32.0/20" #This would change for each network

$IPSplit = $BaseCIDR.Split(".")

$First2Octet = "$($IPSplit[0]).$($IPSplit[1])"

$3rdOctet = [int]$IPSplit[2]

#Set the NSG, VNet and resource group names as variables $NSGName, $VNetName and $VNetRG

$NSG = Get-AzureRmNetworkSecurityGroup -Name $NSGName -ResourceGroupName $VNetRG

$VNet = Get-AzureRmVirtualNetwork -Name $VNetName -ResourceGroupName $VNetRG

$GWSubnet ="$First2Octet.$3rdOctet.0/26"

Write-Output "Creating GW Subnet: $GWSubnet"

$subnets = "$First2Octet.$3rdOctet.64/26","$First2Octet.$3rdOctet.128/26","$First2Octet.$3rdOctet.192/26","$First2Octet.$($3rdOctet+1).0/25",

"$First2Octet.$($3rdOctet+1).128/25","$First2Octet.$($3rdOctet+2).0/24","$First2Octet.$($3rdOctet+3).0/27","$First2Octet.$($3rdOctet+3).32/27",

"$First2Octet.$($3rdOctet+3).64/27","$First2Octet.$($3rdOctet+3).96/27","$First2Octet.$($3rdOctet+3).128/26","$First2Octet.$($3rdOctet+3).192/26"

$subnetno=1

foreach($subnet in $subnets)

{

Write-Output "Creating subnet $subnet"

Add-AzureRmVirtualNetworkSubnetConfig -VirtualNetwork $VNet -Name "Subnet$subnetno" `

-AddressPrefix $subnet -NetworkSecurityGroup $NSG

$subnetno++

}

Set-AzureRmVirtualNetwork -VirtualNetwork $VNet

Write-Output "A /24 gives 251 usable, a /25 gives 123 usable, /26 gives 59 usable and a /27 gives 27 usable"

Write-Output "These subnets are created as defaults and to give examples. You can create additional subnets up to $First2Octet.$($3rdOctet+15).x"

Use an Application Image from the Azure Marketplace using PowerShell

I recently needed to deploy a special type of VM from the Azure Marketplace using PowerShell and the deployment was not the same as regular Windows or Linux VM.

First I knew the app I wanted to use, e.g. https://azuremarketplace.microsoft.com/en-us/marketplace/apps/microsoft-ads.windows-data-science-vm but wasn’t sure of the publisher or offer. With hindsight it shows you right in the URL, Microsoft-ads is the publisher and windows-data-science-vm is the offer but I initially just searched for what I wanted using the following and looked for it (first part of the code), then got the detail as usual (last two lines):

$publishers = Get-AzureRmVMImagePublisher -Location $Region
foreach($publisher in $publishers)
{
    Get-AzureRmVMImageOffer -Location $Region -PublisherName $publisher.PublisherName | ft Offer,PublisherName -AutoSize
}
Get-AzureRmVMImageSku -Location $Region -PublisherName "microsoft-ads" -Offer "windows-data-science-vm"
Get-AzureRmVMImage -Location $Region -PublisherName "microsoft-ads" -Offer "windows-data-science-vm" -Skus "windows2016"

$publishers = Get-AzureRmVMImagePublisher -Location $Region

foreach($publisher in $publishers)

{

Get-AzureRmVMImageOffer -Location $Region -PublisherName $publisher.PublisherName | ft Offer,PublisherName -AutoSize

}

Get-AzureRmVMImageSku -Location $Region -PublisherName "microsoft-ads" -Offer "windows-data-science-vm"

Get-AzureRmVMImage -Location $Region -PublisherName "microsoft-ads" -Offer "windows-data-science-vm" -Skus "windows2016"

Now I knew the publisher, offer and SKU I could add that configuration to my VM.

$vm = Set-AzureRmVMSourceImage -VM $vm -PublisherName microsoft-ads -Offer windows-data-science-vm `
    -Skus windows2016 -Version latest

1 2	$vm = Set-AzureRmVMSourceImage -VM $vm -PublisherName microsoft-ads -Offer windows-data-science-vm ` -Skus windows2016 -Version latest

However, when using the application there are a few other steps. You need to set a plan and also accept the terms of the app. Fortunately it’s easy to do.

$vm = Set-AzureRmVMPlan -VM $vm -Name windows2016 -Product windows-data-science-vm -Publisher microsoft-ads

#Have to accept terms
Get-AzureRmMarketplaceTerms -Name windows2016 -Product windows-data-science-vm -Publisher microsoft-ads |
    Set-AzureRmMarketplaceTerms -Accept

$vm = Set-AzureRmVMPlan -VM $vm -Name windows2016 -Product windows-data-science-vm -Publisher microsoft-ads

#Have to accept terms

Get-AzureRmMarketplaceTerms -Name windows2016 -Product windows-data-science-vm -Publisher microsoft-ads |

Set-AzureRmMarketplaceTerms -Accept

That was it. Basically the only additional work is setting the plan and accepting the marketplace terms and the data needed for those commands are the same values used for the source image, just PublisherName > Publisher, Offer > Product and SKUs > Name. The exact same would apply if using JSON. Easy!

John Savill's Corner of the Web

Computer related thoughts from John

Author: John Savill

Email people via Office 365 from PowerShell when passwords about to expire

Add group members to another tenant via Azure AD B2B and PowerShell

Create AD sites through PowerShell

Bulk created group policy objects with PowerShell

Delivering a Customizable, Graphical Insight into Azure VM Security, Health and Connectivity Using Several Azure Services Together

The Requirement

The Solution

Fix WSUS Console Crash

Using the Azure PS Drive

Writing to files with Azure Automation

Easily create multiple subnets in an Azure Virtual Network

Use an Application Image from the Azure Marketplace using PowerShell