Events

This series of mini-case studies will begin to expand the use of events as a means of triggering a job. Events are typically external conditions that should they occur are to act as a stimulus for triggering a plan or job instance.

For our first mini-case study we will look at the File Trigger mechanism. A File Trigger is a mechanism within Microsoft Windows and ActiveBatch that allows a designer to indicate that should a file be created, appeared, deleted or modified, that action will cause an associated plan/job to begin execution. For most users, a file creation trigger is the typical situation. For example, a customer FTPs a file to a machine and rather than poll every few minutes looking for the presence of the file. For our second mini-case study we will issue a WMI Event Trigger using WQL. We’ll create a plan named Events to hold the mini-case study jobs in this chapter.

File Trigger Events

Specific File

Let’s suppose we need to process a certain file that a customer FTPs to us. In the past, we would have written a script that looped every few minutes and essentially polled to determine whether the file really existed. The more straightforward approach would be to simply initiate the job when the file actually arrived and the FTP process completed populating it. This straightforward approach is the File Trigger event. Events are part of the Trigger category of Plan and Job properties and File Trigger is a type of event.

In the example above the file specification we’re waiting for is ${app_path}\test.txt (“app_path” is a constant variable whose value was set to “c:\ActiveBatchTutorial”). The Filter field is set for Created. To change the filter to support other file directory operations click the … button. This will bring up a checkbox dialog for you to select any of the file directory operations that ActiveBatch supports. For this example we want to know when this file is created.

For Windows based systems, the File Trigger, Filter and Recursive properties are sufficient. For non-Windows systems you also need to specify an Execution Queue (that represents the machine the file is located) and User Credentials so ActiveBatch can access the file and directory for event processing. Windows systems can also specify Queue and/or User properties. Specifying a Queue avoids the centralization of file trigger processing on the Job Scheduler’s machine. Specifying a User allows specific security credentials to be used instead of the Job Scheduler’ service account (which may be problematic if the file is located on a non-trusted system).

To cause this job to be triggered, we simply copy a file named “test.txt” (to the appropriate directory) and the job will begin to execute.

The instance properties reveal more about the file trigger event.

Note the Execution Reason. The reason the job ran was due to being “Event Triggered”.

The image above is an extract of the job variables section. Notice the @Trigger variable. This built-in structure variable provides information to the job (or plan) about the nature and information of the triggering event. The .Type variable indicates that this is a File event. The .ChangeType indicates it was a Created operation. The .FileName variable provides the actual file specification that caused the trigger (more about this a bit later). For more advanced usage the .MofText variable provides a Managed Object Format (MOF) representation of the event. This will be useful when we discuss WMI.

Directory Events

In the previous Case Study we needed to know when a specific file arrived. That’s fine but another problem to be solved is when you don’t know the actual name of the file. You know its directory path and perhaps its extension. For example, let’s modify the earlier FTP problem and say that customers will be sending files with extensions of .FTP to our C:\TEST directory. We’ll also one step further and show several methods in which we can get and use the actual filename that was created.

We’ll create a job named “DirectoryFileTrigger” under the Events plan. This job will be an embedded script job.

The VBscript demonstrates two methods for obtaining the name of the actual file that triggered this job. The built-in variable @Trigger is set when a job/plan is event triggered for execution. @Trigger.FileName is set with the full file specification of the actual file that triggered the object. We’re substituting the value directly into the script as well as passing the file as an argument through the Parameters property.

Let’s move on to the Scheduling – Events category. The File Trigger would look like this:

Note the wildcard use in the trigger specification. That’s reasonably intuitive but leaves us with the problem of being triggered for a file that we really don’t know the name of.

Copy the file to the appropriate location (by default that’s “c:\ActiveBatchTutorial\*.ftp” we used “FileA.ftp” in the example below).

The @Trigger variable shows the complete breakdown of the file that actually triggered the job. Note also that the @Arguments variable shows the file specification again. That’s because @Arguments is a mirror of the Parameters property in the job.

Let’s go one step further and examine the log file of the job. We should see two lines that show the same data through two different mechanisms.

The third line shows the data as passed in through the Parameters property. The fourth line shows the data as directly substituted in the script.

WMI Events

WBEM is the industry-wide term for Web Based Enterprise Management and was created by the industry Distributed Management Task Force (DMTF) that created standards for allowing information interchange between hardware and software vendors. The WBEM initiative lets vendors write Information/Event Providers that communicate with WBEM software using a database “schema” approach. This schema is referred to as the CIM (Common Information Model). Customers write WBEM consumers that ask WBEM providers for object information.

The beauty of this approach is that a single extensible database is available for a wide and disparate variety of sources. WBEM provides a point of integration through which data from management sources can be accessed, and it complements and extends existing management protocols and instrumentation such as SNMP, DMI and CMIP1. The Microsoft implementation of WBEM is known as Windows Management Instrumentation or WMI. WMI is integrated into all releases of Microsoft Windows since 2000 (and as a free add-in for Windows NT). Since the CIM is essentially a database, WMI has a query language named WQL that is patterned after SQL. Two (2) types of queries are possible: Information and Notification. In this section we’ll examine the use of WMI and it’s notification query facility. Notification means we want WMI to notify us (an event) when the request can be satisfied. ActiveBatch fully supports WMI as it is both a provider and consumer of WMI information.

For example, suppose you wanted a job to begin execution when the free space on drive C: dropped below 30 percent. While we could trigger a job that polled the disk free space, an event is more efficient. WMI Events are declared on the same page as File Trigger events (see Scheduling\Events). While this manual cannot substitute for a WMI manual we will provide some brief information on the overall structure of a notification query. We recommend that you run the utility WBEMTEST which is a part of every Windows NT-based operating system since 2000 (it’s a free add-in for Windows NT).

All WQL event query statements begin with the word SELECT. A typical WQL event query looks like this:

SELECT * FROM eventclass {WITHIN | GROUP | HAVING}

WHERE property Operator value

The eventclass is any WMI class that supports notification queries. Not every class does, however, three (3) generic classes are available that can make any class essentially support a notification query: __InstanceCreationEvent, __InstanceModificationEvent and __InstanceDeletionEvent. Note the double underscores (__) before each class name. These three classes allow you to generate events based on new instance creation, modification and deletion (just as File Trigger allowed you to generate events on file creation, modification and deletion.

For example our event query concerning freespace on drive C: might look like this:

SELECT * FROM __InstanceModificationEvent WITHIN 60

WHERE TargetInstance ISA “Win32_LogicalDisk”

AND TargetInstance.DeviceId = “C:”

AND TargetInstance.Freespace < 1000000

This event query checks every 60 seconds as to whether logical disk device “C:” freespace drops below 1 million bytes. If so, an event would be generated which would trigger the job. Let’s breakdown the syntax:

SELECT * FROM __InstanceModificationEvent is standard WQL syntax, the __InstanceModificationEvent means that you’re looking for something that modifies an existing instance (as opposed to the creation of a new instance, for example, the running of a new process). This class also means that the actual class you’re interested in (Win32_LogicalDisk) doesn’t natively support notification events. When you use the __Instance classes, you will also need to specify a WITHIN time clause. In the above example, the WITHIN 60, means that every sixty seconds WMI will check whether a notification should be produced. WHERE establishes the notification criteria (just as WHERE for an information query establishes the search criteria). TargetInstance ISA “Win32_LogicalDisk” means the __Instance class should examine the properties of the class Win32_LogicalDisk. If you’re a C/C++ engineer, think cast. The remaining AND TargetInstance.DeviceId = “C:” AND TargetInstance.Freespace < 1000000 uses the Win32_LogicalDisk properties to refine the notification criteria.

Another example:

SELECT * FROM __InstanceModificationEvent WITHIN 30

WHERE TargetInstance ISA "Win32_Service"

AND TargetInstance.Name = "Tlntsvr"

AND TargetInstance.State = "stopped"

This event query checks every 30 seconds as to whether the “Telnet Server” service transitions to “stopped”. If it does, an event is generated and the job would be triggered.

Now that we’ve described some events let’s see how we can define this event within ActiveBatch.

The WQL query is the same as we’ve previously discussed. The Namespace for the classes we’re referencing is in root\cimv2. The WMI CIM schema is arranged within a namespace so different schemas can be referenced. CIMV2 is the one that typically contains all the Windows classes. ActiveBatch classes can be found in root\default. The dropdown menu to the right of the namespace field conveniently shows you the most popular namespaces. Remote machines are referenced by prefixing the namespace using UNC style notation (i.e. \\server\root\cimv2). Security credentials must be specified for remote machines and are ignored for local namespace access (your currently connected credentials are used).

If you now stop the Telnet Server service you’ll see this job event triggered for execution.

Note the “Execution Reason” is “Event Triggered”. Now let’s look at the Variables section.

The Trigger variable shows that .Type is set to Wmi. The machine that generated the event is ASCI62 and the MOF Text describes the entire event in more detail. To display the MOF Text in a more readable fashion click the Show MOF Text button.

For this type of notification query you will both the previous and current instance properties. Since MOF text is a built-in variable you can perform various processing based on the event.

MSMQ Event

ActiveBatch supports the triggering of plans/jobs via Microsoft’s Message Queue facility. Our mini-case study for this event requires two jobs. One (MSMQTrigger) is the job with the MSMQ trigger and the other (MSMQSend) issues an MSMQ Send operation to trigger the job.

Beginning with MSMQTrigger; the Trigger – Events for MSMQ, in our sample, looks like this:

In the example above, Machine Name is a system with an MSMQ server and Message Queue Name is the messagequeue we’re interested in.

The Job Properties of this job demonstrates how we can pass data to the job we’re triggering.

As with the other events, the built-in @trigger variable can be used. For MSMQ, @Trigger.Message contains the received message. The script above echoes the data to the job’s log file.

The following Job Step Editor shows that portion of the job that sends a message to the specified MSMQ.

When this job is executed, MSMQTrigger will begin execution.

The above figure shows the @Trigger variable and its value. The job’s log file extract:

Note that @Trigger.Message has been substituted in the echo to the job’s log file.

E-Mail Trigger

ActiveBatch supports the triggering of Plans and Jobs via reception of e-mail. Two (2) e-mail methods are currently supported: Microsoft Exchange and POP3. Our mini-case study for this event requires two jobs. One (EMailTrigger) is the job with the E-Mail trigger and the other (EMailSend) composes an e-mail message that will be used to trigger the job. Beginning with EMailTrigger; the Trigger – Events for EMail, in our sample, looks like this:

In the example above, Mailbox indicates the type of e-mail trigger method. ExchangeMailBox or MailBoxPop3. This example will be using Exchange. The two (2) sub-properties are MailServer which should be entered as a fully qualified specification for your e-mail server. Credentials should be a User Account object with sufficient credentials to read the target mailbox. Filter allows you to filter the e-mail message you would like to trigger the job. In the above example, no filters have been set so any e-mail message received will trigger the job. ExclusiveWords is a list of words (or phrases), comma separated, that if specified must not appear in the message body. If more than one word is specified, each word is considered separately (as in an OR list). From, if specified, indicates a match on the sender of the message. HasAttachment if true indicates that the message must have an attachment; if false, no attachment filtering is performed. InclusiveWords is a list of words (or phrases), comma separated, that if specified must appear in the message body. If more than one word is specified, each word is considered separately (as in an OR list). Subject, if specified, indicates a list of words (or phrases) within the subject line that must appear. To represents the recipient (you can also specify a comma separated list of recipients).

ExclusiveWords, InclusiveWords and Subject also support wildcards matching. An asterisk and/or question mark (for single character wildcard) may be specified. All matches are performed in a case-less fashion.

In the above example, we’ve specified the “TrainingAccount” credentials and its associated mailbox. Under

InclusiveWords we’ve also specified “trigger message”. This phrase must appear in the body of the e-mail in order to match and event trigger the job.

Note: In the above example, we used a User Account object that we had not previously created. This was necessary because we needed to use an account that also had an e-mail account. If you attempt to reproduce this example, please make a note about the mailbox requirement for this job.

The Job Properties of the EMailTrigger job contains this embedded VBscript:

When this job is triggered the @Trigger.Subject variable will substitute into the Subject line of the e-mail. The job EMailSend consists of the following E-Mail type job:

When this job is triggered, EMailTrigger will also be triggered for execution. The variables section for @Trigger was:

(Note: The From/To fields were intentionally blurred). The log file appears below:

Web Services Trigger

ActiveBatch supports the triggering of Plans and Jobs via the establishment of a Web Services endpoint. The endpoint can be sent a Trigger message to allow the target object to be triggered for execution. You can also trigger an object and pass parameters.

The job WebServicesTrigger establishes the Web Services event triggering mechanism.

The properties that establish the Web Services endpoint are very important. The Identity property is appended to the end of the Endpoint and WsdlLocation properties in order to create a unique endpoint. Therefore the Identity property value must be unique within a specific Job Scheduler machine. In the above example, UserCaseStudy is appended to the end of the Endpoint and WsdlLocation properties. The Endpoint property is what other Web Services communicate with. The WsdlLocation property contains the WSDL that defines the ActiveBatch Trigger facility for other Web Services applications. EndpointType is an enumerated list of keywords which defines the security level of the endpoint. Basic represents a normal HTTP session. Secure ensures that the session is HTTPS. SecureCertificate indicates that the client must have a corresponding SSL certificate. SecureUserName indicates that username and password credentials are required to complete the connection to this endpoint. PublishMetadata indicates whether the underlying WSDL metadata be published. IsGeneric indicates whether all inbound SOAP messages will be allowed. XPathRule, if deployed, indicates that the specified SOAP query must be satisfied for the inbound message to trigger the object. This is particularly useful when parameters are sent as part of the Trigger. You can then filter the parameters to determine whether to honor the trigger event.

With the event trigger portion completed, our sample script will consist of a wscript.echo “${Trigger.UserDefined}”. UserDefined is a property that we will send as part of the actual Trigger itself.

Since the event definition defines both an endpoint and a wsdl document we need to create a Service Library object to establish that Web Service itself.

Service Library – Web Services

This Service Library object, named Case5WebService, defines our Web Service that was initially created as part of the event. Basically we want to use that published Web Service.

Note the single method this Web Service offers: Trigger.

Web Services – Trigger

The job WebServiceEventTrigger will actually invoke the Trigger method. We begin by associating the Case5WebService as a Service Library with this job. Next, we change the job type to “Jobs Library” and start the Job Steps Editor.

The above figure depicts the Web Service job step that will be used to trigger the WebServicesTrigger job. At the bottom on the left side is the TriggerService job step which is made available from the Service Library object. By dragging the Trigger job step into the main Steps area the job step properties can be completed. The Endpoint property contains the information listed in the endpoint of the Web Services trigger itself (not to be confused with WsdlLocation). The request property must be selected and set to TriggerRequest. Variables are normally optional but in our case we expect to trigger the WebServicesTrigger job and pass it a variable-value pair. You can see that Param1 is being equated to Param1Value. When the WebServicesTrigger job is instantiated, let’s examine two areas: Variables and LogFile.

Note that @Trigger contains the passed in variable and its value. Also, note that the variable has been directly defined as Param1 and can be used without the requirement of specifying @Trigger.Param1. In the extract of the Log File, you can see the variable substitution correctly performed.

Database Triggers

Many customers have asked whether you can trigger a job based on the data changing within a user database. This case study provides the basis for this functionality. The database and table that this case study provides is simple, however the functionality is powerful.

Two approaches can be used. The easiest approach, if your database is Oracle based, is to purchase the ActiveBatch Oracle Trigger add-in which allows a seamless approach to triggering objects based on changes within a database. With this add-in your database is not changed or modified in any manner. The second approach requires that you create a database trigger in which to invoke the ActiveBatch ‘trigger object’ functionality through an ActiveBatch COM invocation.

Database Definition

This example uses Microsoft’s SQL Server database family. You can create an empty database using the SQL Enterprise Manager utility. The table itself is described below. You can create the table using the Query Analyzer utility or AbatExecSql command. The example database was previously defined in the CaseStudy4 section.

Note: For this example, you must install the ActiveBatch COM client on the SQL Server machine.

CREATE TABLE CustomerTable

(

nCustomerID INT IDENTITY,

sCustomerName VARCHAR(50),

sAddress VARCHAR(50),

sCity VARCHAR(50),

sState CHAR(2),

sZip CHAR(5)

)

ALTER TABLE CustomerTable ADD PRIMARY KEY (nCustomerID)

INSERT INTO CustomerTable

(sCustomerName, sAddress, sCity, sState, sZip)

VALUES ( 'Company X', '1 Lois Lane', 'Metropolis', 'NY', '55555')

INSERT INTO CustomerTable

(sCustomerName, sAddress, sCity, sState, sZip)

VALUES ( 'Company Y', '2 Somewhere Drive', 'Anytown', 'NY', '44444')

INSERT INTO CustomerTable

(sCustomerName, sAddress, sCity, sState, sZip)

VALUES ( 'Company Z', '3 Nowhere Street', 'Who Knows', 'NC', '33333')

Oracle Trigger Add-in

This approach uses the ActiveBatch Oracle Trigger add-in that must be licensed for use.

In this example, an OracleTrigger is declared on the table ‘namevalues’. INSERT operations are to be examined when a column named ‘Value’ is changed to a value represented by the ActiveBatch variable ‘VALUE’. If Filter wasn’t specified then every table insert would result in an event trigger for this object.

SQL Server Database Trigger

Defining the Trigger

So at this point we have a table named CustomerTable and three (3) records. The purpose of this case study is to trigger a job whenever the state field is changed to GA (Georgia). To achieve this processing we will create a database trigger procedure. This procedure will be executed whenever an update is issued to our table. The annotated code appears below. The code itself is bold and any comments are in normal font.

CREATE TRIGGER trU_CustomerUpdate ON CustomerTable AFTER UPDATE AS

DECLARE @nJSS INT,

@nJob INT,

@sProgID VARCHAR(255),

@sSource VARCHAR(255),

@sDescription VARCHAR(255),

@nCOMResult INT,

@sMsg VARCHAR(255),

@sJSSMachine VARCHAR(20),

@sJobName VARCHAR(50),

@sUsername VARCHAR(MAX),

@sPassword VARCHAR(MAX)

SET @sProgID = 'ActiveBatch.AbatJobScheduler'

SET @sJSSMachine = 'asci82'

SET @sJobName = '/CaseStudy1'

SET @sUsername =’TestUser’

SET @sPassword = ‘userPw’

Two variables are specific to the instance of ActiveBatch and the job that we want to trigger. “asci62” is the Job Scheduler machine name and CaseStudy1 is the label path of the desired job.

IF UPDATE(sState)

IF EXISTS

(

SELECT 1

FROM INSERTED i

WHERE (i.sState = 'GA')

)

The preceding code extract focuses on the update operation itself. We check whether the state has been modified and if so if the value “GA”. If so, we continue through the code below.

BEGIN

EXECUTE @nCOMResult = sp_OACreate @sProgID, @nJSS OUT

IF @nCOMResult <> 0

BEGIN

EXEC sp_OAGetErrorInfo @nJSS, @sSource OUT, @sDescription OUT

SET @sMsg = 'Error creating object ' + @sProgID +

'. Error # = ' + CAST(@nCOMResult AS VARCHAR(20)) +

'. Description = ' + @sDescription

RAISERROR (@sMsg, 16, 1)

RETURN

END

The preceding code instantiates an instance of the ActiveBatch COM object.

EXECUTE @nCOMResult = sp_OAMethod @nJSS, 'Connect', NULL, @sJSSMachine, @sUsername, @sPassword

IF @nCOMResult <> 0

BEGIN

EXEC sp_OAGetErrorInfo @nJSS, @sSource OUT, @sDescription OUT

SET @sMsg = 'Error connecting to JSS Machine ' + @sJSSMachine +

'. Error # = ' + CAST(@nCOMResult AS VARCHAR(20)) +

'. Description = ' + @sDescription

RAISERROR (@sMsg, 16, 1)

RETURN

END

The preceding code connects to the Job Scheduler machine. Typically, you will specify a username and password for authentication purposes. Since the password must be specified in the clear we would advise using the WITH ENCRYPT option and encrypting the trigger procedure.

EXECUTE @nCOMResult = sp_OAMethod @nJSS, 'GetAbatObjectLite', @nJob OUT, @sJobName

IF @nCOMResult <> 0

BEGIN

EXEC sp_OAGetErrorInfo @nJSS, @sSource OUT, @sDescription OUT

SET @sMsg = 'Error finding job ' + @sJobName +

'. Error # = ' + CAST(@nCOMResult AS VARCHAR(20)) +

'. Description = ' + @sDescription

RAISERROR (@sMsg, 16, 1)

RETURN

END

The preceding code looks up the job named “CaseStudy1” and retrieves the lightweight object.

EXECUTE @nCOMResult = sp_OAMethod @nJob, 'Trigger3', NULL

IF @nCOMResult <> 0

BEGIN

EXEC sp_OAGetErrorInfo @nJSS, @sSource OUT, @sDescription OUT

SET @sMsg = 'Error triggering Job ' + @sJobName +

'. Error # = ' + CAST(@nCOMResult AS VARCHAR(20)) +

'. Description = ' + @sDescription

RAISERROR (@sMsg, 16, 1)

RETURN

END

The preceding code issues the Trigger operation.

SELECT 'It seems to have worked'

EXECUTE @nCOMResult = sp_OADestroy @nJob

EXECUTE @nCOMResult = sp_OADestroy @nJSS

The preceding code issues a message saying it worked and destroys the object to avoid any leaks with the SQL Server process itself.

To test the trigger issue the following statement.

UPDATE CustomerTable SET sState = 'GA' WHERE sState = 'NC'

UPDATE CustomerTable SET sState = 'NC' WHERE sState = 'GA'

The first statement will activate the trigger and the second update will reset the value back to what it was for another trigger.

Java Message Service (JMS) – Trigger

The job JMSTrigger will establish the JMS Trigger Event.

For this case we’re using the JMS provider ‘Glassfish’ and a queue destination of UGCaseStudyEvents.

To generate the JMS message, let’s create a job named JMSSend that sends a JMS message to that machine and queue.

When we trigger the above job, the JMSTrigger job is executed. An examination of the @Trigger variable shows that the message itself is a part of the structure of the variable

Summary

As you can see from these series of min-case studies the event facility can be very powerful. Rather than rely solely on date/time scheduling, you can specifically indicate the series of actions that are required that should cause an object to be triggered.

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