Check Data Warehouse ETL Jobs with PowerShell

Gerhard_Goossens

Good day everyone, we had a situation where some of the data warehouse ETL jobs failed and caused the DW reporting to be outdated.

We use Nagios to monitor our environment, and I decided to create an NRPE PowerShell script to monitor the DW ETL Jobs.

This script can easily be changed to accommodate other needs you may have. It takes one parameter; this parameter is the job you want to check. I am no PS script master, so this might seem a bit patched together :-)

# Set parameters
param($Job=$(throw "You must specify a ETL Job"))
$message = "OK"
$status = "0"
# Imports DW scripts
Import-Module 'C:\Program Files\Microsoft System Center\Service Manager\Microsoft.EnterpriseManagement.Warehouse.Cmdlets.psd1'
# get last run time
[datetime]$lastRun = Get-SCDWJobSchedule -JobName $Job | Format-Table -Property LastRunTime -HideTableHeaders | Out-String | Get-Date
# Get current time
$now=(Get-Date)
# Calculate time diff
$timeDiff = New-TimeSpan -Start $lastRun.AddHours(2) -End $now
# Convert time to integer
$timeString = [int]$timeDiff.TotalMinutes
# Do Case
switch($Job) {
DWMaintenance                                       {if ($timeString -gt 60)   {$status = 1; $message = $Job + " is behind by " + $timeString + " minutes" } }
MPSyncJob                                           {if ($timeString -gt 60)   {$status = 1; $message = $Job + " is behind by " + $timeString + " minutes" } }
Transform.Common                                    {if ($timeString -gt 30)   {$status = 1; $message = $Job + " is behind by " + $timeString + " minutes" } }
Load.OMDWDataMart                                   {if ($timeString -gt 60)   {$status = 1; $message = $Job + " is behind by " + $timeString + " minutes" } }
Load.Common                                         {if ($timeString -gt 60)   {$status = 1; $message = $Job + " is behind by " + $timeString + " minutes" } }
Load.CMDWDataMart                                   {if ($timeString -gt 60)   {$status = 1; $message = $Job + " is behind by " + $timeString + " minutes" } }
Extract_DW_SCSM_NWU                                 {if ($timeString -gt 5)    {$status = 1; $message = $Job + " is behind by " + $timeString + " minutes" } }
Extract_NWUSCSM                                     {if ($timeString -gt 5)    {$status = 1; $message = $Job + " is behind by " + $timeString + " minutes" } }
Process.SystemCenterServiceCatalogCube              {if ($timeString -gt 1440) {$status = 1; $message = $Job + " is behind by " + $timeString + " minutes" } }
Process.SystemCenterChangeAndActivityManagementCube {if ($timeString -gt 1440) {$status = 1; $message = $Job + " is behind by " + $timeString + " minutes" } }
Process.SystemCenterWorkItemsCube                   {if ($timeString -gt 1440) {$status = 1; $message = $Job + " is behind by " + $timeString + " minutes" } }
Process.SystemCenterPowerManagementCube             {if ($timeString -gt 1440) {$status = 1; $message = $Job + " is behind by " + $timeString + " minutes" } }
Process.SystemCenterSoftwareUpdateCube              {if ($timeString -gt 1440) {$status = 1; $message = $Job + " is behind by " + $timeString + " minutes" } }
default {$status = 1; $message = $Job + " could not be checked" }
}
# Nagios Output
$NagiosOutput += " | Behind=$($timeString);;;;"
if ($status -eq 1) {
    Write-Host "WARNING: $message $($NagiosOutput)"
   exit 1
}  
else{
    Write-Host "OK: $Job last run $timeString minutes ago$($NagiosOutput)"
   exit 0
}

Adam_Dzyacky

Nice work @Gerhard_Goossens !