JCR-SQL2 Query to get reference statistics for assets

[alexc1]

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]

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.

[alexc1]

Hi Arun,

Do you happen to have a Groovy script that does this, that you could share?

[arunpatidar · Accepted solution]

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]

more sample at https://github.com/icfnext/aem-groovy-console/tree/develop/src/main/scripts