Quick and Dirty Project Server Forensics: Rummaging in the Archive Attic

Disclaimer: What I am about to discuss is bad, and wrong, and should never, ever be done.  I would never encourage anyone to do this, nor would I ever do this myself.  Hence, this post is entirely hypothetical in nature.

(Weird mornings tend to beget weird ideas.)

Let’s say that hypothetically you’re working in an organization that has had a less than smooth transition to the world of centralized scheduling.  Perhaps the initial implementation didn’t go very well, or the network infrastructure wasn’t quite up to the task, or you were just plagued with bad luck.  In these kinds of environments, it becomes quite common to begin blaming the system for any data issues – and not pinpointing specific user behavior.

I’m not saying the system isn’t at fault.  I am saying however, that people always tend to blame the system first, and as a result, our hypothetical system administrator has to spend a fair amount of time reviewing older versions of existing schedules to verify that “Yes, that task has never been marked as completed….it probably didn’t get completed 3 months ago and then spontaneously revert to 67% complete last Friday,” and other queries of that nature.

Performing these sorts of forensic explorations can be time consuming and also risky, as they often involve restoring archived copies of the project over the live version.  (Other options would be periodically to do a bulk export of your project schedules to an offline repository using the macro I documented here.)

One more option would be to directly query the Archive database (on-premises installations only).  I wouldn’t recommend doing so to support serious reporting, but if an administrator were to say, spill a cup of coffee on their desk, and in the process of mopping it up, accidentally triggered a query against the Archive database, why, that wouldn’t be so bad, would it?

Hence, assuming you actually have scheduled backups running, which most organizations tend to, the following query (or a variation thereof) will net you the status of specific tasks in each of the backed up project versions:

USE ProjectServer_Archive

SELECT P.PROJ_NAME, T.TASK_UID, T.TASK_NAME, 
T.TASK_START_DATE, T.TASK_PCT_COMP, 
P.PROJ_VERSION_DATE, P.PROJ_SESSION_DESCRIPTION
FROM dbo.MSP_TASKS T INNER JOIN
dbo.MSP_PROJECTS P ON T.PROJ_UID = P.PROJ_UID
ORDER BY P.PROJ_NAME, P.PROJ_VERSION_DATE DESC
Advertisements
Quick and Dirty Project Server Forensics: Rummaging in the Archive Attic

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s