Convert Frame from rows error-Dupe column name?

General
#1

I seem to have an issue with the Disk Space Used% frame in the instance overview. I have tried to modify the query that pills data in various ways…but I continue to get the following error

convert frame from rows error: duplicate column names are not allowed, found identical name “” at column indices 1 and 2

Here is the query

select time=date_last_seen, left([volume_name],2)+ ’ ’ + isnull(label,’’),1.0-[free_space_percentage]
from [dbo].[vw_sqlwatch_report_dim_os_volume]
where sql_instance = ‘$sql_instance’
order by left([volume_name],2)

If you run the query by itself on the database server and replace ‘$sql_instance’ with the server name, the query works. From trouble shooting, it seems to be the where clause that is causing the issue. I have tried different variations of “Where sql_instance =…” but nothing seems to work.

#2

Is that because there are no column names so they come back as “”?

left([volume_name],2)+ ’ ’ + isnull(label,’’),1.0-[free_space_percentage]

should this be:

column1 = left([volume_name],2)+ ’ ’ + isnull(label,’’), column2 = 1.0-[free_space_percentage]

(I’m just guessing here, didnt test it)

#3

Another thought if the column name idea is no good, have you tried typing the actual server name instead of the variable, just to make sure the variable isn’t causing the problem? i.e.

where sql_instance = 'YourInstance1'
#4

You gave me an idea…and it was just the columns had no names. So there was no value for both column, which apparently means they are “the same” name. Anyways, here is the correct format. I just imported the dashboard into my grafana instance, so some of the issues I have been fixing are formatting issues. Curious, what was the last time you updated the dashboard files in the releases?

Working query:

select time=date_last_seen, left([volume_name],2)+ ’ ’ + isnull(label,’’) as Drive ,1.0-[free_space_percentage] as Percentage
from [dbo].[vw_sqlwatch_report_dim_os_volume]
where sql_instance = sql_instance
order by left([volume_name],2)

Anyways, THANK YOU for the help. You are the best!

#5

Been a while to be fair. I got stuck with the version 5.0 not quite working as expected.

Glad you got it working and thank you for the kind words, I’m far from the best but I’ll let you think that :smiley:

#6

Well, I appreciate the help regardless. I am currently going through a process of removing our dependency on SQL Sentry by utilizing your solution and pairing it with CheckMK to provide proactive monitoring for our SQL Servers both on prem and in azure.

So far, I find myself editing some of the tiles in the dashboard, but I feel better that it was not just me that some of the queries had to be fixed. I think one of the easier fixes was just switch the format of the tile information from time series to table. If needed, I can export the dashboard with my edits and send them to you so you could maybe include them in the releases in the future? Let me know