Monitoring the Project Server Job Queue with PowerShell

!UPDATE! 20-Jan-2015.  Updated the SQL query to also query for jobs that do not have a completion date, as these can also indicate a problem with the queue.

Recently we had an issue with our Project Server where there were some jobs stuck in the queue that were preventing other jobs, such as Project checkins, from being processed.  Since I was on vacation at the time, I was not aware of the issue until several days after the problem occurred.  Even when I’m in the office, I don’t make a habit of checking the Project Server queue as most of the time it is working fine.  But on the odd occasion it doesn’t, it can be a pain!  There is no built-in method of monitoring the queue, which seems to me to be a major oversight, but nevertheless, it was a good excuse to brush up on some scripting 😉  I decided to write a Powershell script that checks the Project Server queue jobs and sends me an email when there are a certain number of ‘unsuccessful’ jobs.  By ‘unsuccessful’ I mean any job that is not in the ‘success’ state, such as failed jobs, or those that are blocked due to another failed job.  The script can be run as a scheduled task using any schedule you specify, to provide continuous ‘monitoring’ of the queue.  This way you can be notified of any potential problems with the queue without having to make manual checks in PWA.

The recommended way to programmatically check the queue is to use the PSI Web Services, but I decided instead to query the Project Server database directly instead, since I already have some Powershell code which I use for querying SQL server.  The script was created for use with Project Server 2010, but may work with other versions too.

Download the Script

You can download the script from the Technet gallery here.

What Does the Script Do?

First, we open a connection to the draft Project Server database, and run a query against a view called dbo.MSP_QUEUE_PROJECT_GROUP_FULL_VIEW.  This SQL view contains most of the information we need from the queue, although we need to use the query to ‘translate’ some of the fields into something more meaningful, including the queue state and message type codes.  We then check the total number of unsuccessful jobs in the queue in the time period we have specified.  If that number is greater than the minimum, we create and send an html-formatted email with the results of the query.

Note that we are only checking for Project jobs in the queue, we are not checking for Timesheet jobs.

Configuring the Script

To use the script in your own environment, you will need to configure a few parameters at the top of the script, including your SQL database server and instance, the database name (use the draft database), your smtp server name, email recipient address, email from address.  You can also change the location of the temp file created for preparing the email text if you wish.  This file gets deleted at the end of the script.

# Database info
$dataSource = “mysqlserver\myinstance”
$database = “PS_2010_PWA_DRAFT_DB”

# Mail settings
$smtpserver = "mysmtpserver"
$MailSubject = "Project Server Queue Unsuccessful Jobs"
$MailRecipients = "joe.bloggs@mycompany.com"
$FromAddress = "ProjectServer@mycompany.com"

# Location of temp file for email message body (will be removed after)
$msgfile = 'c:\temp\mailmessage.txt'

Security

To run the script, you will need to do so with an account that has at least db_datareader access to the draft PS database.  If you wish to use your own account for that, or the account that will be running the scheduled task, then the script will work as-is, but if you want to use a specific SQL account, then you will need to comment line 110, uncomment line 112, and enter the SQL credentials in the in the connection string (uid and pwd).

# ConnectionString for integrated authentication
$connectionString = “Server=$dataSource;Database=$database;Integrated Security=SSPI;”
# ConnectionString for SQL authentication
#$connectionString = "Server=$dataSource;Database=$database;uid=ProjServer_Read;pwd=Pa$$w0rd;Integrated Security=false"

Schedule the Script

Create a new scheduled task and enter the action as ‘Start a program’:

Be sure to use double quotes when specifying the file name of the script, eg

-file “C:\Scripts\Powershell\Monitor-ProjectServerQueue.ps1”

Set the schedule of your choice (I run it every hour).

Parameters

The script will accept two non-mandatory parameters, and these are documented in the script’s help file:

-TimeInHours

The number of hours past in which to check the queue jobs.  The default is the last 24 hours.

-MinJobCount

The minimum number of unsuccessful jobs.  If the number of unsuccessful jobs reaches this number or higher, an email will be sent.  The default is 5.

The Result

This is an example email telling me there are 3 unsuccessful jobs in the queue: