Read output from InvokeAscmd in azure automation PowerShell runbook

Question

I want to read the output of last refreshed time of a AAS tabular table and then send an email if the refresh was successful on the current day.

Below MDX query is being used to fetch the same

While running the same Query through below powershell script -

$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

Following is the output -

<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>

How do I fetch the RefreshedTime from this ?? Tried converting output to XML as well getting below - updated Script -

$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

Output -

Answer 1

Your workaround is valid till you convert it into XML. However, when retrieving refreshed time, you need to capture it as $result.root.row.RefreshedTime. It gives you access to and displays the Refreshed Time value as shown.

$TenantId= "xxxxx"
$Password = ConvertTo-SecureString "xxxx" -AsPlainText -Force
$AplicationId ="xxxxx"
$psCred = New-Object System.Management.Automation.PSCredential($AplicationId , $Password)
$database = "xxxdata"
$Query = @"
SELECT Refreshedtime
FROM $SYSTEM.table_jahn
where TableID=1
"@
$result=[xml](Invoke-ASCmd -ServicePrincipal -Credential $psCred -TenantId $TenantId -Server "jahnserver.database.windows.net" -Database $database -Query $Query -Confirm:$False)
$refreshedTime = $result.root.row.Refreshedtime
Write-Host "$refreshedTime"

Try the same script in Automation runbook to achieve your requirement.