This power Shell scripts gives each table processing date on mail .
This power Shell Scripts will fetch the data from Azure analysis services and send email to email id with each cube table process date like below :-
#Install Join Module
#connectionstring = "Provider=MSOLAP;Data Source=<Servername>;Initial Catalog=cube name;User ID=app:XXX;Password=<>.;Persist Security Info=True;Impersonation Level=Impersonate"
$cred = Get-AutomationPSCredential -Name 'aasautomationcred'
$ServerName = "servername"
#$DatabaseNames = "cubename"
$TenantId = "XXX"
$FIRSTDAYOFMONTH=GET-DATE -Format o -Day 1 -Hour 0 -Minute 0 -Second 0 | ForEach-Object { $_ -replace “:”, “.” }
$FIRSTDAYOFMONTH
$table = ''
$tableCubeStruct = ''
$htmlBody = "
<html>
<style>
table, th, td {
border:1px solid black;
}
</style>
<body>
<h1>Monitor Current Status of Cube Processing.<br><br></h1>"
$tablebody = "<table style=`"width:50%`">"
$tableCaption = ''
$tableHeader = "<tr>
<th>TableName</th>
<th>PartitionName</th>
<th>RefreshDate</th>
</tr>"
#$tableNameAndCount = ''
$htmlfooter = "
</body>
</html>"
foreach ($DatabaseName in $DatabaseNames)
{
Write-Output $DatabaseNam
#Read all Tables in the Cubes
[xml]$TableList = Invoke-ASCmd -Server $ServerName -Database $DatabaseName -Credential $cred -Query "select [ID],[Name] AS TableName from `$SYSTEM.TMSCHEMA_Tables"
$lookupList = [PSCustomObject]$TableList.return.root.row | Select-Object -Property ID, TableName
#Read all partition in the Cube
[xml]$XmlPartition = Invoke-ASCmd -Server $ServerName -Database $DatabaseName -Credential $cred -Query "SELECT [ID] AS [PartID],[Name],tableID,ModifiedTime,RefreshedTime FROM `$SYSTEM.TMSCHEMA_PARTITIONS"
$partitionlist = [PSCustomObject]$XmlPartition.return.root.row | Select-Object -Property PartID,Name,tableID,ModifiedTime,RefreshedTime
$finallist = $partitionlist | Group-Object -Property tableID |ForEach-Object{$_.Group | Sort-Object ModifiedTime | Select-Object -Last 1} | Select-Object -Property PartID,Name,tableID,ModifiedTime,RefreshedTime
$TableCount = @($finallist).Count
$tableBodytag = ''
$tableCaption = "<caption style=`"text-align:left`"><b>$($DatabaseName) : $($TableCount)</b></caption>"
$tableBodytag = $tablebody + $tableCaption + $tableHeader
$tableOutput = Join-Object -Left $lookupList -Right $finallist -LeftJoinProperty 'ID' -RightJoinProperty 'tableID' -Type 'OnlyIfInBoth' | Select-Object -Property PartID,Name,ModifiedTime,RefreshedTime,TableName
$tableOutput | ForEach-Object -Process {
if ($_.RefreshedTime -lt $FIRSTDAYOFMONTH){
$table = $table + " <tr>
<td>$($_.TableName)</td>
<td>$($_.Name)</td>
<td bgcolor=`"red`">$($_.RefreshedTime)</td>
</tr>"
}
else{
$table = $table + " <tr>
<td>$($_.TableName)</td>
<td>$($_.Name)</td>
<td bgcolor=`"green`">$($_.RefreshedTime)</td>
</tr>"
} }
$tableCubeStruct = $tableCubeStruct + $tableBodytag + $table +"</table> <br><br>"
$table = ''
}
$ServerName = "second server name if you have "
$DatabaseNames = "cubename"
foreach ($DatabaseName in $DatabaseNames)
{
Write-Output $DatabaseNam
#Read all Tables in the Cubes
[xml]$TableList = Invoke-ASCmd -Server $ServerName -Database $DatabaseName -Credential $cred -Query "select [ID],[Name] AS TableName from `$SYSTEM.TMSCHEMA_Tables"
$lookupList = [PSCustomObject]$TableList.return.root.row | Select-Object -Property ID, TableName
#Read all partition in the Cube
[xml]$XmlPartition = Invoke-ASCmd -Server $ServerName -Database $DatabaseName -Credential $cred -Query "SELECT [ID] AS [PartID],[Name],tableID,ModifiedTime,RefreshedTime FROM `$SYSTEM.TMSCHEMA_PARTITIONS"
$partitionlist = [PSCustomObject]$XmlPartition.return.root.row | Select-Object -Property PartID,Name,tableID,ModifiedTime,RefreshedTime
$finallist = $partitionlist | Group-Object -Property tableID |ForEach-Object{$_.Group | Sort-Object ModifiedTime | Select-Object -Last 1} | Select-Object -Property PartID,Name,tableID,ModifiedTime,RefreshedTime
$TableCount = @($finallist).Count
$tableBodytag = ''
$tableCaption = "<caption style=`"text-align:left`"><b>$($DatabaseName) : $($TableCount)</b></caption>"
$tableBodytag = $tablebody + $tableCaption + $tableHeader
$tableOutput = Join-Object -Left $lookupList -Right $finallist -LeftJoinProperty 'ID' -RightJoinProperty 'tableID' -Type 'OnlyIfInBoth' | Select-Object -Property PartID,Name,ModifiedTime,RefreshedTime,TableName
$tableOutput | ForEach-Object -Process {
if ($_.RefreshedTime -lt $FIRSTDAYOFMONTH){
$table = $table + " <tr>
<td>$($_.TableName)</td>
<td>$($_.Name)</td>
<td bgcolor=`"red`">$($_.RefreshedTime)</td>
</tr>"
}
else{
$table = $table + " <tr>
<td>$($_.TableName)</td>
<td>$($_.Name)</td>
<td bgcolor=`"green`">$($_.RefreshedTime)</td>
</tr>"
} }
$tableCubeStruct = $tableCubeStruct + $tableBodytag + $table +"</table> <br><br>"
$table = ''
}
$htmltext = $($htmlBody) + $($tableCubeStruct) + $($htmlfooter)
#Write-Output $htmltext
#,"emailid","emailid2","emailid3"
$Parameters = @{
FromAddress = "donotreply@notreply.com"
ToAddress = "email address"
Subject = "Important : Production Cube Processing Status"
BodyAsHTML = $htmltext
Token = "SG.XXX"
FromName = "Monitoring"
}
Send-PSSendGridMail @Parameters
