How can I define a metric that shows List of Expensive Queries executed in MSSQL like this query: https://blog.sqlauthority.com/2021/03/17/sql-server-list-expensive-queries-updated-march-2021/

[javad87]

How can I define a metric that shows List of Expensive Queries executed in MSSQL like this query: https://blog.sqlauthority.com/2021/03/17/sql-server-list-expensive-queries-updated-march-2021/

I want to show result of T-SQL query as a table inside Grafana dashboard... That data is not changing instantly with time like time series data model that prometheus supports (e.g. like cpu usage), and I guess using counter , gague or summary metric type is not suitable for showing result as a Grafana table, I'm wondering how can I do it with sql_exporter....
BTW, I know how to add query to *.collector.yaml file but what type of metric should I use for prometheus so that it can recognize it as a table?

[RickMura]

@javad87 I'm using this in prometheus.
- metric_name: mssql_long_query_running
type: gauge
help: 'Long Running Queries'
value_label: minutes
values:
- status
key_labels:
- text
query: |
Select
db_name(es.database_id) + ': ' + et.text as text,
floor(er.total_elapsed_time / (1000 * 60)) % 60 as status
from
sys.dm_exec_requests er inner join sys.dm_exec_sessions es on er.session_id=es.session_id
CROSS APPLY sys.dm_exec_sql_text(er.sql_handle) et
where
es.is_user_process = 1
and er.wait_type <> 'SP_SERVER_DIAGNOSTICS_SLEEP'

And this in grafana
mssql_long_query_running{minutes="status",instance="myinstancename:myport"}