...
The SQL to run. This SQL can contain almost anything you would like. Currently there is one special "substitution" value that can be specified. When "user.customer" is found in a query, it is replaced with the name of the customer of the current user (or in the admin's case, the one specified in the advanced menu, if none is selected the query will likely fail).
query subsitutions
Currently supported subsitutions are:
| query token | substituted value |
|---|---|
| user.customer | The customer name of the current user is assigned to, if the user does not have a customer the query can fail. Administrators have an option to set this value in the advanced menu. |
| time.start | unix timestamp of the starting time selected from the advanced menu, default value is 15min before now |
| time.end | unix timestamp of the ending time selected from the advanced menu, default value is now |
For the timestamps the time column being queried may need to be converted into a unix timestamp to make a comparison valid. MySQL's timestamp function is UNIX_TIMESTAMP( timecolumn )
groupby:
If the data returned from the SQL statement needs to be grouped (for summing or counting, works much like SQL GROUP BY) use this field to specify the group, as an array, order matters. This works in tandem with the aggregation function to produce results. Just like in an SQL GROUP BY each column requires a function to aggregate it's result.
...