Generate excel report for bundle, component, page total publishing time

Question

I want to generate excel report for Bundle, Component, Page and Multimedia Component total publishing time with each publishing stage process.

Transport Time(s) Prepare Transport Time(s) Transporting Time(s) Deployment Preparation Time(s) Deploying Time(s) Deployment Committing Time(s)

Looking for ideas and solution.

Answer 1

We have seen an idea similar to the above on meta stack exchange. To my knowledge, there is no simple solution to the above :).

Since you are looking for a report for all stages of deployment from transport to deployment, you can start by adding instructions to your package during rendering, publishing and deployment. Using a deployer extension or tridion event system (need to see if we can capture all of your above information), you can update a CUSTOM database with the information from rendering, transporting and deployment upon successful publishing of Pages, DCPs, Binaries and Bundles.

You can write a custom page or GUI customization in Tridion CME to retrieve these values from your database and develop an export to excel functionality based on filters in your UI.

Hope it helps!

Answer 2

Thanks for feedback. I think it`s better connect cms db directly and get value from tables. please excute below scirpt and validate with publishing queue.

SELECT PT.SOURCE_ITEM_REFERENCE_ID AS ITEM_REFERENCE_ID, PT.SOURCE_ITEM_TITLE, PT.SOURCE_ITEM_TYPE, SUBSTR(PTT.TITLE,0,INSTR(PTT.TITLE,'_',1,1)-1) AS PUBLISH_TARGET, PT.PUBLICATION_TARGET_ID, PT.PUBLISH_TIME, PT.STATE_CHANGE_TIME, PT.PUBLISHER_REQUEST, PT.PUBLICATION_ID, P.TITLE, EXTRACTVALUE(XMLTYPE(P.METADATA_XML), '/ns:Metadata/ns:platform', 'xmlns:ns=http://www.test.com/tridion/schema/publication') AS PLATFORM, SOURCE_ITEM_TITLE,SOURCE_ITEM_PATH, BP.BUNDLE_ID, BP.BUNDLE_METADATA AS ReleaseType,PT.INFORMATION, PT.ID AS TRANSACTION_ID, BP.CONFIGURATION, CASE WHEN PT.STATE = 6 THEN 'Success' WHEN PT.STATE = 5 THEN 'Failed' END AS PublishStatus FROM PUBLISH_TRANSACTIONS PT INNER JOIN PUBLICATIONS P ON P.ID = PT.PUBLICATION_ID INNER JOIN PUBLICATION_TARGETS PTT ON PT.PUBLICATION_TARGET_ID = PTT.ID INNER JOIN WFTCMREPORT.T_BUNDLE_SUPP BP ON BP.BUNDLE_ID = PT.SOURCE_ITEM_REFERENCE_ID ORDER BY PT.PUBLICATION_TARGET_ID, PT.PUBLISH_TIME DESC