How to report on published status: "Published" or "Not Published"?

[Blue-Dog]

I am trying to create a pages report that will display the status of a page: either "Published" or "Not Published".

I know that the published status information is displayed in the authoring UI for a given page, as shown in the following screenshots:

  

Here is my base Query:

SELECT * FROM [cq:Page] AS s WHERE ISDESCENDANTNODE([/site/folder1/folder2/])

But I don't know how to build and display the "Published" status using the "ACS Commons Report Builder Text Column" component. Which page property or combination of properties need to be used?

[Harwinder-singh]

@Blue-Dog You can refer to the replication API documentation to understand what status is set on the node when it is activated / deactivated. It will also let you tap into the last replication action as well. 

https://experienceleague.adobe.com/docs/experience-manager-cloud-service/content/operations/replicat...

[arunpatidar · Accepted solution]

Here is the sample query

SELECT * FROM [cq:Page] AS page
WHERE ISDESCENDANTNODE(page, '/content')
AND page.[cq:lastReplicated] IS NOT NULL
AND page.[cq:lastReplicationAction] = 'Activate'
ORDER BY page.[cq:lastReplicated] DESC

[yashp0808]

This should work!