I am able to use query builder to view report detail data for a particular report or group of reports.
I’m wondering if it is possible to use query builder to show a list of reports that have a particular email address as the destination. For example, an employee has left the company and I would like to remove this person’s email address from all reports that are scheduled. The problem is that there are many reports and I’m not sure which ones contain the email address. I’m hoping query builder can help.
I tried the following code however nothing was returned…even though the email address is indeed listed in the To: field on several scheduled reports.
SELECT SI_NAME FROM CI_INFOOBJECTS WHERE SI_MAIL_ADDRESSES = ‘firstname.lastname@example.org’
You can’t use SI_MAIL_ADDRESSES in the where clause because it is a nested property. The best you can do using strictly Query Builder is to pull back the SI_DESTINATIONS field:
SELECT SI_ID, SI_NAME, SI_SCHEDULEINFO.SI_DESTINATIONS FROM CI_INFOOBJECTS WHERE SI_RUNNABLE_OBJECT = 1
The “runnable_object” piece returns all pending/recurring scheduled jobs. Using some Excel magic, you could format the output in such a way as to allow for filtering. The alternative is to use the SDK.