jKQL Scripts (Admin Settings)

The functionality described in this article is available in XRay versions 1.4 and later, as well as Track versions 11.0 and later.

jKQL Scripts allow custom processing functionality to be executed.  For those familiar with SQL systems, these are analogous to stored procedures/functions.  With them, data can be loaded from jKQL data store, processed, and written back out to data store and/or returned for display in the user interface.

jKQL Script definitions are kept in jKQL data store.  The definition contains either the complete text for the script, or a URI from which to retrieve the text.  jKQL scripts can be implemented directly using jKQL, or through the user interface. Some examples of the direct implementation of jKQL scripts are shown below. These are defined using the Upsert statement:

Upsert Script Name = 'TestScript', Text = 'var rs = executeJKQL(\'Get number of events for latest year group by eventname\'); setReturnResult(rs);'

Upsert Script Name = 'TestUrl', Url = 'file:/home/me/example.js', Properties = ('FilterField'='STRING', 'FilterValue'='STRING', 'GroupField'='STRING'), Options = ('MaxRawRows'=30000)

Add a script

In version 1.6, jKQL Scripts are no longer required to be defined within the context of a single repository. Instead, each jKQL statement executed by the script can be run against any specific repository in the same organization as the "default" repository (that is, the one in which the script is being run). Also, the user who is running the script must have access to the specified repository.

Refer to the jKQL User Guide v1.6 for information on the following new methods added to the the jKQL Script API. These methods accept a repository in which to run the jKQL statement or upsert:

    executeJKQLInRepo(repoId, jkql)
    executeJKQLOnResultInRepo(repoId, jkql, rs)
    upsertInRepo(repoId, fieldValues)

To add a script through the user interface, start on the jKQL Scripts tab of Admin Settings.

1. In version 11.1 and earlier, go to Main Menu > Admin Settings.
   In version 11.2, go to the left toolbar and select Administrator Settings > jKQL Scripts.
2. Select the jKQL Scripts tab.
   
3. Click Create.
4. In the Script editor (see jKQL Create Window below for an example), enter the script that you want to save. It will take the form of JavaScript code that interfaces with jKQL. For example:

var startTime = getScriptParam('starttime');
var endTime = getScriptParam('endtime');
var interval = getScriptParam('interval');
var query = "get number of activities fields avg(elapsedtime) as elapsedtime where starttime > '"
    + startTime
    + "'  and starttime <= '"
    + endTime
    + "' group by starttime bucketed by " + interval;
var rsActivities = executeJKQL(query);
for (var arow = 1; arow <= rsActivities.getRowCount(); arow++) {
    var dataset = createDataset();
    dataset.setField(FieldType.DATASET_NAME, 'TestDataset1');
    dataset.setMapFieldKey(FieldType.PROPERTIES, "time", rsActivities.getValue(arow, 1).getBegin()); // starttime
    dataset.setMapFieldKey(FieldType.PROPERTIES, rsActivities.getColumnName(2), rsActivities.getInteger(arow, 2)); // number of rows
    dataset.setMapFieldKey(FieldType.PROPERTIES, rsActivities.getColumnName(3), rsActivities.getTimeInterval(arow, 3)); // average elapsedtime
    upsert(dataset);
}
executeJKQL("gt datasets fields all")

You can even write an entire JavaScript program (that interacts with JKQL) and save it as a script. To learn how to call jKQL from within the JavaScript, refer to the Script section of the jKQL User’s Guide.

5. In the panel on the right side, enter the name of the script in the jKQL Script Name field, and select the Repository it will use.
6. Use the Time Period parameter to define the timeframe for which you want the query and the script to be applied.
7. Click Save to save the script for future reuse.
8. Use the jKQL Script Parameters area to enter any runtime values for the parameters that you are asking users to enter via the ‘getScriptParam’ function (such as 'starttime'). These parameters are displayed immediately after a script is saved, and whenever a script is run.
9. Click Run. Results are displayed in a Results Viewlet in the Console panel.

Before data can be made available to queries, it must be committed to index files in Solr. Therefore you may experience a delay before results become available. After you run a script, try waiting several minutes, then use a temporary viewlet to query your results (click the Create temporary viewlet button  next to the Console label).

10. Click Close to return to the jKQL Scripts list.

Edit a script

1. Click the Edit icon . The Script editor opens.
2. Make your changes to the script. See Add a script above for details.
3. Click Save.

Clone a script

The functionality described below is available in XRay versions 1.6 and greater. 

1. Click the Clone icon . The Script editor opens. The jKQL Script Name of the new script is the name of the cloned script followed by "copy."
2. Update the jKQL Script Name field to rename the new script as needed.
3. Make your changes to the new script. See Add a script above for details.
4. Click Save.

Import a script

1. Click Import (in the lower-right corner of the screen).
   
2. Click Choose files to navigate to and select one or more files. Or drag one or more files onto the outlined area.
3. Click Next to preview the script. If you already have a script of the same name, you must choose Yes to override the existing one.
   
4. Click Finish to view the imported script in the list of jKQL scripts.

Export a script

1. Select the checkbox of the jKQL script you would like to export.
   
2. Click Export (in the lower-right corner of the screen). The browser indicates that the file has been downloaded.
   
   You can find the file in your default downloads folder.
   

jKQL Create Window