Canvas - Datatable

Elastic Stack Kibana
1

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

2

Any help please ?

3

Hi @Raj_Kumar,

Let me see if @tims can help?

Thanks,
Liza

4

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

5

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

6

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

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
7

Hi Tims,

Thanks for the reply but I get

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

8

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
nessus_exp.png1826×481 38.8 KB

9

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.

10

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

11

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

12

Any Help please

13

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

14

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