Smart Event Triggers using Command Manager

If you are in a reasonably large environment it's likely you have Events that send out reports (via subscriptions) to your users.  Hopefully these Events are fired by triggers, which in turn are fired by whatever enterprise scheduling tool you are using.  I won't get into why time based Events are bad practice today as I want to talk about something else.

I had an issue that one of the events we had had so many subscriptions attached to it (that fired so many different jobs) that if was triggered it would crash the I-Sever.  Yes this is a 32 bit environment but it's crashes like this that will have Microstrategy telling you it's time to upgrade.  The issue is the I-Sever can only hold so many jobs (either executing or waiting) at one time I think we established it was around 500 jobs before it crashed.  However this event would invoak around 1500 jobs.  So something had to be done.

Initially what I would do is extract all the subscriptions for the event using command manager.  If you have never seen the list of out of the box outlines before make sure you do as there is plenty there

I was interested in this one

Then I took this line

LIST [ALL] SUBSCRIPTIONS FOR SCHEDULE "schedule_name" FOR PROJECT "project_name";

altered it to be

LIST ALL SUBSCRIPTIONS FOR SCHEDULE "MySchedule" FOR PROJECT "MyProject";

If you run that it returns a list that you can export to excel if you like.  Once there I using a little bit of filtering and a pivot table and created another script using this as a base:

TRIGGER [ALL] SUBSCRIPTIONS [FOR SCHEDULE "schedule_name"] FOR OWNER "login_name" FOR PROJECT "project_name";

I altered it to be like this:

TRIGGER ALL SUBSCRIPTIONS FOR SCHEDULE "MySchedule" FOR OWNER "TheOwner" FOR PROJECT "MyProject";

What I had was around 50 lines where the thing that changed each time TheOwner.  I then put that back into command manager and ran each one, waiting for the job monitor to clear then started the next  one.  It allowed me to break up the one event into a series of smaller events.  But it was very manual and I knew there was a better way.

It wasn't until I found this post at Bryan's Micostrategy Blog that I knew what I should do.  In Bryans post he accesses the subscription list then uses that in a loop statement to change the owner of the subscription.  Which is very handy if a user leaves and you need to claim all their subscriptions.  Where as Bryan alters the subscription I knew that I could extract the GUID from each Trigger it then do the same again in a loop.  The trick would be having a pause between each Trigger or even better checking the number of jobs in the I-Sever before triggering the next subscription.

I also wanted to record what was being set in case of a failure or some sorts.  At least then I could take the log and compare it to the full set and work out what still needs to be sent.

So here is what the end result looks like (not this is a Procedure and not a script - but it uses Syntax from the script world):

This script must be run via Command Manager as a Procedure.  To run it, simply go to File -> New Procedure and paste it in.  You'll need to set the variables at the top, and also under the Test Information tab on the right hand side of Command Manager, you'll need to connect to your IServer.

//You will need to set these

String sProject = "MyProject";
String sSchedule = "MySchedual";

// File Logging Properties - Again will need to be changed as per your requirements
String sPCID = "pcName";
String USER_NAME = "UserName";
String FILE_PATH = "\\\\" + sPCID +"\\C$\\Users\\" + USER_NAME +"\\Desktop";
String FILE_NAME = "trigger_log.txt";
File f = new File(FILE_PATH, FILE_NAME);
Calendar cal = Calendar.getInstance();

BufferedWriter logWriter = null;

try {
logWriter = new BufferedWriter(new FileWriter(f, true));
} catch(Exception e) {
printOut("Error Creating log file writter: " + e.toString());
}

//Gets all the subscriptions for your event / project
ResultSet oSubs = executeCapture("LIST ALL SUBSCRIPTIONS FOR SCHEDULE \'" + sSchedule +"\' FOR PROJECT \'" + sProject +"\';");

oSubs.moveFirst();

try {
while(!oSubs.isEof()) {
                //Finds the GUID
String sGUID = oSubs.getFieldValueString(DisplayPropertyEnum.GUID);

                //Sets a value - we use it soon
int jobcheck = 100;

while (jobcheck > 50) {
                        //Counts the actual number of jobs running, the 50 above is the max number of jobs that can                       be running
ResultSet oJobs = executeCapture("LIST ACTIVE JOBS ;");
int count = 0;
oJobs.moveFirst();
while (!oJobs.isEof()) {
count = count +1;
oJobs.moveNext();
}
jobcheck = count;
Thread.sleep(1000);
}
//Send subscription - We only get to here if jobCheck < 50
String s = ("TRIGGER SUBSCRIPTION GUID " + sGUID +" FOR PROJECT \'" + sProject +"\';");
execute(s);
//Log output
String log_message = cal.getTime() + ": Project = '" + sProject + "' GUID = '" + sGUID + "'\n";

try{
if(!f.exists()) {
f.createNewFile();
}
if(logWriter != null) {
logWriter.write(log_message);
}
}catch (Exception e) {
printOut("Error writing to log: " + e.getMessage());
}

printOut(log_message);
//Just wait a bit before trying again
Thread.sleep(5000);

oSubs.moveNext();
}
} finally {
try {
logWriter.close();
} catch(Exception e) {
// :(
}
}



I would suggest you trial this in your Development environment, set up some subscriptions to yourself and try it.  Adjust the Max Jobs number (currently 50 and could move to a variable if you wish) and try it out.

Taking it further you could alter this to accept run time parameters and call it from a scheduling tool meaning all your Events would work like this.  However I might save that for another post.