Powershell to access the screen Job Activity Monitor

Reply

Brett Posted: 10-10-2011 7:55 AM

rated by 0 users

Hello All !

I have literally just joined this forum to post the follow query....

Having spent a long time Googling I've drawn a blank so hopefully somebody here can help me...

I wish to use Powershell to query the "Job Activity Monitor" screen

1) Is this possible ?
2) Are there any pre-requisites e.g. import modules ?
3) Can somebody point me in the general direction...I particularly need to query the "Status" column

many thanks!

Brett

Reply

Richard Giles replied on 10-10-2011 11:12 AM

rated by 0 users

You can use SMO to get the information you need. SQL Server only ships PowerShell snapins in 2008, not Modules, and the cmdlets and provider in the snapins are fairly limited.

Here is a code snippet to give you an idea on how to get started:

#Load SMO assemblies
[void][reflection.assembly]::LoadWithPartialName( "Microsoft.SqlServer.Smo" );

$namedInstance = new-object('Microsoft.SqlServer.Management.Smo.server') ("Server01\Instance1")
$jobs = $namedInstance.jobserver.jobs | where-object {$_.isenabled}

# Process all SQL Agent Jobs looking for failed jobs based on the last run outcome
# Job object is documented here:
#  http://msdn.microsoft.com/en-us/library/microsoft.sqlserver.management.smo.agent.job.aspx
foreach ($job in $jobs) 
{
    #Get status
    #SqlSmoState is documented here: 
    # http://msdn.microsoft.com/en-us/library/microsoft.sqlserver.management.smo.sqlsmostate.aspx 
    $status = $job.State
}