Sort order in Repository Dashboard

[DickUMCG]

Is your feature request related to a problem? Please describe.
A clear and concise description of what the problem is. Ex. I'm always frustrated when [...]

I edited the origina Repository Dashboard to get a quick overview with servernames and their responding status-colors. This works nicely and looks like this:

Well, ofcourse with the servernames in it, but for security-reasons I made a screenshot with the shown value None. :-) But it is sorted based on servername so first ServerA001, ServerA002, ServerC423, ServerC555 etc.

Describe the solution you'd like
A clear and concise description of what you want to happen.
What I would like to implement is: first show all the red colors, then the orange and then the green. So if it won't fit on one page the critical servers would show up first.

Describe alternatives you've considered
A clear and concise description of any alternative solutions or features you've considered.
Tried editing the underlying query but I didn't get a good result. Guess it might be easy, but I don't see it. And my Grafana-knowledge isn't that thorough yet.

Additional context
Add any other context or screenshots about the feature request here.

[marcingminski]

Yeah, I wanted this but I think that's a Grafana limitation. If you can work out how to do this I will be grateful.

[DickUMCG]

Ok, I'll keep testing, and maybe someone else with more Grafana-knowledge knows a solution.

[pluim003]

Following query would give the correct sort order in the dashboard:

select [sql_instance], [Checks] = max(case when last_check_status = 'OK' then 0 when last_check_status = 'WARNING' then 1 when last_check_status = 'CRITICAL' then 2 else 3 end) from [dbo].[vw_sqlwatch_report_dim_check] where check_enabled=1 group by sql_instance order by [Checks] desc, sql_instance;

And...

SELECT [sql_instance] FROM (select top 100 [sql_instance], [Checks] = max(case when last_check_status = 'OK' then 0 when last_check_status = 'WARNING' then 1 when last_check_status = 'CRITICAL' then 2 else 3 end) from [dbo].[vw_sqlwatch_report_dim_check] where check_enabled=1 group by sql_instance order by [Checks] desc, sql_instance);

gives the same order. And I need a top x rows as otherwise it would complain.

To use variable servername as the JSON notes @@ServerName, and replacing that by @@sql_instance results in an error message that I must the declare the scalar variable @@sql_instance I tried with

SELECT [servername] FROM (select top 100 [sql_instance] as servername, [Checks] = max(case when last_check_status = 'OK' then 0 when last_check_status = 'WARNING' then 1 when last_check_status = 'CRITICAL' then 2 else 3 end) from [dbo].[vw_sqlwatch_report_dim_check] where check_enabled=1 group by sql_instance order by [Checks] desc, sql_instance);

But the dashboard only now shows one panel with @@ServerName.

So if anyone knows how to solve this.... I must be overseeing something very simple.

[pluim003]

After testing and trying I came up with the following:

create view dbo.vw_sqlwatch_grafana_overview as
SELECT [servername] FROM (select top 1000 [sql_instance] as servername, [Checks] = max(case when last_check_status = 'OK' then 0 when last_check_status = 'WARNING' then 1 when last_check_status = 'CRITICAL' then 2 else 3 end) from [dbo].[vw_sqlwatch_report_dim_check] where check_enabled=1 group by sql_instance order by [Checks] desc, sql_instance) v;

And then in the overview-dashboard. I entered the query so part of the JSON looks like

"datasource": {
"uid": "$DataSource"
},
"definition": "SELECT [servername] from [dbo].[vw_sqlwatch_grafana_overview]",
"hide": 0,
"includeAll": true,
"label": "SQL Instance",
"multi": true,
"name": "sql_instance",
"options": [],
"query": "SELECT [servername] from [dbo].[vw_sqlwatch_grafana_overview]",
"refresh": 1,
"regex": "",
"skipUrlSync": false,
"sort": 0,
"tagValuesQuery": "",
"tagsQuery": "",
"type": "query",
"useTags": false
},

This gives me the dashboard I wanted to see with the reds first, then the oranges and then the greens. And if it's the case it will start with the purples.

[pluim003]

Still have to figure out one issue: the autorefresh doesn't refresh the query so after a refresh the color-order might be different again. Pressing F5 solves this then.

[pluim003]

Filed grafana/grafana#44889 for this. See what comes out of it.