Question

Fetch pages in CRXDE where last modified date is greater than last replicated date

Forum|Forum|3 months ago
October 24, 2025
5 replies
476 views

Hey

I am trying to find all the pages under the site tree that were modified after they were last published (where last modified dates is greater than last replicated date). I am running this query in CRXDE choosing SQL2 option but get the "static operand error"

I need this information on production environment which has the querybuilder URL blocked on it. I have tried to add CAST as date but to no avail. Do I have a way out here? Thanks in advance...

Sneh

rampai

Community Advisor

Hi @snehwood ,

This is due to a limitation with Querying where the value being compared against has to be static. You can workaround this either by using a Groovy script to fully automate the process or if you cannot run Groovy for Security reasons, then you can use ACS Commons Report Builder and add result columns that contain both the last modified and last replicated dates. Once report is generated you could use Excel to compare and filter out rows where last modified dates is greater than last replicated date.

If you cannot use ACS Commons as well, then you can build a custom servlet or service that:

Queries pages using JCR API. You can also use PageManager API if you want to avoid Queries.
Compares jcr:lastModified and cq:lastReplicated.
Outputs results in JSON, CSV, or stores in DAM.

Please let me know if you need more details around any of the approaches.

Regards,

Ram

VishalKa5

Level 5

Hi @snehwood ,

You are getting the “static operand error” because AEM’s SQL2 does not allow comparing two properties in a query, like cq:lastModified > cq:lastReplicated. SQL2 only supports comparing a property to a fixed value, not to another property, so this kind of query will never work in CRXDE. Even using CAST or switching to XPath won’t help because this is a limitation of the Oak query engine. Since you also mentioned that QueryBuilder URL is blocked on production, the right solution is to use the QueryBuilder Java API or a Groovy script instead of relying on SQL2. The idea is to first fetch all pages under your site and then programmatically compare their cq:lastModified and cq:lastReplicated values.

Here is the working approach:

Map<String, String> params = new HashMap<>();
params.put("path", "/content/my-site");
params.put("type", "cq:PageContent");
params.put("property", "cq:lastModified");
params.put("p.limit", "-1");

SearchResult result = queryBuilder.createQuery(PredicateGroup.create(params), session).getResult();
for (Hit hit : result.getHits()) {
    Resource res = hit.getResource();
    Calendar lastModified = res.getValueMap().get("cq:lastModified", Calendar.class);
    Calendar lastReplicated = res.getValueMap().get("cq:lastReplicated", Calendar.class);
    if (lastReplicated == null || lastModified.after(lastReplicated)) {
        // this page has unpublished changes
    }
}

This approach works safely on production, avoids SQL2 limitations, and helps you accurately find all pages modified after last publication.

Thanks & regards,

Vishal

giuseppebaglio

Level 10

hi @snehwood,

If you're on AEM 6.5, you can use the dateComparison predicate with a query like this:

path=/content/project/de/en
type=cq:Page
dateComparison.property1=jcr:content/cq:lastModified
dateComparison.property2=jcr:content/cq:lastReplicated
dateComparison.operation=greater
p.limit=3

If it isn't working, check the Services console to see if the predicate exists.

M

martin_knyazyan

Level 2

Hi @snehwood .
Unfortunately, it is not possible to compare dynamic operands using JCR-SQL2 as well as Query Builder. Consider using groovy console for such reports: https://github.com/valtech/aem-easy-content-upgrade

lavishvasuja

Level 3

Hi @snehwood ,

Since SQL2 cannot compare two properties directly, you can fetch pages and compare jcr:lastModified vs cq:lastReplicated programmatically:

Map<String, String> params = new HashMap<>();
params.put("path", "/content/my-site");
params.put("type", "cq:PageContent");
params.put("p.limit", "-1");

SearchResult result = queryBuilder.createQuery(PredicateGroup.create(params), session).getResult();
for (Hit hit : result.getHits()) {
    Resource res = hit.getResource();
    Calendar lastModified = res.getValueMap().get("cq:lastModified", Calendar.class);
    Calendar lastReplicated = res.getValueMap().get("cq:lastReplicated", Calendar.class);

    if (lastReplicated == null || lastModified.after(lastReplicated)) {
        // page has unpublished changes
    }
}

This works on production and avoids SQL2 limitations.

Thanks,

Lavish Vasuja

Sign up

Login with SSO

Login to the community

Login with SSO

Scanning file for viruses.

This file cannot be downloaded