Automating the Management of Checked-out Projects in Project Server

If you’re a Project Server administrator, you’ve probably had to deal with Projects that seem to remain checked out for a long time.  Sometimes this could be the result of a check-in failure and sometimes Project Manager forgetfulness, but whatever the cause, as an admin you have to periodically force check-in those Projects for good housekeeping.  Checking the checked-out Projects is not on my list of favourite to-do’s, so I decided to write a Powershell script to automate the task.

This script will check the Project Server draft database for any Projects that are currently checked-out.  If the checkout date is greater than the number of days past that we define (default 7), then we email the person who has the Project checked out and ask them to check it back in again.  If the checkout date is greater than the second defined number of days (default 28), then we force the check-in of the Project and advise the user who had it checked out that we have done so.  Then we simply run the script once a week using a Windows Scheduled Task, and our housekeeping is now automated 🙂

Download

Download the script from the Technet Gallery.

Permissions

The account that runs the script needs to have read-access to the Project Server draft database, and also needs to be able to check in Projects.  The PSI Web Services are used to check Projects in.

Variables

There are some variables at the top of the script that should be set for your environment:

$smtpserver: The name of your smtp server for email sending
$From: The email address you want to send from
$admin: The email address of the admin who receives bcc copies of each email sent
$datasource: The SQL server and instance name
$database: The name of your Project Server Draft database
$ProjectServerURL: the URL of your PWA instance

# Mail server info
$smtpserver = "mysmtpserver"
$From = "ProjectServer@contoso.com"
$admin = "Iam.admin@contose.com"

# Database info
$dataSource = “sqlserver\instance”
$database = “PS_2010_PWA_DRAFT_90_DB”

# Project Server PWA URL
$ProjectServerURL = "http://project/PWA"

Parameters

The script has two optional parameters:

-DaysUntilEmailUser

This is the number of days the Project needs to be checked out for before we send an email to the person who has it checked out, asking the to check it in again.  The default is 7.

-DaysUntilForceCheckin

This is the number of days the Project needs to be checked out for before we force the check-in. The default is 28.

Verbose output is also supported which returns each action being taken on the Project plans to the console.

.\ Handle-CheckedoutProjects.ps1 -DaysUntilEmailUser 5 -DaysUntilForceCheckin 10 -verbose

The script has a help file for reference.

Scheduled Task

To automate this process, simply create a scheduled task in Windows, using the action ‘Start a Program’:

Program: Powershell.exe
Arguments: -ExecutionPolicy Bypass -File “<path to script>\Handle-CheckedoutProjects.ps1”

Example Emails

Capture

Capture2

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’:

CaptureBe 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:

Capture2