Canvas and Dashboard show different values

Elastic Stack Kibana
1

Hi together,
I have created a metric visualization on a dashboard. The metric just counts the number of records (1.436) see picture below.

2021-11-04 15_17_03-
2021-11-04 15_17_03-1918×652 55.8 KB

I have created a metric visualization on a canvas (some index) and the metric shows a value of 1.000?

2021-11-04 15_07_14-
2021-11-04 15_07_14-1918×586 65.5 KB

2021-11-04 15_07_25-
2021-11-04 15_07_25-1917×584 61.6 KB

Can you please point me in the right direction?
Thanks in adbanced for your support.
Regards,
Rainer

2

Can you share the expression behind the metric in canvas? Also, could you make sure the time range is the same in visualize by clicking the "Inspect" button in the top right and switch to request?

3

Hi Joe,
here comes the expression behind the metric in canvas:

filters
| essql query="SELECT * FROM \"smartperform_statistics*\""
| math "size(@timestamp)"
| metric "Interactions" 
  metricFont={font size=48 family="'Open Sans', Helvetica, Arial, sans-serif" color="#000000" align="center" lHeight=48} 
  labelFont={font size=14 family="'Open Sans', Helvetica, Arial, sans-serif" color="#000000" align="center"} metricFormat="0,0.[000]"
| render

Here comes the inspect->request info from the dashboard_

{
  "aggs": {},
  "size": 0,
  "fields": [
    {
      "field": "@timestamp",
      "format": "date_time"
    }
  ],
  "script_fields": {},
  "stored_fields": [
    "*"
  ],
  "runtime_mappings": {},
  "_source": {
    "excludes": []
  },
  "query": {
    "bool": {
      "must": [],
      "filter": [
        {
          "match_phrase": {
            "fields.ComputerName.keyword": "I7L018"
          }
        },
        {
          "range": {
            "@timestamp": {
              "format": "strict_date_optional_time",
              "gte": "2021-11-03T23:00:00.000Z",
              "lte": "2021-11-04T22:59:59.999Z"
            }
          }
        }
      ],
      "should": [],
      "must_not": []
    }
  }
}

And here the debug filter from the canvas
grafik

Thanks for your support.
Rainer

4

I think a SELECT query won't give you the full data because of the cost - it has to transfer all documents into the browser. Doing essql query="SELECT COUNT(*) as count FROM \"smartperform_statistics*\"" instead of size in the math moves this into Elasticsearch and makes sure it's executed on the full data. The math can be replaced by a getCell.

5

Hi Joe,
I have not created this query. I have just used the menu from the selected element.
Question: Can I simply replace the query inside the Expression editor? And / or how can I save this query?

6

Excellent this query works!
Thanks a lot for your help!

7

Hi Joe,

following your advice, I have tried another visual and created the following SELECT query to perform the work on the server:

SELECT COUNT(fields.Event) FROM "smartperform_statistics*"

WHERE "fields.Event" = View.End

I get the error message

Expression failed with the message:

[essql] > Couldn't parse Elasticsearch SQL query. You may need to add double quotes to names containing special characters. Check your query and try again. Error: line 2:29: no viable alternative at input 'View.End'

I cannot find a hint inside the documentation. All examples I have found so far, are looking quit similar to me.

Sorry to ask for your support again.

Regards,

Rainer

8

You need to put the quotes around the value, not the field name

WHERE fields.Event = "View.End"
9

Hi Joe,

for some reason, the query works only with “ ‘ “.

10

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