BusinessObjects Query Builder - How to find name of tagged events of publications?

[Former Member]

I have attached success and trigger events in publications. I have about 2400 publication batches and I need to check each and every attachced event.
I don't want to go to each publication batch -> right click on properties -> go to events -> check manually for the name of the events, instead can I populate the details using SQL?

[Joe_Peters · Accepted solution]

You want to know which events are associated with your publications? You can do it, but not in one step.

First, get the list of events with their infoobject IDs:

select si_id,si_name,si_specific_kind from ci_systemobjects where si_kind = 'event'

Next, get the recurring schedules that either wait for or fire events:

select top 10000 si_name,si_scheduleinfo.si_dependants,si_scheduleinfo.si_dependencies from ci_infoobjects where si_recurring = 1 and (si_scheduleinfo.si_dependants.si_total > 0 or si_scheduleinfo.si_dependencies.si_total > 0)

The "si_dependants" represent the events that the schedules fire when complete, "si_dependencies" are the events that the schedule waits for.

[Former Member · Accepted solution]

Thank you for your quick response. This actually helped but I wanted to see the publication batch property. It only shows for scheduled batches in recurring state if I'm not wrong?
Also, is there any way to find out the same for paused instances?

[Joe_Peters · Accepted solution]

Yes, this is only recurring instances, but it will include paused recurring instances.

To capture completed publications, change "si_recurring = 1" to "si_schedule_status in (1,3)".

[Former Member · Accepted solution]

Thanks Joe, you helped a lot.

One more question? If I need the destination path for all recurrences, how can I get it?

When I run

SELECT * FROM CI_INFOOBJECTS WHERE SI_KIND='Publication' AND SI_ID='XXXXXX'

I can see the required destination path at SI_DESTINATIONS -> SI_OUTPUT_FILES -> 1 and there is my required path.

I require that path for all recurring batches just like I got the tagged events.

Could you please help me with that? I really appreciate your help.

[Joe_Peters · Accepted solution]

You can't SELECT just the path property; you have to SELECT si_schedueinfo.si_destinations, then extract the path from there.