query regarding dam asset publish status

[Amir_khan4]

Hi,

I want to make an SQL2 query which will catch the unpublished dam assets and also the page path where those same assets are used.

Can somebody help me with that.

Thanks in advance

@nitesh_kumar @arunpatidar @lukasz-m @DEBAL_DAS 

[Magicr]

Try following query to get unpublished assests:

SELECT * FROM [nt:base] AS comp WHERE ISDESCENDANTNODE (comp, '<Put here your path from your dam') AND comp.[jcr:primaryType] =
 "dam:AssetContent" and AND comp.[jcr:primaryType] = "dam:AssetContent" and comp.[cq:lastReplicationAction] = 'Deactivate'

To get the path wich a dam is used, take a look at 

com.adobe.granite.references.ReferenceAggregator

You have to write a servlet to use this fearture.

[Mani_kumar_]

Hi @Amir_khan4 

As mentioned by @Magicr you can only use the Query to get unpublished assets and with that result set write custom logic to get the references if the assets in pages.

You cannot combine both in one query

Hope this is helpful.

[Kiran_Vedantam]

Hi @Amir_khan4 

QQ: Do you want to know the unpublished dam status and the pages that the assets are used before publishing? If that's the use-case then you might not be able to achieve it as AEM would already remove the asset references.

Thanks,

Kiran Vedantam.

[Amir_khan4]

Hi @Kiran_Vedantam

I need to check whether the asset is referenced anywhere.

[Kiran_Vedantam]

SELECT * FROM [cq:Page] AS parent INNER JOIN [nt:unstructured] AS child ON ISDESCENDANTNODE(child,parent)WHERE ISDESCENDANTNODE(parent,[CONTENTPATH]) AND child.[sling:resourceType]='ASSET_RESOURCE_TYOE' AND parent.[cq:lastReplicationAction] = 'Activate'

You can use this query for finding the asset references.

[Amir_khan4]

Don't have sling:resourceType under /content/dam

[DEBAL_DAS · Accepted solution]

Could you please give a try with below code [servlet] -

/**
 * 
 */
package com.task.core.servlets;

import java.io.IOException;
import java.util.Map;
import java.util.Objects;

import javax.jcr.Node;
import javax.jcr.NodeIterator;
import javax.jcr.RepositoryException;
import javax.jcr.Session;
import javax.jcr.query.Query;
import javax.jcr.query.QueryManager;
import javax.jcr.query.QueryResult;
import javax.servlet.Servlet;

import org.apache.sling.api.SlingHttpServletRequest;
import org.apache.sling.api.SlingHttpServletResponse;
import org.apache.sling.api.resource.ResourceResolver;
import org.apache.sling.api.servlets.HttpConstants;
import org.apache.sling.api.servlets.SlingAllMethodsServlet;
import org.osgi.service.component.annotations.Component;
import org.osgi.service.component.annotations.Reference;

import com.task.core.services.JcrUtility;
import com.day.cq.wcm.commons.ReferenceSearch;
import com.day.cq.wcm.commons.ReferenceSearch.Info;

/**
 * @author debal
 *
 */
@Component(service = Servlet.class, property = { "sling.servlet.paths=" + "/bin/assetreport",
		"sling.servlet.methods=" + HttpConstants.METHOD_GET })
public class UnpublishedImageReferenceDetailsServlet extends SlingAllMethodsServlet{

	@Reference
	private JcrUtility jcrUtility;

	protected void doGet(SlingHttpServletRequest slingHttpServletRequest,
			SlingHttpServletResponse slingHttpServletResponse) {

		try {
			ResourceResolver resourceResolver = jcrUtility.getResourceResolver();
			Session session = resourceResolver.adaptTo(Session.class);

			String failedWorkItemQuery = "SELECT * FROM [dam:Asset] AS s WHERE ISDESCENDANTNODE([/content/dam/we-retail/en]) and [jcr:content/cq:lastReplicated] is  null";
			if (Objects.nonNull(session)) {
				QueryManager queryManager = session.getWorkspace().getQueryManager();
				Query query = queryManager.createQuery(failedWorkItemQuery, Query.JCR_SQL2);
				QueryResult queryResult = query.execute();
				NodeIterator nodeIterator = queryResult.getNodes();
				while (nodeIterator.hasNext()) {
					Node nextNode = nodeIterator.nextNode();
					String assetPath = nextNode.getPath();
					Map<String, Info> search = new ReferenceSearch().search(resourceResolver, assetPath, -1, 0);
					for (Map.Entry<String, Info> entry : search.entrySet()) {

						Info info = entry.getValue();
						for (String p : info.getProperties()) {
							String pagepath = info.getPage().getPath();
							slingHttpServletResponse.getWriter().println(assetPath.concat("***********").concat(pagepath));

						}

					}
				}
			}
		}

		catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (RepositoryException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

	}
}

/**
 * 
 */
package com.task.core.services;

import org.apache.sling.api.resource.Resource;
import org.apache.sling.api.resource.ResourceResolver;



/**
 * @author debal
 * 
 *         This service will be used as a utility and it will help us to get
 *         resource resolver object  and close resource resolver
 *
 */
public interface JcrUtility {

	public ResourceResolver getResourceResolver();

	public void closeResourceResolver(ResourceResolver resourceResolver);
	
	
}

/**
 * 
 */
package com.task.core.impl;

import java.util.HashMap;
import java.util.Map;
import java.util.Objects;

import javax.jcr.Session;

import org.apache.sling.api.resource.LoginException;
import org.apache.sling.api.resource.Resource;
import org.apache.sling.api.resource.ResourceResolver;
import org.apache.sling.api.resource.ResourceResolverFactory;
import org.osgi.service.component.annotations.Component;
import org.osgi.service.component.annotations.Reference;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import com.task.core.services.JcrUtility;

/**
 * @author debal
 *
 */
@Component(service = JcrUtility.class, immediate = true) 
public class JcrUtilityImpl implements JcrUtility {

	private final Logger logger = LoggerFactory.getLogger(JcrUtilityImpl.class);

	@Reference
	ResourceResolverFactory resourceResolverFactory;
	
	

	@Override
	public ResourceResolver getResourceResolver() {
		Map<String, Object> map = new HashMap<String, Object>();
		map.put(resourceResolverFactory.SUBSERVICE, "readWriteService");
		ResourceResolver serviceResourceResolver = null;
		try {

			serviceResourceResolver = resourceResolverFactory.getServiceResourceResolver(map);
		} catch (LoginException e) {
			logger.error("Could not get service user [ {} ]", "demoSystemUser", e.getMessage());
		}
		return serviceResourceResolver;

	}

	@Override
	public void closeResourceResolver(ResourceResolver resourceResolver) {
		if (Objects.nonNull(resourceResolver)) {
			resourceResolver.close();
		}

	}

	
}

I am using JCR_SQL2 query below to identify unpublished assets

SELECT * FROM [dam:Asset] AS s WHERE ISDESCENDANTNODE([/content/dam/we-retail/en]) and [jcr:content/cq:lastReplicated] is null 

[Nupur_Jain]

Hi @Amir_khan4 

You can use ReferenceSearch API to get references of an asset. Below is the example sample:

      Collection<ReferenceSearch.Info> refs = new ReferenceSearch()
          .search(adminResolver, path).values();
      for (ReferenceSearch.Info info : refs) {
        String refPath = info.getPage().getPath();
      }

 Hope it helps!

Thanks,

Nupur