AEM-Campaign Sync issue only on PROD env(endpoint /api/content/sites/campaigns.json returns only limited results)

[PrasannaMo]

Hi Team,

I am facing a minor issue with AEM-Campaign Sync.

Only emails until 19th Jan 2024 are appearing in the ACC UI, the emails created after 19th Jan till date are not appearing. This is the case with ONLY PROD environment.

Therefore, currently the email-content authors are doing the email creation activity manually, as a workaround.

Below are the observations,

1. PROD : Only emails until 19th Jan'24 are getting listed on ACC UI.
2. PROD : Only emails who's replicator was xxx-campaign-user are getting listed on ACC UI.
3. PROD : Only email's until 19th Jan'24, shows their replicator as xxx-campaign-user.
4. PROD : No emails post Jan'24 has any user as xxx-campaign-user as their replicator, but other regular users.
5. Lower environment does not have any last replicator criteria as such, ALL emails irrespective of whether they were published or not, are getting listed on ACC UI.
6. As we got to know from the email-content authors, they have been creating the emails under /content/campaigns, and never trigger manual or automated Campaign workflows(Approve or Publish), and all of sudden mails stops appearing/synching with ACC on PROD.

Here is the endpoint : /api/content/sites/campaigns.json?query=SELECT%20*%20FROM%20page%20WHERE%20%5Bchild%5D.%5Bcq%3AacLinks%5D%20IS%20NULL%20ORDER%20BY%20%5Bcq%3AlastModified%5D%20DESC&limit=100&filter.rt=mcm%2Fcampaign%2Fcomponents%2Fcampaign_newsletterpage%2Cmcm%2Fneolane%2Fcomponents%2Fnewsletter%2Cmcm%2Fcampaign%2Fcomponents%2Fnewsletter%2Cxxx-email-authoring-foundation%2Fcomponents%2Fpage%2FemailSummaryPage&showAllProperties=true%27

Hitting this endpoint on non-PROD environment, returns ALL available emails, but for PROD, it returns until 19th Jan'24. On the ACC UI, we have this REFRESH button, above API is triggered on click of that button I believe. 

I was wondering why the same API endpoint call on PROD and Non-PROD environments are responding differently? How can the endpoint be debugged? Where does the servlet reside in AEM? What makes given email to return as a response of above servlet/endpoint?

How can I troubleshoot/debug the API endpoint, where can I see the logs - /api/content/sites/campaigns.json I believe this is present in AEM.

Appreciate your responses

Rgds,

Prasanna

[PrasannaMo · Accepted solution]

Hi Community,

I was able to fix this issue, here's what I did.

The campaign endpoint hits AEM instance with the SQL-like query, which I converted to SQL2 JCR as below,

select * from [cq:PageContent] as node where isdescendantnode(node, '/content/campaigns')
AND
node.[sling:resourceType] = "XXX-email-authoring-foundation/components/page/emailSummaryPage"
ORDER BY [cq:lastModified] DESC

And I got same results as that of ACC Client. When I checked this query with the queryexplainer.html tool, I saw that oak:ntBaseLucene and oak:cqPageLucene were corrupted on 22nd Jan 2024 therefore no results after 19th Jan were getting returned.

Reindexed the corrupted indices and issue got fixed.

Rgds,

Prasanna