Using PowerShell to Check DataWarehouse Job Module Status

  • Article

I have had many times where I would want to see what is going on with all the ETL jobs, MPSync, DWMaitenance and the cubes for ServiceManager.  So to make it easier I created this simple PowerShell script to go through all the jobs and their modules inside and the count in each status.  It's a very simple script but has helped me many times when I am troubleshooting ETL issues.

import-module "C:\Program Files\Microsoft System Center 2012\Service Manager\Microsoft.EnterpriseManagement.Warehouse.Cmdlets.psd1"

$DWMGMT = "localhost"

$jobs = get-scdwjob -computername $DWMGMT

 

foreach ($job in $jobs)

    {

        $jobmodule = get-scdwjobmodule -jobname $job.name -computername $DWMGMT

        $completedjobs = $jobmodule | ? {$_.Status -eq "Completed" -and $_.jobname -eq $job.name}

        $runningjobs = $jobmodule | ? {$_.Status -eq "Running" -and $_.jobname -eq $job.name}

        $notstartedjobs = $jobmodule | ? {$_.Status -eq "Not Started" -and $_.jobname -eq $job.name}

        $waitingjobs = $jobmodule | ? {$_.Status -eq "Waiting" -and $_.jobname -eq $job.name}

        $failedjobs = $jobmodule | ? {$_.Status -eq "Failed" -and $_.jobname -eq $job.name}

        write-host "Completed Jobs: " $completedjobs.count "`t" "`t" "Running Jobs: "$runningjobs.count "`t" "Not Started Jobs: "$notstartedjobs.count "`t" "Waiting Jobs:" $waitingjobs.count "`t" "Failed Jobs:" $failedjobs.count "`t" $job.name

    }

Below is the output in PowerShell ISE