Script to generate office 365 groups created on last 30 days

By default it is enabled for users to create the office365 groups. There are few organizations where they do not need to restrict this group creation because these groups are heavily influenced on utilizing the office365 services Sharepoint,Yammer, Microsoft Teams, PowerBI , Outlook, Planner and Road Map which in turn might decline the office 365 user adoption rate.

The below script can be used to run in task scheduler on a monthly basis for reviewing the Office 365 groups which have been created in last 30 days and will email us the report.

Below is the sample output of the script which will provide us the below details.


########################################################################################################################
# Description   :- Powershell Script To extract office365 groups created less than 30 days time and send them in email
# Author        :- Sathish Veerapandian
# Created       :- 15-Jul-2019
# Updated       :- 15-Oct-2019
# Version       :- 0.2
# Notes         :- 
#########################################################################################################################

$Header = @"

TABLE {border-width: 1px; border-style: solid; border-color: black; border-collapse: collapse;}
TH {border-width: 1px; padding: 3px; border-style: solid; border-color: black; background-color: #6495ED;}
TD {border-width: 1px; padding: 3px; border-style: solid; border-color: black;}

"@

# Load MFA Module
$MFAExchangeModule = ((Get-ChildItem -Path $($env:LOCALAPPDATA+"\Apps\2.0\") -Filter CreateExoPSSession.ps1 -Recurse).FullName | Select-Object -Last 1)
. "$MFAExchangeModule"

# Initiate Session
Connect-EXOPSSession -UserPrincipalname mentionadminid@domain.com

# Get Office365 Groups
Get-UnifiedGroup -ResultSize unlimited | select DisplayName,PrimarySMtpAddress,WhenCreated,ManagedBy,RecipientTypeDetails,AccessType | Export-Csv C:\Scripts\365groups.csv -NoTypeInformation

# Define the date variable for Cutoffdate less than 31 days
$CutoffDate = get-date -date $(get-date).adddays(-31) -format "M/dd/yyyy h:mm:ss tt"

# Get the office365 groups created lesser than 31 days
$Data = Import-CSV "C:\Workspace\Messaging\Output\365groups.csv" | Where-Object {$_.WhenCreated -as [datetime] -gt $CutoffDate}


# Export the office365 groups  created lesser than 31 days in csv file
$data | ConvertTo-Html -Head $Header | Out-File -FilePath C:\Scripts\365.html


# Send the exported csv email to the helpdesk team for evaluation
Send-MailMessage -From senderemailID -To recipientemailid -Attachments "C:\Scripts\365.html" -BodyAsHtml -SmtpServer mentionsmtpserver -Subject Office365GroupStatus 

Thanks

Sathish Veerapandian

Configure Exchange Online to reject emails that fail DMARC validation with organizations having policy of reject

By default Office 365 DMARC validation for internet emails that fails for policy P=Reject will make the email to land in junk folder of the recipient mailbox.Microsoft 365 will treat DMARC policies of quarantine and reject in the same way, which means that if the sender’s DMARC policy is set to reject or quarantine, the emails that fail DMARC will be sent to the junk folder of the recipient mailbox.

Microsoft believes that the main agenda of doing this is to ensure that any legitimate emails which misses in DMARC alignment shouldn’t be lost and its better to get them delivered to recipient junk mail folder. There are few cases wherein few organizations would still need the DMARC policy to be stringent due to their security regulations.

Microsoft validates DMARC and overrides the failure with a header value for a domain whose DMARC TXT record has a policy of p=reject oreject. Instead of deleting or rejecting the message, Office 365 marks the message as spam.

To test it further we are publishing SPF, DKIM and DMARC record for the domain ezcloudinfo.com as below:

SPF record: Adding only Exchange online as authorized sender.

DKIM Record: Having the Signing key only for office 365

DMARC Record: Having strict policy of P=reject

For a successful email from a legitimate sender where it has passed spf, dkim & dmarc we see the below value for DMARC.

dmarc=pass action=none

Now we are triggering an email from a registered mailchimp account for ezcloudinfo.com where we do not have the SPF and DKIM records added in our DNS records.

The email from mailchimp from sender address sathish@ezcloudinfo.com gets landed in junk email.

We can see the header value of above email and the DMARC validation is failed.

WorkAround:

We received a workaround which can be accomplished to reject the emails that fails with DMARC validation from redsift cyber security analysis .

Create a Transport Rule:

Include the below value oreject or action=oreject or dmarc=fail in the message header include option.

Reject the message with the custom status code.

Now if we send a test email after this transport rule from an unauthorized sender the email will be rejected and could see the below NDR message.

So after this transport rule any spoof emails that are coming from a domain that is DMARC protected will not be delivered to the spam folder. They will all be rejected and never reach the recipient.

Thanks & Regards

Sathish Veerapandian

Readiness and steps to Configure Direct Routing in Microsoft Teams

Earlier to enable enterprise voice with calling plan on skype for business online we would need to install cloud connector locally on a virtual machines as a separate appliance which requires complex configuration for integrating with the certified session border controllers.

Now Microsoft have made it easier to configure them with direct routing where we do not need to deploy the cloud connector agent locally in the on-premise systems.

When paired with Microsoft Calling plans or direct routing with local ISP calling plan, they provide a full enterprise experience for office 365 users in Teams on a global scale. With Direct Routing we can Connect Existing Telephony Infrastructure to MS teams with the help of  local session border controllers. A SIP connection is created between the cloud call controllers and our local session border controllers.

In this article we will look at the options , readiness and steps  to Enable users for Direct Routing from the Microsoft office 365 perspective.

Readiness for Direct Routing:

Decide on Session Border Controller (Self or hosted SBC):

Session border controller connects Teams call to PSTN next hop or to the configured sip trunk with the local ISP. Here we have two options either to have own session border controllers on premise or to have this functionality hosted to a managed service provider who will host the session border controller for your organization to perform the SIP proxy and the PSTN routing for Microsoft Teams.

Make sure to select the supported session border controllers by Microsoft to configure direct routing in Microsoft Teams.

Figure out licenses based on deployment: Decide on media bypass Configuration

We need to figure out licenses on Microsoft office 365 to utilize the full enterprise functionality of Microsoft Teams.

Option1: Full Microsoft License

In this case no direct routing is required unless there is coexistence required with existing telephony system because we will be having the full calling plan with Microsoft and will utilize the Microsoft call controller, PSTN, Media controllers and Media processor.

Below Licenses are required:

  1. Enable Microsoft Teams.
  2. Office 365 Phone System License
  3. Skype for business online plan2  License
  4. Audio Conferencing
  5. Microsoft Calling plan (Available in selected regions as of now)

The first 4 licenses are available by default in office365 E5 License. For other license types separate SKus needs to be procured along with the calling plan available in the region

Below is the call flow for all in the cloud for Teams:

Option 2 : Full Teams feature plus Local Telcom Calling plan

This option requires to perform direct routing with Microsoft Teams SIP proxy  services to create the SIP trunk between Microsoft Teams in the cloud and local session border controllers to utilize the calling plan from local PSTN provider.

Below Licenses are required:

  1. Microsoft Teams
  2. Phone System
  3. Skype for business online plan2 
  4. Audio Conferencing
  5. Local SIP calling plan with your telecom provider

Phone System with own carrier via Direct Routing:

SBC readiness:

Decide on SBC Host Name:

Microsoft communicates to session border controllers only via FQDN. We need to decide on a hostname for Session border controller which will be public available to configure direct routing. In our case we will be having voicegw.ezcloudinfo.com

Configure the certificate:

The SBC must be configured with a certificate from public certificate authority with the decided host name. We could also use wild card and SAN certs but the CSR needs to be generated from the certified SBC.

Firewall:

Below source and destination ports needs to be opened for communication between Microsoft PSTN hub FQDNs and the session border controllers.

Above Necessary source and destination ports needs to be opened in Firewall for the SIP Signaling, SIP Proxy ,Media Processing and Media Bypass to happen for the STUN, TURN , ICE connectivity and for successful Teams audio/video call .

Direct Routing configuration in Microsoft Teams:

Ensure that the users are fully transformed to Teams Only Mode.

Pair the SBC to the Direct Routing Service of Phone System:

Connect to Skype for Business Online admin center using PowerShell

Verify the online PSTN gateways.

Get-Command *onlinePSTNGateway*

Now add the new online PSTN gateway to add our SBC in the list.

New-CsOnlinePSTNGateway -Fqdn voicegw.ezcloudinfo.com -SipSignallingPort 5067 -MaxConcurrentSessions 50 -Enabled $true

Check the added SBC configuration.

Get-CsOnlinePSTNGateway -Identity sbc.contoso.com

Configure the phone number and enable enterprise voice.

Set-CsUser -Identity “Will Smith” -OnPremLineURI tel:+97155368846 -EnterpriseVoiceEnabled $true -HostedVoiceMail $False

Create the Voice Route to go via SBC.

New-CsOnlineVoiceRoute -Identity “UAE” -NumberPattern “^\+9(71|206)(\d{7})$” -OnlinePstnGatewayList voicegw.ezcloudinfo.com -Priority 1 -OnlinePstnUsages “UAE and India”

IMP Notes:

  1. Flow differs for external and internal media bypass.
  2. Internal media bypass – Flows within the network teams and SBC and traffic is routed to local PSTN provider.
  3. External Media bypass – Flows users will try to connect via certified SBC if now will take the SIP Proxy Route.
  4. Office 365 network is enhanced for teams traffic.
  5. Call Queues and Auto attendant configuration needs to be verified and configured according to the current setup.

Thanks & Regards

Sathish Veerapandian

Script to offboard resigned employee in a hybrid environment

The below script can be used in off-boarding below tasks for a resigned employees as a bulk operation.

This script will help in below actions for Exchange online and AD tasks to be removed in a Exchange hybrid environment:

  1. Convert exchange online mailbox to shared Mailbox.
  2. Disable the Mailbox protocols – OWA,ActiveSync, POP, IMAP, MAPI & OWA for devices.
  3. Hide the user from GAL.
  4. Remove the user from respective licenses E3,E5,EMS E3 & EMS E5 Licenses.
  5. Cancel all the calendar future meetings.
  6. Remove the user account from all groups.
  7. Set the account expiry of the AD account.
  8. Remove the IP Phone Attribute.
  9. Remove the manager field.
  10. Set out of office.

Prerequisites:

1.Run this from a management server where it has Exchange, Active Directory, MSonline and exchange online MFA PowerShell modules installed on it.

2.This will run from MFA enabled Admin accounts from windows powershell,connect to exchange online and msonline. Make sure to run this script from an elevated windows powershell mode.

3.Change the csv file location to your location
Connect-EXOPSSession -UserPrincipalname adminid@domain.com – Change the admin userprincipalname to your admin id.
Export-csv “c:\ops\Output\disabledusers.csv” – mention the location of the csv file

4. Create a CSV file which has only the userprincipalname of the resigned employees.

5. Change the OOF message details with the required information.


$MFAExchangeModule = ((Get-ChildItem -Path $($env:LOCALAPPDATA+"\Apps\2.0\") -Filter CreateExoPSSession.ps1 -Recurse).FullName | Select-Object -Last 1)
. "$MFAExchangeModule"
$cred= Get-Credential
Add-PSSnapin Microsoft.Exchange.Management.PowerShell.SnapIn
Import-Module activedirectory
Connect-MsolService -Credential $cred
Connect-EXOPSSession -UserPrincipalname adminid@domain.com
$E3 = "tenantname:ENTERPRISEPREMIUM"
$E5 = "tenantname:ENTERPRISEPACK"
$EMSE3 = "tenantname:EMSPREMIUM"
$EMSE5= "tenantname:EMS"
Import-csv  "mention the CSV path location" | foreach {
$UPN = $_.userPrincipalName
#Convert to shared mailbox
Set-Mailbox $UPN -Type “Shared” 
#Disable the Mailbox protocols
Set-CASMailbox  -identity $upn -OWAEnabled:$false -ImapEnabled:$false -MAPIEnabled:$false -PopEnabled:$false -ActiveSyncEnabled:$false -OWAforDevicesEnabled:$false -Confirm:$false -verbose
#Cancel all the future meetings
Remove-Calendarevents -identity $UPN.userprincipalname -CancelOrganizedMeetings -Confirm:$False 
#Remove the license
$msolupn= Get-Msoluser -Userprincipalname $UPN | select Objectid,Userprincipalname,Licenses 
Set-MsolUserLicense -UserPrincipalName $UPN.UserPrincipalName -RemoveLicenses $E3 -ErrorAction SilentlyContinue
Set-MsolUserLicense -UserPrincipalName $UPN.UserPrincipalName -RemoveLicenses $E5 -ErrorAction SilentlyContinue
Set-MsolUserLicense -UserPrincipalName $UPN.UserPrincipalName -RemoveLicenses $EMSE3 -ErrorAction SilentlyContinue
Set-MsolUserLicense -UserPrincipalName $UPN.UserPrincipalName -RemoveLicenses $EMSE5 -ErrorAction SilentlyContinue
#Hide from GAL
Set-RemoteMailbox  -identity $upn -HiddenFromAddressListsEnabled:$True
#Set the OOF
Set-MailboxAutoReplyConfiguration -Identity $UPN -AutoReplyState Enabled -ExternalMessage "“Please note that i no longer work for ezcloudinfo anymore.Kindly contact HR department via hr@ezcloudinfo.com for further communication.“"    
#Remove from Distribution Lists
Get-ADUser -Identity $UPN -Properties MemberOf | ForEach-Object {
  $_.MemberOf | Remove-ADGroupMember -Members $_.DistinguishedName -Confirm:$false
#Remove the manager field
Set-Aduser -Identity $UPN -Manager $null
#Remove IP Phone attribute
Set-ADuser -Identity $UPN -Clear ipPhone
#Set the Account Expiry
Set-ADAccountExpiration -Identity $UPN -TimeSpan 0.0:30
Write-Host The Users have been offboarded successfully -ForegroundColor Green
Get-Mailbox $UPN | select-Object name,recipienttypedetails | Export-csv "c:\ops\Output\disabledusers.csv"  -NoTypeInformation -Force -Append
}
}

Thanks & Regards

Sathish Veerapandian

Migrate onpremise SQL DB to the Azure SQL Database

Azure dataplatform also provides Azure SQL database as a relational database as a service PAAS which is fully managed by Microsoft.This helps the developers to build their apps very quickly and removes the overhead of database administration.

There are few methods to migrate an on premise SQL database to Azure SQL Database and in this article we will have a look at migrating them with two options.

1) Using BACPAC export and import.

2) Data Migration Assistant.

Using BACPAC export and import:

With BACPAC export and import firstly we need to export the SQL database from the on premise SQL instance as a data tier application.

To export – Open SQL Management Studio – Right Click on the desired database and click on tasks – select export data tier application.

Now we need to save them in bacpac format.

The exported bacpac file will be successful.

Now this bacpac file needs to be imported to the Azure SQL database. Now we need to connect to the Azure SQL database to from SQL Management Studio.

Once after it is connected right click on the database folder and select import data tier application.

Select the exported bacpac file from the local disk and select the new database name that needs to be mentioned. Here we need to choose the Edition of Microsoft Azure , size of the database and the service type for this database in Microsoft Azure.

Having selected the required option select import and the import operation will start.

After a successful import we can see the status to be green and result successful.

Now we can see the migrated database in the Azure SQL database which have been successfully imported. Now we need to provide the username and the connection strings to the application owner to access their data which is present on the Azure SQL database.

Data Migration Assistant:

We can use the SQL migration assistant with source and target end points and migrate the data to SQL PAAS Azure easily.

Below are the readiness to be prepared for migrating the SQL data from on premise to Azure :

  • Download and install SQL data migration assistant.
  • We need to enable TCPIP on source.
  • Port 1433 must be accessible.
  • SQL browser service must be started.
  • Enable SQL remote connection must be enabled.

Once the Data Migration Assistant is installed open  and click on new

Here we have two options assessment or migration. Assessment helps us to identify the readiness required for this migration and will let us know if any connection or prerequisites missing. Here we can click on assessment.

Now we can select the authentication type and click on next

Select the desired DB’s that needs to be migrated to Azure.

Now we have the option to click on start assessment

 

  Check the assessment report once it is completed.  

To Migrate – rerun the agent and choose the option migrate and specify the source server details.

Once after its authenticated successfully now we have an option to choose the database that needs to be migrated.   

Now we need to specify the target Azure SQL PAAS Db details and the credentials.

Once after its been authenticated successfully , we can see the schema objects from the source database which we would like to migrate to Azure SQL database.

For the schema to migrate successfully we need to deploy the schema which will help us to migrate the schema initially.

Later once the schema is populated successfully now we have an option to migrate the data.Click on migrate data.

Choose the list of tables that needs to be migrated.

Once the table have initiated the migration  we can see the progress.

On a successful migration we get the below message.

The result of the online migration is that the data is successfully migrated to the cloud.

Thanks & Regards

Sathish Veerapandian

Microsoft Azure – Copy VHDs, between storage accounts in managed and unmanaged disks

The most common tasks that we might be receiving in Azure is to copy the blobs between the storage accounts. This article outlines the steps involved in copying the VHDs between managed and unmanaged disks

Copying the VHDs from unmanaged disks to a new storage account is pretty simple and we have two options copying via AzCopy or use Storage explorer

Option 1: Using Az Copy

Step 1: Get the VHD URL – 

Navigate to storage account – Choose the Associated VM SG account – Click on Blobs – Select the container name – Choose Properties – Copy the URL 

Step 2 : Copy the access key of the source storage account.

Step 3: Download and install the AZ copy 

Step 3:  Follow Step 1 & 2 and get the URL and access key details  of the target storage account.

Now we need to open command prompt and run the below command replacing the required entries that we have taken from the respective storage accounts

.\AzCopy.exe /Source:https://StorageAccountSource.blob.core.windows.net/vhds /Dest:https://StorageAccountDestination.blob.core.windows.net/vhds /SourceKey:”keyXXX”  /DestKey:”DestinationKeyXX” /SyncCopy /s

We will see the copy progress once after the command have been initiated

Once the task is completed we get the below message

And can see the file successfully copied to the target storage container location

Option 2: Using storage explorer (preview)

Using storage explorer is pretty much simpler task

Open storage explorer – click on the subscription – expand the blob containers of the source VHD – Select the VHD – and click on copy




Now navigate to the destination blob container and paste the copied vhd file.

Copy from Managed disks:

Copying the data from the managed disks is much easier using Azcopy or power shell script. There are lot available in the GitHub and have used this one was taken from GitHub

All we need to provide subscription ID, Resource group name , disk name , target storage account name , storage container , storage account key and destination VHD file name.

#Provide the subscription Id of the subscription where managed disk is created
$subscriptionId = "provide subscriptionID"

#Provide the name of your resource group where managed is created
$resourceGroupName ="Provide RG name"

#Provide the managed disk name 
$diskName = "provide disk name"

#Provide Shared Access Signature (SAS) expiry duration in seconds e.g. 3600.
#Know more about SAS here: https://docs.microsoft.com/en-us/Az.Storage/storage-dotnet-shared-access-signature-part-1
$sasExpiryDuration = "3600"

#Provide storage account name where you want to copy the underlying VHD of the managed disk. 
$storageAccountName = "provide SG account name"

#Name of the storage container where the downloaded VHD will be stored
$storageContainerName = "provide storage container name"

#Provide the key of the storage account where you want to copy the VHD of the managed disk. 
$storageAccountKey = 'provide storage account key'

#Provide the name of the destination VHD file to which the VHD of the managed disk will be copied.
$destinationVHDFileName = "provide destination VHD file"

#Set the value to 1 to use AzCopy tool to download the data. This is the recommended option for faster copy.
#Download AzCopy v10 from the link here: https://docs.microsoft.com/en-us/azure/storage/common/storage-use-azcopy-v10
#Ensure that AzCopy is downloaded in the same folder as this file
#If you set the value to 0 then Start-AzStorageBlobCopy will be used. Azure storage will asynchronously copy the data. 
$useAzCopy = 1

# Set the context to the subscription Id where managed disk is created
Select-AzureRMSubscription -SubscriptionId $SubscriptionId

#Generate the SAS for the managed disk 
$sas = Grant-AzureRmDiskAccess -ResourceGroupName $ResourceGroupName -DiskName $diskName -DurationInSecond $sasExpiryDuration -Access Read 

#Create the context of the storage account where the underlying VHD of the managed disk will be copied
$destinationContext = New-AzureStorageContext -StorageAccountName $storageAccountName -StorageAccountKey $storageAccountKey

#Copy the VHD of the managed disk to the storage account
if($useAzCopy -eq 1)
{
    $containerSASURI = New-AzureStorageContainerSASToken -Context $destinationContext -ExpiryTime(get-date).AddSeconds($sasExpiryDuration) -FullUri -Name $storageContainerName -Permission rw
    .\azcopy copy $sas.AccessSAS $containerSASURI

}else{

    Start-AzureStorageBlobCopy -AbsoluteUri $sas.AccessSAS -DestContainer $storageContainerName -DestContext $destinationContext -DestBlob $destinationVHDFileName
}

Disk name of managed VM can be taken from the disks section tab in the managed VM

Once the script started running we could see the SAS URL being generated through the commandlet Grant-AzureRMDiskaccess and we have an option to download them directly from this URL.

With all the details we run the script from Azcopy or powershell and it will copy the VMs successfully to the destination storage account.

Thanks & Regards

Sathish Veerapandian

Microsoft Teams – Side load 3rd party & custom built apps in Microsoft Teams pane

With all the more new improvements in Microsoft Teams,we have more alternatives to modify the end user client choices from the application perspective to get access to the most frequently used applications from Microsoft Teams.

The Custom built in-house applications can be effectively side-stacked in Microsoft Teams which makes the end users to adequately use these applications.

To start utilizing these options login to Office 365 admin portal and verify if the teams side loading options are migrated to Teams admin portal.

Once logged in navigate to settings – services & addins – search for Microsoft Teams – And see if external apps in turned on.

In below case in this tenant these configurations have been migrated to Microsoft Teams admin portal and hence these settings are greyed out. This will be the case for almost every office 365 tenants.

Now we have got app permission policies in Microsoft Teams.

App permissions policies control what applications we need to make accessible to Teams clients in our organization. Now we have got the better flexibility to customize the default policy or create custom policy and assign to only targeted users. The better option is to create a custom policy and assign them to targeted users.

Login to Microsoft Teams Admin portal – Select Teams Apps – and choose permission polices – Click Permission policies – Click Add

Here we have the flexibility to control Microsoft Apps, Third party Apps and Self developed custom inbuilt tenant apps which are published in Microsoft Teams as an App Package.

Once the required applications are selected the created application is ready to be assigned to individual users.

We can create app setup policies which decides the way we want to display the prepinned apps in Microsoft Teams pane.

To create custom one navigate to setup policies and click on Add

We do have further customization of the default apps or remove them and add more custom applications.

In the policy there is option to select the appropriate app permission policies which makes the default policy not affected and apply only for targeted users.

Assigning the App Permission policies and Setup Policies to end users.

Having the policy created now it is easier to assign the custom policy to targeted users.

Navigate to users tab – select policies tab – Now we have option to assign custom app permission and app setup policy.

End user Experience –

Once the policy is assigned we have the custom apps side-loaded in Microsoft Teams.

With these above options Application arrangement strategies can be improved and modified dependent on the business prerequisites, integrated with Microsoft Teams and rolled out to the end users.

Thanks & Regards

Sathish Veerapandian

%d bloggers like this: