# Canvas - Datatable

**URL:** <https://discuss.elastic.co/t/canvas-datatable/208560>\
**Category:** Kibana\
**Created:** [November 19, 2019, 6:27pm UTC](https://discuss.elastic.co/t/canvas-datatable/208560 "2019-11-19T18:27:59Z")\
**Posts on this page:** 14\
**Page:** 1

<div class="post-metadata">

**Author:** ![Raj\_Kumar](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/raj_kumar/32/25420_2.png) [@Raj\_Kumar](https://discuss.elastic.co/u/Raj_Kumar)\
**Post date:** [November 19, 2019, 6:27pm UTC](https://discuss.elastic.co/t/canvas-datatable/208560/1 "2019-11-19T18:27:59Z")

</div>

Hi There,

Am trying to create a datatable in canvas , but Iam not sure how to execute this condition. I have a fields hostname and risk\_factor (critical, high, medium, low ) and Iam trying to create a datable on top hosts basedon the count of risk factor like my picture for last 7 days, but am not sure how to implement it .Please help me to fix it .

Thanks,  
Raj  
 ![datatable](https://us1.discourse-cdn.com/elastic/original/3X/f/d/fd51431ad81a5082e47acd3f65b1c484f27d4863.png)

---

<div class="post-metadata">

**Author:** ![Raj\_Kumar](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/raj_kumar/32/25420_2.png) [@Raj\_Kumar](https://discuss.elastic.co/u/Raj_Kumar)\
**Post date:** [November 20, 2019, 10:37am UTC](https://discuss.elastic.co/t/canvas-datatable/208560/2 "2019-11-20T10:37:54Z")

</div>

Any help please ?

---

<div class="post-metadata">

**Author:** ![LizaD](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/lizad/32/51074_2.png) [@LizaD](https://discuss.elastic.co/u/LizaD)\
**Post date:** [November 20, 2019, 7:22pm UTC](https://discuss.elastic.co/t/canvas-datatable/208560/3 "2019-11-20T19:22:59Z")

</div>

Hi @Raj_Kumar,

Let me see if @tims can help?

Thanks,  
Liza

---

<div class="post-metadata">

**Author:** ![tims](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/tims/32/46750_2.png) [@tims](https://discuss.elastic.co/u/tims)\
**Post date:** [November 20, 2019, 7:36pm UTC](https://discuss.elastic.co/t/canvas-datatable/208560/4 "2019-11-20T19:36:44Z")

</div>

Hey @Raj_Kumar can you post the entire expression that you are using to create that datatable?

---

<div class="post-metadata">

**Author:** ![Raj\_Kumar](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/raj_kumar/32/25420_2.png) [@Raj\_Kumar](https://discuss.elastic.co/u/Raj_Kumar)\
**Post date:** [November 20, 2019, 8:53pm UTC](https://discuss.elastic.co/t/canvas-datatable/208560/5 "2019-11-20T20:53:58Z")

</div>

Hi Tims,

**SELECT fname, count(_) as total from "nessus-_" where risk\_factor = 'Critical' GROUP BY fname HAVING count(\*) \> 0 ORDER BY total DESC**

But with this I can list only risk factor = critical but I would like to include other risk factor like High and medium in the same data table , but i dont how to do it . Same like my screenshot above.

Thanks,  
Raj

---

<div class="post-metadata">

**Author:** ![tims](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/tims/32/46750_2.png) [@tims](https://discuss.elastic.co/u/tims)\
**Post date:** [November 21, 2019, 7:49pm UTC](https://discuss.elastic.co/t/canvas-datatable/208560/6 "2019-11-21T19:49:12Z")

</div>

Hey @Raj_Kumar,

I saw your question come in via support as well but submitting a proposal here as well.

You can do the sub queries in the Canvas expression I think by using the `mapColumn` function.  
[https://www.elastic.co/guide/en/kibana/current/canvas-function-reference.html#mapColumn\_fn](https://www.elastic.co/guide/en/kibana/current/canvas-function-reference.html#mapColumn_fn)

So do an initial query that returns the hostnames and maybe the total count if they want that.  
Then `mapColumn` to get the count for each severity level:

Some pseudocode:

```
| filters
| essql 'SELECT host from index'
| mapColumn name='Critical' fn={filters | essql 'SELECT host, count( <em>) from index where severity='CRITICAL'}
| mapColumn name='High' fn={filters | essql 'SELECT host, count(</em> ) from index where severity='HIGH'}
| mapColumn name='Medium' fn={filters | essql 'SELECT host, count( <em>) from index where severity='MEDIUM'}
| mapColumn name='Low' fn={filters | essql 'SELECT host, count(</em> ) from index where severity='LOW'}
| render
```

---

<div class="post-metadata">

**Author:** ![Raj\_Kumar](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/raj_kumar/32/25420_2.png) [@Raj\_Kumar](https://discuss.elastic.co/u/Raj_Kumar)\
**Post date:** [November 22, 2019, 10:04am UTC](https://discuss.elastic.co/t/canvas-datatable/208560/7 "2019-11-22T10:04:40Z")

</div>

Hi Tims,

Thanks for the reply but I get

Unable to parse expression: Expected [\t\r\n] or function but "|" found

---

<div class="post-metadata">

**Author:** ![Raj\_Kumar](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/raj_kumar/32/25420_2.png) [@Raj\_Kumar](https://discuss.elastic.co/u/Raj_Kumar)\
**Post date:** [November 22, 2019, 12:19pm UTC](https://discuss.elastic.co/t/canvas-datatable/208560/8 "2019-11-22T12:19:20Z")

</div>

filters  
| essql  
query="SELECT "@timestamp" + INTERVAL '1' HOURS as ABATime, "fname" as fname, "risk\_factor" as risk\_factor FROM "nessus-_" WHERE "@timestamp" \> NOW() - INTERVAL '7' DAYS ORDER BY ABATime DESC"  
| mapColumn risk\_factor='Critical' fn={filters | essql 'SELECT fname, count(_) FROM "nessus-_" WHERE risk\_factor='CRITICAL'}  
| mapColumn risk\_factor='High' fn={filters | essql 'SELECT fname, count(_) FROM "nessus-_" WHERE risk\_factor='HIGH'}  
| mapColumn risk\_factor='Medium' fn={filters | essql 'SELECT fname, count(_) FROM "nessus-_" WHERE risk\_factor='MEDIUM'}  
| mapColumn risk\_factor='Low' fn={filters | essql 'SELECT fname, count(_) FROM "nessus-\*" WHERE risk\_factor='LOW'}  
| render

 ![nessus_exp](https://us1.discourse-cdn.com/elastic/original/3X/3/a/3a208f96156d63a2f30549fb8186122398681884.png)

---

<div class="post-metadata">

**Author:** ![tims](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/tims/32/46750_2.png) [@tims](https://discuss.elastic.co/u/tims)\
**Post date:** [November 22, 2019, 1:53pm UTC](https://discuss.elastic.co/t/canvas-datatable/208560/9 "2019-11-22T13:53:46Z")

</div>

Couple of things:

"name" is an actual argument to mapColumn so it should be `name='Critical'` not `risk_factor='Critical'` as the first argument to mapColumn.

Other than that you have some quotation problems in your query. In your `WHERE` clause use double quotes for `risk_factor='Critical'` and then remember to close out the query string with a single quote.

---

<div class="post-metadata">

**Author:** ![Raj\_Kumar](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/raj_kumar/32/25420_2.png) [@Raj\_Kumar](https://discuss.elastic.co/u/Raj_Kumar)\
**Post date:** [November 25, 2019, 9:00am UTC](https://discuss.elastic.co/t/canvas-datatable/208560/10 "2019-11-25T09:00:58Z")

</div>

Hi Tims,

You mean like this

filters

| essql

query="SELECT "@timestamp" + INTERVAL '1' HOURS as ABATime, "fname" as fname, "risk\_factor" as risk\_factor FROM "nessus-" WHERE "@timestamp" \> NOW() - INTERVAL '7' DAYS ORDER BY ABATime DESC"

| mapColumn name='Critical' fn={filters | essql 'SELECT fname, count(\*) FROM "nessus-" WHERE "risk\_factor='CRITICAL'"}

| mapColumn name='High' fn={filters | essql 'SELECT fname, count(\*) FROM "nessus-" WHERE "risk\_factor='HIGH'"}

| mapColumn name='Medium' fn={filters | essql 'SELECT fname, count(\*) FROM "nessus-" WHERE "risk\_factor='MEDIUM'"}

| mapColumn name='Low' fn={filters | essql 'SELECT fname, count(_) FROM "nessus-_" WHERE "risk\_factor='LOW'"}

| render

still the same i couldnt run the expression

---

<div class="post-metadata">

**Author:** ![Raj\_Kumar](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/raj_kumar/32/25420_2.png) [@Raj\_Kumar](https://discuss.elastic.co/u/Raj_Kumar)\
**Post date:** [November 25, 2019, 12:48pm UTC](https://discuss.elastic.co/t/canvas-datatable/208560/11 "2019-11-25T12:48:09Z")

</div>

Edu was trying to help me ,but he gets null values with this expression and even I also tried the same

filters  
| essql query=essql 'SELECT hostname as hostname from secscan\_sample\_data group by hostname'  
| mapColumn name='Critical' fn={ filters | essql 'SELECT count(\*) from secscan\_sample\_data where risk\_factor = 'Critical'' }  
| render

---

<div class="post-metadata">

**Author:** ![Raj\_Kumar](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/raj_kumar/32/25420_2.png) [@Raj\_Kumar](https://discuss.elastic.co/u/Raj_Kumar)\
**Post date:** [November 25, 2019, 7:31pm UTC](https://discuss.elastic.co/t/canvas-datatable/208560/12 "2019-11-25T19:31:17Z")

</div>

Any Help please

---

<div class="post-metadata">

**Author:** ![tims](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/tims/32/46750_2.png) [@tims](https://discuss.elastic.co/u/tims)\
**Post date:** [November 26, 2019, 5:26pm UTC](https://discuss.elastic.co/t/canvas-datatable/208560/13 "2019-11-26T17:26:33Z")

</div>

Hey @Raj_Kumar,

I've spent some time on this and the problem is that you can't hang on to the context at the individual row level, so in this case the sub-expression won't return the data you need. The best way to do this is to use `ply` so you can at least split out and group the numbers into unique rows.

Here's an example:  
`filters | essql 'SELECT hostname, risk_factor, count(*) as "count" from secscan_sample_data group by hostname, risk_factor' | ply by="risk_factor" | render`

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\
**Post date:** [December 24, 2019, 5:26pm UTC](https://discuss.elastic.co/t/canvas-datatable/208560/14 "2019-12-24T17:26:34Z")

</div>

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.