none
Who modified the project for the last time? RRS feed

  • Question

  • Can we know who updated/modified the project for the last time. Is there any OOB functionality for the same.
    Thanks & Regards, Benno
    Thursday, March 3, 2011 7:11 AM

Answers

  • Hi Stuart,

    This article outlines the guiding principles - http://technet.microsoft.com/en-us/library/ff686786.aspx, whilst there is no formal 'not supported' statement, the following is stated:

    Only the Reporting database schema is documented. You should access the Draft, Published, and Archive databases only through the PSI. You can add data tables, fields (properties), and entities that are not defined in the Project Server 2010 database schema to the Reporting database. If you add tables to the core databases, you must also provide the full stack of a custom assembly, Web service, business objects, and data access. You can easily modify the Reporting database; we recommend that you do not modify the core Project Server databases.

    There is a potential around direct queries on the database to lock tables or impact performance. If you need data from these tables, it is best to use the PSI via a custom app.

    Hope this helps,


    Alex Burton
    www.epmsource.com | Twitter
    Project Server TechCenter | Project Developer Center | Project Server Help | Project Product Page
    Saturday, March 5, 2011 5:55 PM
    Moderator

All replies

  • There's no field that I am aware of, but it is in the queue management interface.
     I typically like to extend the retention date of the queue data from the
    default 24 hours to watch stuff like that.
     
     

    Andrew Lavinsky [MVP] Twitter: @alavinsky
    Thursday, March 3, 2011 1:44 PM
    Moderator
  • OOB you can add "last published" field in your Project center view to track the last update of the project.

    there is also EPM Report pack released by MS some time back, contains administrative dashboard reports to monitor such activities.

    Hope this helps.

     


    | Khurram Jamshed | Follow my blog about Enterprise Project Management Solution | http://khurramjamshed.blogspot.com |
    Thursday, March 3, 2011 8:46 PM
  • Benno,

    There is a table which keeps the assignment transaction data (and a similar one for timesheet transactions) and you can use this to see what assignments/tasks/projects have been modified and by whom/when.

    As far as I can see, it only logs the data if an assignment has been changed and here is a query that might help. You could put this query into the SQL Server Management studio view designer and add other fields to it quite easily.

    As it stands, it will show you the UID for the transaction, when the transaction was created, the comment (if applicable), who created it and what project/task was affected.

    Run on the _published database:

    SELECT     TOP (100) PERCENT dbo.MSP_ASSIGNMENT_TRANSACTIONS.ASSN_TRANS_UID, dbo.MSP_ASSIGNMENT_TRANSACTIONS.CREATED_DATE,

                          dbo.MSP_ASSIGNMENT_TRANSACTIONS_COMMENTS.ASSN_TRANS_COMMENT, dbo.MSP_RESOURCES.RES_NAME, dbo.MSP_PROJECTS.PROJ_NAME,

                          dbo.MSP_TASKS.TASK_NAME

    FROM         dbo.MSP_ASSIGNMENT_TRANSACTIONS INNER JOIN

                          dbo.MSP_ASSIGNMENT_TRANSACTIONS_COMMENTS ON

                          dbo.MSP_ASSIGNMENT_TRANSACTIONS.ASSN_TRANS_UID = dbo.MSP_ASSIGNMENT_TRANSACTIONS_COMMENTS.ASSN_TRANS_UID INNER JOIN

                          dbo.MSP_RESOURCES ON dbo.MSP_ASSIGNMENT_TRANSACTIONS.ASSN_TRANS_SUBMITTER_RES_UID = dbo.MSP_RESOURCES.RES_UID INNER JOIN

                          dbo.MSP_PROJECTS ON dbo.MSP_ASSIGNMENT_TRANSACTIONS.PROJ_UID = dbo.MSP_PROJECTS.PROJ_UID INNER JOIN

                          dbo.MSP_TASKS ON dbo.MSP_ASSIGNMENT_TRANSACTIONS.TASK_UID = dbo.MSP_TASKS.TASK_UID

    ORDER BY dbo.MSP_ASSIGNMENT_TRANSACTIONS.CREATED_DATE

     

    Friday, March 4, 2011 1:20 AM
  • Benno,

    Please be aware that whilst the query above will work, running queries directly on the published db is not supported.

     


    Alex Burton
    www.epmsource.com | Twitter
    Project Server TechCenter | Project Developer Center | Project Server Help | Project Product Page
    Saturday, March 5, 2011 5:40 AM
    Moderator
  • Alexander makes a good point. Be carefull what you do at the database level.

    Alexander, could you please point me at the document that mentions what is supported and what is not. I was under the impression that updates are not supported but Queries are.

    The reason I ask is because there are many examples of data that are not available in the reporting database but are in the other databases. If we are not able to even query those tables, then what are we to do in order to get at the data?

    Saturday, March 5, 2011 5:40 PM
  • Hi Stuart,

    This article outlines the guiding principles - http://technet.microsoft.com/en-us/library/ff686786.aspx, whilst there is no formal 'not supported' statement, the following is stated:

    Only the Reporting database schema is documented. You should access the Draft, Published, and Archive databases only through the PSI. You can add data tables, fields (properties), and entities that are not defined in the Project Server 2010 database schema to the Reporting database. If you add tables to the core databases, you must also provide the full stack of a custom assembly, Web service, business objects, and data access. You can easily modify the Reporting database; we recommend that you do not modify the core Project Server databases.

    There is a potential around direct queries on the database to lock tables or impact performance. If you need data from these tables, it is best to use the PSI via a custom app.

    Hope this helps,


    Alex Burton
    www.epmsource.com | Twitter
    Project Server TechCenter | Project Developer Center | Project Server Help | Project Product Page
    Saturday, March 5, 2011 5:55 PM
    Moderator
  • Thanks Alex, that provides me with a lot of comfort.

    I have used the PSI extensively and it works well for me but most users will not have the programming skills to do so.

    BTW: It's not only locking the published or draft databases that can cause an issue. I have a custom OLAP cube which refreshes periodically off the reporting database. While I was developing it, I inadvertantly locked up one of the timesheet tables and that caused a reporting synch job to fail.

    Fortunately I had retries on the job but the message is clear to me - locking up tables on any of the databases, even the reporting database can cause problems.

    Thanks for the assistance Alex - Benno, sorry for taking over your thread but I think you  get the picture.

    Saturday, March 5, 2011 6:46 PM
  • Great. Thanks for all the comments
    Thanks & Regards, Benno
    Sunday, March 6, 2011 7:38 AM
  • But Khurram, this pack is for PS 2007 not for PS 2010. :(
    Thanks & Regards, Benno
    Sunday, March 6, 2011 7:47 AM
  • I know, but it will work for PS2010 as well - might require little tweaking with the data source. that also depends which dashboard you want to use.

    Although this post is quite off from the original track now, but the answer to your original query is still the dashboard report (basic or advance level) to monitor such administrative activities.OR you can use last modified and last published fields in your Project center view to monitor such changes.

    the only difference in the above statement is that the later is the OOB offerings for you, and for dashboard you have to do the effort to achieve. (i prefer to use report pack, and alter as per my needs - saves lot of time and effort)

    Hope this helps.


    | Khurram Jamshed | Follow my blog about Enterprise Project Management Solution | http://khurramjamshed.blogspot.com |
    Sunday, March 6, 2011 7:58 AM
  • Thanks a Lot for the swift reply Khurram
    Thanks & Regards, Benno
    Sunday, March 6, 2011 8:00 AM
  • You are more than welcome Benno.
    | Khurram Jamshed | Follow my blog about Enterprise Project Management Solution | http://khurramjamshed.blogspot.com |
    Sunday, March 6, 2011 8:05 AM