How to find list of reports that are scheduled to particular BO inbox?

[Former Member]

Hello Experts !

I got struck in the below issue .....need help from experts !!

Customer use to get the automated schedule Excel file on every month 10th. Now customer has raised the issue that since February he was not getting any file.

I am anticipating that the schedule is failing due to the person who created the schedule had left the company and his id got obsoleted.because of this reason schedule might failing.

But now to find that particular report which is failing is very difficult to us because we have more than 10k reports including webi and crystal reports. Customer also doesn't have any information about the report whether its webi report or crystal report or report name or path of the report .

I have requested to customer to share the auto email which he use to get on every month 10th. I thought In that mail with the subject line we may find some results through instance manager.

I am searching in instance manager with the filters

Status=success and completion time =jan10th and i got the results, in that I am looking for type =excel and owner =XXXXX user id who left the company

But I couldn't find any reports related to the subject line in the auto mail.

Is there any way to find the list of reports that are scheduled to the particular customer inbox ???

I would really appreciate your help !!!!

Thanks in advance.

[julien_drouvin]

Hi,

Did you succeed using Query Builder to retrieve the scheduled instances?

[Joe_Peters]

First, some notes on the effect of deleting a user who owns scheduled reports:

If you are correct and the user who scheduled the reports has been deleted, then any objects owned by that user will revert to being owned by Administrator. So querying for that user's ID as the owner will return no results (even for past successful instances prior to the user being deleted).

Any recurring schedules owned by that user will fail once upon the next scheduled run, and will not run again. Even though the schedules are now owned by "Administrator", they will fail for insufficient access. So while there should be one failed instance (likely on February 10), there will not be any subsequent failures.

With that said, you can perform a "brute force" CMS query to identify scheduled reports that contain a particular string anywhere in their scheduling settings. You might be able to find the report using the subject line from the email. For example:

select from ci_infoobjects where si_scheduleinfo.si_destinations like '%xxx%' and si_instance = 1

This will return all scheduled instances (regardless of status) that contain the string "xxx" anywhere in the scheduling settings (email recipient, subject, body, etc.).

[Former Member]

Thanks for your detailed explanation 🙂

Could you please help me with query as I am very new to Query Builder

[patelyogesh]

Hello Venky,

Download EDST Tool v1.3.zip from SAP note - 1448881 - Multiple instances spawned after daylight savings time change

Run tool to find all scheduled reports.

Cheers,

Yogesh

[former_member182521]

You need to look at SI_DESTINATIONs property of the report instance. Try to Identify the list of scheduled instances first and apply the filter on SI_DESTINATIONS property.

Thanks

Mani

[Former Member]

Thanks for your response !

I am very new to Query Builder, Could you please help me with the query

[former_member191664]

You said "the automated schedule Excel file on every month 10th" and "since February he was not getting any file"

here is the CMS SQL query to compile a list of failed scheduled jobs since 2/10/2017 and you'd see the destination in the

SI_SCHEDULE_STATUS on the Query Builder output. Search the recipient's email address (email) or the user LAN id (BO Inbox) to find out which report it is failing.

SELECT top 100000 SI_NAME, SI_STARTTIME, SI_ENDTIME, SI_OWNER, SI_STATUSINFO, SI_SCHEDULE_STATUS
from CI_INFOOBJECTS
where SI_INSTANCE = 1 AND SI_SCHEDULE_STATUS = 3 AND SI_ENDTIME >= '2017.2.10'
ORDER BY SI_OWNER, SI_NAME

Regards,

Jin-Chong

[Former Member]

Thanks for your query 🙂

what does mean by these filters SI_INSTANCE = 1 AND SI_SCHEDULE_STATUS = 3

and also I didn't find recipient's email address (email) or the user LAN id (BO Inbox) in the SI_SCHEDULE_STATUS field.

[former_member191664]

SI_SCHEDULE_STATUS: The current status of the scheduled job.
Schedule Status Values Description
3 The job failed. Use error message or outcome to get more information.
8 The job is paused. Even if all dependencies are satisfied, it will not run.
9 The job has not started because dependencies are not satisfied. Dependencies include time constraints and events.
0 The job is currently being processed by the job server.
1 The job completed successfully.

SI_INSTANCE = 1 means this is a scheduled job instance not the report itself.

Regards,

Jin-Chong

[former_member185603]

You mentioned BO Inbox in one place and email in another place. Can you please clarify? How did you search in subject line? There is no field in Instance manager to look for subject line.

[Former Member]

Thanks for your quick response !

Honestly I didn't understand your question

I am searching in instance manager for all the schedules has been successes on Jan 10. In that I am checking for First column Instance title which may equal to Excel file. But it didn't match