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

[snehwood]

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]

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]

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]

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.

[martin_knyazyan]

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]

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