Expand my Community achievements bar.

July 31st AEM Gems Webinar: Elevate your AEM development to master the integration of private GitHub repositories within AEM Cloud Manager.
SOLVED

JCR-SQL2 Query to get reference statistics for assets

Avatar

Level 1

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

1 Accepted Solution

Avatar

Correct answer by
Community Advisor

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()
		}


Arun Patidar

View solution in original post

4 Replies

Avatar

Community Advisor

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.



Arun Patidar

Avatar

Level 1

Hi Arun,

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

Avatar

Correct answer by
Community Advisor

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()
		}


Arun Patidar