MicroStrategy - Command Manager - Procedures

Command Manager

What is the name of the procedure that can be use to back up and restore project configuration?

Save_Reuse_Project_Configuration (String projectName, String scriptFile);

What is a Command Manager procedure?

This is in the same vain as a database stored procedure. Command Manager procedures are reusable scripts that can be executed from other scripts. You can reuse procedures with different input values, so that the procedure performs the same task in a slightly different way. Procedures can use Command Manager syntax, or they can be written in the Java programming language and incorporate Command Manager statements in Java commands.

What is a Command Manager script and how is it different from a Command Manager procedure?

This is the same as database scripts and stored procedures. A Command Manager stored procedure is equivalent to a database stored procedure. A Command Manager script is equivalent to a database script. A script is just a series of statements or procedure invocations.

What does a Command Manager procedure has to do with Java?

This is in the same vain as a database stored procedure. Command Manager procedures are reusable scripts that can be executed from other scripts. You can reuse procedures with different input values, so that the procedure performs the same task in a slightly different way. Procedures can use Command Manager syntax, or they can be written in the Java programming language and incorporate Command Manager statements in Java commands.

How can we create a Command Manager procedure?

  1. In Command Manager, from the File menu, select New Procedure. A Procedure window opens.
  2. In the Properties tab, in the Name field, type the name of the procedure. This is the name that the procedure is saved under in the Procedures folder.
  3. In the Description field, type a description of the procedure. This description is shown in the comments for the procedure's outline.
  4. To include any additional classes, click the Browse (…) button next to the Classpath field. Browse to the class file and click Accept.
  5. Type your procedure in the Code Editor tab
  6. To create an input for your procedure, in the Properties tab click the plus icon. Click in the Name column to rename the input. To change the type of the input (for example, String or Integer), click the Type column and select the new type from the drop-down list
  7. In the Sample Editor tab, type an example of how to execute the procedure, with inputs.
  8. To save the procedure, from the File menu select Save. The procedure is saved in the directory specified in the General tab of the Options dialog box, under the name specified in the Name field in the Properties tab.

How can we open an existing Command Manager procedure?

If our intention is to just look at the source code of the procedure, we do not need to connect to a specific project source. However, if we want to edit, compile, and run the procedure, we will need to connect to a specific project source. To open the procedure:

  1. Select "File -> Open Procedure"

How can we test a Command Manager procedure?

  1. In Command Manager, compose or open a procedure.
  2. Select the Test Information tab.
  3. In the Values column of the Inputs for execution list, enter a value for each input. These values are used when you execute the procedure.
  4. From the Project Source drop-down list, select the project source you want to test the procedure against.
  5. In the Login Id and Password fields, enter the user name and password you wish to use to connect to the project source.
  6. Click Connect to connect to the project source.
  7. To execute the procedure against this project source, with the specified inputs, from the Connection menu select Execute. The procedure executes.

How can we access the Command Manager help system?

C:\Program Files (x86)\Common Files\MicroStrategy\Help\English\CommandManagerUniversal\cmdmgrhelp.htm

The complete script that I am using to purge old user:

Vector doNotDeleteUsers = new Vector();
doNotDeleteUsers.add("ADMINISTRATOR");
doNotDeleteUsers.add("U_2782874_555002521_802");
doNotDeleteUsers.add("U_2782874_555001621_802");
doNotDeleteUsers.add("U_2782874_555002761_802");
doNotDeleteUsers.add("U_2782874_555001882_802");
doNotDeleteUsers.add("U_2782874_555001886_802");
doNotDeleteUsers.add("U_2782874_555001881_802");
doNotDeleteUsers.add("U_2782874_555001884_802");
doNotDeleteUsers.add("U_2782874_555001883_802");
doNotDeleteUsers.add("U_2782874_555001885_802");
doNotDeleteUsers.add("U_2782874_555002181_802");
doNotDeleteUsers.add("U_2782874_555005081_802");
doNotDeleteUsers.add("U_2782874_555005085_802");

ResultSet oUsers  = executeCapture("LIST LOGIN,CREATIONTIME,MODIFICATIONTIME FOR USERS IN GROUP \"Everyone\";");
oUsers.moveFirst();
while (! oUsers.isEof()) {
    String login = oUsers.getResultCell(DisplayPropertyEnum.LOGIN).getValueString();
    Date modificationTime = (Date) oUsers.getResultCell(DisplayPropertyEnum.MODIFICATION_TIME).getValue();
    Date today = new Date();
    long todayEpoch = today.getTime();
    long modificationTimeEpoch = modificationTime.getTime();
    long diff = todayEpoch - modificationTimeEpoch;
    long millisecondsPerDay = 24 * 60 * 60 * 1000;
    if (! doNotDeleteUsers.contains(login.toUpperCase())) {
        if (diff > numberOfDays * millisecondsPerDay) {
            long days = diff / millisecondsPerDay;
            String command = "DELETE USER \"" + login + "\"  CASCADE PROFILES;";
            printOut("command: " + command);
            execute(command);
        }
    }
    oUsers.moveNext();
}

The complete script that I used for allowing standard authentication:

At one time, when my SDK code create or update the user, I wanted to prevent the user from being able to directly access MicroStrategy Web. Unfortunately, due to some other reason, MicroStrategy Web behave inconsistently, and I was forced to roll back my change, which in theory should work just fine. Unfortunately, somehow it didn't, and the user account is still not allowed to access MicroStrategy Web directly. This script is used to update the user accounts so that they are able to access MicroStrategy Web direct.

ResultSet oUsers  = executeCapture("LIST LOGIN,CREATIONTIME,MODIFICATIONTIME FOR USERS IN GROUP \"Everyone\";");
oUsers.moveFirst();
while (! oUsers.isEof()) {
    String login = oUsers.getResultCell(DisplayPropertyEnum.LOGIN).getValueString();
    Date modificationTime = (Date) oUsers.getResultCell(DisplayPropertyEnum.MODIFICATION_TIME).getValue();
    Date today = new Date();
    long todayEpoch = today.getTime();
    long modificationTimeEpoch = modificationTime.getTime();
    long diff = todayEpoch - modificationTimeEpoch;
    long millisecondsPerDay = 24 * 60 * 60 * 1000;
    if (! login.equalsIgnoreCase("administrator")) {
        String command = "ALTER USER \"" + login + "\"  ALLOWSTDAUTH TRUE;";
        printOut("command: " + command);
        execute(command);
    }
    oUsers.moveNext();
}

How can we create a procedure that takes parameters?

As we create a procedure or edit an existing procedure using Command Manager graphical interface, on the right hand side, there a a couple of tabs. The Properties tab allows us to specify:

  1. the name of the procedure
  2. the version number
  3. the description of the procedure
  4. the Java classpath if necessary

The bottom of this tab allows us to specify the input parameters. You can specify the name of the parameter, and the type of the parameter. If you need to have more than one input parameter, click on the green plus icon.

In the above procedure, numberOfDays is an input parameter

How can we execute a procedure with input parameters?

In the Command Manager script editor, put:

EXECUTE PROCEDURE PURGEOLDUSERS(3);
EXECUTE PROCEDURE PROCEDURENAME(param1, param2, param3);

and hit the Execute icon in the top toolbar (this icon looks like a lightening strike).

How can we test our procedure?

As we create a procedure or edit an existing procedure using Command Manager graphical interface, on the right hand side, there a a couple of tabs. The Test Information tab allows us to specify the project source, the username and password for the project source. The bottom of this tab allow us to specify the input parameters that are required for this procedure. Before testing the procedure, you must first save the procedure by click on the Save icon at the top. To run the test, you must click on Execute icon at the top.

What do I have to do in order to use Java to create Command Manager procedure?

Java can be integrated into Command Manager procedures to automate repetitive tasks such as creating multiple users, or recursively listing all the folders in a project. Java is supported in Command Manager out-of-the-box; no additional software must be installed to execute Java commands.

To include Java in a Command Manager script, you write a procedure containing the Java code, and execute the procedure from a Command Manager script. Java cannot be included directly in a Command Manager script, but can be used directly for procedure.

Java is only supported in procedures, and procedures are only supported with project sources. Java commands cannot be used in scripts to be executed against a Narrowcast Server metadata.

Do not use the System.exit command to exit a procedure. This command terminates the entire Command Manager process.

Command Manager provides two special commands that can be used by Java scripts to execute Command Manager commands:

  1. execute runs any Command Manager command, but does not return the results.
  2. executeCapture runs any Command Manager command and returns the results in a ResultSet object. This object behaves like a standard ResultSet object in Java: you can iterate through the results and retrieve individual items, which can then be used to extract properties of the results. This enables you to use the results elsewhere in the procedure. For a detailed list of the ResultSet columns used in each Command Manager LIST statement, see the statement syntax guide for that statement in the Command Manager Help.

If you wish to use a third party JAR file or library within your Command Manager procedure, when editing the procedure, look for the Classpath field on the right hand side of the screen. You can add the third party JAR file there. Command Manager seems to not support the import statement. You do not need to use the import statement. Just declare your variables and use them. Furthermore, the <> operator was not introduced until Java 7, so if your code contains any <>, remove them.

Does Command Manager automatically lock and unlock project or configuration and what are the consequences?

No. Command Manager does not automatically lock a project or configuration when it executes statements. To avoid metadata corruption, use the LOCK PROJECT or LOCK CONFIGURATION statements in any Command Manager scripts that make changes to a project or server configuration.

Command Manager does not automatically lock a project or configuration when it executes statements. Thus, any time you alter a project metadata or Intelligence Server configuration with a Command Manager script, there is the possibility that another user could alter the metadata or configuration at the same time. This can cause metadata or configuration inconsistencies, and in the worst case may require you to reinstall Intelligence Server or restore your project from a backup.

To avoid these inconsistencies, use the LOCK PROJECT or LOCK CONFIGURATION statements in any Command Manager scripts that make changes to a project or server configuration. These statements place a lock on the metadata or configuration. A metadata lock prevents other MicroStrategy users from modifying any objects in the project in Developer or MicroStrategy Web. A configuration lock prevents other MicroStrategy users from modifying any configuration objects, such as users or groups, in the project source.

When other users attempt to open an object in a locked project or configuration, they see a message that informs them that the project or configuration is locked because another user is modifying it. Users can then choose to open the object in read-only mode or view more details about the lock.

There are two kinds of locks in Command Manager:

  1. Transient locks are automatically released after disconnecting
  2. Permanent locks are only released after an UNLOCK command, or when the project is manually unlocked. Permanent locks are indicated by the word PERMANENT in the LOCK command.

If you lock a project or configuration in a Command Manager script, make sure you release the lock at the end of the script with the UNLOCK PROJECT or UNLOCK CONFIGURATION statement.

Other examples:

ResultSet oUsers = executeCapture("LIST LOGIN, ENABLED FOR USERS IN GROUP \"Everyone\";");
printOut("There are " + oUsers.getRowCount() + " listed users");

oUsers.moveFirst();
while (!oUsers.isEof()){
    Boolean bEnabled = (Boolean)oUsers.getResultCell(DisplayPropertyEnum.ENABLED).getValue();
    // Log the users based on some condition
    if ((bEnabled != null) && (! bEnabled.booleanValue())){
        String sLogin = oUsers.getResultCell(DisplayPropertyEnum.LOGIN).getValueString();
        printOut("User '" + sLogin + "' is disabled. ");
    }
    oUsers.moveNext();
}

printOut("Recursively listing all users under '" + sTopUserGroup + "' user group");
// Retrieve the initial folder list under the specified top folder
ResultSet oTopResult = executeCapture("LIST MEMBERS FOR USER GROUP '" + sTopUserGroup + "';");
// All nested groups including the top user group
List<ResultSet> oGroups = new ArrayList<ResultSet>();
oTopResult.moveFirst();
oGroups.add(oTopResult);
// List of users
Set<String> oUsers = new HashSet<String>();
String sUser = null;
// Loop all nested groups and the top user group
while (oGroups.size() > 0){
    // Pick up the first result set and remove it once it has been consumed
    oGroups.get(0).moveFirst();
    ResultSet oResult = (ResultSet)oGroups.get(0).getFieldValue(MEMBER_RESULTSET);        
    oResult.moveFirst();
    while(!oResult.isEof()){
        sUser = (String)oResult.getFieldValue(LOGIN);
        if(!(Boolean)oResult.getFieldValue(IS_GROUP)){
            oUsers.add(sUser);
        }else{            
            oGroups.add(executeCapture("LIST MEMBERS FOR USER GROUP '" + sUser + "';"));
        }
        oResult.moveNext();
    }        
    oGroups.remove(0);
}

// Print out all users
if(oUsers.size() > 0){
    for(String sStr : oUsers){
        printOut("User = " + sStr);
    }
}else{
    printOut("No members under this user group '" + sTopUserGroup + "'.");
}

ResultSet USERS = executeCapture("LIST MEMBERS FOR USER GROUP \"Everyone\";");
USERS.moveFirst();
while (!USERS.isEof()) {
    ResultSet member=(ResultSet)USERS.getFieldValue(MEMBER_RESULTSET);
    printOut("Hey");
    member.moveFirst();
    while (!member.isEof()) {
        String USER = member.getFieldValueString(LOGIN);
        printOut("LOGIN ID:" + USER);
        member.moveNext();
    }
    USERS.moveNext();
}

ResultSet USERS = executeCapture("LIST MEMBERS FOR USER GROUP \"Everyone\";");
USERS.moveFirst();
while (!USERS.isEof()) {
    ResultSet member=(ResultSet)USERS.getFieldValue(MEMBER_RESULTSET);
    printOut("Hey");
    member.moveFirst();
    while (!member.isEof()) {
        String USER = member.getFieldValueString(LOGIN);
        String modificationTime = member.getFieldValueString(MODIFICATION_TIME);
        printOut("LOGIN ID:" + USER);
        printOut("Modification time: " + modificationTime);            
        member.moveNext();
    }
    USERS.moveNext();
}

.getFieldValueString
.getFieldValue

ResultSet USERS = executeCapture("LIST MEMBERS FOR USER GROUP \"Everyone\";");
USERS.moveFirst();
while (!USERS.isEof()) {
    ResultSet member=(ResultSet)USERS.getFieldValue(MEMBER_RESULTSET);
    printOut("Hey");
    member.moveFirst();
    while (!member.isEof()) {
        String USER = member.getFieldValueString(LOGIN);
        String modificationTime = member.getFieldValueString(MODIFICATION_TIME);
        printOut("LOGIN ID:" + USER);
        printOut("Modification time: " + modificationTime);            
        member.moveNext();
    }
    USERS.moveNext();
}

final long MIN_WAIT = 1000 * 10; // The min amount of time the procedure will wait
final long START_TIME = Calendar.getInstance().getTimeInMillis();
final String CMD = "LIST ALL JOBS FOR USER\"" + userName + "\";";
boolean jobsObserved = false;

ResultSet userJobsResults = executeCapture(CMD);
int currentJobs = userJobsResults.getRowCount(); // the number of jobs currently active
int maxJobs = currentJobs;
if(currentJobs > 0) jobsObserved = true;
long currentTime = Calendar.getInstance().getTimeInMillis();

while(currentJobs > 0 || (START_TIME + MIN_WAIT) > currentTime){
    try{
        Thread.sleep(1000);
    }catch(InterruptedException e){
        // Do nothing, best effort to sleep
    }
    userJobsResults = executeCapture(CMD);
    // get the new count of active jobs
    currentJobs = userJobsResults.getRowCount();
    if(maxJobs < currentJobs){ 
        jobsObserved = true;
        maxJobs = currentJobs;
    }
    currentTime = Calendar.getInstance().getTimeInMillis();
}
if(jobsObserved)
    printOut("User " + userName + " execute " + maxJobs + " jobs in " + (currentTime - START_TIME)/1000 + " seconds.");
else
    printOut("No jobs were started for user " + userName + " in " + (currentTime - START_TIME)/1000 + " seconds.");

printOut("Killing all the low priority jobs executed by '" + sUserName + "'.");
//list all jobs running by the user
ResultSet oResultSet = executeCapture("LIST ALL JOBS FOR USER '" + sUserName + "';");
if(oResultSet.getRowCount() > 0) {
    boolean atLeastOne = false;
    oResultSet.moveFirst();
    while(!oResultSet.isEof()) {
        //get job id
        int id = Integer.parseInt(oResultSet.getFieldValueString(JOB_ID));
        ResultSet job = executeCapture("LIST ALL PROPERTIES FOR JOB " + id + ";");
        String priority;

        job.moveFirst();
        //get job piority
        priority = job.getFieldValueString(JOB_PRIORITY);
        if (priority.equalsIgnoreCase("low")) {
            atLeastOne = true;
            execute("KILL JOB " + id + ";");
        }
        oResultSet.moveNext();
    }

    if (!atLeastOne) {
        printOut("User '" + sUserName + "' has no low priority jobs.");
    }
} else {
    printOut("User '" + sUserName + "' has no jobs.");
}
Unless otherwise stated, the content of this page is licensed under Creative Commons Attribution-ShareAlike 3.0 License