Single view and widget to show all unique assessment failures in the workspace

[nfx]

Adding counter widgets is also nice to see total unique and total failures numbers. Later, this view to be queried from 2000 workspaces in the account and providing the report to the driving group.

Imagine you have

$inventory.table_failures:	catalog	database	table	failure
hive_metastore	foo	bar	NULL
hive_metastore	foo	baz	Cannot read metadata

$inventory.clusters:	cluster_id	...	failures
3409-lskajd-0932	...	[]
3409-lskajd-0933	...	["Uses Azure SPN conf", "Uses passthrough", "..."]
3409-lskajd-0934	...	[]

and then other tables with failures columns, please create a view named $inventory.failures, that will have the following sample output for these two tables:

object_type	object_id	failures
table	hive_metastore.foo.bar	[]
table	hive_metastore.foo.baz	["Cannot read metadata"]
cluster	3409-lskajd-0932	[]
cluster	3409-lskajd-0932	["Uses Azure SPN conf", "Uses passthrough", "..."]
cluster	3409-lskajd-0934	[]

and then a visualization for it using the framework: https://github.com/databrickslabs/ucx/blob/main/src/databricks/labs/ucx/assessment/queries/database_summary.sql

the visualization should be called "top failures" and have the structure:

failure	count
Uses Azure SPN conf	928
Incompatible DBR	2309
Uses passthrough	8
Cannot read metadata	3

[larsgeorge-db]

@nfx should we not first update the code and make all of them write a JSON structure instead of plain text?

[larsgeorge-db]

And why is this a view? This looks more like a regular query with some CTEs for the subsets. No?