JCR-SQL2 Query to get reference statistics for assets

Question

Dear AEM Community,
 
Can you please offer guidance on a way to do any of the following, either with the Bulk Editor or through ACS-Commons Reports:

Get a COUNT of all references for assets at a certain path
Get the list of references for all assets at a certain path

What I would like to achieve is to get a summary of all our assets that are used vs. not used on live pages (i.e. assets with live References) to be able to clean them, de-duplicate, etc.
 
I know there is a default 'References Report' in ACS-Commons that finds all of the references to the provided page and any subpages, but while it does allow to select Type (All / Pages / Assets), I do not see any results coming back for the parent folder of certain Assets I know have references. The query in the References Report is:
 
SELECT * FROM [dam:Asset] AS s WHERE CONTAINS(s.*, '/content/dam/.../products/.../...')
 
Thank you,
Alex

arunpatidar · Accepted Answer

Hi @alexc1

I don't have for this use case but it is a java. Groovy console uses java api.

Let me add some sample

/* Script to delete below pages with child apges*/
import com.day.cq.wcm.msm.api.LiveRelationshipManager;
import com.day.cq.wcm.msm.api.LiveCopy;
import com.day.cq.wcm.msm.api.LiveRelationship

import com.adobe.granite.references.Reference
import com.adobe.granite.references.ReferenceAggregator
import com.adobe.granite.references.ReferenceList
import com.adobe.granite.ui.components.Config

def path = "/content/wereati/product/77397";
 List<LiveCopy> liveCopyList = new ArrayList();
 LiveRelationshipManager liveRelManager;
	try {
		Resource res = resourceResolver.getResource(path)
	    liveRelManager = resourceResolver.adaptTo(LiveRelationshipManager.class);
		RangeIterator rangeIterator = liveRelManager.getLiveRelationships(res,"",null);
		while (rangeIterator.hasNext())
        {
            LiveRelationship liveCopy =(LiveRelationship) rangeIterator.next();
            println liveCopy.getLiveCopy().path;
            liveCopyList.add(liveCopy.getLiveCopy());

        }
        println liveCopyList.size;
        
       // another approach
       ReferenceAggregator referenceAggregator = bundleContext.getService(bundleContext.getServiceReference(ReferenceAggregator.class));
       Set<String> types = new HashSet<>();
       types.add("liveCopy");
       //,'launch','languageCopy');
        ReferenceList referenceList = referenceAggregator.createReferenceList(
            resourceResolver.getResource(path),
            types.isEmpty() ? null : types.toArray(new String[types.size()])
    );
       
       
       
	}catch (Exception e) {
			println e
				println e.getMessage()
		}

arunpatidar · Answer

Hi,With SQL2 or any other queries it is not possible.If you have groovy console enabled/installed in AEM, you can try with groovy script.

Sign up

Login with SSO

Login to the community

Login with SSO

Scanning file for viruses.

This file cannot be downloaded