Trigger, routing and blocking conditions
Conditions in the Auron SMS Server are SQL expressions. You’ll find conditions when creating:
- Triggers. To automatically process messages;
- Routing rules. To route messages to specific channels;
- Blocking rules. To block outgoing messages.
The conditions always specify to which messages a rule should apply.
Message views use SQL expressions as well. In the case of views we call them a filter. But all of the below rules apply to these filters as well.
This document explains some of the rules that apply to writing a condition or a filter.
Basic syntax and operators
If you’re not familiar with SQL here’s just a very short overview of the basic syntax and operators available.
SQL requires strings to be expressed using single quotes. Boolean values are 1 for true and 0 for false.
All SQL operators can be used. Find a full list of operator here.
A short list of useful operators:
| Operator | Example | Description | ||
|---|---|---|---|---|
| = | ToAddress = '+316211223344' | Equal to | ||
| < | Retries < 3 | Less than | ||
| > | Retries > 3 | Greater than | ||
| <> | IsDeliveryReport <> 0 | Not equal to | ||
| + | Retries + 1 < 3 | Addition for numbers or concatenation for strings | ||
| - | Retries - 1 < 3 | Subtraction | ||
| % | ID % 5 = 0 | Modulo | ||
| LIKE | Body LIKE 'QUIZ%' | Compare a string against a pattern. '%' means zero, one or multiple characters. '_' means 1 character | ||
| IN | ToAddress IN ('+31611223344', '+31611223355', '+31611223366') | Test if a field compares to one value out of the specified list | ||
| AND | ToAddress = 'email@yourdomain.com' AND FromAddress = 'email@yourdomain.com' | Combine expressions such that the expression is true if all parts of the expression are true | ||
| OR | ToAddress = 'sales@yourdomain.com' OR ToAddress = 'contact@yourdomain.com | Combine expressions such that the expressions is true if one part of the expression is true | ||
| NOT | Body NOT LIKE 'QUIZ%' | Reverse the expression | ||
| EXISTS | EXISTS (SELECT ID FROM Channels WHERE ID='SMPP1' AND Enabled <> 0) | True if a sub-query contains any rows |
Functions
You can use SQL functions in your SQL expression as well. Find a full list of all functions here.
A short list of useful functions:
| Function | Example | Description | ||
|---|---|---|---|---|
| SYSUTCDATETIME() | LastUpdate > DATEADD(d, -7, SYSUTCDATETIME()) | Returns the current date/time in UTC. The SMS Server database records all date/time values in UTC. | ||
| DATEADD(datepart, number, date) | LastUpdate > DATEADD(d, -7, SYSUTCDATETIME()) | Add time to a given date and return the new value. The datapart can be: n - Minutes hh - Hours d - Days m -Months yy - Years The number can be negative to subtract. The date can be a data field or an expression that returns a date. | ||
| RAND() | RAND() < 0.5 | Generate a random number between 0 and 1 | ||
| CONVERT(datatype, expression) | CONVERT(INT, RAND() * 100) % 10 = 1 | Converts the expression into the specified datatype. Use it to convert date to string or floats to integers so you can use the modulo operator. | ||
| fnRouteProportional(randnr, lanes) | fnRouteProportional(RAND(ID), 4) | Returns a random number between 0 and lanes - 1. This function is intended to do proportional routing inside routing rules. When called as per example the returned number will be the same for the same ID. | ||
| fnNumQueued(channelid) | fnNumQueued(ChannelID) | Returns the number of queued messages on a given channel. |