ActionQ is an inexpensive BAM (Business Activity Monitoring) software. It can trigger, within seconds, dynamic SQL, command lines, batch files, PowerShell scripts, REST API calls, or emails in response to database, email, PowerShell, and folder events.
It can also alert you when Windows Task Scheduler fails to launch a task.
Typical triggers include new leads, documents, credit holds, inventory shortage, schedule delays, complaints, support requests, pricing exceptions, returns, defects, cancelations, and system exceptions.

ActionQ can use ODBC data source/target (including cloud databases). It can call any windows software (EXE, Batch Files, PowerShell, etc.) and pass dynamic arguments and parameters. In the case of Visual CUT, this supports reporting, exporting, printing, emailing, uploading/downloading/processing files, capturing/parsing email attachments, and facilitating approval workflows.

video demo [] of automating responses to database & email events.

video demo [] of calling a REST API (Twilio's SMS service).
video demo [] of monitoring the health of a SQL Server.

video demo [] of PowerShell scripts as events & responses.

The software includes two programs:

1. ActionQ_Service.exe is a service, always running in the background. It responds to events by triggering dynamic SQL statements, emails, command lines, or RERST API calls. It can also move files, download email attachments, and download/delete processed emails from monitored inboxes.

2. ActionQ_Manager.exe is a GUI for managing and monitoring the service. The service facilitates monitoring by also sending you emails when starting/stopping or encountering failures.

Installation

Follow the instruction received via email.

After the install, use file explorer to locate:
C:\Program Files\Millet Software\ActionQ\ActionQ_Manager.exe
Right-click this exe, Properties, Compatibility tab, and set the properties for all users to
run this EXE as administrator.
If you forget to do so, a message box will remind you when you start the application.

Right-click this exe and ‘pin to taskbar’ for easy access.

Launch

When you start ActionQ_Manager for the first time, it takes care of a) Copying the sample ActionQ.ini file from the installation folder to: %APPDATA%\MilletSoftware\ActionQ\ActionQ.ini
Make sure users have MODIFY permissions on that folder.

If you get an error about ChilkatDotNet47.dll, your machine is probably missing this C++ runtime:
Microsoft Visual C++ Runtime 2017 64-bit . Please download & install.

The initial Screen of ActionQ_Manager allows you to:
Manage Events (create, edit, clone, delete).
Manage global settings such as sleep interval.

Manage Encrypted String (create and modify encrypted string for passwords and connection strings).
Manage Database Connections (create, edit, clone, delete, and test connections).
Manage Email Profiles (create, edit, clone, delete, and test email profiles).
Manage Email Inbox (POP3) Settings

Install/Uninstall and Start/Stop the ActionQ Service.
Refresh the service log (typically not needed since it auto-refreshes every few seconds).
Open the ActionQ.ini file (typically, not needed)

See Version Information and Open the user manual.

Initial Setup

Before creating events you should set up:

1. Encrypted Strings to protect sensitive information (e.g. passwords).
in particular, ES_Email_Password you should select as the password for 'Email' (the mandatory email profile) used to communicate administrative information (service start/stop, and failures).

2. Database Connections to allow the service to connect to source/mirror databases (optional).

3. Email Profiles for:

a. 'Email' -- the ActionQ service administrator (so the service can communicate with you).

b. Email_From_ accounts for events that send email (optional)

4. Optional: Email Inbox (POP3) Settings, for events that monitor incoming emails

5. Optional: Use the Global Settings Dialog,
including the option to Set the ActionQ Service to Run Under a User Account

Encrypted Strings

In the ActionQ.ini, a section called [Encrypted_Strings] stores a set of named encrypted strings.
Other sections can refer to these entries whose names always start with ES_ as a prefix.
For example, ES_Email_Password protects a particular email password:

[Encrypted_Strings]

ES_Email_Password="C95EA425116ADF35E951A9486FB1B39B40637242E00A2F16DACE67"

ES_ERP_DB="196994C9944EF7124D518634935B53378F7F439AD99E12AA91E629"

ES_AQ_Mirror_DB="196994CB6E0ED179C832B24B9E07AC55B5B580C16298C06C06F28"

ES_Service_Account_Password="A6949FC149F8CB8371063E9953E520A0354AACD32D624AE5"

ES_MS_Invoices_Password="D22E83F37BF870AB4BED33AE3AA0ABE4CD3D731E68C50"

Using the Encrypted Strings Manager

The button starts the dialog shown below. It allows you to create and modify named encrypted strings.

In this example, an encrypted string called ES_Email_Password being created for an email password.

The ES Name drop-down allows you to select and change previously created named encrypted strings.

Database Connections

The service may need to connect to various databases to detect database events, update a database in response to an event, or maintain a mirror table to avoid duplicate processing. Connection properties within event settings simply name a connection. Those connection names are maintained in a [Connection_Strings] section like this:

[Connection_Strings]

ERP="ES_ERP_DB"

Mirror="dsn=ERP;uid=ixm7;pwd=ES_ERP_PW"

If you wish to encrypt the whole connection string, you may do so as demonstrated by the first example above. Otherwise, protect just the password as demonstrated by the 2^nd example.

You may edit that section manually using Notepad. But it is easier to use the dedicated connections manager.

Using the Connections Manager

Clicking starts a connection manager:

The left panel lists the connections (Name and Connection String).
The right panel is a property grid for a selected connection.

As shown in the image above, the connection string should refer to named encrypted strings instead of specifying sensitive information directly.

The Test Connection button allows you to test the connection. If a problem is found, a detailed error message is provided.

Using the Encrypted Strings Dialog (old method)

Note: In most cases, avoid this old method. Instead, create connections Using the Connection Manager.

As a somewhat less intuitive option, you may use the Encrypted Strings dialog, to also take care of adding a connection string entry to the ini file (where the whole connection string is encrypted).

If the String to Encrypt starts with ‘dsn=…’ ActionQ assumes it is for a connection string.
A group box becomes visible and lists the named connections using that encrypted string, like this:

In the case above, the encrypted string is used by a connection called ERP.

For a ‘dsn=…’ string that is not yet in use by a named connection, the group box becomes activated.
You can then enter a name for the connection and click the ‘Create Connection’ button.
That automatically creates the named connection and saves the encrypted string in the ActionQ.ini file:

Email Profiles

You need to set up one email profile called ‘Email’ for notifying the administrator about the status of ActionQ. You can set up additional profiles (named as ‘Email_From_???’) to allow custom email responses to events.

The user manual describes how these settings are saved as sections in the ini file.
But instead of editing the ini file manually, you can simply use the Email Profiles Manager.

Using the Email Profiles Manager

Clicking starts the Email Profiles Manager:

The left panel lists the email profiles.
The right panel is a property grid for a selected profile.

The Test Email Profile button allows you to test the email. If a problem is found, a detailed error message is provided. If successful, a test message is sent to the addresses specified in the Failure_Notices_To property.

Email Settings for the Service Administrator

The service needs to communicate with an administrator when it starts, stops, or encounters failures.
Open the %APPDATA%\MilletSoftware\ActionQ\ActionQ.ini file in Notepad and modify the [Email] settings to match yours (note: it is much easier to use the GUI to create/edit/clone these settings. See previous section.):

[Email]

Email_SMTP_Server="mail.milletsoftware.com"

Email_SMTP_Port="26"

Email_SMTP_Domain=""

Email_User_ID="ido@milletsoftware.com"

Email_Password="ES_Email_Password"

Email_StartTLS="True"

Email_Connect_Timeout="10"

; Email_From=""ActionQ Service" <Email_Failure_Notices_From>"
Email_Failure_Notices_From="ido@MilletSoftware.com"

Email_Failure_Notices_To="ido@MilletSoftware.com,ido.millet@gmail.com"

Email_Signature=" Ido Millet www.MilletSoftware.com (814) 825-6009"
Email_Auth_JSON_PATH="C:\ProgramData\MilletSoftware\ActionQ\Client_Secret_SMTP_Office365.json"

Note how the Email_Password entry is referring to an Encrypted String called ES_Email_Password.
The creation of Encrypted String entries is described in the previous section.

For the service administrator, the Email_From entry is ignored.
Instead, it is set from this logic: "ActionQ Service" <Email_Failure_Notices_From>

The Email_Signature option allows you to override the default email signature.
To insert line break, use as shown in the example above.

The Email_Auth_JSON_PATH is needed only when requiring OAuth 2.0 via Gmail or Office365.

Email_From Accounts for Events as Senders

Each event that responds by sending an email message has an Email_From_<Sender> property pointing at an ini section that defines email sending properties. Different events may use the same Email_From_<Sender> property but each event also has unique properties such as email to/cc/bcc, email subject and message.

Here is an example of the ini section defining email sender properties for the Sales Department:

[Email_From_Sales]

Email_SMTP_Server="mail.milletsoftware.com"

Email_SMTP_Port="26"

Email_SMTP_Domain=""

Email_User_ID="ido@milletsoftware.com"

Email_Password="ES_Email_Password"

Email_StartTLS="True"

Email_Connect_Timeout="10"

Email_From=""Sales Department" <ido@MilletSoftware.com>"

Email_Failure_Notices_From="ido@MilletSoftware.com"

Email_Failure_Notices_To="ido@MilletSoftware.com,ido.millet@gmail.com"

Email_Signature=" VP of Sales www.MilletSoftware.com"

Email_Auth_JSON_PATH=

Note that these settings follow the same structure as the email settings for the Service Administrator (as described in the previous section.

The Email_Signature option allows you to specify an email signature.
To insert line break, use as shown in the example above.

The Email_Auth_JSON_PATH is needed only when requiring OAuth 2.0 via Gmail or Office365.

Note how the Email_Password entry is referring to an Encrypted String called ES_Email_Password.
The creation of Encrypted String entries is described in a previous section.

Email Inbox Settings

Several events may monitor the same inbox. Each event may specify a different filter based on conditions such as the content of the email subject. This is why the general properties of each inbox are maintained in a centralized ini section called [Email_Server_InBoxes].

You need to set these entries only if you have events that monitor incoming emails.

Here is an example of an inbox I monitor for invoice requests to trigger automated invoices via Visual CUT:

[Email_Server_InBoxes]

MilletSoftware_Invoice=host4.hostmonster.com||invoice@MilletSoftware.com||ES_MS_Password||10

The 4 elements are: a) the POP3 server, b) the inbox email account, c) the encrypted password name,
and d) the maximum number of emails to inspect in each scan cycle.

Using the Email Inboxes (POP3) Manager

Clicking starts the Email Profiles Manager:

The left panel lists the inbox profiles.
The right panel is a property grid for a selected profile.

The JSON_File property is needed only when requiring OAuth 2.0 via Gmail or Office365.

The Test Inbox button allows you to test connection and authentication to the inbox. If a problem is found, a detailed error message is provided. If successful, a message box confirms it.

Use the Global Settings Dialog

Clicking starts the Global Settings Manager:

The bottom text area provides explanations for each property.

A typical use for this dialog is to set the ActionQ service to run as a user account (instead of the default local SYSTEM account). This is discussed in more detail in the following section.

Set the ActionQ Service to Run Under a User Account

By default, the service will be running under the local SYSTEM account. That might mean that:

a) Access to network folders might be a problem. For example, rpt files located in shared folders may not be recognized when triggering a Visual CUT process.

b) Allowing the SYSTEM account to use NT Authentication to SQL Server might require the procedure described in NT authentication.

To address such scenarios, you can elect to run the ActionQ Service under a user account.
First, you need to add a Logon as a Service permission to that user account using the procedure described here:
https://docs.microsoft.com/en-us/previous-versions/windows/it-pro/windows-server-2008-R2-and-2008/cc794944(v=ws.10)?redirectedfrom=MSDN

Here are the steps:

a) Start, Control Panel, Administrative Tools, Local Security Policy.

b) double-click Local Policies, and then click User Rights Assignment.

c) In the details pane, double-click Log on as a service.

d) Click Add User or Group, and
add the user account to the list of accounts that possess the Log on as a service right.

e) Click on the encrypt & save string button and
set a Service_Account_Password entry to the user’s Windows login password.

Then, use the Global Setting dialog (as described in the previous section to set the User and Password for the service. These changes result in entries such as the example below in the ini file:

[Service]

User="MILLETSOFTWARE2\ixm7"

Password="ES_Service_Account_Password"

Setting Up Events

Clicking the button launches a window that allows you to create, clone, delete, and modify events:

The event grid on the left lists all defined events and allows you to set event type, sleep multiplier, and include/exclude the event from scanning by the ActionQ service.

The Property Panel on the right allows you to review/change properties for each event. The displayed properties adapt to the type of event. Common and unique event properties are discussed in the sections below.

Global Properties

When the ActionQ service loads, it uses the following ini section in ActionQ.ini to detect the events to monitor and set its own sleep period after each monitor cycle.

[Events]

EventNames="VC_QFolder, Invoice_Overdue, Report_Request, Credit_Hold"

Sleep="5000"
Failure_Retry_Sleep_Multiplier="10"

In the example above, there are 4 events that the service will monitor. After each monitor cycle, the service will sleep for 5 seconds (5000 milliseconds).

A value of 10 in Failure_Retry_Sleep_Multiplier tells the service that a failing event in a retry mode should be checked at a frequency 10 times lower than normal. For example, if Failure_Retry_Sleep_Multiplier is set to 10, global Sleep is set to 5 Seconds, and the process has a Sleep_Multiplier of 3, when that process enters failure retry mode, it would be checked every 150 seconds (5 x 3 x 10).
If it recovers, it will immediately resume its normal frequency of every 15 seconds (5 x 3).

Individual Events Properties

Each event has an ini section with its name.

Example

Common properties for all events include the following example:

[Event_Name]

Event_Type="DB_Flag"

Sleep_Multiplier="3"
Failure_Retry_Minutes="5"
Snapshot_Table_Connection="Snapshot"
SQL_After_Connection=""

SQL_After=""
SQL_When=”Each”

EXE2Call="C:\Program Files (x86)\Visual CUT 11\Visual CUT.exe"

Arguments_Template="-e "C:\Reports\Credit_Hold.rpt" "Parm1:{Cust_ID}""

Call_When="Once"

SMTP_Settings="Email_From_Sales"

Email_To=”ido@MilletSoftware.com”
Email_Attachments=”c:\temp\Attendance_{Student_ID}.xlsx; c:\temp\Grades_{Sudent_ID}.pdf”

Email_To_BCC=""

Email_To_CC=""

Email_Subject="Invoice Request"

Email_Message="Invoice Request Received from {Email_From}"

Email_When="Each"

Supported Event Types

Currently, the supported Event Types are:

1. "Folder_Not_Empty" –detect files appearing in a local/cloud folder.

2. “DB_LastMaxN” –detect new records based on an incrementing numeric column

3. “DB_Flag” – detect records and delete or update them in the database to avoid duplication

4. “DB_Mirror_Skip_Remove_T1” – detect records based on comparisons to a mirror table

5. “PS_Mirror_Skip_Remove_T1” – same as above, using PowerShell script as the triggering event

6. “Email_POP3_T1” – detect new emails matching a filter condition. Trigger command lines and database updates. Optionally download the email message and/or attachments, and delete the email from the server.

Monitoring Frequency

Sleep_Multiplier tells the service to check that event only every Nth cycle. In the case above, a value of 3 indicates that the trigger condition for this event would be checked only every 3^rd cycle.

Failure Retry Period

Failure_Retry_Minutes (if > 0) is an optional setting available to all processes. It tells the service to retry monitoring this event even after a failure (e.g. connectivity to monitored database is down).
For details, see Setting Failure Retry Period.

Automatic Snapshot Table

Snapshot_Table_Connection (if specified) directs the service to load the event data (after removing cases due to any Mirror table logic) into a snapshot table. The table is created on the fly at the target database specified by the connection. It is named based on the event name like this: aqt_EventName. (for example aqt_CreditHold).
Besides facilitating debugging, a snapshot table can expose the event data to downstream processes.
For example, ActionQ can trigger a Visual CUT process whereby a report consumes the data in the snapshot table. This can remove the need to pass parameters via dynamic tokens in the arguments template.

Response Properties

Each event can trigger EXE and/or SQL and/or Email responses.

SQL Response

SQL_After_Connection and SQL_After specify the connection name and the SQL statement to call.
SQL_When specifies if the SQL response is called for each Record/File/Email in the event or only once.
The SQL statement can use dynamic tokens to incorporate dynamic information from the event.
In the example above, these options are left blank, indicating no SQL response is needed.

If the SQL statement starts with SELECT, ActionQ assumed you wish to add dynamic token information for each column from the query. Make sure the SELECT statement returns only zero or one row. If zero rows are returned the dynamic tokens for each column name is set as blank (“”).

The dialog to edit the SQL Response provides syntax highlighting:

EXE or CMD Response

EXE2Call and Arguments_Template specify the EXE, Batch, or CMD files to call and the arguments to pass. The arguments can use dynamic tokens (for example, "Parm1:{Cust_ID}") to incorporate dynamic information from the event.

Call_When tells the service when to call the EXE2Call. A value of “Each” runs the EXE for each triggering event row (e.g. query row, email, file, PowerShell object). A value of “Once” triggers the response only once.

PowerShell Response

See video demo of PowerShell scripts as events & responses.

Let's assume your script is at: C:\PS\Script.ps1

Call_When tells the service when to call the PowerShell script. A value of “Each” runs the script for each triggering event row (e.g. query row, email, file, PowerShell object) matching the event filter conditions.
A value of “Once” triggers the response only once.

Set EXE2Call to Powershell.exe

Set the Arguments_Template to:
-NoProfile -ExecutionPolicy Bypass -Command "C:\PS\Script.ps1";

The Arguments_Template can use dynamic tokens to incorporate dynamic information from the event.

Typically, you would insert those dynamic tokens as parameters to the PowerShell Script, as discussed below:

With Named Parameters:

If the script uses named parameters, add parameter name & value pairs to the end of the Arguments_Template like this:
-NoProfile -ExecutionPolicy Bypass -Command "C:\PS\RenameFile.ps1 -newName {FileName}";

In the example above, the {FileName} may have a dynamic value provided by Folder_Not_Empty event.

More Complex Scenarios:

For more complex scenarios, such as running the script with Admin permissions, or handling values with embedded spaces, please refer to this blog.

REST API Call Response

See Video demo of calling a REST API (Twilio's SMS service).

Set the EXE name as REST_API_Execute_Curl. The Arguments_Template specifies the cURL command. Most REST API providers offer examples and online help for constructing cURL commands.

For example, here is an example of calling Twilio's REST API to send an SMS in response to an incoming email:

Here is the ActionQ dialog for editing the CURL command. Note the dynamic tokens. In this example, they provide information from the email message that triggered the event.

Here are the dynamic token values shown when you turn on the Preview option:

Notes:

· As demonstrated in the video, the JSON response is parsed into dynamic tokens that can be used in an email response.

· The -: command-line option allows to chain multiple REST API calls.
For example, to send multiple requests on the same connection:
curl https://ABC.com/?To=+18148251234 -: https://ABC.com/?To=+19141231234

Email Response

SMTP_Settings specifies the outgoing email profile that can be reused by multiple events to send emails as discussed in Email_From Settings.
The rest of the event email properties control the unique aspects (to/cc/bcc/subject/attachments/message) for emails sent by the event.

These properties can refer to dynamic tokens to incorporate information from the event.
For example, the Email_To property can be set to {FromAddress} in the case of an event triggered by an email or to {Contact_Email} in the case of an event triggered by SQL.

Viewing and Editing Properties

When clicking the button the Common Properties are in the property groups of:
‘Event’, ‘Response_EXE’, ‘Response_SQL’, and ‘Send_Email’ as shown below:

As you select a particular event row in the grid on the left, the Property Panel on the right updates to also reflect the unique properties for that event type. In the example above, the ‘Triggered By SQL’ property group reflects the unique Trigger-Type properties of the ‘DB_Flag’ event type.

As demonstrated by the image above, when selecting a property, an ellipsis button on the right edge provides access to a matching property editor. This can be a simple drop-down providing a choice of relevant values. Or it can be a text/html editor for easy editing and embedding dynamic tokens as described in the next sections.

Embed Dynamic Tokens in Response Properties

The property grid associates a special editor for response properties that may reference dynamic tokens.

You open the dialog by clicking the ellipsis button to the right of the text property. For example:

The dialog provides several benefits:

1. It breaks the text into multiple lines using typical key words (such as “ Parm” “FROM” “WHERE”) to make it easier to review and edit the text. When the user clicks ‘OK’ the dialog re-assembles the content back to a single line.

2. Available dynamic tokens are listed in a panel on the right with tooltips revealing sample values.

3. Double-clicking a token on the right inserts it into the cursor location within the editing panel on the left.

4. You can change the font size using Ctrl-Scroll-Up or Ctrl-Scrolll-Down.

5. Turning on the ‘Preview’ checkbox toggles a display of the text with token references substituted by their sample values, like this:

Edit Text for Non-Response Properties

Text properties that describe the triggering event (such as source SQL statement for DB events) cannot reference dynamic tokens. Because of this, when clicking the ellipsis button, they are edited in a simple text editor that looks like this:

This simpler editor still provides several benefits:

1. It breaks the text into multiple lines using typical key words (such as “ Parm” “FROM” “WHERE”)
to make it easier to review and edit the text.

2. When the user clicks ‘OK’ the dialog re-assembles the content back to a single line.

3. You can change the font size using Ctrl-Scroll-Up or Ctrl-Scroll-Down.

HTML Editor for Email Message Body

When editing email message body, you can elect to activate an HTML editor as shown below.
This allows you to design and preview HTML email messages.
In Windows 8 or higher, the HTML editor also provides a spell checker (that is why “Noot” is highlighted):

Turning on the Preview checkbox shows a Preview with dynamic token values:

Using {aq_HTML_Table} Token in Email Responses

ActionQ automatically loads event data as an HTML table into a token called {aq_HTML_Table}.

Using the email message editor, you can place that token inside HTML email messages:

Since the HTML table shows all event rows, it makes sense to use it in email responses that are designated to fire ONCE rather than for EACH event row.

Clicking the Preview checkbox, replaces the token with its dynamic value, displaying a nicely formatted table:

ActionQ formats the HTML table using inline CSS to ensure it renders properly in email clients such as Gmail:

Excluding Columns from the Auto-Generated HTML Table

Column names starting with ‘_’ or ‘ _’ are excluded from auto-generated HTML tables. This allows you to include useful columns in your SQL statements, yet exclude them from html tables injected into email responses. The ‘ _’ options allows you to exclude the column from both Mirror table logic as well as from HTML tables.

View Dynamic Tokens for Each Process

When a process is triggered for the first time after the ActionQ service is restarted, a text file is created to document the dynamic tokens for that process.
The text file is named as: Tokens_<ProcessName>.txt
It is created in the ProgramData folder (see next section).

You can open the tokens documentation file for each process by clicking the Tokens toolbar button:

Example for an event triggered by email:

{EmailDT} <=> Fri, 22 Jan 2021 14:09:06 -0500

{BodyText} <=> test

{Subject} <=> Please Send Sales Report

{ReplyTo} <=>

{FromAddress} <=> ido.millet@gmail.com

{Attachments} <=>

Example for an event triggered by SQL

{How_Many} <=> 1

{Cust_Name} <=> Dwight Wyse

{Cust_ID} <=> 1

Example for an event triggered by Folder event:

{Move2_Folder} <=> C:\Visual CUT\vcQ\Queued

{Extension} <=> .cmd

{DateTimeStamp} <=> 20210122135722

{FileNameNoExtension} <=> QFolder_Test

{Watch_Folder} <=> C:\Users\ixm7\OneDrive – MS\VC_QFolder

{FileName} <=> QFolder_Test.cmd

{FileFullPath} <=> C:\Users\ixm7\OneDrive - MS\VC_QFolder\QFolder_Test.cmd

{MovedFileFullPath} <=> C:\Visual CUT\vcQ\Queued\QFolder_Test.cmd

Event Types

"Folder_Not_Empty" Event Type

Here is an example of options section for this type of event:

[VC_QFolder]

Event_Type="Folder_Not_Empty"

Sleep_Multiplier="3"

Watch_Folder="C:\Users\ixm7\OneDrive - The Pennsylvania State University\VC_QFolder\"

Move2_Folder="C:\Visual CUT\vcQ\Queued\"
Target_Files="*.cmd;*.bat"
Renamed_File=""
SQL_After_Connection=""

SQL_After=""

EXE2Call="C:\Program Files (x86)\Visual CUT 11\Visual CUT.exe"

Arguments_Template=""Batch:QFolder_P1>>0>>""

Call_When="Once"

In the example above, ActionQ will move any .cmd or .bat files in the Watch_Folder to the Move2_Folder and then trigger Visual CUT with a command line argument requesting processing of all batch files in that folder.

Dynamic Tokens for Response Logic

This event supports embedding these tokens in the response logic (SQL, App arguments, Email): Argument_Template: {Watch_Folder} {Move2_Folder} {FileName} {FileNameNoExtension} {Extension} {FileFullPath} {MovedFileFullPath} {DateTimeStamp}

Dynamic Tokens for Renaming Files

If the Mover2_folder already contains the target file, the event logs & emails a failure message. To avoid such failures, the Renamed_File property allows you to dynamically rename the incoming file:

1. You can inject into the Renamed_File property tokens such as:
{FileNameNoExtension}, {Extension}, and {DateTimeStamp}

2. You can inject current date/time with flexible formatting using {[fdt]DateTimeFormatString} tokens.

The [fdt] prefix instruct ActionQ to format the current date & time according to the formatting string.
See Microsoft Date/Time formatting string documentation: https://bit.ly/3tOW7S0

For example, if Renamed_File is: {FileNameNoExtension}_{[fdt]yyMMdd_HHmm}{Extension}
an incoming Claims.csv file may be renamed to 'Claims_210917_0445.csv

3. You Can add a counter, using a token such as {[V]N4}, to ensure unique file name.
This token would be replaced by 4 digits (right-padded with 0’s) and incremented to a number ensuring a unique file name.

For example, if Renamed_File is: {FileNameNoExtension}_{[V]N4}{Extension}
an incoming Claims.csv file may be renamed to 'Claims_0001.csv‘
If that file already exists, ActionQ will try 'Claims_0002.csv‘ etc.

Database Events

The choice between database event types depends on how you wish to avoid repeat responses:

· DB_LastMaxN events avoid repeat responses by tracking an auto-increment key and responding only to records a key larger than the max value seen before.

· DB_Flag events avoid repeat responses by updating the database. For example, they may set a ‘Processed’ column for the new record(s) to true.

· DB_Mirror_Skip_Remove_T1 events avoid repeat responses by automatically creating & maintaining a Mirror table. If you are not allowed to touch the source database (e.g. an ERP system), you can direct the mirror table to a different database.

Source_SQL_Template Editor

All database events use a SQL statement (SELECT or, for stored procedures, EXEC) to identify new events.
When you click to edit the Source_SQL_Template property, a special editor helps you test the SQL by displaying the result set.
Upon a test, the editor also populates dynamic tokens based on the column names and values found in the first row. This facilitates constructing dynamic event responses incorporating these tokens.

Using a Stored Procedure as Data Source

The image below demonstrates using a Stored Procedure as the data source for an event.
This happens to be a DB_Mirror_Skip_Remove_T1 event type. So, after populating the data, a button becomes visible, allowing you to display only rows that are not in the mirror table. See video demo.

"DB_LastMaxN" Event Type

Here is an example of options section for this type of event:

[Invoice_Overdue]

Event_Type="DB_LastMaxN"

Sleep_Multiplier="6"

Source_Connection="ERP"

Source_SQL_Template="Select [Web_Request] From Command_Queued Where [ID] > {LastMaxN} ORDER BY [ID] ASC"

SQL_Statement_Subquery=

LastMaxN="1023"

LastMaxN_ColumnName="ID"

SQL_After_Connection=""

SQL_After=""

EXE2Call="C:\Program Files (x86)\Visual CUT 11\Visual CUT.exe"

Arguments_Template="-e {Web_Request}"

Call_When="Each"

{LastMaxN} is a dynamic token replaced with the maximum value seen by ActionQ for the LastMaxN_ColumnName in the query result set returned by the Source_SQL_Template.

That maximum value is maintained for each event in memory as well as in the LastMaxN entry in the event’s ini section. That is why it can be used in the SQL statement to restrict the returned rows to only new ones.

The value in any column returned by the source query, Subquery, and SQL_After query can be used as a dynamic token in any later actions. In the example above, the argument template passed the value found in {Web_Request} to Visual CUT for processing. This allows any application to trigger reports and emails by simply inserting a new record into a database table.

Notes:

· The LastMaxN entry in ActionQ.ini is read and used only for initialization purposes. Once a new LastMaxN value is established, it is written to ActionQ_Data.ini file. From that point on, it is read and managed by the service only at that location. That ensures the ActionQ service never attempts to write to the ActionQ.ini file (which might be open for editing by you at the same time). So:
ActionQ.ini is for you to change settings
ActionQ_Data.ini is for the service to persist values in case the service gets stopped.

· You can use any column names in the SQL result set as dynamic tokens.

· Source_Connection refers to a connection string entry in the [Connection_Strings] ini section. To protect passwords, those entries can refer to encrypted strings as explained in Managing Encrypted Strings.

"DB_Flag" Event Type

Here is an example of options section for this type of event:

[Report_Request]

Event_Type="DB_Flag"

Sleep_Multiplier="4"

Source_Connection="ERP"

Source_SQL_Template ="Select * From Command_Queued_Flag Where [Processed] = 0"

SQL_Statement_Subquery=

SQL_After_Connection="ERP"

SQL_After="Update Command_Queued_Flag Set [Processed]=1 WHERE ID = {ID}"

EXE2Call="C:\Program Files (x86)\Visual CUT 11\Visual CUT.exe"

Arguments_Template="-e {Report} {Arguments}"

Call_When="Each"

SQL_After is executed for each row in the result set. In this case, it uses a token of one of the columns in the result set ({ID}) to update a Status to ‘Processed.’ This avoids duplicate processing.
In a similar way, it uses two other columns from the source query ({Report} and {Arguments}) to pass dynamic arguments to the called executable.

Notes:

· You can use any column names in the SQL result sets as dynamic tokens.

· Source_Connection and SQL_After_Connection refer to a connection string entry in the [Connection_Strings] ini section. To protect passwords, those entries can refer to encrypted strings as explained in Managing Encrypted Strings.

"DB_Mirror_Skip_Remove_T1" Event Type

Video demo of using ActionQ to monitor the health of a SQL Server using this type of event.

This diagram shows the general logic:

Here is an example of options section for this type of event:

[Credit_Hold]

Event_Type="DB_Mirror_Skip_Remove_T1"

Mirror_Connection="SQLExpress_Mirror"

; mirror table always named as 'aqm_<Event_Name>'. so for this event: aqm_Credit_Hold).

Sleep_Multiplier="4"

Source_Connection ="ERP"

Source_SQL_Template ="Select Cust_ID From Customer WHERE Credit_Hold = 1"

SQL_Statement_Subquery= "Select Cust_ID,Date,Amount,Balance_After FROM Payments
WHERE Cust_ID = {Cust_ID}"

EXE2Call="C:\Program Files (x86)\Visual CUT 11\Visual CUT.exe"

Arguments_Template="-e "C:\Visual CUT\vcQ\Credit_Hold.rpt" "Parm1:{Cust_ID}""

Call_When="Each"

Failure_Retry_Minutes="5"

This type of event avoids duplicate processing by maintaining a mirror table with rows retrieved last time by the source Query. The mirror table is created automatically be ActionQ based on the column names and data types in the source query. It is names as aqm_<Event_Name>.
so for the event above the mirror table name is aqm_Credit_Hold.

In the source query, any row that matches an existing row in the Mirror table gets skipped.
Column names starting with a space are not included in this logic. That allows you to include extra data for event responses, but treat only some columns as keys in determining if a new event is a duplicate.

In the Mirror table, any row that is no longer in the source query gets removed. For example, if the customer is no longer on credit hold, removing that row from the Mirror table allows ActionQ to recognize a case when the same customer is placed on a new credit Hold.

This event type is particularly useful for cases where the source SQL comes from an ERP or Cloud database where you have no Modify permissions. Setting the Mirror_Connection to another database where you have full permissions, allows you to maintain the Mirror table in the database of your choice.

Besides using column names that start with a space to indicate they are not key columns for detecting new events, another option is to include only key columns that identify true duplicates. For example, in the case above, the source query returns just the Cust_ID column. The SQL_Statement_Subquery is used to access more database details. As demonstrated above, the SQL_Statement_Subquery can be correlated to the main query or the main event (File or email) by referencing tokens (e.g. {Cust_ID}) from the main event.

The SQL_Statement_Subquery can also be used in cases where the event data has one to many relationship to desired database information. For example, in the case above, the database event identifies customers placed on hold. But for each such customer you wish to email a manager an HTML table containing that customer's payment and balance history.

Notes:

· ActionQ facilitates using any property name in the result set as a dynamic token in responses.

· Source_Connection and Mirror_Connection refer to a connection string entry in the [Connection_Strings] ini section. To protect passwords, those entries can refer to encrypted strings as explained in Managing Encrypted Strings.

· Failure_Retry_Minutes is an optional setting available to all processes. See Setting Failure Retry Period.

· Column names starting with a space are not included in detecting new events by comparing to the mirror table. You can think of them as non-keys. To save space, increase speed, and improve security, only key columns are stored in the mirror table. This also improves resiliency because you can add/remove/change non-key columns to the event design without breaking the mirror table logic.

PowerShell "PS_Mirror_Skip_Remove_T1" Event Type

See video demo of PowerShell scripts as events & responses.

This is very similar to DB_Mirror_Skip_Remove_T1 except that the source event is a PowerShell script rather than an SQL statement. The PowerShell script (.ps1 file) returns a collection of PowerShell objects as a result. ActionQ parses that result into a data table, similar to how it parses the data returned by an SQL event.

Calling REST APIs such as RabbitMQ

PowerShell is very powerful. You can leverage this event type to get data from many types of data sources. For example, you can call any REST API and not only return data but also "shape" (filter columns/rows, rename columns, add columns). For a good introduction to how you can call REST APIs from PowerShell see this blog. For discussion of how you can call RabbitMQ via PowerShell, see this blog.

Typical Properties

Here is an example of options section for this type of event:

[Disk_Full]

Event_Type="PS_Mirror_Skip_Remove_T1"
PS2Call="C:\PS\Disks_Getting_Full.ps1"

Mirror_Connection="Mirror"

Sleep_Multiplier="5"

EXE2Call="PowerShell.exe" ; call any EXE, batch file, REST API, or PowerShell as in this example

Arguments_Template= "-NoProfile -ExecutionPolicy Bypass -Command "C:\PS\my.ps1 –log {Name}.txt";"

Call_When="Each"

Failure_Retry_Minutes="0"
SQL_After="Insert into [dbo].[ActionQ_PowerShell_Log] values (GetDate(), 'Low Disk Space', '{DeviceID}{VolumeName}, Size: {Size(GB)}GB, { FreeSpace(%)}% free.');"

SQL_When="Each"

SQL_After_Connection="ERP"
SMTP_Settings="Email_From_IT"

Email_To="ido.millet@gmail.com"

Email_Subject="Disk Full Alert "

Email_Message="<HTML> …disks are getting full{ps_HTML_Table} … </HTML>"

Email_When="Once"

Avoiding Duplicate Processing

This type of event avoids duplicate processing by maintaining a mirror table with rows of results returned last time by the triggering PowerShell script. The mirror table is created automatically be ActionQ based on the properties of the PowerShell objects collection returned by the script. It is names as aqm_<Event_Name>.
so for the event above the mirror table name is aqm_Disk_Full.

In the source query, any row that matches an existing row in the Mirror table gets skipped.
Column names starting with a space are not included in this logic. That allows you to include extra data for event responses, but treat only some columns as keys in determining if a new event is a duplicate.

In the Mirror table, any row that is no longer in the source query gets removed. For example, if the disk is no longer full, removing that row from the Mirror table allows ActionQ to recognize a case when the same disk is again getting full.

Note: to allow ActionQ to parse the results of the PowerShell script, let the script return PowerShell's default objects. Do NOT pipe the results into text tables for display.

Notes:

· ActionQ facilitates using any property name in the result set as a dynamic token in responses.

· Mirror_Connection refer to a connection string entry in the [Connection_Strings] ini section. To protect passwords, those entries can refer to encrypted strings as explained in Managing Encrypted Strings.

· Failure_Retry_Minutes is an optional setting available to all processes. See Setting Failure Retry Period.

"Email_POP3_T1" Event Type

This event type responds to incoming emails by triggering dynamic processes or database updates. It can detect and use text tokens within the email subject/body/attachments to trigger different actions.

Typical Use Case:

Imagine a customer was placed on credit hold (Credit_Hold column set to True in the database):

A Database event in ActionQ detects this and uses Visual CUT to email the VP of Sales:

The manager can then manually reply and add the text Reverse or Approve in a designated text area.
Or, better yet, the manager can simply click one of the decision buttons.
Those buttons are just images with mailto hyperlinks.

You can use a web site such as this to generate the button images and this to generate mailto syntax as hyperlinks for these images.
For example:

Clicking on the Reverse button triggers the following email message dialog.
The manager can then simply click Send:

Another event in ActionQ (of type Email_POP3_T1) then:

1. Detects that incoming email

2. Collects data in tokens such as [Cust_ID: 1] and [Decision: Approve].

3. Responds by updating the database, so the customer record changes to:

If, for another case, the manager indicates Reverse, ActionQ would issue a different SQL statement
resulting in a customer record shown for Ken Hamady:

This demonstrates the ability to automate workflows by chaining multiple events.

Setting Default Email Client

When a user clicks a mailto link, if the wrong email client opens up (e.g. Outlook instead of Gmail), the user can change their default email client.
In Windows, head to Settings > Apps > Default apps. Scroll down and pick Choose default apps by protocol. For ‘Mailto’, choose the client of your choice. For Gmail, select Google Chrome.

Email Server and Inbox Settings

Multiple events can target the same inbox. Server/Inbox settings are specified like this:

[Email_Server_InBoxes]

MilletSoftware_Invoice=host4.hostmonster.com||invoice@MilletSoftware.com||ES_MS_Password||10

The 4 elements are: the server, the inbox email account, the encrypted password name, and the maximum number of emails to inspect in each scan cycle.

Event Settings

Each Email_POP3_T1 event has a section with settings such as these:

[Credit_Hold_Decision]

Event_Type="Email_POP3_T1"

Email_Server_InBox="MilletSoftware_Invoice"

Sleep_Multiplier="10"

Filter="(Subject contains "Decision Needed: Credit Hold" AND From contains "ido@milletsoftware.com")"

Message_Sent_in_Last_N_Minutes="9000"

Save_As_EML_To_Folder="C:\Visual CUT\Invoice_Requests\eml\"

Save_Attachments_To_Folder=""

Save_Attachments_To_Unique_File_Names="true"
Delete_Email_From_Server="true"

Parse_Expressions="Cust_ID:::\d{5,6}|||Cust_Email:::\S+@\S+\.([a-zA-Z0-9])+"

SQL_After_Connection="ERP"

SQL_After="SQL_IF_{Decision}"

SQL_IF_Reverse="Update Customer Set Credit_Hold = 0, Credit_Hold_Reversed_By='{FromAddress}',
Credit_Hold_Decision_DT=GETDATE() WHERE Cust_ID = {Cust_ID}"

SQL_IF_Approve="Update Customer Set Credit_Hold_Approved_By='{FromAddress}',
Credit_Hold_Decision_DT=GETDATE() WHERE Cust_ID = {Cust_ID}"

Call_When="Each"
EXE2Call=""
Arguments_Template=""

The Email_Server_InBox entry points at the corresponding server and inbox this event monitors.

The Filter entry specifies what email messages within that inbox are targeted. Here are some examples:
Subject like "Re: Credit Hold*"
Subject contains "Trip Cancelation" and From = "Cruises@Titanic.com"
Any email header field name can be used, case is insensitive.

The "*" wildcard matches 0 or more occurrences of any character.

Parentheses can be used to group conditions.

The logical operators are: AND, OR, NOT (case insensitive)

Comparison operators are: =, <, >, <=, >=, <>

String comparison operators are: CONTAINS, LIKE (case insensitive)

Message_Sent_in_Last_N_Minutes entry allows only newer messages to be targeted.

Save_As_EML_To_Folder entry, if populated, directs the process to download the messages as eml files.

Save_Attachments_To_Folder and Save_Attachments_To_Unique_File_Names entries control if, how, and to what folder attachments are downloaded. If attachments are downloaded, ActionQ maintains an {Attachments} token with the list of downloaded files separated by ‘||’.

Delete_Email_From_Server entry controls whether ActionQ deletes a targeted email message from the server after processing it. Important: if this option is set to false, ActionQ avoids duplicate processing of old messages by tracking the maximum date & time found in the date header of already-processed messages.

Here is a screenshot showing the properties in the GUI:

ActionQ parses dynamic tokens from each email message using two mechanisms:

1. By Brackets (e.g. [Name: Value] patterns located in message subject and body

2. By Regular Expressions matching text in message subject, body, and attachments.

Standard Dynamic Tokens

For email events, ActionQ populates these standard dynamic tokens:
{EmailDT}, {FromAddress}, {ReplyTo}, {Attachments},{Subject}, and {BodyText}

which can be referenced inside event responses (SQL statements, Command line arguments, Email messages):

Dynamic Tokens By Regular Expressions

Regular expressions allow you to extract values from text using very powerful and flexible expressions.
You can specify multiple patterns using the Parse Expressions property of email events.

For example:
Parse_Expressions="Cust_ID:::\d{5,6}|||Cust_Email:::\S+@\S+\.([a-zA-Z0-9])+"
tells ActionQ to parse the email message subject, body, as well as any attachments (with a file type that can be treated as text).
If a pattern with 5-to-6 digits is found, it gets loaded into a {Cust_ID} token.
If a pattern that looks like an email address is found, it gets loaded into a {Cust_Email} token.
This use of such powerful patterns adds a lot of flexibility in automating responses to email messages based on message as well as attachments text.

Dynamic Tokens By Brackets

In addition, ActionQ automatically scans the subject and body for tokens that look like this:
[anyName: anyValue] and loads them into tokens.
For example, having the following text [Decision: Approve] anywhere within the subject or body of the email would result in a {Decision} token with a value of ‘Approve’.

SQL_After Logic

You can use the value of a dynamic token to control the SQL statement generated in response to the event.

For example, setting this event property: SQL_After="SQL_IF_{Decision}"

indicates that the SQL statement to be triggered depends on the value of the {Decision} token.
If the Decision value is Reverse, the statement specified by SQL_IF_Reverse is executed.
If the Decision value is Approve, the statement specified by SQL_IF_Approve is executed.

You can use any token(s) and any number of alternatives with this logic.
If no matching ini entry is found, the SQL step is skipped.

In the example above, the EXE2Call is left blank, but you are free to trigger just SQL, just EXE, both, or even none (for example, if you wish to use the process just for downloading targeted email attachments).

Setting Up Alerts for Windows Scheduled Task Failures

ActionQ can alert you when Windows Task Scheduler fails a task. For example, when a scheduled task designed to trigger a batch file fails to locate the batch file.

Simply add the following section to the ActionQ.ini file:

[Scheduled_Tasks]

Monitor_Scheduled_Tasks=True

Every_N_Minutes=10

Ignore_Repeats_for_N_Minutes=1440

In the example above, ActionQ would check for failures every 10 minutes and would avoids duplicate alerts (same task name & error code) for 1440 minutes (24 hours).

When failures are detected, the ActionQ administrator would receive an email alert such as this:

ActionQ monitors only enabled scheduled tasks under the root folder of task scheduler:

Monitoring Snapshot and Mirror Tables

The button in the Action Manager menu launches a window displaying the Snapshot and/or Mirror tables for the currently selected event. Here is an example for an event that has both tables.

This allows you to monitor that information for debugging and training purposes.

Hit F5 to refresh the data in the grids.

Options

Customizing About Window in ActionQ Manager

Set the following lines in the ini file:

[Options]

About_Line1="email: ido@MilletSoftware.com"

About_Line2="Skype: ido_millet"

About_Line3=www.MilletSoftware.com

Handling Failures

When a failure occurs, the service emails a message to the addresses specified in Email_Failure_Notices_To option as described in Setting Email Options.

Here is an example of such an email triggered when a database table used in a Source_SQL_Template is not found:

As stated in the email message, once an event fails, the service will no longer monitor it.
You need to fix the problem and restart the service.

Setting Failure Retry Period

To handle transitory problems such as loss of connectivity to a database or to a cloud folder, you can set an optional entry in the specific event properties:
Failure_Retry_Minutes="5"

This would place a failing event in a Retry mode for 5 minutes.
And the user would receive an email message such as this:

If after 5 minutes the process still fails, it is disabled and a final alert email is sent:

The event recovers if, before getting disabled as above, it:
a) experiences a success (triggering an EXE or moving a file)

* Or *

b) stops experiencing failures during one more retry period beyond the initial retry period.

An email confirms the good news like this:

Technical Notes

Database Connectivity

64 vs 32-Bit DSNs

The 64-bit version of ActionQ can only use 64-bit ODBC DSNs.
A 32-bit version of ActionQ is available upon request if you can only use 32-bit ODBC DSNs.

NT Authentication

If you are using SQL Server with NT Authentication, unless you Set the ActionQ Service to Run Under a User Account (and that user account has login permissions to SQL Server), you need to map the SYSTEM account to have Login permissions. Go to SQL Server >> Security >> Logins and set the User Mapping for NT AUTHORITY\NETWORK SERVICE or for NT AUTHORITY\SYSTEM as shown below:

SQL Statements

SQL_After as SELECT Query

If the SQL_After is a SELECT statement, ActionQ adds extra {af_<columnName>} tokens as well as {aq_HTML_Table_After} token for each row in the main event tokens.
The information in these extra dynamic tokens can be used in the Email or EXE responses because those responses are always triggered AFTER the SQL response.

SQL_After statements are now (Version 2.0) allowed to return multiple rows. {af_<colName>} tokens are generates from the first returned row. The {aq_HTML_Table_After} token is generated from all the rows. Note: When set to run for Each row in the main event (rather than Once), the SQL_After logic fires and repopulates these tokens for each row in the main event.

Here is an example of an email from Visual CUT (EXE response) where information from a SELECT statement in the SQL response (how many customers are on credit hold) was used as a dynamic token in the command line arguments within the call to the Visual CUT EXE.

SQL Statement Subquery

If the SQL Statement Subquery is a SELECT statement, ActionQ adds extra {sb_<columnName>} tokens as well as {aq_HTML_Table_Subquery} token for each row in the main event. The information in these extra dynamic tokens can be used in any follow-up actions/responses, including in the SQL_After statement

SQL Server (fully qualify table names)

To avoid errors such as:

[SQL Server]Invalid object name 'dbo.Email'

use the fully qualified table name (including the database name) in SQL statements.

Good example (where AQ is the database name):

INSERT INTO AQ.dbo.Email ("Email_DT", "Email_From", "Email_Subject", "Email_BodyText", "Attachments", "Answer", "Note") Values ('{EmailDT}', '{FromAddress}', '{Subject}', '{BodyText}', '{Attachments}', '{Answer}', '{Note}');

Bad example:

INSERT INTO dbo.Email ("Email_DT", "Email_From", "Email_Subject", "Email_BodyText", "Attachments", "Answer", "Note") Values ('{EmailDT}', '{FromAddress}', '{Subject}', '{BodyText}', '{Attachments}', '{Answer}', '{Note}');

ProgramData Folder

The settings file (ActionQ.ini), the service private ini file (ActionQ_Data.ini) and the daily log files are maintained at:
%APPDATA%\MilletSoftware\ActionQ\

For example, on a Windows 10 machine, the ActionQ.ini file is at:

C:\ProgramData\MilletSoftware\ActionQ\ActionQ.ini

Settings Files (ActionQ.ini and ActionQ_Data.ini)

All entry values (even numeric ones) must be enclosed in double quotes. For example:
Sleep_Multiplier="4"

All comments in the ini file must start with a semi-colon: ;

While ActionQ Service is running it maintains an in-memory copy of the ActionQ.ini file as a Read Only version. It never attempts to write to that file. That makes it safe for you to make changes to the settings while the service runs.

Values the service needs to “remember” even if it is stopped or uninstalled (such as LastMaxN) are saved by the service to ActionQ_Data.ini file. Typically, you should not open or edit that file.

UNC Paths rather than Mapped Drives

Use UNC paths rather than mapped drive letters when referring to resources such as Crystal Reports files in command line arguments.

ActionQ automatically converts mapped drive to UNC paths for the following properties:
Watch_Folder, Move2_Folder, smtpQ_Folder .

Invisible Mapped Drives

ActionQ Manager runs as administrator and hence may not display mapped drive letters in folder selection dialogs unless you use one of the solutions discussed in this Microsoft Note.
You can copy & paste UNC paths manually into options for folder and file targets.

Batch File to Make Mapped Drives Visible

As a solution for the problem above, a batch file is provided in the ActionQ application folder:
C:\temp\Enable Mapped Drives in Elevated Apps (Right-Click and Run as admin).cmd
Running that batch file sets a registry entry allowing elevated applications to see mapped drive paths.
A machine restart is needed to apply the change.

Queuing Emails to smtpQ Outgoing Folder

If you also have Visual CUT installed with smtpQ enabled, you can queue outgoing emails to the Outgoing folder by setting this entry in the ActionQ ini file under the [Events] section:

[Events]

smtpQ_Folder="C:\Visual CUT\smtpQ\Outgoing"

ActionQ Service Fails to Start

If you get the following error:
Error 1053: the Service Did Not Respond to the Start or Control Request in a Timely Fashion

Try to:

· Reboot the computer

· Review the suggestions in this blog.

Stopping the ActionQ Service Outside Business Hours

You can use the Windows Task Scheduler to START the service using a command line of:
NET start <ServiceName> 
You can use another scheduled task to STOP the service using a command line of:
NET stop <ServiceName> 

The service name for ActionQ 64-bit is ActionQ

The service name for ActionQ 32-bit is ActionQ32

Custom Logging (override appsettings.json)

By default, ActionQ logs only to a file (C:/ProgramData/MilletSoftware/ActionQ/ActionQ_Log.txt). That log is what gets displayed in ActionQ Manager.

To add custom logging, you can override the default logging options (specified in the appsettings.json file).
Simply create a logsettings.json file, and place it in the application folder.

The example below shows adding logging to Seq and to the Console.

{

"Serilog": {

"Using": [ "Serilog.Sinks.Console", "Serilog.Sinks.File", "Serilog.Sinks.Logz.Io", "Serilog.Sinks.Seq", "Serilog.Sinks.RabbitMQ" ],

"MinimumLevel": { "Default": "Information",

"Override": { "Microsoft": "Warning", "Microsoft.Hosting.Lifetime": "Warning", "Microsoft.AspNetCore.Mvc.Internal": "Warning",

"System": "Warning"}

"WriteTo": [

         "Name": "Seq",

         "Args": {

           "apiKey": "your key",

           "serverUrl": "http://localhost:5341"

},

{

"Name": "Console",

"Args": {

"outputTemplate": "[{Timestamp:HH:mm:ss} {Level:u3}] {Message:lj} {Properties}{NewLine}{Exception}"

}

{

"Name": "File",

"Args": {

"path": "C:/ProgramData/MilletSoftware/ActionQ/ActionQ_Log.txt",

"outputTemplate": "{Timestamp:HH:mm:ss} [{Level:u3}] {Message:lj}{NewLine}{Exception}",

"rollingInterval": "Day"

}

"Properties": {

//"Application": "[AQ]"

}

Adding Custom Logging as an Event Response

When a particular event is triggered, you may want to log a dynamic message to your log(s).
To do that, locate the ini section for that event in C:\ProgramData\MilletSoftware\ActionQ\ActionQ.ini

and add 2 lines, such as:

"C:\ProgramData\MilletSoftware\ActionQ\ActionQ.ini"

You add 2 lines to the event section in the ini file (no GUI yet):

---

Log_Message="New ToDo for User {userId} from {processName}. {status}||{userId}^{processName}^Critical"
Log_When=Each

---

The yellow part is the message template.

You can have any number of arguments after the || delimiter.
The arguments are separated by ^
The {userId} argument property is a dynamic token from the event.
The {processName} argument is an internal dynamic token provided by ActionQ
The Critical argument is a static token for structured logging.

Here's a console image demonstrating the log response.
As indicated by the different font color, the arguments are indeed recognized as structured logging properties:

Update History

Version 3.0.5.9 (entered testing December 03, 2024):

Main New Features:

· New response type of calling a PowerShell script. You can pass dynamic tokens to the PowerShell script. For detail, see PowerShell Response. See video demo []

· New event type of PowerShell script (PS_Mirror_Skip_Remove_T1). See video demo []
This is similar to the Database event, except that the data comes from a PowerShell script.
For example, you may want to get data via REST API calls (e.g. RabbitMQ). Or monitor system health.
Typical responses are email alerts and updating databases.
For detail, see PowerShell "PS_Mirror_Skip_Remove_T1" Event Type.

· Column names starting with ‘_’ or ‘ _’ are excluded from auto-generated HTML tables. This allows you to include useful columns in your SQL statements, yet exclude them from html tables injected into email responses. The ‘ _’ options allows you to exclude the column from both Mirror table logic as well as from HTML tables.

Mirror Table Features

· Mirror Table logic now ignores column names that start with a space (e.g. ' Balance').
This allows including useful data (e.g. balance of account placed on credit hold) in event data, yet avoiding triggering another response when such non-key information changes.
For example, once a customer is placed on credit hold, we may not want to trigger another alert just because their balance changed.

· Newly created mirror tables store only key columns (column names that do not start with a space).
This saves space, increases speed, and improves security. It also improves resilience because you can add/remove/change non-key columns to the event design without breaking the mirror table logic.

Logging Features

· Added dynamic logging as an event response (typical targets are Seq or RabbitMQ).

· Added support for logging to Serilog Seq and Serilog RabbitMQ.
This can facilitate notifications via other platforms such as Microsoft Teams.

· Added more structured logging properties, including the event name.

· Added support for custom logging settings (logsettings.json)

Other Features

· You can now use both 64-bit and 32-bit versions on the same machine.
The new 32-bit version uses a service called ActionQ32 instead of ActionQ.
It automatically renames & uses separate files (ActionQ32.ini, ActionQ32_Data.ini, ActionQ32.log).

· The ActionQ Service was upgraded to .NET 8.0

· When the service fails to install/start/stop/uninstall, a message explains the reason.

· Fixed a problem with how the Encrypted Strings editor displays previously saved settings.

· Installation now includes a sample ini file with many examples of events, encrypted strings, database connections, mailboxes, etc.

· Installation now includes a sample json file for Office365 OAuth 2.0 email authentication.

· Added a batch file to automate the installation process (ActionQ_Install_RClick_Run_As_Admin.cmd).

· When adding a database connection profile, a helpful text of dsn=?;uid=?;pwd=ES_? is provided.

· When saving global settings with User ID and Password (for running the ActionQ service under), a message is provided if those settings fail validation (e.g., user lacks ‘Log on as a service’ permissions.

Fixes

· Fixed a problem in Mirror table handling of Boolean values.

· Fixed a problem in how the 64-bit version shows the connection string for database connections.

· Fixed a problem with how the service handled OAUTH for SMTP via Office365.

· Fixed naming/description problems in email profiles property grids.

Version 2.0.8.01 (February 28, 2024):

· Added support for OAuth2 when sending/receiving emails (SMTP & POP3) via Office365 and Gmail.Contact Millet Software for detailed instructions.

· Fixed multi-row SQL population of dynamic tokens.

· Fixed {aq_HTML_Table} bug (introduced in V2.0.5, and remained in V2.0,6)

· Removed several CSS style defaults for tables in email messages (email clients require inline styling).

· Fixed display of object properties (event, email, inbox, database connections) after cloning the object.

· Fixed a bug in adding a new inbox profile.

Version 2.0.2.01 (August 1, 2023):

· ActionQ can now call a REST API as a response to an event. See video demo []

· Returned JSON is parsed to dynamic tokens that can be embedded in a follow-up email message.
For example, an incoming email event can trigger an SMS message via Twilio's REST API.
Twilio's JSON response is parsed into tokens that can be embedded in an email as another response.

Version 2.0.1.01 (July 04, 2023):

· Fixed sanitizing of connection strings when logging connection failures.

Version 2.0.0.01 (June 12, 2023):

· Breaking Change! SQL After statements tokens names are {af_<colName>} instead of {<colName>}.
For example, {af_ID} instead of {ID}
This solves cases where other SQL statements for the same event have columns with the same name.
If you have an event that uses tokens from an SQL After statement, be sure to update that event.

· SQL After statements are now allowed to return multiple rows. For SELECT statements, this provides a new {aq_HTML_Table_SQL_After} token for embedding in email responses.

Note: When set to run for Each row in the main event (rather than Once), the SQL_After logic fires and repopulates these tokens for each row in the main event.

· Added SQL Statement Subquery property to database events. ActionQ runs that correlated subquery for each row in the main query. For SELECT statement, it populates new {aq_HTML_Table_Subquery} and {sb_<colName} tokens. For example, a backorder event can easily embed line items detail as HTML table in an email response. Thanks to Corey Durthaler for the feature idea.

· Dynamic tokens are now sorted alphabetically in the token panel.

· Fixed double-prompting for unsaved changes.

· Added green highlights for action descriptions (e.g. 'emailed to', 'Ran', 'Subquery') in the log display.

· The SQL editor now starts Cast() expressions on new lines for improved readability.

· The SQL editor's data grid now right-aligns date and number columns.

· Auto-generated HTML tables now handle Date values as dates rather than DateTime.

· The log display automatically abbreviates content of HTML tables to <table … </table>.

Version 1.1.97.02 (November 20, 2022):

· Updated internal component to a later version.

· Bug fix (null object reference).

Version 1.1.89.02 (July 10, 2022):

· Fixed a problem in testing email profiles.

· Fixed an emailing problem from the service.

· Added debug option to review mirror table vs fresh data.

· Fixed Mirror Table comparisons for Decimals values with different number of trailing 0's.

· Fixed tooltip issue for the {aq_HTML_Table} token.

· Updated the email message HTML editor. Preview scrollbars are now enabled.

Version 1.1.0.95 (March 2, 2022):

· Email events can now use Regular Expressions to parse dynamic tokens from the email subject, message body, and even file attachments. See Dynamic Tokens By Regular Expressions.

Version 1.1.0.93 (January 6, 2022):

· ActionQ now automatically builds an HTML table token called {aq_HTML_Table}. You can place that token in email messages to display event data as a nicely formatted table.
See Using {aq_HTML_Table} Token in Email Responses.

· Fixed handling of NULL values in Mirror table logic (‘DB_Mirror_Skip_Remove_T1’ event type).

· SQL Query editor now provides a button (only for DB_Mirror_Skip_Remove_T1 event types) allowing you to display only new cases (rows that are not already in the Mirror table). See example.

· Added an example for Using a Stored Procedure as Data Source.

· Added a video demo.

· Fixed bug in properties grid for email inboxes.

· Fixed a bug in handling of encrypted strings.

· Added syntax highlighting to SQL editor.

Version 1.1.0.87 (November 6, 2021):

· Added a 32-bit version of ActionQ (to support 32-bit ODBC drivers). Contact Millet Software for detail.

· Fixed support for HTML email signatures.

· Added a special dialog for entering & testing Source_SQL_Template statements for database events. When ActionQ displays the result set from the SELECT statement, it also takes care of populating dynamic tokens to facilitate designing dynamic responses. See Source_SQL_Template Editor.

· When saving a new Folder or Incoming Email event, ActionQ now automatically generates and saves sample tokens to facilitate designing dynamic responses even before the events get triggered.

Version 1.1.0.83 (September 19, 2021):

· Folder_Not_Empty events now support an optional Renamed_File property, allowing you to rename incoming files. For in-place renaming, you may leave the Move2_Folder blank.

· Folder_Not_Empty events now support flexible DateTime and File Counter tokens to ensure the Renamed_File is unique. See Dynamic Tokens for Renaming Files.

· Email/SQL/App responses to Folder_Not_Empty events, which are set to fire ONCE (instead of for EACH incoming file) now indeed fire only once.

· Ctrl-S now acts as a Save hot key for event and global properties.

· A ? top-right menu button in various windows (or F1 key) now launches relevant online help.

Version 1.1.0.82 (September 8, 2021):

· Added a window for managing global settings via a property grid with help text.
This avoids manual editing of the ini file to manage settings such as sleep interval and service account.

· Added a blank option in SMTP_Settings drop-down to allow removing email response target.

· Events can now use the admin email profile (“Email”) settings to send emails.

· Added right-click menu for the log window with menu options to Refresh (F5) and Clear (F12) the log.

· Changing the ‘Included’ checkbox for an event saves the change (no need to click ‘Save Event’).

· Added alert dialogs when a user is about to lose unsaved changes to property values due to closing a window or navigating to another event. The user can elect to save or discard the unsaved changes.

· A failure message is now logged & emailed when a Folder_Not_Empty event finds the file already exists in the Move2_Folder.

· Added a Batch File to Make Mapped Drives Visible .

· Added automatic conversion from mapped drive to UNC paths.

· Folder_Not_Empty events now support an optional Target_Files property, allowing you to target only specified file names or wildcard patterns. For example: *.xls;*.xlsx;Claims*.csv

Version 1.1.0.74 (August 17, 2021):

· Fixed handling of single quotes when populating Mirror and Snapshot tables

· Fixed saving of Mirror Connection property.

· Fixed property grid initialization when saving a new event.

Version 1.1.0.71 (July 23, 2021):

· Added an Email Profiles Manager. Use it to add, edit, delete, clone, and test email profiles.

See: Using the Email Profiles Manager.

· Added an Email Inbox (POP3) Manager. Use it to add, edit, delete, clone, and test settings for email inboxes. See: Using the Email Inboxes (POP3) Manager.

· For security reasons, the user can no longer opt to display unobscured encrypted strings.

· Instead of encrypting the whole connection string, you can now refer to a named encrypted string from just the sensitive portions. For example: dsn=ERP;uid=jsmith;pwd=ES_ERP_PW
This facilitates managing connection strings by making the non-sensitive parts visible.

· Increased font size in property grids to improve readability.

· Fixed duplicate key bug in detecting failed schedules tasks.

· Fixed handling of dummy/blank encrypted strings

· Fixed email profile test bug

· Added tooltips to menu buttons

· Removed Email_Bounce_Address property (by definition, must be the Email_From).

· Added to the main form a button to open the ActionQ.ini file.

· The User Manual button now opens the user manual in your browser.

· The user manual now provides an Initial Setup section with 3 required steps and 2 optional steps.

Version 1.1.0.63 (June 25, 2021):

· Updated HTML editor for email messages.

· Added a Connections Manager. Use it to add, delete, clone, and test named database connections.
See Using the Connections Manager.

Version 1.1.0.61 (March 29, 2021):

· New Feature: see Setting Up Alerts for Windows Scheduled Task Failures.

· When saving an event that uses the LastMaxN property, a dialog allows the user to avoid overwriting the current value used by the service in a case where the value shown by the manager dialog is old.

· Fixed an issue with showing LastMaxN in event properties.

· Updated HTML editor component for email messages.

Version 1.1.0.57 (February 9, 2021):

· Added Attachments property for email response. Separate multiple attachments with ‘;’.

· Email responses can now have HTML message bodies.

· Added an HTML editor (including inline spell-checker) for designing HTML email messages.
See HTML Editor for Email Message Body.

Version 1.1.0.52 (January 24, 2021):

· You can now use a SELECT statement in the SQL Response to add extra dynamic tokens for use in the Email or EXE response. For details, see SQL Response as SELECT Query.

· For email events, if Delete_Email_From_Server entry is set to false, ActionQ now avoids duplicate processing of old messages by tracking the maximum date & time found in the date header of
messages processed in previous scans.

· Editor window for text properties now allows font size increment/decrement using Ctrl-Scroll Up/Down.

· Editor window for text properties now adds a new line when pressing ‘Enter’ instead of closing.

· Added automated documentation of all dynamic tokens and sample values for each process.
A new toolbar button allows you to view this documentation for a selected process.
See View Dynamic Tokens for Each Process.

· Added special dialog for editing response properties with dynamic tokens.
See Embed Dynamic Tokens in Response Properties.

Version 1.1.0.47 (January 13, 2021):

· The encrypted string creation dialog now also allows creation of a named connection string referencing the encrypted string. This allows creation of named connection strings without manual editing of the ini file. See Database Connections.

· Added special handling for POP3 (incoming emails) from Gmail.

· Fixed error message when event triggers only an email (no exe and no SQL).

· Fixed saving of SQL_After_Connection property from event properties editing window.

· Improved layout (spacing between sections) and comments in Ini file.

· Event properties grid now shows Response SQL properties before Response_EXE properties.
This reflects the response sequence: SQL response occurs before EXE response.

· Fixed informational logging issue.

· Improved handling of cases where user forgot to activate at least one event.

· Fixed SQL handling of single quotes inside email subject and body.

· Fixed dynamic tokens handling for multiple files in Folder events.

· Added more tokens for Folder events: {FileNameNoExtension}, {Extension}, {DateTimeStamp}

Version 1.1.0.34 (December 7, 2020):

· Performance tweaks.

· Added message suggesting closing AQ Manager before manually editing the ini file in Notepad.

Version 1.1.0.33 (November 30, 2020):

· ActionQ can now trigger dynamic email messages in response to events.

· Added SQL_When (‘Once’ or ‘Each’) event property to support cases where the SQL response should be triggered only Once per event as well as for Each Record/File/Email detected within the event.

Version 1.1.0.31 (November 12, 2020):

· You can now queue outgoing emails using smtpQ. See Queuing Emails to smtpQ Outgoing Folder.

· ‘Pass These Arguments’ event property now offers a multi-line editor for easier viewing/editing.

· Fixed a bug in saving setting for Move2_Folder

· Fixed a bug in handling dynamic tokens for SQL_After statements.

· Fixed a bug in some cases of reading Mirror database connection string.

· Fixed unhandled exception scenario.

· Fixed data type handling issue in creating snapshot/mirror tables.

· Form for viewing snapshot and mirror tables now shows the DSN name used by their connections.

Version 1.1.0.22 (October 3, 2020):

· Added Snapshot_Table_Connection property to activate saving of event details to a table. This facilitates debugging and exposing the event data to downstream processes. For example, a Crystal report can use the snapshot table, removing the need to pass parameters via dynamic tokens in the arguments template.

· Added a button in ActionQ_Manager to display the Snapshot and/or Mirror tables for the selected event. See Monitoring Snapshot and Mirror Tables.

· Property Grid now provides a multi-line editor for pasting or viewing SQL statements.

· Log file refreshes no longer change user’s clipboard.

· Fixed Email_POP3_T1 connection problem.

Version 1.1.0.21 (September 23, 2020):

· Added window for creating, cloning, and modifying events.

· Merged Install and Uninstall buttons into a single Install/Uninstall button.

· Merged Start and Stop buttons into a single Start/Stop button.

· Tweaked service installation logic

Version 1.1.0.12 (September 7, 2020):

· Added ActionQ_Data.ini for the service to maintain values in case it is stopped. This ensures the service never writes to the ActionQ.ini file, making it safe for you to change settings while the service runs.

Version 1.1.0.10 (August 27, 2020):

· Enhanced saving of error messages to log and to Status entry in ini file.

Version 1.1.0.9 (August 25, 2020):

· Added an event type of ‘Email_POP3_T1’ to detect incoming emails (matching filter criteria), and triggering SQL and/or EXE with dynamic command line arguments in response.
This includes automatic extraction of data from [Answer: <…>] and [Note: <…>] tokens in the message subject or body, allowing managers to approve/reject email requests and trigger processes and database updates by responding to an email.

Version 1.0.0.8 (August 16, 2020):

· Added options to Set the ActionQ Service to Run Under a User Account.

· Starting ActionQ_Manager for the first time takes care of copying the sample ini file to the AppData subfolder and setting Modify permissions for users.

· Several enhancements to user interface (e.g. changing color for service status text)

· Added support for running on older operating systems (e.g. Windows 2008 R2)

· Added toolbar button to open user manual.

· Added msi installer