Editing Locale and Timezone Settings for Office 365 Groups using PowerShell

Problem definition:

  • Files created under Office 365 Groups or OneDrive for Business exhibit PST timezone formatting. You can replicate this by creating an Excel document and typing 1/12, which is then auto-formatted by Excel to say 12th Jan. In the UK, we mean 1st Dec.
  • This timezone issue occurs even if Groups are created with en-GB as the locale.

The following code snippet allows you to programmatically edit the Locale and Timezone for an Office 365 Group using PowerShell. It requires you to be an owner on the group. It may also be adapted to edit a user’s personal site (see bottom of article).

Prerequisites:

Solution:

# Define required variables:
$Office365GroupURL = 'https://tenancyname.sharepoint.com/sites/YourO365Group'
$TimeZoneID = 2
$LocaleID = 2057

# Specify Office 365 UPN with group (site) ownership permissions:
$User = "user@yourdomain.com"
$Password = Read-Host -Prompt "Please enter your password" -AsSecureString

# Add references to SharePoint client assemblies:
Add-Type -Path "C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\16\ISAPI\Microsoft.SharePoint.Client.dll"
Add-Type -Path "C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\16\ISAPI\Microsoft.SharePoint.Client.Runtime.dll"
Add-Type -Path "C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\16\ISAPI\Microsoft.SharePoint.Client.UserProfiles.dll"

# Create SharePointOnlineCredentials class object:
$Creds = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($User,$Password)

# Perform the work:
$Context = New-Object Microsoft.SharePoint.Client.ClientContext($Office365GroupURL)
$Context.Credentials = $Creds
$Context.ExecuteQuery()
$Context.Web.RegionalSettings.LocaleId = $LocaleID
$Context.Web.RegionalSettings.TimeZone = $Context.Web.RegionalSettings.TimeZones.GetById($TimeZoneID)
$Context.Web.Update()
$Context.ExecuteQuery()

Notes:

  • This works against personal sites, using the personal site URL. An example might be: https://tenancyname-my.sharepoint.com/personal/username_domain_com
  • Still investigating how to perform this operation as an administrator against sites (possibly using Set-SPOSite to add an owner, then removing afterwards).

VMware Log Insight – Duplicate Hosts Listed

After returning from a VMware Design course and having learned that Log Insight was now free to valid vCenter licence holders, I set straight to work deploying it into our development environment. However, despite having under 25 hosts Log Insight soon started complaining about exceeding the allowed 25 OSI limit. ESXi hosts were being displayed in the statistics with both their defined hostname, but also what appeared to be their FQDN (e.g. vmhosta-01 and vmhosta-01.domain.com).

Checking on the hosts themselves (esxcli system hostname get) there was no domain or FQDN set, but they do have FQDNs in DNS, and vCenter knows them by their FQDN.

To fix the issue, I made things consistent by setting the domain and FQDN on all hosts using PowerCLI:

$vmhosts | Get-VMHostService | Where-Object -FilterScript { $_.key -eq 'TSM-SSH' } | Start-VMHostService
$vmhosts | % {
    $esxcli = Get-EsxCli -VMHost $_ 
    $esxcli.system.hostname.set($null,$($_.name),$null) 
    Start-Sleep -Seconds 3 
    $esxcli.system.hostname.get()
}
$vmhosts | Get-VMHostService | Where-Object -FilterScript { $_.key -eq 'TSM-SSH' } | Stop-VMHostService

You can verify whether the change has helped quite easily, by visiting: https://your-loginsight/admin/hosts – the ‘Last Received Event’ for hosts without FQDNs simply growing greater and greater. After 12 hours, the ‘Average Active OSIs’ (as reported by https://your-loginsight/admin/license) had dropped slightly, but it’s got a way to go before being under 25.

PowerCLI 6.0 and PSModulePath

This article is to look at the problems with PSModulePath when installing PowerCLI 6.0 on Windows 10 (I’m aware that it is not yet certified for Win 10). The problem does not occur with the NetApp Data ONTAP PowerShell Toolkit (as noted at the bottom).

On a clean installation of Windows 10 we can observe the following behaviour with regard to PSModulePath:

PS C:\Users\DefaultAdmin> [Environment]::GetEnvironmentVariable('PSModulePath') -split ';'
C:\Users\DefaultAdmin\Documents\WindowsPowerShell\Modules
C:\Program Files\WindowsPowerShell\Modules
C:\Windows\system32\WindowsPowerShell\v1.0\Modules\

PS C:\Users\DefaultAdmin> [Environment]::GetEnvironmentVariable('PSModulePath','User') -split ';'

PS C:\Users\DefaultAdmin> [Environment]::GetEnvironmentVariable('PSModulePath','Machine') -split ';'
C:\Windows\system32\WindowsPowerShell\v1.0\Modules\

The first command translates to $env:PSModulePath. When PowerShell loads, it dynamically sets this session only variable equal to:
$home\Documents\WindowsPowerShell\Modules ($home is something like C:\Users\username)
$pshome\Modules ($pshome is the location of PowerShell, typically C:\Windows\System32\WindowsPowerShell\v1.0)
Based on the output above it also appends the contents of the system wide, SYSTEM environment variable for PSModulePath.

The second command retrieves USER environment variable called PSModulePath (available to all users of the system), and as you can see there is none set. If there were one, it would prefixed to $env:PSModulePath (i.e. it would be the first path visible in the output of the first command).

The last command here retrieves the SYSTEM environment variable called PSModulePath. It is also present in the GUI here:
Capture

$env:PSModulePath is a rather important variable that is used not only for querying for available modules, but also module autoloading and tab completion of commands.

Sadly after manually installing PowerCLI (version 6.0.0-3056836) by running the exe as a local administrator as many would do, the behaviour of PowerShell on load is changed (some might call it broken!). Running the same query above we see:

PS C:\Users\DefaultAdmin> [Environment]::GetEnvironmentVariable('PSModulePath') -split ';'
C:\Windows\system32\WindowsPowerShell\v1.0\Modules\

PS C:\Users\DefaultAdmin> [Environment]::GetEnvironmentVariable('PSModulePath','User') -split ';'

PS C:\Users\DefaultAdmin> [Environment]::GetEnvironmentVariable('PSModulePath','Machine') -split ';'
C:\Windows\system32\WindowsPowerShell\v1.0\Modules\
C:\Program Files (x86)\VMware\Infrastructure\vSphere PowerCLI\Modules

The SYSTEM environment variable for PSModulePath is modified, nothing is created for the USER environment variable for PSModulePath, and critically PowerShell no longer performs any customisation of $env:PSModulePath! The paths:
C:\Users\DefaultAdmin\Documents\WindowsPowerShell\Modules
C:\Program Files\WindowsPowerShell\Modules

are now gone.

This post on the VMware Community Forums 7 months ago seems to suggest the behaviour of PowerCLI on Windows 8.1 is different (or perhaps an older version of PowerCLI?):

The PowerCLI 6.0 installer does create a USER PSModulePath variable if one does not already exist.

… we can see at least on Windows 10, under the current version, it does not.

There is advice on the VMware PowerCLI blog under the v6 release which suggests:

#Save the current value in the $p variable.
$p = [Environment]::GetEnvironmentVariable("PSModulePath")

#Add the new path to the $p variable. Begin with a semi-colon separator.
$p += ";C:\Program Files (x86)\VMware\Infrastructure\vSphere PowerCLI\Modules\"

#Add the paths in $p to the PSModulePath value.
[Environment]::SetEnvironmentVariable("PSModulePath",$p) 

The problem with this is that when calling SetEnvironmentVariable there is no target specified so it only updates the local session variable (i.e. $env:PSModulePath). This would therefore be required in every script (not ideal). Also we’ve established that installing PowerCLI has broken the population of this variable somehow. The best workaround is already in the comments; running the above code but targeting the SYSTEM variable for PSModulePath (i.e. [Environment]::SetEnvironmentVariable(“PSModulePath”,$p,’Machine’)).

Now, if I repeat this process after installing the Netapp Data ONTAP PowerShell Toolkit (3.2.1.68) (accepting a UAC prompt), I get the perfect behaviour:

PS C:\Users\DefaultAdmin> [Environment]::GetEnvironmentVariable('PSModulePath') -split ';'
C:\Users\DefaultAdmin\Documents\WindowsPowerShell\Modules
C:\Program Files (x86)\NetApp\Data ONTAP PowerShell Toolkit\
C:\Program Files\WindowsPowerShell\Modules
C:\Windows\system32\WindowsPowerShell\v1.0\Modules\

PS C:\Users\DefaultAdmin> [Environment]::GetEnvironmentVariable('PSModulePath','User') -split ';'

PS C:\Users\DefaultAdmin> [Environment]::GetEnvironmentVariable('PSModulePath','Machine') -split ';'
C:\Program Files (x86)\NetApp\Data ONTAP PowerShell Toolkit\
C:\Windows\system32\WindowsPowerShell\v1.0\Modules\
PS C:\Users\DefaultAdmin>

I think the appropriate clever people at VMware and NetApp need to have a chat with each other :)

Note: If I right click the PowerCLI installer and “Run as Administrator” I get the same behaviour.
Note: The Cisco UCS PowerTool also breaks the PSModule behaviour (using version CiscoUcs-PowerTool-1.5.1.0).

Office 365, PowerShell, SKUs and Service Plans

Working with Office 365 licensing and PowerShell can get a little confusing with SKU names like “STANDARDWOFFPACK_IW_FACULTY” and plan names like “MCOSTANDARD” (which is Skype, of all things!). In order to help reporting and management, I wrote a couple of advanced functions to translate SkuPartNumber and ServicePlanName into friendly names as shown in the Office 365 / O365 Portal. They accept pipeline input. Note the “LU” prefix in the function name; this is our chosen prefix to avoid naming conflicts/overlap with any official cmdlets. I work in Education, so these names are specific to educational plans, but you may adapt them to suit your needs. I’m also happy to add more to the functions if this serves as a useful reference.

function Convert-LUMsolServicePlanName
{
    <#
            .SYNOPSIS 
            This function will convert between the ServicePlanName property relating to an individual service within an Office 365 Licence and vice versa.
            .DESCRIPTION
            This function will convert between the ServicePlanName property relating to individual services within an Office 365 Licence and vice versa.
            It allows you to obtain a user friendly name (as viewed in the O365 Portal) when passed a "backend" property value, and also pass a friendly name value
            back to get the value for using in scripting licensing.
            .PARAMETER ServicePlanName
            .EXAMPLE 
            Get-MsolAccountSku | Select @{Name="ServicePlanName";Expression={$_.ServiceStatus.ServicePlan.ServiceName}} | % { $_.ServicePlanName } | Convert-LUMsolServicePlanName
            .EXAMPLE 
            Convert-LUMsolServicePlanName -ServicePlanName "EXCHANGE_S_STANDARD"
            .EXAMPLE 
            Convert-LUMsolServicePlanName -ServicePlanFriendlyName "Exchange Online (Plan 1)"
            .NOTES
            Author: Robin Malik
    #>

    
    [CmdletBinding()]
    Param
    (
        [Parameter(
                Mandatory = $true,
                Position = 0,
                HelpMessage='ServicePlanName from PowerShell query, e.g. "ONEDRIVESTANDARD"',
                ValueFromPipeline = $true,
                ValueFromPipelineByPropertyName = $true,
        ParameterSetName = 'ByServicePlanName')]
        $ServicePlanName,

        [Parameter(
                Mandatory = $true,
                Position = 0,
                HelpMessage='Friendly name of a plan, e.g. "OneDrive for Business (Plan 1)"',
        ParameterSetName = 'ByFriendlyName')]
        $ServicePlanFriendlyName
    )

    Begin
    {
        Write-Verbose -Message "$(Get-Date -Format `"dd/MM/yyyy HH:mm:ss`"): Begin block."
        Write-Verbose -Message "$(Get-Date -Format `"dd/MM/yyyy HH:mm:ss`"): Parameter set triggered: $($PSCmdlet.ParameterSetName)"
    }
    Process
    {
        Write-Verbose -Message "$(Get-Date -Format `"dd/MM/yyyy HH:mm:ss`"): Process block."
        $ServicePlanFriendlyNameHashtable = @{
            'BI_AZURE_P0'              = 'Power BI' # This AccountSku doesn't show a 'friendly' service plan name in the portal
            'BI_AZURE_P2'              = 'Power BI Pro'
            'CRMSTANDARD'              = 'Microsoft Dynamics CRM Online Professional'
            'EXCHANGE_S_STANDARD'      = 'Exchange Online (Plan 1)'
            'INTUNE_O365'              = 'Mobile Device Management for Office 365'
            'MCOSTANDARD'              = 'Skype for Business Online (Plan 2)'
            'MDM_SALES_COLLABORATION'  = 'Microsoft Dynamics Marketing Sales Collaboration'
            'NBPROFESSIONALFORCRM'     = 'Microsoft Social Engagement Professional'            
            'OFFICESUBSCRIPTION'       = 'Office 365 ProPlus'
            'OFFICE_FORMS_PLAN_2'      = 'Forms (Plan 2)'
            'ONEDRIVESTANDARD'         = 'OneDrive for Business (Plan 1)'
            'PROJECTWORKMANAGEMENT'    = 'Planner' # This AccountSku doesn't show a 'friendly' service plan name in the portal
            'SHAREPOINT_PROJECT_EDU'   = 'Project Online for Education'
            'SHAREPOINTENTERPRISE_EDU' = 'SharePoint Plan 2 for EDU'
            'SHAREPOINTSTANDARD_EDU'   = 'SharePoint Plan 1 for EDU'
            'SHAREPOINTWAC_EDU'        = 'Office Online for Education'
            'SWAY'                     = 'Sway'
            'YAMMER_EDU'               = 'Yammer for Academic'
        }

        if($ServicePlanName)
        {
            return $ServicePlanFriendlyNameHashtable["$ServicePlanName"]
        }

        if($ServicePlanFriendlyName)
        {
            # Look up by value:
            return ($ServicePlanFriendlyNameHashtable.GetEnumerator() | Where-Object -FilterScript {
                    $_.Value -eq "$ServicePlanFriendlyName" 
            }).Name  
        }
    }
    End
    {
        Write-Verbose -Message "$(Get-Date -Format `"dd/MM/yyyy HH:mm:ss`"): End block."
    }
}

function Convert-LUMsolAccountSkuName
{
    <#
            .SYNOPSIS 
            This function will convert between the SkuPartNumber property value in a Get-MsolAccountSku (AccountSkuDetails) object to a friendly SKU/licence name, and vice versa.
            .DESCRIPTION
            This function will convert between the SkuPartNumber property value in a Get-MsolAccountSku (AccountSkuDetails) object to a friendly SKU/licence name, and vice versa.
            It allows you to obtain a user friendly name for the SKU (as viewed in the O365 Portal) when passed a "backend" property value, and also pass a friendly name value
            back to get the value for using in scripting licensing.
            .PARAMETER SkuPartNumber
            .EXAMPLE 
            Get-MsolAccountSku | Select -ExpandProperty SkuPartNumber | Convert-LUMsolAccountSkuName
            .EXAMPLE 
            Convert-LUMsolAccountSkuName -ServicePlanName "STANDARDWOFFPACK_FACULTY"
            .EXAMPLE 
            Convert-LUMsolAccountSkuName  -ServicePlanFriendlyName "Office 365 Education for Faculty"
            .NOTES
            Author: Robin Malik
    #>

    
    [CmdletBinding()]
    Param
    (
        [Parameter(
                Mandatory = $true,
                Position = 0,
                HelpMessage = 'ServicePlanName from PowerShell query, e.g. "OFFICESUBSCRIPTION_STUDENT"',
                ValueFromPipeline = $true,
                ValueFromPipelineByPropertyName = $true,
        ParameterSetName = 'BySkuPartNumber')]
        $SkuPartNumber,

        [Parameter(
                Mandatory = $true,
                Position = 0,
                HelpMessage = 'Friendly name of a plan, e.g. "Office 365 ProPlus for Students"',
        ParameterSetName = 'BySkuFriendlyName')]
        $SkuPartNumberFriendlyName
    )

    Begin
    {
        Write-Verbose -Message "$(Get-Date -Format `"dd/MM/yyyy HH:mm:ss`"): Begin block."
        Write-Verbose -Message "$(Get-Date -Format `"dd/MM/yyyy HH:mm:ss`"): Parameter set triggered: $($PSCmdlet.ParameterSetName)"
    }
    Process
    {
        Write-Verbose -Message "$(Get-Date -Format `"dd/MM/yyyy HH:mm:ss`"): Process block."
        $SkuPartNumberFriendlyNameHashtable = @{
            'CRMSTANDARD'                  = 'Microsoft Dynamics CRM Online Professional'            
            'OFFICESUBSCRIPTION_FACULTY'   = 'Office 365 ProPlus for Faculty'
            'OFFICESUBSCRIPTION_STUDENT'   = 'Office 365 ProPlus for Students'
            'PLANNERSTANDALONE'            = 'Office 365 Planner'
            'POWER_BI_STANDARD_FACULTY'    = 'Power BI (free) for faculty'
            'POWER_BI_PRO_FACULTY'         = 'Power BI Pro for faculty'
            'PROJECTONLINE_PLAN_1_FACULTY' = 'Project Online for Faculty'
            'STANDARDWOFFPACK_FACULTY'     = 'Office 365 Education for Faculty'
            'STANDARDWOFFPACK_IW_FACULTY'  = 'Office 365 Education Plus for Faculty'
            'STANDARDWOFFPACK_STUDENT'     = 'Office 365 Education for Students'
            'STANDARDWOFFPACK_IW_STUDENT'  = 'Office 365 Education Plus for Students'
        }

        if($SkuPartNumber)
        {
            return $SkuPartNumberFriendlyNameHashtable["$SkuPartNumber"]
        }

        if($SkuPartNumberFriendlyName)
        {
            # Look up by value:
            return ($SkuPartNumberFriendlyNameHashtable.GetEnumerator() | Where-Object -FilterScript {
                    $_.Value -eq "$SkuPartNumberFriendlyName"
            }).Name  
        }
    }
    End
    {
        Write-Verbose -Message "$(Get-Date -Format `"dd/MM/yyyy HH:mm:ss`"): End block."
    }
}