Determining Blackbaud Ad Hoc Queries that use Site in Designation
We had to trace which among our hundreds of Blackbaud Enterprise CRM (BBEC) ad hoc queries were using Site from the Designation node. We also needed to figure out if they were being used as output or being used as filter.
Here’s the script that did the trick.
-- BBEC Ad Hoc Queries that use SITE in DESIGNATION WITH XMLNAMESPACES(DEFAULT N'Blackbaud.AppFx.WebService.API.1') SELECT A.NAME, AP.USERNAME [OWNER], A.DESCRIPTION, CASE WHEN QUERYDEFINITIONXML.exist('//SelectFields/f[@ObjectName="V_QUERY_DESIGNATIONLEVEL"][@ColumnName="SITENAME"]') = 1 THEN 'Output' ELSE '' END USED_SELECTFIELD, CASE WHEN QUERYDEFINITIONXML.exist('//FilterFields/f[@ObjectName="V_QUERY_DESIGNATIONLEVEL"][@ColumnName="SITENAME"]') = 1 THEN 'Filter' ELSE '' END USED_FILTERFIELD, A.DATEADDED, A.DATECHANGED FROM ADHOCQUERY A LEFT JOIN APPUSER AP ON A.OWNERID = AP.ID WHERE QUERYDEFINITIONXML.exist('//f[@ObjectName="V_QUERY_DESIGNATIONLEVEL"][@ColumnName="SITENAME"]') = 1 ORDER BY A.NAME
Advertisements