ProcessTimedQueryJob How-To


Functionality: Queries a database with a user-configured SQL query. An alert notification is sent if any records are returned from the query.
Potential uses: Query a database table for new records or specific patterns of data and send alert notifications if any records are found.
Supported Platform: All

Sample Scenario:
A Production web-based application occasionally fails when users are uploading documents. When the upload fails, an error is logged to a database table.
The Database is named BTPRD and is located on the server PRDUPLDDB01, and the error details are in the HL_CUSTUPLD table.
We want HEYMon to notify Chet Masterson when there are any new rows in the table, and send an alert with all the details.

We can configure HEYMon to execute the SQL needed to find our data using a query property: <query1>SELECT CreateDate, ErrorType, ErrorDesc FROM HL_CUSTUPLD WHERE CreateDate > $lastIntervalDate ORDER BY CreateDate ASC </query1> This configuration entry specifies: "Execute the query and create an alert notification if any rows are found."
Notice that we used a HEYMon query variable of $lastIntervalDate. When the query is executed, this variable will be replaced with the last datetime HEYMon executed the job.
The value of the variable $lastIntervalDate is based on the last time that HEYMon ran the same job.
If we set this job to execute every 10 minutes, then HEYMon will calculate the datetime from 10 minutes ago and use that for the SQL query.
The result is that if HEYMon runs this job at 09:10am, the SQL will be generated with a datetime of 09:00am. (10 minutes earlier).
If the datetime in the sql query is the value from 10 minutes ago, then the query will find records that were created in the last 10 minutes. As a result, each time HEYMon runs this job it will only find data that occurred since the last time HEYMon ran the job.
With the ProcessQueryJob you are not restricted to one query. For the needs of this scenario there will be only one SQL query, but any number of queries are allowed. Also, each item configured as a query is treated with a OR condition. Each query is run regardless of the outcome of a prior query.

With the SQL query criteria defined, let's take a look at our job properties:

<properties>
       <property name="prdupl_cnnstr">jdbc:jtds:sqlserver://PRDUPLDDB01:1433/BTPRD;user=admin;password=foo</property>
       <property name="monitor_server">PRDUPLDDB01</property>
       <property name="notify_chet">chetm@emailsrv.com</property>
       <property name="file_tmp_dir">c:/heymontmp</property>
</properties>
<jobs>
    <job name="File Upload monitor on PRDUPLDDB01">
       <description>Monitors Our App log files to send a notification when an error has occurred</description>
       <connection>prdupl_cnnstr</connection>
       <systemID>monitor_server</systemID>
       <scanIntervalSeconds>600</scanIntervalSeconds>
       <destination>notify_chet</destination>
       <queries>
          <query1>SELECT CreateDate, ErrorType, ErrorDesc FROM HL_CUSTUPLD WHERE CreateDate > $lastIntervalDate ORDER BY CreateDate ASC </query1>
       </queries>
       <notifySubject>Upload error logged on %s</notifySubject>
       <notifyBody> </notifyBody>
       <type name="ProcessQueryJob" useagent="true">
          <dateFormat>yyyy-MM-dd HH:mm:ss</dateFormat>
       </type>
    </job>
</jobs> 

This job has the following properties and settings:

Some tips when using the ProcessErrorLogJob: