How can i formatting result of {{QUERY_RESULT_ROWS}} in custom alerts that i can see something like table , not like rubbish?

Support Usage Support
1

I used custom alerts for describing result of query in body of email. The {{QUERY_RESULT_COLS}} gives no data , just names of columns in query, instead of it QUERY_RESULT_ROWS gives all needed information , but it is not readable cause there are 4 columns and all data described like one row, without order, with some “/u” and {{. And it gives nothing because finding needed information very hard process.
Could you please share with me solution how configure it like a table in query in pretty way.
Thank you in advance

2

From our alerts documentation:

The preview is useful for verifying that template variables get rendered correctly. It is not an accurate representation of the eventual notification content, as each alert destinations can display notifications differently.

To format the message further you’ll need to configure an Alert Destination that can perform more detailed transformations.

3

For example, how should i change configuration an Alert Destination for gmail emailing?

4

You’ll need to have a web-service that transforms Redash’s alert data into a nicely formatted email. The flow would look something like this:

image
image1725×237 15.2 KB

5

How to connect to Alert result data via Python, what configuration i should use?Please give me an example

6

Hi viktoria,

My workaround for this is:

  1. Output a new column that contains values in all rows that you want to display in your alert using group_concat (If you are using Query Results data source). Format it as you would like to appear in slack. Something like:

SELECT A,
(SELECT group_concat(A, ‘
‘
FROM table) as slack_display
FROM table

And use formatting type ‘Number’

Notice that I gave a newline(using enter) in the group concat delimiter, so that new row will rendered as newline in slack

  1. Use slack_display as trigger column on your alert
  2. Use {{QUERY_RESULT_VALUE}} in your description. Note that: the description has character limitation, so it will truncate your last row to column_valu… Try to limit your data

One of my usage, to track which query that has updated or created by someone in the last 1 hour. It has hyperlink, so we can go to the specific query. You could also mention someone or @channel. Read more about slack formatting guide here

image
image828×797 318 KB

Another workaround is using Webhook Alert Destination, getting the visualization image screenshot link and post it to slack. Currently I am using google apps script to get the image screenshot and post it to slack. Something like Pulse in Metabase if you are familiar with it :slight_smile:
Example (using counter viz):

image
image826×401 81 KB

1 Like
7

Hello Rahi)

Thank you very much!

8

These alerts are actually rendered with Mustache. So an alert template like this will work to render the results of a query with columns column_one and column_two into an HTML table. Works just fine in emails in my experience.

<table>
<tr><th>Column one</th><th>Column two</th>
{{#QUERY_RESULT_ROWS}}
<tr>
<td>{{column_one}}</td> <td>{{column_two}}</td>
</tr>
{{/QUERY_RESULT_ROWS}}
</table>

This isn’t documented anywhere, so I guess it’s not supported, but unless Redash swaps out the Mustache template engine for something else, it will work.

3 Likes
9

Hi I tried this approach and i can see empty been printed

My data looks something like this

{
  "QUERY_RESULT_ROWS": [
    {
      "_id": {
        "user": "testet@tester.com",
        "product_name": "Cut-Off",
        "product_description": "Wheel; Cut-Off; 6.00 in. Diameter; 60 Grit; Type 1; Aluminum Oxide; 0.047 in. Thickness Edge; 7/8 in. Arbor Mount; 10200 RPM; Used on Stainless Steel, Steel\n",
        "comment": "Testing",
        "product_id": "66298024654-1",
        "product_substitute_id": "69905322073-1"
      }
    },
    {
      "_id": {
        "user": "test@test.com",
        "product_name": "Cut-Off",
        "product_description": "Wheel; Cut-Off; 6.00 in. Diameter; 60 Grit; Type 1; Aluminum Oxide; 0.047 in. Thickness Edge; 7/8 in. Arbor Mount; 10200 RPM; Used on Stainless Steel, Steel\n",
        "comment": "Testing",
        "product_id": "66298024654-1",
        "product_substitute_id": "69905322073-1"
      }
    }
  ]
}

Template:

<ul>
{{#QUERY_RESULT_ROWS}}
<li>{{ _id.user }}</li>
{{/QUERY_RESULT_ROWS}}
</ul>

But when i try this in mustache or handlebars, it works fine compared to in preview mode of template or even the emails been passed down,

what is see is empty li tags been generated.

Any help would be grateful.
Thanks.