none
How to know 'Who last published project plan' from PS 2010 database?

    Question

  • Hello,

    I got a requirement to list out a number of projects in SSRS report with 'Last Published Date' and 'Who Published Last' from PS database.
    I am able to find out the 'Last Published Date' from Published DB but I am not able to find who published last.

    I know project from queue history I can check the project publishing time and owner of that queue job but fetching it from DB is looking complex for me now.

    If some one has worked on this requirement and can share some input on this,would be very helpful.

    Thanks.

    Wednesday, August 26, 2015 3:18 PM

Answers

  • Hi

    I was looking for the same out put, thanks i managed to create a query for that for project Server 2013. thought to share

    SELECT 
    Proj.ProjectName as PROJ_NAME
     ,RES.ResourceName as LAST_PUBLISH
          ,  CONVERT(datetime, SWITCHOFFSET(CONVERT(datetimeoffset,QPG.[COMPLETED_DATE] ), DATENAME(TzOffset, SYSDATETIMEOFFSET()))) AS LASTPublised

      FROM [PWA_ITProjects].[draft].[MSP_QUEUE_PROJECT_GROUP_FULL_VIEW] QPG INNER JOIN [PWA_ITProjects].[dbo].[MSP_EpmProject] PROJ On Proj.ProjectUID = QPG.JOB_INFO_UID INNER JOIN [PWA_ITProjects].[dbo].[MSP_EpmResource] RES on RES.ResourceUID = QPG.RES_UID
      
      WHERE (QPG.PROCESSING_DATE IS NOT NULL and QPG.GRP_QUEUE_PRIORITY = 4  and QPG.GRP_QUEUE_MESSAGE_TYPE = 24)
      Order by QPG.COMPLETED_DATE DESC


    Yasser

    Thursday, July 21, 2016 10:21 AM

All replies

  • Hi,

    A way could be to create a project custom field and to push the user's name in this custom field with some VBA code in MS Project Pro.


    Hope this helps,


    Guillaume Rouyre, MBA, MVP, P-Seller |

    Wednesday, August 26, 2015 3:33 PM
    Moderator
  • You can create a table in DB with all fields you need like [Last Published time], [Last Published By] . Write PSI code for OnPublished event and capture all the in this table.later you can use same table for your SSRS reporting.

    Hope this helps

    Wednesday, August 26, 2015 4:02 PM
  • Hi Mayur,

    Thanks for your reply.

    The above mentioned solution would work in future publishing scenario but for the past projects which are either closed or no longer in use and published long time back,how to see the information for them.

    Any input on this please.

    Thanks,


    Wednesday, August 26, 2015 4:22 PM
  • Hi Guillaume,

    Thanks for your reply.

    The above mentioned solution would work in future publishing scenario but for the past projects which are either closed or no longer in use and published long time back,how to see the information for them.

    Any input on this please.

    Thanks,

    Wednesday, August 26, 2015 4:23 PM
  • Hi Ashish,

    I don't think it is possible to get information of previously published projects as there are no default fields in DB where this information is captured.

    Thursday, August 27, 2015 10:13 AM
  • Hi Guillaume,

    Thanks for your reply.

    The above mentioned solution would work in future publishing scenario but for the past projects which are either closed or no longer in use and published long time back,how to see the information for them.

    Any input on this please.

    Thanks,

    There is a WRES_UID column - but that MAY not be the correct person (more than likely it is - but you can test yourself ) 

    I'm checking out the Queue to see if there is a way to get the who saved.  Of course you'd have to make sure your queue settings are saved for a period of time OR you can create your own table and nightly you can get the person  who last saved. OR you can do it several methods with your own tables and views...

    hopefully this answers your question though... 

    USE PS_Published 

    SELECT

    res.RES_UID as 'Last Saved (potentially'),
    PROJ_NAME as 'Project Name', 
    PROJ_PROP_AUTHOR as 'OWNER',
    WRES_UID,
    proj.PROJ_LAST_SAVED,
    proj.PROJ_LAST_PUB

    FROM MSP_PROJECTS proj

    JOIN MSP_RESOURCES res 
    ON proj.WRES_UID=res.RES_UID

    --optional
    --WHERE proj.PROJ_UID = 'XXXX-XXXX-XXXXX-XXXX'

    please mark as the answer if this answers your problem

    Tuesday, September 01, 2015 2:13 PM
  • Hello kbwrecker,

    Thanks for your response.

    I was also in impression initially that RES_UID gives us the 'last published by' values but unfortunately it doesn't.This gives us the value of 'Project Owner' setup in project information.I tested it and verified that person who saved or published was different than of person in RES_UID and RES_UID is the person who is setup as owner of the project in project information.

    Thanks.

    Tuesday, September 01, 2015 4:40 PM
  • MASSIVE EDIT: Please mark as answer if this answers your question

    There is a script that is already written which will give you the answer on a web page - however if you look into the script it will give you the query you need

    First: answer was here : https://smsagent.wordpress.com/tag/project-server-queue-sql/

    Second: Script here :https://gallery.technet.microsoft.com/Server-Queue-Monitor-01bcdb74

    Third: Query for anyone - From the Draft DB


    SELECT DISTINCT QPG.GRP_QUEUE_ID as 'Queue ID',
    case when qpg.GRP_QUEUE_STATE = 8 then 'Blocked Due To A Failed Job'
     when qpg.GRP_QUEUE_STATE = 9 then 'Cancelled'
     when qpg.GRP_QUEUE_STATE = 5 then 'Failed And Blocking Correlation'
     when qpg.GRP_QUEUE_STATE = 6 then 'Failed But Not Blocking Correlation'
     when qpg.GRP_QUEUE_STATE = 2 then 'Getting Queued'
     when qpg.GRP_QUEUE_STATE = 3 then 'Processing'
     when qpg.GRP_QUEUE_STATE = 7 then 'Skipped For Optimization'
     when qpg.GRP_QUEUE_STATE = 4 then 'Success'
     when qpg.GRP_QUEUE_STATE = 1 then 'Waiting To Be Processed'
     when qpg.GRP_QUEUE_STATE = 10 then 'Waiting to be Processed (On Hold)'
     when qpg.GRP_QUEUE_STATE = 12 then 'Waiting to be Processed (Ready for Launch)'
     when qpg.GRP_QUEUE_STATE = 11 then 'Waiting to be Processed (Sleeping)'
    End as 'Job State',

    case when qpg.GRP_QUEUE_MESSAGE_TYPE = 2 then 'Active Directory Sync (Enterprise Resource Pool)'
     when qpg.GRP_QUEUE_MESSAGE_TYPE = 3 then 'Active Directory Sync (Group)'
     when qpg.GRP_QUEUE_MESSAGE_TYPE = 92 then 'Analysis Create'
     when qpg.GRP_QUEUE_MESSAGE_TYPE = 91 then 'Analysis Delete'
     when qpg.GRP_QUEUE_MESSAGE_TYPE = 93 then 'Analysis Update'
     when qpg.GRP_QUEUE_MESSAGE_TYPE = 20 then 'Archive and Delete Project'
     when qpg.GRP_QUEUE_MESSAGE_TYPE = 5 then 'Archive Custom Fields '
     when qpg.GRP_QUEUE_MESSAGE_TYPE = 6 then 'Archive Global Project '
     when qpg.GRP_QUEUE_MESSAGE_TYPE = 19 then 'Archive Project '
     when qpg.GRP_QUEUE_MESSAGE_TYPE = 9 then 'Archive Project Web App Views '
     when qpg.GRP_QUEUE_MESSAGE_TYPE = 7 then 'Archive Resources '
     when qpg.GRP_QUEUE_MESSAGE_TYPE = 4 then 'Archive Security Categories '
     when qpg.GRP_QUEUE_MESSAGE_TYPE = 8 then 'Archive System Settings '
     when qpg.GRP_QUEUE_MESSAGE_TYPE = 106 then 'Change Workflow '
     when qpg.GRP_QUEUE_MESSAGE_TYPE = 101 then 'Exchange Server Task Sync '
     when qpg.GRP_QUEUE_MESSAGE_TYPE = 11 then 'Internal (CBS Project Rendezvous) '
     when qpg.GRP_QUEUE_MESSAGE_TYPE = 13 then 'Internal (CBS Timesheet Rendezvous) '
     when qpg.GRP_QUEUE_MESSAGE_TYPE = 75 then 'Internal (Timer Message) '
     when qpg.GRP_QUEUE_MESSAGE_TYPE = 76 then 'Internal (Timer Rendezvous Notify) '
     when qpg.GRP_QUEUE_MESSAGE_TYPE = 77 then 'Internal (Timer Rendezvous Project ) '
     when qpg.GRP_QUEUE_MESSAGE_TYPE = 78 then 'Internal (Timer Rendezvous Timesheet) '
     when qpg.GRP_QUEUE_MESSAGE_TYPE = 65 then 'Internal (Timer1) '
     when qpg.GRP_QUEUE_MESSAGE_TYPE = 66 then 'Internal (Timer10) '
     when qpg.GRP_QUEUE_MESSAGE_TYPE = 67 then 'Internal (Timer2) '
     when qpg.GRP_QUEUE_MESSAGE_TYPE = 68 then 'Internal (Timer3) '
     when qpg.GRP_QUEUE_MESSAGE_TYPE = 69 then 'Internal (Timer4) '
     when qpg.GRP_QUEUE_MESSAGE_TYPE = 70 then 'Internal (Timer5) '
     when qpg.GRP_QUEUE_MESSAGE_TYPE = 71 then 'Internal (Timer6) '
     when qpg.GRP_QUEUE_MESSAGE_TYPE = 72 then 'Internal (Timer7) '
     when qpg.GRP_QUEUE_MESSAGE_TYPE = 73 then 'Internal (Timer8) '
     when qpg.GRP_QUEUE_MESSAGE_TYPE = 74 then 'Internal (Timer9) '
     when qpg.GRP_QUEUE_MESSAGE_TYPE = 18 then 'Notifications '
     when qpg.GRP_QUEUE_MESSAGE_TYPE = 12 then 'OLAP Database Build '
     when qpg.GRP_QUEUE_MESSAGE_TYPE = 96 then 'Optimizer Portfolio Selection Scenario Create '
     when qpg.GRP_QUEUE_MESSAGE_TYPE = 97 then 'Optimizer Portfolio Selection Scenario Delete '
     when qpg.GRP_QUEUE_MESSAGE_TYPE = 94 then 'Planner Portfolio Selection Scenario Create '
     when qpg.GRP_QUEUE_MESSAGE_TYPE = 95 then 'Planner Portfolio Selection Scenario Delete '
     when qpg.GRP_QUEUE_MESSAGE_TYPE = 10 then 'Priority Bump '
     when qpg.GRP_QUEUE_MESSAGE_TYPE = 21 then 'Project Checkin '
     when qpg.GRP_QUEUE_MESSAGE_TYPE = 22 then 'Project Create '
     when qpg.GRP_QUEUE_MESSAGE_TYPE = 23 then 'Project Delete '
     when qpg.GRP_QUEUE_MESSAGE_TYPE = 100 then 'Project Detail Pages Update Project Impact Values '
     when qpg.GRP_QUEUE_MESSAGE_TYPE = 24 then 'Project Publish '
     when qpg.GRP_QUEUE_MESSAGE_TYPE = 30 then 'Project Publish Notifications '
     when qpg.GRP_QUEUE_MESSAGE_TYPE = 107 then 'Project Publish Summary '
     when qpg.GRP_QUEUE_MESSAGE_TYPE = 25 then 'Project Rename '
     when qpg.GRP_QUEUE_MESSAGE_TYPE = 1 then 'Project Save from Project Professional '
     when qpg.GRP_QUEUE_MESSAGE_TYPE = 15 then 'Project Site Create '
     when qpg.GRP_QUEUE_MESSAGE_TYPE = 16 then 'Project Site Delete '
     when qpg.GRP_QUEUE_MESSAGE_TYPE = 86 then 'Project Site Membership Synchronization '
     when qpg.GRP_QUEUE_MESSAGE_TYPE = 28 then 'Project Update from PSI '
     when qpg.GRP_QUEUE_MESSAGE_TYPE = 29 then 'Project Update Team '
     when qpg.GRP_QUEUE_MESSAGE_TYPE = 105 then 'Project Workflow Check-in '
     when qpg.GRP_QUEUE_MESSAGE_TYPE = 114 then 'Project Workflow Commit '
     when qpg.GRP_QUEUE_MESSAGE_TYPE = 31 then 'Queue Cleanup '
     when qpg.GRP_QUEUE_MESSAGE_TYPE = 102 then 'Reporting (Attribute Departments Sync) '
     when qpg.GRP_QUEUE_MESSAGE_TYPE = 32 then 'Reporting (Attribute Settings Sync) '
     when qpg.GRP_QUEUE_MESSAGE_TYPE = 33 then 'Reporting (Base Calendar Sync) '
     when qpg.GRP_QUEUE_MESSAGE_TYPE = 113 then 'Reporting (Committed Portfolio Selection Scenarios Decision Sync) '
     when qpg.GRP_QUEUE_MESSAGE_TYPE = 34 then 'Reporting (Custom Field Metadata Sync) '
     when qpg.GRP_QUEUE_MESSAGE_TYPE = 110 then 'Reporting (Enterprise Project Type and Workflow Information Sync) '
     when qpg.GRP_QUEUE_MESSAGE_TYPE = 111 then 'Reporting (Enterprise Project Type Sync) '
     when qpg.GRP_QUEUE_MESSAGE_TYPE = 35 then 'Reporting (Entity User View Refresh) '
     when qpg.GRP_QUEUE_MESSAGE_TYPE = 36 then 'Reporting (Fiscal Periods Sync) '
     when qpg.GRP_QUEUE_MESSAGE_TYPE = 85 then 'Reporting (Global Data Sync) '
     when qpg.GRP_QUEUE_MESSAGE_TYPE = 37 then 'Reporting (Lookup Table Sync) '
     when qpg.GRP_QUEUE_MESSAGE_TYPE = 108 then 'Reporting (OLAP Database Settings Sync) '
     when qpg.GRP_QUEUE_MESSAGE_TYPE = 38 then 'Reporting (Project Delete) '
     when qpg.GRP_QUEUE_MESSAGE_TYPE = 39 then 'Reporting (Project Publish) '
     when qpg.GRP_QUEUE_MESSAGE_TYPE = 112 then 'Reporting (Project Summary Publish) '
     when qpg.GRP_QUEUE_MESSAGE_TYPE = 49 then 'Reporting (Project Sync) '
     when qpg.GRP_QUEUE_MESSAGE_TYPE = 41 then 'Reporting (Resource Sync) '
     when qpg.GRP_QUEUE_MESSAGE_TYPE = 40 then 'Reporting (Resources Capacity Range Sync) '
     when qpg.GRP_QUEUE_MESSAGE_TYPE = 45 then 'Reporting (Time Reporting Period Delete) '
     when qpg.GRP_QUEUE_MESSAGE_TYPE = 46 then 'Reporting (Time Reporting Period Sync) '
     when qpg.GRP_QUEUE_MESSAGE_TYPE = 42 then 'Reporting (Timesheet Adjust) '
     when qpg.GRP_QUEUE_MESSAGE_TYPE = 115 then 'Reporting (Timesheet Assignments Refresh) '
     when qpg.GRP_QUEUE_MESSAGE_TYPE = 104 then 'Reporting (Timesheet Assignments Upgrade) '
     when qpg.GRP_QUEUE_MESSAGE_TYPE = 43 then 'Reporting (Timesheet Class Sync) '
     when qpg.GRP_QUEUE_MESSAGE_TYPE = 44 then 'Reporting (Timesheet Delete) '
     when qpg.GRP_QUEUE_MESSAGE_TYPE = 103 then 'Reporting (Timesheet Project Aggregation) '
     when qpg.GRP_QUEUE_MESSAGE_TYPE = 47 then 'Reporting (Timesheet Save) '
     when qpg.GRP_QUEUE_MESSAGE_TYPE = 48 then 'Reporting (Timesheet Status Sync) '
     when qpg.GRP_QUEUE_MESSAGE_TYPE = 109 then 'Reporting (Workflow Metadata Sync) '
     when qpg.GRP_QUEUE_MESSAGE_TYPE = 88 then 'Reporting Database Refresh '
     when qpg.GRP_QUEUE_MESSAGE_TYPE = 50 then 'Resource Plan Checkin '
     when qpg.GRP_QUEUE_MESSAGE_TYPE = 51 then 'Resource Plan Delete '
     when qpg.GRP_QUEUE_MESSAGE_TYPE = 52 then 'Resource Plan Publish '
     when qpg.GRP_QUEUE_MESSAGE_TYPE = 53 then 'Resource Plan Save '
     when qpg.GRP_QUEUE_MESSAGE_TYPE = 55 then 'Restore Custom Fields '
     when qpg.GRP_QUEUE_MESSAGE_TYPE = 56 then 'Restore Global Project '
     when qpg.GRP_QUEUE_MESSAGE_TYPE = 26 then 'Restore Project '
     when qpg.GRP_QUEUE_MESSAGE_TYPE = 59 then 'Restore Project Web App Views '
     when qpg.GRP_QUEUE_MESSAGE_TYPE = 57 then 'Restore Resources '
     when qpg.GRP_QUEUE_MESSAGE_TYPE = 54 then 'Restore Security Categories '
     when qpg.GRP_QUEUE_MESSAGE_TYPE = 58 then 'Restore System Settings '
     when qpg.GRP_QUEUE_MESSAGE_TYPE = 90 then 'Start Workflow '
     when qpg.GRP_QUEUE_MESSAGE_TYPE = 64 then 'Status Update '
     when qpg.GRP_QUEUE_MESSAGE_TYPE = 61 then 'Status Update Rules (Auto-run Rules) '
     when qpg.GRP_QUEUE_MESSAGE_TYPE = 60 then 'Status Update Rules (Process All) '
     when qpg.GRP_QUEUE_MESSAGE_TYPE = 62 then 'Status Update Rules (Run All Rules) '
     when qpg.GRP_QUEUE_MESSAGE_TYPE = 63 then 'Status Update Rules (Run Single Rule) '
     when qpg.GRP_QUEUE_MESSAGE_TYPE = 80 then 'Timesheet Delete '
     when qpg.GRP_QUEUE_MESSAGE_TYPE = 98 then 'Timesheet Line Approval '
     when qpg.GRP_QUEUE_MESSAGE_TYPE = 79 then 'Timesheet Message '
     when qpg.GRP_QUEUE_MESSAGE_TYPE = 81 then 'Timesheet Recall '
     when qpg.GRP_QUEUE_MESSAGE_TYPE = 82 then 'Timesheet Review '
     when qpg.GRP_QUEUE_MESSAGE_TYPE = 83 then 'Timesheet Submit '
     when qpg.GRP_QUEUE_MESSAGE_TYPE = 84 then 'Timesheet Update '
     when qpg.GRP_QUEUE_MESSAGE_TYPE = 119 then 'Timesheet Update Resource '
     when qpg.GRP_QUEUE_MESSAGE_TYPE = 116 then 'Update Project Site Path '
     when qpg.GRP_QUEUE_MESSAGE_TYPE = 120 then 'Update Scheduled Project '
     when qpg.GRP_QUEUE_MESSAGE_TYPE = 89 then 'Update Scheduled Project '
     when qpg.GRP_QUEUE_MESSAGE_TYPE = 117 then 'User Synchronization (Add Operation) for Project Web App App Root Site and Project Sites '
     when qpg.GRP_QUEUE_MESSAGE_TYPE = 118 then 'User Synchronization (Delete Operation) for Project Web App App Root Site and Project Sites '
     when qpg.GRP_QUEUE_MESSAGE_TYPE = 87 then 'User Synchronization for Project Web App App Root Site and Project Sites '
    End as 'Job Type',

    RES.RES_NAME as 'Resource Name',
    QPG.GRP_QUEUE_PRIORITY as 'Queue Priority',
    QPG.PERCENT_COMPLETE as 'Percent Complete',
    QPG.CREATED_DATE as 'Entry Time',
    QPG.COMPLETED_DATE as 'Completed Time'

    from dbo.MSP_QUEUE_PROJECT_GROUP_FULL_VIEW QPG (NOLOCK)

    left outer join dbo.MSP_PROJECT_RESOURCES_PUBLISHED_VIEW RES (NOLOCK) on qpg.RES_UID = RES.RES_UID
    Where (QPG.GRP_QUEUE_STATE <> 4 
    and (DATEDIFF(hour,QPG.COMPLETED_DATE,GETDATE()) < '$TimeInHours' and DATEDIFF(hour,QPG.CREATED_DATE,GETDATE()) < '$TimeInHours'))
    or (QPG.COMPLETED_DATE is null)
    ORDER BY qpg.COMPLETED_DATE Desc


    • Edited by kbwrecker Tuesday, September 01, 2015 8:10 PM
    Tuesday, September 01, 2015 8:01 PM
  • Hi Ashish,

    Same issue i am facing with PS2013,hope below link is help full for you.

    https://social.msdn.microsoft.com/Forums/en-US/c48e4e73-8792-4234-8b7e-00a8e3786cdd/how-to-get-to-know-modifiedbypublished-by-projects-in-project-server-2013-web-access?forum=projserv2010setup


    Hasan Jamal Siddiqui(MCTS,MCPD,ITIL@V3),Sharepoint and EPM Consultant,TCS

    Sunday, March 20, 2016 5:43 AM
  • Hi

    I was looking for the same out put, thanks i managed to create a query for that for project Server 2013. thought to share

    SELECT 
    Proj.ProjectName as PROJ_NAME
     ,RES.ResourceName as LAST_PUBLISH
          ,  CONVERT(datetime, SWITCHOFFSET(CONVERT(datetimeoffset,QPG.[COMPLETED_DATE] ), DATENAME(TzOffset, SYSDATETIMEOFFSET()))) AS LASTPublised

      FROM [PWA_ITProjects].[draft].[MSP_QUEUE_PROJECT_GROUP_FULL_VIEW] QPG INNER JOIN [PWA_ITProjects].[dbo].[MSP_EpmProject] PROJ On Proj.ProjectUID = QPG.JOB_INFO_UID INNER JOIN [PWA_ITProjects].[dbo].[MSP_EpmResource] RES on RES.ResourceUID = QPG.RES_UID
      
      WHERE (QPG.PROCESSING_DATE IS NOT NULL and QPG.GRP_QUEUE_PRIORITY = 4  and QPG.GRP_QUEUE_MESSAGE_TYPE = 24)
      Order by QPG.COMPLETED_DATE DESC


    Yasser

    Thursday, July 21, 2016 10:21 AM
  • This is not correct answer,by this query only we can get queued project publish details,we can not get information which project already published.Guillaume Rouyre


    Hasan Jamal Siddiqui(MCTS,MCPD,ITIL@V3),Sharepoint and EPM Consultant,TCS

    Sunday, July 24, 2016 7:53 AM