Get Instances By Submission Date
One of LaunchWorks‘ lead engineers, Don Collins was asked by a BusinessObjects customer to get a list of the latest failed instances filtered by submission date of the scheduled job. The Instance Manager in the CMC only allows filtering on the completion date.
Here is the final cms query that should make things easier. It grabs all recently failed queries and returns the id, name and scheduleinfo (error) of the instance.
There was an odd filter issue when certain dates were used. Not exactly sure what that was about, but the query returned the most recent first.
SELECT TOP 9999 SI_ID, SI_NAME, SI_UPDATE_TS, SI_SCHEDULE_STATUS,
SI_STATUSINFO FROM CI_INFOOBJECTS WHERE SI_UPDATE_TS > ’2012-02-09′ AND
SI_INSTANCE = 1 AND SI_SCHEDULE_STATUS = 3 ORDER BY SI_UPDATE_TS DESC