Data Table - Display count as a metric and split it by filters

[prashu421 (Prashanth Sripathi)]

Hello,

I am working with Kibana through Elasticsearch version 7.4 (AWS Elasticsearch Service), and hoping to build a data table that looks similar to this -

Before splitting the count by pipeline id, I am trying to create two count metrics -
Total count, Grief count (using the Advanced json input section to filter out executions with grief) which doesn't seem to work -

The inline script is -

{
	"script": {
		"inline": "doc['GriefCount'].value > 0",
		"lang": "painless"
	}
}

Am I using this advanced section correctly?

Of course if I add a filter to the query, it affects both of the count metrics -

How can I add a filter only to the GriefCount metric? so I can split the Total Count metric by pipeline to achieve the intended results?

Thank you for reading through, any suggestions are appreciated.

Best,
Sri

[wylie (Wylie Conlon)]

I don't think you are using the advanced JSON correctly, and in general we strongly discourage its use. The concept of a "filtered metric" was introduced in Kibana Lens in 7.13, which is only available from Elastic. In previous versions the only possible workarounds are:

1. Perform any calculations that you need to before storing the data in Elasticsearch, then visualize in Kibana.

2. Use the Vega plugin to perform custom queries

[prashu421 (Prashanth Sripathi)]

Hello Wylie, could you please help me understand how to use the advanced json property correctly? any links would be appreciate too. As this would solve half of my problem.

Otherwise, I followed your suggestion and built a visualization using Vega, this is my first time working with this, so I am hoping to get some guidance here -

1. I am able to display the pipeline executions and grief count, but unable to figure out how to show the grief percentage here as well.
2. The sort order from 'data' section doesn't seem to be retained here, as you can see my sort is defined on 'grief_percentage', the query in Kibana dev tools does seem to reflect results in sorted order.
3. Is it possible to increase the width of y axis, as my pipeline ids are cut short, and even a hover doesn't show the entire id.

Here's my Vega query for reference -

{
	"$schema": "https://vega.github.io/schema/vega-lite/v2.json",
	"title": "Pipeline health",
	"data": {
		"url": {
			"%context%": true,
			"%timefield%": "CreatedOn",
			"index": "pipeline-execution",
			"body": {
				"size": 0,
				"aggs": {
					"Pipelines": {
						"terms": {
							"field": "PipelineId"
						},
						"aggs": {
							"total_exec": {
								"value_count": {
									"field": "PipelineId"
								}
							},
							"grief_count": {
								"filter": {
									"range": {
										"GriefToDeveloper": {
											"gte": 1
										}
									}
								},
								"aggs": {
									"dev_grief": {
										"value_count": {
											"field": "PipelineId"
										}
									}
								}
							},
							"grief_percentage": {
								"bucket_script": {
									"buckets_path": {
										"TotalExecutions": "total_exec",
										"DeveloperGrief": "grief_count>dev_grief"
									},
									"script": "(params.DeveloperGrief/params.TotalExecutions)*100"
								}
							},
							"SortByGrief": {
								"bucket_sort": {
									"sort": [
										{
											"grief_percentage": {
												"order": "desc"
											}
										}
									]
								}
							}
						}
					}
				}
			}
		},
		"format": {
			"type": "json",
			"property": "aggregations.Pipelines.buckets"
		}
	},
	"encoding": {
	  "x": {
			"field": "doc_count",
			"type": "ordinal",
			"title": "Total Executions"
		},
		"y": {
			"field": "key",
			"type": "ordinal",
			"title": "PipelineId"
		}
	},
	"layer": [
    {
      "mark": "bar",
      "encoding": {
        "color": {
          "field": "grief_count.doc_count",
          "type": "quantitative",
          "title": "Grief Count",
          "legend": {
            "direction": "horizontal", 
            "gradientLength": 150
          }
        }
      }
    },
    {
      "mark": "text",
      "encoding": {
        "text": {"field": "grief_count['doc_count']", "type": "ordinal"},
        "color": {"value": "white"}
      }
    }
  ],
  "config": {
    "axis": {"grid": false, "tickBand": "extent"}
  }
}

Sample output from dev tools for the query using _search -

[wylie (Wylie Conlon)]

1. You've done a good job understanding the Vega docs and Kibana examples and I think your spec is close.

2. You seem to be using some features that are not available in Vega-Lite v2- the most recent version is v5, which was released in Kibana 7.13. You should test out the online Vega editor to validate your spec is using the right version.

3. Since you have provided both sample data & a spec, it was possible to reproduce your case. If you had not provided sample data I would not have been able to do this.

Based on your spec, I added a tooltip: true to your rectangle mark, and second layer with a mark: text, dy: -20 to offset the percent label. I'm not really sure what you meant about the sorting, but FYI you can sort at the axis level. So I added sorting to your X and Y just in case.

{
  "$schema": "https://vega.github.io/schema/vega-lite/v2.json",
  "title": "Pipeline health",
  "width": 200,
  "height": 200,
  "data": {
    "values": [
      {
        "key": "Pipeline_id",
        "doc_count": 21678,
        "grief_count": {"doc_count": 15820, "dev_grief": {"value": 15820}},
        "total_exec": {"value": 21448},
        "grief_percentage": {"value": 73.234}
      },
      {
        "key": "Pipeline_id_2",
        "doc_count": 123443,
        "grief_count": {"doc_count": 12433, "dev_grief": {"value": 12433}},
        "total_exec": {"value": 2141},
        "grief_percentage": {"value": 60.234}
      }
    ],
    "format": {"type": "json"}
  },
  "encoding": {
    "x": {
      "field": "doc_count",
      "type": "ordinal",
      "title": "Total Executions",
      "sort": { "op": "sum", "field": "grief_percentage.value", "order": "descending" }
    },
    "y": {
      "field": "key",
      "type": "ordinal",
      "title": "PipelineId",
      "sort": { "op": "sum", "field": "grief_percentage.value", "order": "descending" }
    },
    "color": {
      "field": "grief_count.doc_count",
      "type": "quantitative",
      "title": "Grief Count"
    }
  },
  "layer": [{
    "mark": {
      "type": "rect",
      "tooltip": true
    }
  }, {
    "mark": "text",
    "encoding": {
      "text": {"field": "grief_count.doc_count", "type": "nominal"},
      "color": {"value": "white"}
    }
  }, {
    "mark": {
      "type": "text",
      "dy": -20
    },
    "encoding": {
      "text": {"field": "grief_percentage.value", "type": "nominal", "format": "0.0f"},
      "color": {"value": "white"}
    }
  }],
  "config": {"axis": {"grid": false }}
}

[prashu421 (Prashanth Sripathi)]

Hello Wylie, thank you for updating my script, I've tried it in the online vega editor and it works just fine, but not in my Kibana instance as suspected due to my current version.

After upgrading my instance this is working as expected.

Appreciate all the help!