Bulk deletion of Forms Portal submissions

[#1]

Bulk deletion of Forms portal Submissions

  by Pankaj Girotra

How to delete Forms Portal submissions?

Adobe Experience Manager (AEM) 6.5 Forms provides multiple form submission options, one of which is the Forms Portal. By default, Forms Portal stores submission data in the AEM's CRXDE repository. Although this approach isn't recommended by Adobe, numerous organizations still rely on this built-in feature for their form submissions.

When an organization stores their Forms Portal data in CRXDE, it gets stored internally on the file system. As submissions pile up over time, this can lead to low disk space issues. AEM Administrators can use the delete action of of Form Portal's the Draft & Submission component to remove these submission. The action actually hides the submissions instead of deleting the actual data node. This is as per design to keep the submissions on the system for auditing. This leaves AEM Forms Administrators with the tedious and error-prone task of manually locating and deleting these nodes in CRX/DE.

I have created and sharing sample script to automate identification and deletion of such nodes. The script is written in JSP and performs the following actions:

• Queries submissions based on the specified path and date range.
• Deletes data, attachments, and metadata nodes associated with each submission.
• Logs the deleted submissions.
• Saves you time to play your favourite sport. 

Before you start

To use the script:

• The user should have all the read/write/delete, in short jcr:all, permissions on the /content/forms/fp node.
• Install the Curl utility on your machine. It is required to run the script.

Deleting the nodes using script

This script accepts two parameters:

• path: Specifies the path of the Forms Portal CRXDE node that contains to be deleted submissions. 
• date: Specifies as date. The script deletes all submissions created before the specified date. For example, if you specify date as, 2023-08-28, all the submissions prior to the date would be deleted on successful execution of the script.

Perform the following steps to create and execute the script:

1. Login to your AEM Forms environment and open CRXDE.
2. Under the '/apps' directory, create two nodes, "delete-submission (nt:unstructured)" and "delete-submission-script (sling:Folder)," .
3. For the delete-submission directory, set the set the 'sling:resourceType' to '/apps/delete-submission-script'.

4. Under "/apps/delete-submission-script," create a JSP file named "delete-submission-script.jsp"
   

5. Add the script provided in the Script to delete submissions section. 
6. Use the Save all option to save the file. Close CRXDE.
7. Open the terminal and Command Window.
8. Run the following command to execute the script after changing http://localhost:4502 with your AEM Forms Server's URL. 

curl -u admin:admin http://localhost:4502/apps/deleteSubmissions/delete-submission.jsp?path=/content/forms/fp/admin&date=2023-08-28

This script deletes nodes synchronously, which may lead to timeouts if a large number of nodes need to be deleted. To address this, it is recommended to create a Sling job for each deletion, enabling an asynchronous execution process. We will cover this approach in a future blog post.

Script to delete submissions

<%
%><%@include file="/libs/foundation/global.jsp"%><%
%><%@page session="false" %><%
%><%@ page import="com.day.cq.wcm.api.WCMMode,
                     com.day.cq.i18n.I18n,
                     org.apache.sling.api.resource.ResourceResolver,
                     org.apache.sling.api.resource.Resource,
                     javax.jcr.Session,
                     javax.jcr.security.AccessControlManager,
                     javax.jcr.security.Privilege,
                     java.io.InputStreamReader,
                     java.io.InputStream,
                     java.io.PrintWriter,
                     org.apache.commons.io.IOUtils,
                     org.apache.commons.lang3.StringUtils,
                     javax.jcr.*,
                     java.io.StringWriter,
                     java.util.*,
                     com.adobe.granite.xss.XSSAPI,
                     com.adobe.fd.fp.util.FormsPortalConstants,
                     com.adobe.fd.fp.fdinternal.service.FormsPortalUtilService,
                     com.day.cq.search.PredicateGroup,
                     com.day.cq.search.Query,
                     com.day.cq.search.QueryBuilder,
                     com.day.cq.search.result.SearchResult,
                     com.adobe.fd.fp.service.* "
%>
<%@taglib prefix="ui" uri="http://www.adobe.com/taglibs/granite/ui/1.0" %>
<%
            long total = 0;
            int deleteSubmissionCount = 0;
            PrintWriter writer = response.getWriter();
            try {
 
                final String path = request.getParameter("path");
                final String date = request.getParameter("date");
                if(StringUtils.isAnyBlank(path, date)) {
                    writer.write("parameters are missing");
                    return;
                }
                QueryBuilder queryBuilder = sling.getService(QueryBuilder.class);
                Session session = slingRequest.getResourceResolver().adaptTo(Session.class);
                Map<String, String> queryMap = new HashMap<String, String>();
                queryMap.put(FormsPortalConstants.STR_PATH, path);
                queryMap.put(FormsPortalConstants.STR_TYPE, FormsPortalConstants.STR_NT_UNSTRUCTURED);
                queryMap.put("daterange.property", "jcr:lastModified");
                queryMap.put("daterange.upperBound", date);
                queryMap.put("1_property", FormsPortalConstants.STR_NODE_TYPE);
                queryMap.put("1_property.value", FormsPortalConstants.STR_FP_SUBMITTED_FORM);
     
 
                PredicateGroup predicates = PredicateGroup.create(queryMap);
     
                Query query = queryBuilder.createQuery(predicates, session);
                SubmitMetadataService fpSubmitMetadataService = sling.getService(SubmitMetadataService.class);
                SubmitDataService fpSubmitDataService = sling.getService(SubmitDataService.class);
                Map<String,Object> propMap = new HashMap<String, Object>();
     
                SearchResult result = query.getResult ();
                total = result.getTotalMatches();
 
                for (Iterator<Resource> it = result.getResources(); it.hasNext();) {
                    Resource res = it.next();
                    ValueMap props = res.getValueMap();
                    final String submitID = props.get("submitID", String.class);
                    if (StringUtils.isNotBlank(submitID)) {
                        boolean isDataDeleted = true;
                        final String userDataID = props.get("userdataID", String.class);
                        if (StringUtils.isNotBlank(userDataID)) {
                            isDataDeleted = fpSubmitDataService.deleteData(userDataID);
                        }
                        if (isDataDeleted) {
                            final String[] attachmentList = props.get("attachmentList", String[].class);
                            if (attachmentList != null && attachmentList.length > 0) {
                                for (final String attachment : attachmentList) {
                                    final String attachmentValue = fpSubmitMetadataService.getProperty(submitID, attachment)[0];
                                    if (StringUtils.isNotBlank(attachmentValue)) {
                                        fpSubmitDataService.deleteAttachment(attachmentValue);
                                    }
                                }
                            }
                            final String dorID = props.get("dorID", String.class);
                            if (StringUtils.isNotBlank(dorID)) {
                                fpSubmitDataService.deleteData(dorID);
                            }
 
                            fpSubmitMetadataService.deleteMetadata(submitID);
                            log.info("deleted submissio "+ submitID);
                            deleteSubmissionCount++;
                        }
 
                    }
                }
 
            } catch(Exception e) {
                log.error("Error occurred while deleting the submissions");
            }
            writer.write("Total submissions deleted " + deleteSubmissionCount);
            return;
%>​

Q&A

Please use this thread to ask questions relating to this article

[#2]

Great post! What about using a Sling Servlet or Groovy instead of JSP? Are there any specific reasons why you chose JSP? I'm just curious.

[#3]

@EstebanBustamante , Thanks for reading the post. There is no specific reason for choosing jsp it just that it's an easier way to integrate this code without the need to create a full project and deployment process. The flexibility to move this code to Servlet/groovy is always there for those who prefer that approach.
And, in future, I am planning to move this code to sling job to make this whole operation async.