what is the best way to find all the obiee scheduled reports and the receiver emails for each schedu

User_27DEY

Hello, due to auditing, we need to find out all the receivers' emails for each scheduled report since some users left the company.  Since we have hundreds of reports, it is very time consuming to find the reports one by one.

can you please let me know if you know the best way to get the receiver emails for each scheduled reports, like query the tables, or catalogs etc.

thanks a lot!!

Federico Venturin

Hi @User_27DEY ,

OBIEE/OAS currently does not provide this information out-of-the-box.

Since information about recipients are stored in Agent objects in the catalog (in XML format), I suggest you to take an offline copy of the catalog and write a Python script to scan Agent objects and extract information about recipients in the format that you prefer.

Gianni Ceresa

I would slightly disagree with Federico, the info is available out of the box, but it isn't available as a nicely formatted list like your audit asks for...

Your are on OBIEE, this means you have already direct offline access to your catalog (no need of a copy), you can use Catalog Manager (the GUI will do the job if it's a one-shot thing, but can also be scripted) and do a report (a functionality of Catalog Manager). This will get you very close to what you look for.

Things get more complicated when it comes to the real meaning of "find out all the receivers' emails for each scheduled report. An agent can be disabled but still be executed with a simple click, or even just with a webservice call: do this one count as something you want in your list? Or are you actually only looking for a list of all the agents that have been executed over the past 6 months and who they did deliver content to?

Because there is another situation that you should consider: agents can change, an agent can be sending an email to 1'000 email addresses and then it was changed 5 minutes ago and you now see only 4 addresses. Still the agent did send 1'000 emails until 5 minutes ago.

If you look more into what has been sent and to whom, you can also just work on the agents logs tables in the database and query them. You will get a list of all the emails sent.

Because there is also another case with agents: what about an agent having a dynamic list of recipients? The case when an OBIEE analysis is the source of the recipients email addresses. That list can produce different results every single time you run the query, because the underlying tables content changed or things like that.

Keeping it "simpler": it all depends how deep you want to go with this work. For auditing, knowing that you are aware of how complex and difficult to clearly define what this request should return based on all the possible conditions of agents, you can probably just stick with the agents log tables giving you a view on what did happen (therefore not telling you anything about future agents possibly sending something to a recipient) over the last X months.

User_27DEY

Thanks Gianni and Federico,

 we only need to know the active agent/scheduled reports receivers, we donot care any of the ones that have been disable/inactive. All we want is simple: Need all receivers for each active /enable scheduled report (Agent) . Thanks again, very appreciate it

User_27DEY

Thanks Gianni,

 Can you please let me know what is the name of the table "agents logs tables in the database" ? Thanks

Gianni Ceresa

The tables are s_nq_job, s_nq_instance, s_nq_err_msg (not just errors, logging in general) and s_nq_job_param.

All we want is simple: Need all receivers for each active /enable scheduled report (Agent) . 

And if one of your agents use a dynamic list of recipients, what you want is not simple anymore and just become impossible because the answer change every time you run the query. But in the end it's all about the story you tell to the people doing the audit as they will not really have any idea of how the tool work.

User_27DEY

Thanks Gianni,

All the reports owned by the single/same owner and we do not care any dynamic ones if there is any.

which table is the primary one/key that has the report names as the primary key? thanks again!!

Gianni Ceresa

None has the report name as primary key because the tool couldn't care less about the report name. The key is the JobID.

The path of the agent should be one of the parameters or properties there around. Depending on the history of your environment it can be wrong, let's hope your environment is "clean". If you want a perfect match with 100% accuracy you can use Catalog Manager to get the name, path and JobID of all existing agents in your catalog and then you can join it with the tables (here again it is fully valid if you have a clean environment, if there have been some freestyle upgrades or deployments of content, JobIDs can mismatch but should still be correct for active agents).

I believe there are MOS documents explaining the structure of the tables and the content of the columns, but you can also have a look at https://obieehome.blogspot.com/2015/02/scheduler-tables-in-obiee-11g.html : this part of the tool didn't change much over time.