4

Is there an easy way to check the publish status of all items from ITEMS table in the Content Manager database?

I am thinking of creating SQL query to list them all, but am not able to find Publish status in the database.

Listing all items using Core Service takes much more time.

I need this to identify all items that are published in broker but look as unpublished in CM.

Thanks in advance.

Improve this question
6
  • 1
    Wouldn't the broker db consist items that are published only?
    – Huston Lopes
    Commented Mar 3, 2017 at 8:36
  • Yes, exactly, but some of those items appear unpublished in CM, or are completely deleted. These items i need to identify. So i need to get all items in broker that dont exist / are unpublished in CM.
    – Marko Milic ♦
    Commented Mar 3, 2017 at 8:40
  • You're asking the wrong question. The solution is to set up a clean publication target with a fresh database and publish everything out nice and clean, then decommission the old one.
    – Dominic Cronin
    Commented Mar 3, 2017 at 10:19
  • Actually, we don't want new broker database. We observe that some items stay as cps in broker, but are deleted in tridion. This is bug in system so we have solution how to fix it (by creating transport packages and sending to deployer --- Solution provided by SDL support). Now we just need to identify all those cases.
    – Marko Milic ♦
    Commented Mar 3, 2017 at 10:22
  • 1
    @DominicCronin, I respectfully have to disagree that creating a fresh target and publishing everything is "THE" solution. It is a very time consuming procedure, often requiring weeks of non-stop publishing time and is not feasible. Identifying the items as suggested and asking SDL Support for a script to clean them up is a better solution IMO.
    – Nickoli Roussakov
    Commented Mar 3, 2017 at 13:33

2 Answers 2

Reset to default
4

I was able to get all published items from broker as:

SELECT distinct COMPONENT_ID, PUBLICATION_ID
FROM COMPONENT_PRESENTATIONS 
order by COMPONENT_ID, PUBLICATION_ID

Also, i got all published items from CM like:

SELECT distinct REFERENCE_ID, PUBLICATION_ID
  FROM [Tridion_cm].[dbo].[PUBLISH_STATES]
  where ITEM_TYPE=16 
  and STATE=1
  order by REFERENCE_ID, PUBLICATION_ID

Now I just need to get all in broker select that are not in CM select result.

Improve this answer
1

Please try this SQL script. you got all publishing items(components,pages,bundle and multimedia component):

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
Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.