Azure自动化PowerShell Runbook中提取AAS表RefreshedTime方法

问题背景 #

需要读取AAS表格的最后刷新时间，若当日刷新成功则发送邮件。通过MDX查询调用Invoke-ASCmd获取到XML格式输出，但尝试转为XML后提取RefreshedTime失败。

原PowerShell脚本 #

$AzureCred = Get-AutomationPSCredential -Name "AzOps.Azure.Group.PROD.EDAP.SA"

$database = "SCP"
$Query = @"
SELECT RefreshedTime
FROM $SYSTEM.TMSCHEMA_PARTITIONS
where TableID=10
"@

$result=(Invoke-ASCmd ` -ServicePrincipal ` -Credential $Azurecred ` -TenantId "95e66ecc-f2c2-464b-84d9-8fda407bc923" ` -Server "asazure://westeurope.asazure.windows.net/asedapprodscp" ` -Database "$database" ` -Query $Query ` -Confirm:$False)

echo $result

Invoke-ASCmd返回的XML输出 #

<return xmlns="urn:schemas-microsoft-com:xml-analysis"><root xmlns="urn:schemas-microsoft-com:xml-analysis:mddataset" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema"><xsd:schema targetNamespace="urn:schemas-microsoft-com:xml-analysis:rowset" xmlns:sql="urn:schemas-microsoft-com:xml-sql" elementFormDefault="qualified" xmlns:xsd="http://www.w3.org/2001/XMLSchema"><xsd:element name="root"><xsd:complexType><xsd:sequence minOccurs="0" maxOccurs="unbounded"><xsd:element name="row" type="row" /></xsd:sequence></xsd:complexType></xsd:element><xsd:simpleType name="uuid"><xsd:restriction base="xsd:string"><xsd:pattern value="[0-9a-zA-Z]{8}-[0-9a-zA-Z]{4}-[0-9a-zA-Z]{4}-[0-9a-zA-Z]{4}-[0-9a-zA-Z]{12}" /></xsd:restriction></xsd:simpleType><xsd:complexType name="xmlDocument"><xsd:sequence><xsd:any /></xsd:sequence></xsd:complexType><xsd:complexType name="row"><xsd:sequence><xsd:element sql:field="RefreshedTime" name="RefreshedTime" type="xsd:dateTime" minOccurs="0" /></xsd:sequence></xsd:complexType></xsd:schema><row xmlns="urn:schemas-microsoft-com:xml-analysis:rowset"><RefreshedTime>2023-10-12T09:57:32.556667</RefreshedTime></row></root></return>

失败的修改脚本 #

$AzureCred = Get-AutomationPSCredential -Name "AzOps.Azure.Group.PROD.EDAP.SA"

$database = "SCP"
$Query = @"
SELECT RefreshedTime
FROM $SYSTEM.TMSCHEMA_PARTITIONS
where TableID=10
"@

$result=[xml](Invoke-ASCmd ` -ServicePrincipal ` -Credential $Azurecred ` -TenantId "95e66ecc-f2c2-464b-84d9-8fda407bc923" ` -Server "asazure://westeurope.asazure.windows.net/asedapprodscp" ` -Database "$database" ` -Query $Query ` -Confirm:$False)

echo $result.return.root.row.Name

问题原因 #

1. 节点访问路径错误：脚本中尝试访问$result.return.root.row.Name，但实际需要提取的是RefreshedTime节点，而非Name节点。
2. XML命名空间未处理：返回的XML中，row和RefreshedTime节点属于urn:schemas-microsoft-com:xml-analysis:rowset命名空间，直接通过.访问会因为命名空间不匹配无法找到目标节点。

解决方案 #

以下两种方法均可正确提取RefreshedTime值：

方法一：使用Select-Xml处理命名空间 #

$AzureCred = Get-AutomationPSCredential -Name "AzOps.Azure.Group.PROD.EDAP.SA"

$database = "SCP"
$Query = @"
SELECT RefreshedTime
FROM $SYSTEM.TMSCHEMA_PARTITIONS
where TableID=10
"@

# 获取并转换XML结果
$resultXml = [xml](Invoke-ASCmd `
    -ServicePrincipal `
    -Credential $Azurecred `
    -TenantId "95e66ecc-f2c2-464b-84d9-8fda407bc923" `
    -Server "asazure://westeurope.asazure.windows.net/asedapprodscp" `
    -Database "$database" `
    -Query $Query `
    -Confirm:$False)

# 定义XML命名空间映射
$namespaceMap = @{
    rs = "urn:schemas-microsoft-com:xml-analysis:rowset"
}

# 通过XPath定位目标节点并提取值
$refreshedTimeNode = Select-Xml -Xml $resultXml -XPath "//rs:row/rs:RefreshedTime" -Namespace $namespaceMap
$refreshedTime = $refreshedTimeNode.Node.InnerText

# 输出提取的时间
Write-Output "最后刷新时间: $refreshedTime"

# 判断是否为当日刷新并执行后续逻辑
$today = Get-Date -Format "yyyy-MM-dd"
if ($refreshedTime.StartsWith($today)) {
    # 此处添加发送邮件的代码逻辑
    Write-Output "今日已完成刷新，将发送通知邮件"
} else {
    Write-Output "今日未刷新"
}

方法二：使用local-name()忽略命名空间直接定位 #

$AzureCred = Get-AutomationPSCredential -Name "AzOps.Azure.Group.PROD.EDAP.SA"

$database = "SCP"
$Query = @"
SELECT RefreshedTime
FROM $SYSTEM.TMSCHEMA_PARTITIONS
where TableID=10
"@

$resultXml = [xml](Invoke-ASCmd `
    -ServicePrincipal `
    -Credential $Azurecred `
    -TenantId "95e66ecc-f2c2-464b-84d9-8fda407bc923" `
    -Server "asazure://westeurope.asazure.windows.net/asedapprodscp" `
    -Database "$database" `
    -Query $Query `
    -Confirm:$False)

# 通过local-name()匹配节点名称，避开命名空间限制
$rowNode = $resultXml.SelectSingleNode("//*[local-name()='row']")
$refreshedTime = $rowNode.SelectSingleNode("./*[local-name()='RefreshedTime']").InnerText

Write-Output "最后刷新时间: $refreshedTime"

# 当日刷新判断逻辑同上
$today = Get-Date -Format "yyyy-MM-dd"
if ($refreshedTime.StartsWith($today)) {
    Write-Output "今日已刷新，准备发送邮件"
}

内容的提问来源于stack exchange，提问作者Sachi Saxena