OBIEE 12c Agents: enable them by code !

by Gianni Ceresa | 13 March 2017 | Under the hood

Agents in OBIEE 12c and 11g, known as iBots back in OBIEE 10g, are a component of the BI platform providing few interesting things like pushing data to users instead of requiring users to connect and get it themselves, have alerts based on data to inform users about something and few more. They are one of the many kind of elements you find in the OBIEE catalog.
Being part of the catalog means they are one of the elements you have to manage when upgrading to a different version of OBIEE or, more frequently, when promoting code/features between environments: from DEV to TEST and finally to PROD for example.

What are Agents?

Agents enable you to automate your business processes.
You can use agents to provide event-driven alerting, scheduled content publishing, and conditional event-driven action execution.
(FMW User’s Guide for OBIEE 12c)

Till here nothing special: archive your catalog (or branch of catalog) from system A and unarchive on system B: done!
Well … not really …
The objects end up on system B, your Agents will be there, sure, but they will always be disabled, so inactive, even if they were enabled and running on the source system.

This is currently an issue as you have to remember which Agents must be enabled and do it by hand on the target system, making automated code deployment only partial waiting for somebody to enable Agents again.

And what about migrations? For example, moving from OBIEE 11g to OBIEE 12c? You maybe migrate a catalog with tens or hundreds of Agents all over the catalog which must be enabled again … by hand! Not what we can call a nice and interesting activity and not the safest way to do a migration.

No need to enable them by hand anymore !

I’m glad to tell you that you don’t have to do it by hand anymore and the process enabling Agents can perfectly be scripted!

How? That’s for the bottom of the post, first let’s have a look at what an Agent is in OBIEE, what pieces of information are stored where.

Creating an Agent

To create an Agent is a really simple activity: in the “New” menu of the OBIEE web interface Agents are under the Actionable Intelligence label (by the way, if you don’t have a Foundation license but by component, make sure you are allowed to use deliveries). In the “Schedule” step is where the details about when the Agent must be executed are defined, if you uncheck the “Enabled” checkbox the Agent will not be executed, even if I define a schedule, for example every 10 minutes all the time, the Agent will not be run at all. That’s the status all the Agents are when migrated or promoted to a different environment.

In the “New” menu select “Agent”

Do not enable it directly, first define all the settings.

Save the Agent, it is still not enabled at this point.

Where the Agent is stored?

There are two places where information about Agents are stored.
The main place is the catalog itself, as any other object, the XML describing the Agent is created in a file (with a corresponding .atr file).

This is the content of that file for a sample Agent I just created:

<?xml version="1.0" encoding="UTF-8"?>

<saw:ibot xmlns:saw="com.siebel.analytics.web/report/v1.1" xmlns:cond="com.oracle.bi/conditions/v1" xmlns:sawx="com.siebel.analytics.web/expression/v1.1" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" version="1" priority="normal" jobID="6">

<saw:schedule disabled="true">

<saw:start startImmediately="false" repeatMinuteInterval="10" endTime="23:59:00" date="2017-03-12" time="01:00:00" />

<saw:recurrence runOnce="false">

<saw:daily dayInterval="1" />

</saw:recurrence>

</saw:schedule>

<saw:dataVisibility type="runAsUser" runAs="weblogic" runAsGuid="weblogic" />

<cond:condition />

<saw:choose>

<saw:when condition="true">

<saw:deliveryContent disposition="deviceDefault">

<saw:reportRef path="/shared/Agents/Sample analysis for agent" />

<saw:headline>

<saw:caption>

<saw:text>Test Agent 01</saw:text>

</saw:caption>

</saw:headline>

</saw:deliveryContent>

<saw:postActions />

</saw:when>

<saw:otherwise>

<saw:postActions />

</saw:otherwise>

</saw:choose>

<saw:deliveryDestinations>

<saw:destination category="dashboard" />

</saw:deliveryDestinations>

<saw:recipients specificRecipients="true" subscribers="false" customize="false">

<saw:specificRecipients>

<saw:user name="weblogic" guid="weblogic" />

</saw:specificRecipients>

<saw:subscribers />

</saw:recipients>

<saw:emailRecipients />

</saw:ibot>

Another place where you find information about the Agents is the database, the BIPLATFORM schema created by the RCU when OBIEE has been configured.
There are few tables for Agents there, the main one is S_NQ_JOB which is the one defining the ID assigned to each Agent (the JOB_ID column) registered by the Scheduler (one of the components of OBIEE).

Entries for the Agents in the S_NQ_JOB table

If you look well, between the XML and the database table there is a link: the XML contains the ID which has been assigned to the Agent in the S_NQ_JOB table. The “driver” isn’t the XML but the database as we will see later in case of conflicting IDs.

The jobID attribute of the saw:ibot node contains the ID assigned in the database

How to enable an Agent?

Once your Agent exists you can of course edit it again and check the “Enabled” checkbox and save, but there is a simpler way. In the catalog clicking on the “More” link just underneath the Agent itself a list of actions is displayed and if the Agent isn’t already enabled the last option will be “Enable Schedule” (if the Agent is already enabled you will have the link to disable it).

The “enable” link is available in the catalog

How to enable Agents by a script in an automated way?

As I’m not aware of a simple webservice to enable the Agents (didn’t search for it in detail as few other people who needed it said it wasn’t there, so I trusted them) the solution must be behind the “Enable Schedule” link in the catalog.

All I have to do is to find that piece of code, easy …

saw.catalogaction.IbotsActionHandler.prototype.enableIBotSchedule = function(l) {

saw.header.showBusyIndicator(true);

var d = saw.getLastPathPart(l);

var p = saw.getFolder(l);

var h = obide.ServerRequests.retrieveIBotInfo(l, true);

var k = XUIGetElementText(obips.XMLDOM.selectSingleNode(h, "IBotInfo/SchedulerRegistered")) == "1";

var a = obips.XMLDOM.selectSingleNode(h, "//saw:ibot", saw.delivers.namespaceMap);

var r = false;

if (!k) {

a.removeAttribute("jobID");

if (p) {

var b = '<?xml version="1.0" encoding="UTF-8"?>' + obips.XMLDOM.getXMLString(a);

r = (this.tItemInfo.extendedAttributes && this.tItemInfo.extendedAttributes.recipient);

saw.catalog.getCatalogManager().writeXML(saw.catalog.ItemCategory.IBOT, b, l)

}

var e = obips.XMLDOM.createXMLDomFromString('<saw:ibotUI xmlns:saw="com.siebel.analytics.web/report/v1.1"/>');

var n = obips.XMLDOM.selectSingleNode(e, "saw:ibotUI", saw.delivers.namespaceMap);

XUIAppendChild(n, a);

var t = obips.XMLDOM.selectSingleNode(a, "saw:schedule", saw.delivers.namespaceMap);

var o = obips.XMLDOM.selectSingleNode(t, "saw:start", saw.delivers.namespaceMap);

var s = null;

if (o) {

s = XUIGetAttributeString(o, "date")

}

if (s && s != "") {

t.removeAttribute("disabled")

} else {

XUISetAttributeBool(t, "disabled", true)

}

var f = XUIInsertNewElement(n, saw.xml.kSawNamespace, "ibotSaveInfo", 1);

var m = XUIForceSingleNode(f, saw.xml.kSawNamespace, "saveMode");

XUISetElementText(m, "save");

var q = XUIForceSingleNode(f, saw.xml.kSawNamespace, "name");

XUISetElementText(q, d);

var i = XUIForceSingleNode(f, saw.xml.kSawNamespace, "oldName");

XUISetElementText(i, d);

var g = XUIForceSingleNode(f, saw.xml.kSawNamespace, "path");

XUISetElementText(g, p);

var c = XUIForceSingleNode(f, saw.xml.kSawNamespace, "oldPath");

XUISetElementText(c, p);

var j = obide.ServerRequests.saveIBot(obips.XMLDOM.getXMLString(e), false);

saw.delivers.handleSaveResult(j);

this.updateDetails(r);

saw.header.showBusyIndicator(false)

};

Let me introduce you saw.catalogaction.IbotsActionHandler.prototype.enableIBotSchedule, the enableIBotSchedule method of the saw.catalogaction.IbotsActionHandler javascript object. That’s a custom object Oracle developed (there are many many others, a lot more) to manage all the interactions with the catalog in the webpage.

This piece of code is, all in all, quite readable even without knowing what does call it, what’s the context and what there is around. A single parameter, by the code I would guess it can be an object or a reference to the Agent, actually the ” l ” parameter is just the path of the Agent, so something like /shared/Example agents/Test Agent 01 for the example Agent I created above.

If you look in your browser’s Developer tools extension what happen when you click the enable link the javascript code makes even more sense.

When clicking on “Enable Schedule” there are 2 ajax calls.

Looking at the URLs it’s easy to match these 2 ajax calls with the javascript code: row 5 and 42 in the above snippet.

To make it easier to understand the rest of the javascript function, this is the result of the first call to saw.dll?RetrieveIBotInfo :

<Subscribed>false</Subscribed>

<CanSubscribe>false</CanSubscribe>

<CanCustomize>false</CanCustomize>

<Name>Test Agent 01</Name>

<Description>This is a just a test agent</Description>

<Author>weblogic</Author>

<CreatedOn>3/12/2017 5:28:49 PM (Server OS Time Zone)</CreatedOn>

<LastModified>3/12/2017 5:28:50 PM (Server OS Time Zone)</LastModified>

<saw:schedule disabled="true">

<saw:start startImmediately="false" repeatMinuteInterval="10" endTime="23:59:00" date="2017-03-12" time="01:00:00" />

<saw:recurrence runOnce="false">

<saw:daily dayInterval="1" />

</saw:recurrence>

</saw:schedule>

<saw:dataVisibility type="runAsUser" runAs="weblogic" runAsGuid="weblogic" />

<cond:condition />

<saw:choose>

<saw:when condition="true">

<saw:deliveryContent disposition="deviceDefault">

<saw:reportRef path="/shared/Agents/Sample analysis for agent" />

<saw:headline>

<saw:caption>

<saw:text>Test Agent 01</saw:text>

</saw:caption>

</saw:headline>

</saw:deliveryContent>

<saw:postActions />

</saw:when>

<saw:otherwise>

<saw:postActions />

</saw:otherwise>

</saw:choose>

<saw:deliveryDestinations>

<saw:destination category="dashboard" />

</saw:deliveryDestinations>

<saw:recipients specificRecipients="true" subscribers="false" customize="false">

<saw:specificRecipients>

<saw:user name="weblogic" guid="weblogic" />

</saw:specificRecipients>

<saw:subscribers />

</saw:recipients>

<saw:emailRecipients />

</saw:ibot>

</AgentXML>

</IBotInfo>

The key element is at row 2: <SchedulerRegistered>1</SchedulerRegistered>
It means the Agent is already registered in the Scheduler, the database entries are there and are fine !
If the Agent wasn’t found in the database (in case of a migration for example) or there would be an issue the value would have been 0.

Based on this XML the rest of the javascript code make sense too as it’s a lot of XML manipulations: if the Agent is already registered in the Scheduler and there is a date for the schedule the code remove the disabled=”true” attribute from the saw:schedule node and then save the new Agent code.

This is actually the same as if you open the Agent to edit it, check the “Enabled” checkbox and finally save the Agent, same thing, same process, same logic.

You now have all the pieces to script and automate these steps if you want, simple standard HTTP calls sending and receiving pieces of XML (almost XML in reality, there is a kind of prefix you have to remove to make it really XML).

You can use the scripting language of your choice and after simulating a login to have a session ID with the required privileges you can execute the same logic and same HTTP calls.

Job ID collision: how is that managed?

Based on what I just wrote you can imagine you could easily have a Job ID collision when promoting code between environments.

For example, if you have 5 Agents in total in DEV and PROD and you add a new one while developing in DEV. This new Agent will have the ID = 6.
In the meantime in PROD a new Agent is created for a special need only in PROD (if you allow that or not it’s a different thing, processes are all different). In PROD the ID = 6 is assigned to this new Agent.

What happen when you promote your development till PROD? You will have 2 Agents with ID = 6: one will be the one promoted by your development release, the other the one created only in PROD.

Job ID collision? Panic! 2 Agents but same Job ID

No need to panic, OBIEE is smart!

The collision is detected, the system will see there is a second Agent with the Job ID = 6 but the path is different when compared with the one stored in the S_NQ_JOB_PARAM database table. It will discard the JobID = 6 and will create a new one when saving and enabling the Agent.

The metadata of the Agent mismatch with the attributes saved in the database

You can test this case by simply copy/paste an existing Agent to a new path in the catalog or with a different name.

To resume

Agents are stored in the catalog but also in the database once they are registered with the Scheduler (enabled or disabled doesn’t matter at this point).
This means that when deploying Agents in a new environment you do not have to care about the content of the S_NQ_JOB and S_NQ_JOB_PARAM database tables, OBIEE will manage that for you. It’s actually better to not write inside these tables by hand trying to migrate content.

Just to be sure you didn’t miss it…

Don’t copy the content of S_NQ_JOB and S_NQ_JOB_PARAM around between environments during code promotion or upgrades. Let OBIEE create these entries.
If needed, you can then copy the content of the log table, but first map the old Job IDs to the new ones.

No need to worry about conflicting Job ID in case you allow your users to create Agents directly in PROD and so ending up with the same ID of the Agents you deploy when releasing new features. OBIEE will detect the conflict thanks to the database and reassign IDs as needed.

Finally, and most important: you can script and automate the steps to enable Agents by replicating the simple javascript function logic: retrieve the Agent XML details, manipulate them a bit to remove the “disabled” attribute and save the Agent again.

Do you want a super simple way to do this in one step from OBIEE itself? Stay tuned, in few days I will post a Dashboard page containing all the code to select all the Agents you want to enable at once and with a single click they will be enabled, using the OBIEE javascript libraries (so it’s like if you will do it by hand in the catalog one by one). All you have to do is to unarchive it somewhere in your catalog.
Do you need to be able to execute an agent by clicking a button or link in an analysis or dashboard? Same as above: stay tuned and in few days a sample code will be posted.

27 Comments

Chris Redgrave on 10 July 2017 at 12:31

Gianni, excellent blog. You have saved me untold hassle while needing to activate 300+ ibots!
Reply
Dileep Kumar on 4 January 2018 at 08:07

need Help on the column-level functional description of the each column present in below OBIEE scheduler tables:

1. S_NQ_JOB
2. S_NQ_JOB_PARAM
3. S_NQ_INSTANCE
4. S_NQ_ERR_MSG

Any inputs/docs related to the same will be highly appreciated.

Thanks in Advance
Reply
- Gianni Ceresa on 4 January 2018 at 11:31
  
  Not really the topic of the post, and you have all the answers in https://community.oracle.com/thread/4110186
  Reply
John FIsk on 27 April 2018 at 21:13

Gianni – great site and topic. In OBIEE 12C, do you know if there’s a way to set up an agent/ibot to send to a file location instead of an email address?

Appreciate any thoughts!

John
Reply
- Gianni Ceresa on 27 April 2018 at 21:27
  
  It isn’t really different than in 11g: you need to write it. EJB for sure (in MOS there are documents about that) and I believe that on a Windows Server you could still use JS as well. EJB of course has the benefit to work on any system, so it has my preference. There blog posts with code inside, MOS has some code too and can’t remember about the doc, there you maybe only find some hints.
  Reply
Marcelo Finkielsztein on 5 September 2018 at 19:09

Hello Gianni,
AFAIK you can enable/disable an Agent by updating the column “s_nq_job.disable_flg” to a 0/1.

e.g.: update s_nq_job a SET a.disable_flg=0 where a.job_id = 190;
will enable the job_id# 190.

Makes sense?
Reply
- Gianni Ceresa on 5 September 2018 at 22:19
  
  Honestly I can’t remember if it’s really enough. If your agent is correctly referenced (so it has an ID and the relative entries are in the DB) you probably can enable and disable it like that, but would need to test it before to really affirm it.
  The issue is when you move an agent from an environment to another, from a version to another or copy it: the agent doesn’t exist in the DB and to record it there you need to edit and enable and save it. If you enter things in the DB by hand it doesn’t work as the system will not find the link between the agent and the DB entries. So it clearly depends on the context in which you want to enable agents: the code provided here is mainly intended for those doing an upgrade who end up in 12c with all the agents disabled and taking ages to enable them one by one.
  Reply
Mindmajix on 23 October 2018 at 09:26

Nice! Thanks for sharing a great article. I Really enjoyed reading your post.
Reply
Carlos on 28 February 2019 at 22:54

What is the folder location of these XML files?
Reply
- Gianni Ceresa on 28 February 2019 at 23:30
  
  You mean the XML of the agent? It’s inside your web catalog, where all the OBIEE objects are stored (analysis, dashboards etc.).
  Reply
Ahmed on 4 July 2019 at 15:44

Hello Gianni,

I tried to Unarchive your file On OBIEE 11.1.1.9, and i got the below error, do you have the same code developed in older OBIEE version:
Incompatible version when reading object “CatalogInputStreamer”. Version Read: 16. Min expected: 11. Max expected: 14.
Reply
- Gianni Ceresa on 4 July 2019 at 15:49
  
  Hi, I don’t have it for 11g versions just because it can’t work as it is. 11g doesn’t come with jQuery by default, therefore it would require to be adapted. As this has been done mainly to help the 12c migration which create all agents but can’t enable them, I don’t have the adapted code for 11g (but I believe I tried it once in the past as somebody else asked for it before, must be here in the comments).
  Reply
Rituraj on 27 November 2019 at 15:29

Hi, I was going through your post and came across one anomaly in my OBIEE 11g application. I checked the XML file for one of my agents and the S_NQ_JOB table as well, while database table has an associated JOB ID for the agent, the corresponding xml file have no trace of this jobID element.

I checked other agents and some of them have jobID in both xml and database table.

Is it okay to have this discrepancy?
Reply
- Gianni Ceresa on 28 November 2019 at 21:06
  
  OBIEE 11g is older and could really be different in behaviour. I don’t remember how it was back in that version. The link between the file and the database is anyway extremely weak, having discrepancy doesn’t chock me as the scheduler anyway find the agent by its path. Honestly I wouldn’t worry more than that, in the end an agent either work or not: as long as it works, no need to care more than that.
  Reply
Gaurav on 11 February 2020 at 17:58

Hi Gianni – In agents headline text how can we use Presentation Variables created by user for eg. Report Month.Any thoughts please?
Reply
- Gianni Ceresa on 12 February 2020 at 10:54
  
  Hi, not really related to the topic of the post. You better ask on the Oracle community forum .
  Reply
Jasmeen on 30 April 2020 at 15:31

Hello Gianni,

I am trying to use the code in 12.2.1.4. and it looks like its not generating any list. I just see loading… for Text3 result. I have html enabled on BI.
Can you please suggest if I can use the same code within 12.2.1.4? or Is it possible for to modify the code and provide it for 12.2.1.4?
Reply
- Gianni Ceresa on 30 April 2020 at 16:20
  
  And what do you see in the console? That’s where you find the reason of what happen (or not), everything is written in your browser’s console.
  Reply
- Gianni Ceresa on 30 April 2020 at 17:22
  
  Started a 12.2.1.4 instance, unarchived the file and everything works fine without any change.
  Reply
Abdul Basit on 10 February 2021 at 11:46

Hi Gianni,

I hope this message finds you well. I have question that I need my OBIEE Analysis report to be sent to multiple people from multiple department through Agent . I need to filter the data based on department and send it. I was unable to put the condition in Agent. is there any way to put the condition in agent?
Reply
- Gianni Ceresa on 10 February 2021 at 12:38
  
  Hi, if you execute your agent “as recipient” and add filters in the analysis being the content delivered by the agent you get what you need. You probably should already have session variables defined for those users with their department, variable that you use when these users connect to OBIEE to view the dashboards online.
  Reply
- Robert Mclean on 19 June 2021 at 05:21
  
  Hi great post for using OBIEE Agents. I am very new to this database system, but I have a question about scheduling and sending analysis reports. To send reports your customers have to be on an OBIEE or Oracle network right? In my setup I am the sole OBIEE person and my customers are all connected via a WAN. I usually email the analysis products, but is there a way to have OBIEE mail these to customers in my domain/WAN. Does anyone have a tutorial on how to schedule/automate analysis to run? As I said I am the sole OBIEE person and it can be a bit overwhelming.
  Reply
  - Gianni Ceresa on 19 June 2021 at 11:36
    
    Hi,
    Not sure what you mean by “Oracle network”, but OBIEE agents can deliver content to users (users of OBIEE) or email addresses (and I’m staying away of any licensing requirement for this, check with your Oracle Account manager if you have doubts).
    If it’s the same exact analysis (content) you are sending to many people, adding all the lists as recipients will work. If you have to customize the output for every single user (generally a filter based on the user so everyone receives his own content), you have to either have them as users in OBIEE with sessions variables and execute the agent as recipient or create as many agents are needed to have all the custom content produced and delivered.
    As comments in a blog aren’t the place for “detailed discussions” on a problem, consider posting in the Oracle forum if needing more info: https://community.oracle.com/mosc/categories/obiee (if you have a valid MOS subscription) or https://community.oracle.com/tech/apps-infra/categories/business_intelligence_suite_enterprise_edition .
    Reply
Maha on 12 June 2023 at 11:25

Hi Gianni,

Is there any way to extract/export the list of agents, along with their recipients and schedule details from catalog.
Reply
- Gianni Ceresa on 12 June 2023 at 11:55
  
  Hi,
  Yes and no, at least not just from the catalog. The agent’s objects in the catalog will contain some of the recipients, others can come from an analysis, and therefore they are a dynamic list.
  For the schedule details, the reference is still what is in the scheduler tables in the DB.
  You can get everything, it just isn’t a “click here to get them all” thing.
  Reply
Jeevan Krishna on 1 August 2023 at 15:11

Hi Gianni,

Any way can we add the existing agents to a dashboard and user can run whenever they need?
Reply
- Gianni Ceresa on 2 August 2023 at 10:07
  
  You can do everything you want…
  For example https://gianniceresa.com/2017/03/scripts-to-use-agents-in-obiee-12c-mass-enabling-and-execution-from-a-page/
  Reply