Case Study 4 - Working with Variables

This case study offers insight to ActiveBatch variable usage. The exercises will be more of a general exploration of variables, as opposed to presenting a problem and solution scenario. Variable types that will be discussed include Active and Execution.

In this case study, the following ActiveBatch objects will need to be created: Plan and Jobs.

The Plan is where the jobs will be stored. The Plan will be the object triggered.
The Jobs will be used to demonstrate variable uses.

Active Variables

In the previous case studies we defined constant variables like app_path and ParmVar. In this case study we will take a look at using Active Variables. An Active Variable stores a value that is obtained from a data source. A variety of data sources are built-in to the ActiveBatch product. Some examples include SQL (querying a SQL database), WQL (WMI query language), INI file (reading a .ini file), XML file, Registry, etc. The advantage of using an Active Variable is if the data source changes, the Scheduler will pull in the most recent value from the data source, whenever the job runs. You don't have to modify a hard-coded value, like you would if a constant variable value changed.

Note: Active Variables are referenced with ${} syntax.

Active Variables are processed when the Plan/Job is instantiated (i.e. a trigger occurs and an instance is created). The Job Scheduler looks for variable references enclosed in ${} (for both Active and Constant), and resolves them. By the time the job begins execution, the Active Variables have already been processed, and therefore they do not change during execution. They are called Active Variables because the values in the data source may change over time.

There are a few ways in which Variables (Active and Constant) are used in ActiveBatch. This includes:

Soft-coding object properties. This is especially useful when you have multiple jobs configured with the same property value. Should the value change, it's easier to change a single variable value as opposed to modifying multiple jobs. We touched on this in Case Study 2.
Passing data from a job to a plan or from job to job. For example, one job may read a certain value (let's say a customer ID) as part of its processing and then need to pass that data to the next job. The data to be passed may sometimes originate from within ActiveBatch or as part of the job execution itself. We touched on this topic a bit in Case Study 3, when we briefly examined the power of passing variables from JobD to JobE using the Set Variable Jobs Library Job Step. We will take another look at this feature in this case study.
To serve as a type of constraint, where you check an external data source prior to allowing a job to run. For example, you can run a SQL query that returns the record count of a database table. The count is stored in an Active Variable. Using constraint logic, you can specify what value the record count variable must be before the job can run (for example, the variable value must be greater than 0). This means there must be at least 1 record in the table for the job to run. If running the job without records in the table would not make sense (because the job processes the records), then setting up a variable constraint would prevent the job from executing if the constraint is not met.

Execution Variables

Execution Variables are mainly used in Jobs Library Jobs. They are processed while the job is executing, not before, like Active and Constant Variables. That is why they are called Execution Variables. They are also referred to as runtime or context variables.

Note: Execution Variables are referenced with %{} syntax.

Below are some key points regarding Execution Variables, as they relate to the Jobs Library Job type.

All Jobs Library job steps return their Status (Success or Failure) in an execution variable.
Some Job steps return additional data in an execution variable.
Following Job step(s) can access a prior step’s Status or additional return data (if applicable) by referencing the <StepName>.Status execution variable.
All looping Job steps (For Each Row, For Each File, etc.) return data that is accessible using an execution variable within the loop.
The Expression step located in the ActiveBatch category of Job steps lets you create your own execution variable(s) that following steps can access.

Now that we have some basic information about variables, let's go ahead and start creating our objects.

Creating a Plan

Right-click on the UsersGuideCaseStudies folder in the Object Navigation pane, then select New > Plan. The plan's property sheets will be tabbed in the Main view. On the General property sheet, enter a Name/Label of CaseStudy4.

We will now create an Active Variable on the plan. All child jobs placed in the plan will have access to the variable.

Registry Active Variable

Click on the plan's Variables tab. Click on the Add button. In the Name/Label property enter: PathVar.

Click on the ActiveVariable radio button. From the Active Variable dropdown select RegistryValue. We will be accessing the Windows Registry on the Scheduler server. We will be returning the ActiveBatch Installation Path in a variable. When you install/configure the Job Scheduler, Registry entries are made, and the installation path is stored as a value.

Next, enter the values as described below:

Computer - Keep the default value of . (dot) - This resolves to the Job Scheduler system

Hive - Keep the default value of HKEY_LOCAL_MACHINE

Key - Enter: SOFTWARE\ASCI\ActiveBatch\V140

Value - Keep the default value of InstallPath

Note: For the Key value, change V140 to V120 if you are running on ActiveBatch Version 12.

Credentials - Click on the Credentials dropdown arrow and navigate to UsersGudieCaseStudies > Objects folder and select TrainingAccount. This is the account that the Job Scheduler will use to resolve the Registry variable. Since we will be triggering the Plan, the Credentials property must be entered to resolve the variable. If we triggered a job in the plan, and not the plan, the system would default to the job's Execution > User Account to resolve the variable.

Note: There are many facts to learn about ActiveBatch variables. We must limit the scope of the discussion to avoid detail overload. Please see the Variables section in the ActiveBatch Reference Manual for more information.

See the image below for the PathVar variable settings. Note: Variable names are case-insensitive.

Click OK to save the variable.

Click Save and Close to save the Plan.

Creating a Job

Right-click on the CaseStudy4 plan in the Object Navigation pane, then select New > Job. The job's property sheets will be tabbed in the Main view. On the General property sheet, enter a Name/Label of JobA. Next, click on the Jobs Library tab.

In the topmost Execution Properties, you should see the Submission Queue set to /UsersGuideCaseStudies/Objects/Server1, the User Account set to /UsersGuideCaseStudies/Objects/Training Account, and the Job Type set as Jobs Library. As a reminder, these properties are pre-populated because in CaseStudy2, we set a default Job Policy. If you do not see these properties set as described, please revisit CaseStudy2 and configure the Job Policies.

Using the Jobs Library ToolBox, expand the General category and drag the Log step to the workspace and drop it where it states "Drop job step here".

In the Text property, enter: ${PathVar}. See the image below.

The log step will capture the value of the variable PathVar in the job log file. This is a simple example of referencing an Active Variable. You can reference the variable in any job property where variable substitution is supported.

Click Save and Close to save the job.

Executing the Plan

In the Object Navigation pane, click on the CaseStudy4 plan. This will set the Instances view focus to the CaseStudy4 plan. Note: If you do not see the Instances pane, click on Open/Close Panes in the lower right-hand corner of the UI, then select the "Instances" menu item. Once you have accessed the Instances Pane, make sure the filters will include the plan you will be triggering shortly. For example. to see today's instances for all status types (e.g. failed, succeeded, etc.), set the filters accordingly. You must click the refresh icon (far left, on the toolbar) whenever a filter is changed.

Next, right-click on the CaseStudy4 plan and select Trigger. Navigate to the Instances view and expand CaseStudy4 to show JobA. The plan and job should have run successfully.

Next, still using the Instances view, right-click on the CaseStudy4 plan and select Properties. Click on the Variables tab, as depicted in the image below. Active Variables are displayed with a purple icon to the left of the variable name.

Observe the PathVar variable. You should see something similar for the value of PathVar (where the only difference may be the ActiveBatch version). If the variable failed to resolve, you would see a message stating the reason for the failure. Note: Variables failing to resolve does not result in an automatic failure of a job attempting to reference it. However, there are property settings that are available to force the failure of a plan or job if the variable doesn't resolve. One option is to check the "Strict Variable Processing" checkbox on the plan or job's Variables property sheet. If any variable in the collection fails to resolve, the job won't be dispatched. The failure message in the Instances view will be: "some variables are missing".

Next, still using the Instances view, right-click on JobA and select Properties. Click on the Log tab. You should see something like the image below. Job log files capture standard errors and standard output as jobs execute. Whenever a Jobs Library Job runs on a Windows system, you will see some additional information added, such as "The workflow has started" and "The workflow has completed sucessfully". In the example below, the Log step wrote out the PathVar variable value, which was captured in the log file, outlined in red below.

SQL Query Active Variable

In this next section we will provide a SQL Query Active Variable example. The SQL Query data source is quite powerful and allows you to issue SQL Query commands to populate an active variable. You can follow along and add this variable providing you have access to a SQL Server database, and have the ability to create the table described below. ActiveBatch does not create the table, it must be done by you or someone in your organization. If you can't create the table, perhaps there is a SQL Server database table you are allowed to query, and can take what you learn from here and generate your own query.

In this example, we are going to sprinkle in some Constant variables, so you can see how they can optionally be used when defining an Active Variable.

Modifying the Plan

The following 3 constant variables need to be created on the CaseStudy4 plan. We will describe how to create the first one. From there, you should be able to create the remaining two.

DbServer - This is the name of the SQL server that will host the example database.
DbDatabase This is the name of the example database.
DbTable This is the name of the database table.

Right-click on the CaseStudy4 plan in the Object Navigation pane, select Properties, then Variables. Click on the Add button. The Variables dialog will pop up. Assign the variable a Name/Label of DbServer. Enter a value in the Constant Variable property that identifies the name of the SQL Server you wish to access. Click OK to save. Add the remaining two variables as described above, and enter your database name for DbDatabase, and enter your table name for DbTable. When you are done adding the variables, you should see the new variables on the Variables property sheet, along with PathVar, created earlier.

Click Save and Close to save the plan.

Below is the table we are using for this example. It's named CustomerTable, and it consists of the definition described below. If you can create this table in your SQL Server environment, please do so. Create it on the Server you specified in the DbServer variable, and add it to the database you specified in the DbDatabase variable.

CREATE TABLE CustomerTable

(

nCustomerID INT IDENTITY,

sCustomerName VARCHAR(50),

sAddress VARCHAR(50),

sCity VARCHAR(50),

sState CHAR(2),

sZip CHAR(5)

)

After creating the table, populate it with a few rows, as you see depicted below.

Now that we have our table created, let's go ahead a create a new Active Variable.

Right-click on CaseStudy4 in the Object Navigation pane, select Properties, then Variables. Click Add. Enter a Name/Label of SQLQuery. Click on the Active Variable radio button, and from the dropdown list, select SqlQuery.

Next, enter the values as described below:

Connection String - Enter: Provider=SQLOLEDB.1;Initial Catalog=$(DbDatabase};Data Source=${DbServer};Integrated Security=SSPI;Persist Security Info=False; This property identifies the parameters required to connect to the database server.
User Account - Leave this property blank because we are using Windows Integrated Security for access to the database, therefore you don’t need to specify database authentication credentials.
Command Text - Enter: SELECT * FROM ${DbTable} WHERE sCustomerName = ‘Thomas Jones' This query will return all the columns for the record that matches Thomas Jones.
Credentials - Specify security credentials which is the account that the Scheduler will use to resolve the variable. From the dropdown, select the TrainingAccount in the UsersGuideCaseStudies > Objects folder.

Notice how the Connection String and Command Text are using context variables as part of the string. Assume you have other jobs you plan on adding to CaseStudy4. Some jobs will be accessing the same database on the same SQL Server. Some may even access the same table. If you set up these values as variables, it will be much easier to update a variable value should something change. For example, if the SQL server database is moved to another server with a different name, you would simply need to modify the DbServer variable value.

The SQL Query is designed to return one row of data. If the command text you enter results in multiple rows being returned, only one is kept and the rest are discarded.

Click OK to save the variable.

Click OK to save the plan.

Modifying the Job

Using the Object Navigation pane, right click on JobA in the CaseStudy4 plan, then select Properties > Jobs Library. Using the Jobs Library ToolBox, expand the General category and drag the Log step and place it under the existing Log step created earlier. The new step will automatically be named Log_2.

In the Text property, enter: ${SQLQuery.sCustomerName}. The Log step will write the value of the sCustomerName field to the job log file.

Click Save and Close to save the job.

Executing the Plan

In the Object Navigation pane, right-click on CaseStudy4 and select Trigger. The plan and job will run.

The SQL query will yield a single row of data stored in the SQLQuery variable. Navigate to the CaseStudy4 plan in the Instances view, and expand it to access the job. Right-click on JobA, then select Properties > Variables. You will see something similar as depicted in the image below.

Observe the variables DbDatabase, DbServer and DbTable. They all have a ../ in front of them. This means the Scheduler went up one level of the job's full path to obtain the variable values. Remember, we added the variables to the job's parent container (CaseStudy4). Therefore, the ../ is letting us know that the variables were defined on the container one level up (i.e. the job's parent container). If we defined the variables on the UsersGuideCaseStudies folder, we would see ../../ (two ../) in front of the variable names, indicating the system searched two levels up to locate the variables to resolve them. If we defined the variables on the job itself, there would be no level indicators in front of the variable name.

As per the SQLQuery variable expanded in the above image, an entire row of data has been returned. Each field and its value is listed. To reference the variable in a job, use the following syntax: ${VariableName.FieldName} - for example ${SQLQuery.sCustomerName}. The period is used to syntactically separate the active variable (i.e. SQLQuery) from the fields that comprise the structure.

WQL (WMI) Query.

A WQL query is syntactically similar to SQL and is used by WMI (Windows Management Instrumentation) to query various objects and their properties. Since almost every Windows object and property is contained in WMI, this query can be extremely useful. WMI is so useful that we’ve written several canned data sources for you. For example, you can query the state of a service; obtain the free disk space of a local device, etc. These are available as Active Variables.

Our first example uses the WQL query. In this case we want to lookup the process ID of a specific running process. We will use the Job Scheduler process in this example, because we know the process exists.

Let's add the variable to the CaseStudy4 plan.

Modifying the Plan

Right-click on CaseStudy4 plan in the Object Navigation pane, select Properties, then Variables. Click on the Add button. In the Name/Label field enter WQLAbatJSS. Click on the Active Variable radio button, then select WMIQuery from the dropdown list.

Next, enter the values as described below:

Computer - Keep the default value of . (dot) - This resolves to the Job Scheduler system
Namespace - This is the WMI namespece. Keep the default value of root\cimv2
Query String - Enter: SELECT * FROM Win32_Process WHERE Name = 'abatjss.exe'
Credentials - Using the dropdown, navigate to UsersGuideCaseStudies > Objects and select TrainingAccount. This is the account the Scheduler will use to resolve the variable.

Click OK to save the variable.

Click OK to save the Plan.

Creating a Job

Let's create a new Job that will reference this variable. Right-click on the CaseStudy4 plan in the Object Navigation pane, then select New > Job. The job's property sheets will be tabbed in the Main view. On the General property sheet, enter a Name/Label of JobB. Next, click on the Jobs Library tab.

In the topmost Execution Properties, you should see the Submission Queue set to /UsersGuideCaseStudies/Objects/Server1, the User Account set to /UsersGuideCaseStudies/Objects/Training Account, and the Job Type set as Jobs Library. These properties are preset as a result of default Job Policies we created in Case Study2.

Using the Jobs Library ToolBox, expand the General category and drag the Log step to the workspace and drop it where it states "Drop job step here".

In the Text property, enter: ${WQLAbatJSS.ProcessID}. See the image below.

The WQLAbatJSS variable will return just about all the properties for the abatjss process. We are only using one of many in the Text property. If you consider the numerous objects that make up Windows and your ability to retrieve one or more properties of any of those objects, this is quite a powerful feature.

Next, click Save and Close to save the job.

Executing the Plan

In the Object Navigation pane, click on the CaseStudy4 plan. This will set the Instances view focus to the CaseStudy4 plan.

Next, right-click on the CaseStudy4 plan in the Object Navigation pane, then select Trigger. Navigate to the Instances view and expand CaseStudy4 so you can see JobB. The plan and job should have run successfully.

Next, still using the Instances view, right-click on the CaseStudy4 plan and select Properties. Click on the Variables tab, as depicted in the image below. Expand WQLAbatJSS. Like the SQL Query variable, the WQL Query variable returns a structure with many values. To access any one of the many values, the ${VariableName.FieldName) syntax is used. In our case, we referenced ${WQLAbatJSS.ProcessID} in JobB, but there are many other values to choose from. It all depends what you need from the return data.

Next, still using the Instances view, right click on JobB and select Properties, then click on Log. You see a numeric value that is the abatjss.exe process ID. The first three lines should look something like what is depicted below (your process ID will be different). In our example, the ID is 4320.

13:50:04 I The workflow has started

4320

13:50:04 I The workflow has completed successfully

Next, click on JobB's Variables tab. You will see the same WQLAbatJSS variable, but with a ../ in front of it, symbolic of the one level the Scheduler had to go up to find the variable definition, then resolve it.

Next, several of the active variable data sources are actually canned WQL queries such as Service State, Disk Free Space, etc. See below the example of the ServiceState Active Variable. You can opt to add this variable to the CaseStudy4 plan, if desired. It is optional to do so.

Service State

In the image below, this is what you would expect to see as the variable's return value if the service was running.

Date Arithmetic

The ActiveBatch Date Arithmetic Active Variable lets you perform date calculations and format the results. Let’s look at a few examples.

The syntax when configuring a Date Arithmetic Variable is <Command/Optional Qualifier(s)>

For example, <TODAY> returns the current calendar date in the default format, which is a two digit day, the month name abbreviated using 3 characters, then the two digit year (i.e. DDMMMYY). For example, if today was February 16, 2024, <TODAY> would return 16Feb24. If you want to use a different format, use the format qualifier such as <TODAY/Format="mm/dd/yyyy">. This would return 02/16/2024.

In the example below, a Date Arithmetic variable has been configured. Observe the Date Tag Expresson <BOM/+Day=2>. This reads: Get the date for the beginning of the current month (BOM), then add 2 days to it (+Day=2). If the current month is February, the date returned would be 03Feb24. You can opt to add one or more Date Arithmetic variables to the CaseStudy4 plan, if desired. It is optional to do so.

If you added the Service State and/or Date Arithmetic variables to the CaseStudy4 plan, save the plan, then trigger it. Navigate to the CaseStudy4 instance, right-click on it, then select the Variables tab. Observe how the variables resolved.

Execution Variables

Execution Variables are primarily used in the Jobs Library. They are updated as the job is running. Execution variables are accessed using %{} syntax.

Let's add a few steps to our existing JobB, which will access some execution variables.

As a reminder, all steps return their status (success or failure) in an execution variable, and some steps return additional data in an execution variable. You can also create your own execution variable by using the Expression step located in the Step Editor's ToolBox, in the ActiveBatch category. Any following step can access execution variables.

Modifying a Job

In the Object Navigation pane, right-click on JobB in the CaseStudy4 folder, then select Properties, then Jobs Library. There is already a Log step from the prior example. You can keep it, delete it or disable it. To delete it, click on the red x on the Step's title bar. To disable it, uncheck the box in the Step's title bar (to the left of the step's name). Any step that is not checked (i.e. disabled) is ignored runtime. As a reminder, the steps execute from top to bottom.

Next, using the Jobs Library ToolBox, expand the General category and drag the EmbeddedScript step and drop it under the Log step (if you didn't delete it. If you did, drop it in the empty workspace).

Set the following Embedded Script properties:

Extension - Select .cmd from the dropdown

Script - Enter the following:

@echo off

set a=3

set b=4

set /a "c=%a%+%b%"

echo The sum is: %c%

Success Code Rule - Enter: 0

The above script adds two numbers (3 and 4) and echoes the hardcoded text "The sum is:" followed by the sum, which is stored in the variable named "c". The Success Code Rule property is set to 0, meaning the script must exit with a 0 to be considered a success.

Next, using the General category, drag a Log step and drop it under the EmbeddedScript step.

In the Text property, enter: Status of Step: %{EmbeddedScript.Status} This step writes the status of the EmbeddedScript step to the job log file (success or failure). Note: When you initially enter %{, you will be presented with a pop-up helper allowing you to select any step that is above the current one. When you select EmbeddedScript, you must then type a period, which will result in another pop-up helper, allowing you to select Status. After doing so, type a closing curly brace }.

Next, in the using the General category, drag another Log step and drop it under the previously added Log step.

In the Text property, enter: Return Value of Step: %{EmbeddedScript.Returnvalue.Output} This step writes the output generated by the EmbeddedScript step to the job log file. Note: When you initially enter %{, you will be presented with a pop-up helper allowing you to select a step that is above the current one. When you select EmbeddedScript, you must then type a period, which will result in another pop-up helper, allowing you to select ReturnValue. After selecting ReturnValue, type another period, which after doing so results in another pop-up helper. Select Output, then type a closing curly brace }. Note: In addition to the script's output, you can also access the script's exit code.

When you are done, your job should look like the image depicted below.

Click Save and Close to save the job.

Executing the Plan

Navigate to CaseStudy4 in the Object Navigation pane. Click on it to set focus to the Instances view. Next, right-click on CaseStudy4 and select Trigger.

Access the Instances view, expand CaseStudy4, right-click on JobB, then select Properties. Click on the Log tab. See the image below. Outlined on the right are the results of both log steps.

Update JobB

Let's take this example a step further. Assume you want the output results of JobB to be passed to another Job. To do this, let's make a change to JobB. Expand the ActiveBatch category, then drag the Set Variable step and drop it under the last log step.

Set the following properties:

Variable Name: PassedVar

Value Value: %{EmbeddedScript.ReturnValue.Output}

The above step creates a variable on-the-fly name PassedVar. The variable value is the output of the .cmd script. It will be passed to another job using a completion trigger. Before we can configure the completion trigger on JobB, we have to create the job that the variable will be passed to.

Create another Job

To save a little bit of time (to avoid creating a new job from scratch), right -click on JobB in CaseStudy4, then select Copy. Next, right-click on CaseStudy4, then select Paste.

You will see a new job named JobB_Copy1 in the CaseStudy4 folder. Right-click on JobB_Copy1, then select Properties. On the General property sheet, remove the existing Name/Label values and replace them with JobC.

Next, click on the Jobs Library tab. Keep one log step (it doesn't matter which one), then delete all the other steps (by clicking on the step's red x in the title bar).

Delete the Text value in the remaining Log step, and replace it with: Passed variable value: ${PassedVar}. This job will echo out the variable passed to it by JobB.

Click Save and Close to save JobC.

Update JobB

Next, we have to add a completion trigger to JobB. This is the mechanism that is being used to pass the variable value from JobB to JoBC. We learned in earlier case studies that the quickest way to configure a completion trigger is to use the Map view. So let's pull up the Map view. Right-click on CaseStudy4 in the Object Navigation pane, then select View > Map. Click on the icon above JobB (the Rubik cube), then drag your mouse towards JobC. Release the mouse. An Add Completion Triggers/Constraint dialog will pop up. Keep the default values (Trigger / Success), and click Save. You will see a green line (symbolic of the completion trigger) originating from JobB and pointing to JobC.

Executing the Plan

Right click on CaseStudy4 in the Object Navigation pane, then select Trigger.

Access the Instances view, expand CaseStudy4, right-click on JobC, then select Properties. Click on the Log tab. See the image below. Outlined is the variable value that was passed from JobB to JobC on a completion trigger.

Summary

Active Variables provide a powerful way of retrieving information from an ActiveBatch data source. All the data sources are built in to the product and you can't add your own data sources. In addition, the data sources are for read-only purposes (i.e. you can't use the Active Variable data source to write to it). If the data source returns a single value, it is accessed with ${VariableName} syntax. If the data source returns a structure, the variable is referenced with ${VariableName.FieldName} syntax. Active Variables can also be used as constraints (something we didn't go over in this case study). All Active Variables require security credentials to operate, and those credentials are used to access the data source. There are some nuances regarding the credentials for Active Variables - see the ActiveBatch Reference Manual for more details. Active Variables are resolved by the Job Scheduler, before the job is dispatched to run. Execution Variables are typically used in Jobs Library Jobs. They are evaluated run time, and their syntax starts with a % sign - e.g. %{VarName}. Variables can be passed from one job to another. To do this, use the Set Variable or Set Variables Jobs Library job step. There is also a command line option for passing variables, named AbatSet. See the ActiveBatch Reference Manual for more details.

Spotted a typo or can’t find help on a certain subject? Reach out to the Documentation team at docsupport@redwood.com.