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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s