☰

以非管理员用户身份将员工时区从 AzureAD 返回到 Excel

tag-icon • tag-icon microsoft-excel powershell power-query time-zone azure-activedirectory
以非管理员用户身份将员工时区从 AzureAD 返回到 Excel

如何通过查询检索用户所在地时区作为 UTC 偏移量，并考虑当前 DST 设置？

我是普通的非管理员用户。我使用 Power Query* 将我们的员工列表从 AzureAD 检索到 Excel 中并执行一些操作。

*这种事： 在此处输入图片描述 不需要 Power Query - 这只是我的想法。但最终结果必须在 Excel 中。

为什么？我们的公司是全球性的。出于许多原因，尤其是基于 Excel 的调度公式，我需要知道它们的 UTC 偏移量。（我不在格林威治标准时间工作。好的了解他们的时区姓名，但为了进行计算，我仍然需要知道他们的时区。）

哪个时区？家庭位置。当前 TZ 变化太大。

夏令时偏移量怎么样？为了“鲍勃”的缘故，我们可以废除夏令时吗？在我们废除之前，是的，我需要知道与 UTC 的净差值。（呃……什么？我住在东部时间。夏令时在夏季适用。我的官方偏移量是 UTC-5，但由于夏令时，我目前的偏移量是 UTC-4。）我不要想要跟踪每个人的 DST 日期规则。微软的一些好心人已经这样做了；我想从他们的创伤中受益。

因此，我知道查询结果在夏令时变更期间总会略有误差。在理想情况下，结果将包含 UTC 偏移量以及其所在地区的夏令时开始/结束日期，我会计算出计算结果，以了解会议当天的时差。（天哪，夏令时……快消失吧。）

为什么不直接使用 Excel 的地理数据类型来针对他们的城市/国家？完全不够。它失败的原因有很多：

  1. 无论 Excel 从哪个神秘的在线数据源获取此信息，该城市都必须足够大才能包含时区条目。
  2. 一个城市可以有多个时区结果。（确实如此。）
  3. Excel 365 使用 #SPILL 方法将它们作为数组返回，这在需要单一结果的公式中并不适用。另一方面，员工只位于一个时区。
  4. 我们的数据是由……人类输入的。这太糟糕了。有时城市字段是一个城市，有时是一个地区，有时是一个公寓楼名称（！），有时是空的。这是我要处理的问题。但这是这种方法行不通的现实原因。

顺便说一句 - 很高兴使用 PowerShell。

我有点惊讶这竟然如此具有挑战性。

答案1

不幸的是，AzureAD 唯一“自动”存储时区的地方是作为该用户邮箱设置的一部分，用户使用 Outlook 客户端手动设置。从 AzureAD 请求此信息需要特定权限 - 至少MailboxSettings.Read

可以使用 MS Graph API 以多种不同的方式导入此信息。以下是使用该模块针对特定用户时区的 powershell 请求Microsoft.Graph.Authentication：

# Getting the time zone for a specific user 
Connect-MgGraph -Scopes MailboxSettings.Read
$userID = [email protected]
$UserTZ = Invoke-MgGraphRequest -Method GET -ContentType "application\json" -Uri "https://graph.microsoft.com/v1.0/users/$userID/mailboxSettings/TimeZone"

# TZ stored as string in Windows format by default, like 'Eastern Standard Time'
# to convert the time zone text to UTC offset:
[System.TimeZoneInfo]::FindSystemTimeZoneById($UserTZ.value)

# outputs:
Id                         : Eastern Standard Time
DisplayName                : (UTC-05:00) Eastern Time (US & Canada)
StandardName               : Eastern Standard Time
DaylightName               : Eastern Daylight Time
BaseUtcOffset              : -05:00:00
SupportsDaylightSavingTime : True

我对 Excel 不太熟悉，无法在那里执行相同的步骤，但是本文描述如何使用 OData 源数据连接导入 ms 图形数据。

获取相同数据的另一种方法是连接到 Exchange 并从那里的邮箱获取时区设置。此方法仅适用于 powershell，并且需要

  • 您至少是View-Only Organization ManagementExchange 中角色组的成员
  • 您的 O365 用户已启用远程 powershell（默认情况下为 true）
  • 您安装Exchange在线管理模块

然后Get-MailboxRegionalConfiguration对于每个用户来说就像这样：

Connect-ExchangeOnline

Get-MailboxRegionalConfiguration -Identity [email protected] | 
  Select Identity,TimeZone

# outputs
Identity     TimeZone             
--------     --------             
John Smith   Eastern Standard Time

相关内容