how to pull the list of reports based on To address setup in delivery notifications

[former_member244450]

Hello Everyone,

We have a requirement where in  the To address needs to be updated in report schedule (success or failure) delivery notifications being sent to the end users. There are n number of reports and opening each and every report to check if that report has this email address that needs a change would be hectic.

So would like to know if there is anyway to get the report names which are scheduled and having this notification with that particular email address in To list.

This would make the task easy to have the reports scheduled notifications updated. Any pointers would be of great help!!

Environment in use:  BI 4.1 SP 5

Thanks,

Satya

[DellSC · Accepted solution]

Unfortunately, there is no easy way to do this. 

You can use QueryBuilder to get the information about schedules using this query:

Select SI_ID, SI_NAME, SI_SCHEDULEINFO.SI_DESTINATIONS from CI_INFOOBJECTS where SI_INSTANCE = 1 and SI_SCHEDULE_STATUS = 9 and SI_SCHEDULEINFO.SI_DESTINATIONS.SI_TOTAL > 0

You would then have to go through the list to find the ones that are going to email and have the email address you're looking for.  I suggest either just using "Find" in your browser to find the specific email address or exporting the result set to a file and searching there.  Note that the default result size for queries is 1000 objects - if you have more than 1000 schedules, you'll want to do something like "Select Top 5000...." using a number that is greater than the number of schedules that are in your system.

-Dell

[former_member244450 · Accepted solution]

thank you Christy for promptly replying and Yes am trying to figure out using query builder only.

I know I need to query folder by folder and then result this into some excel and then filter out. But then I would like to know how to get the list of scheduled reports only which has notifications set and not all those reports which are just scheduled.

If I can pull only those which have notifications set   I can use excel to do my stuff. I am attaching this screen shot and I would like to know how to get this COLUMN SI_MAIL_ADDRESSES.

Cannot we get a column this depth like below:

select SI_NAME, SI_SCHEDULEINFO.SI_NOTIFICATION from ci_infoobjects where SI_PARENTID=XXX AND SI_KIND='CRYSTALREPORT' AND SI_CHILDREN>0 AND SI_SCHEDULEINFO.SI_NOTIFICATION.SI_DESTINATION_FAILURE.SI_DEST_SCHEDULEOPTIONS.SI_MAIL_ADDRESSES='XX...

It doesn't give me any results...how to query against values this deep...?

Thanks,

Satya

[DellSC · Accepted solution]

That's what makes this such a challenge - you can't query against objects that deep.  Every time I've tried, I get an "invalid query" error.

There is a tool available that will export QueryBuilder queries to Excel.  You can download it here:  InfoStore Query Builder (with export to Excel)

I haven't used it much, but you could run your query there and then possibly manipulate the Excel to get the info you need.

-Dell

[former_member244450 · Accepted solution]

Thank you Christy for helping me on this.

The below query would give the list of all the reports which have notifications set :

select SI_NAME, SI_SCHEDULEINFO.SI_NOTIFICATION FROM CI_INFOOBJECTS WHERE SI_ANCESTOR=23 AND SI_KIND IN ('WEBI', 'CRYSTALREPORT')  AND SI_SCHEDULEINFO.SI_NOTIFICATION!=' '

Thanks,

Satya

[DellSC · Accepted solution]

That's good to know!  Thanks for posting that!

-Dell