Grafana v12 breaks my SQL queries when I have Grafana variables

[dandy72]

One of my customers jumped onto Grafana v12 (before I did) and reported one of my dashboards now being broken (it worked fine with previous 11.x versions). I immediately reproduced the problem just by upgrading from 11.4 to 12.0. If I roll back to 11.x, the problem goes away.

The screenshot below shows part of the MS SQL query behind my dashboard, and the problem is rather evident. I’m initializing two SQL variables, @ObjectName and @CounterName (both deal with Windows performance counters), both are initialized with variables provided by Grafana, $PerfCounterObjects and $PerfCounterNames. I surround these with single quotes, as all strings should be.

The query inspector shows the first variable has been substituted correctly, and has the expected single quotes (highlighted in green).

The second variable OTOH somehow substituted the value for the variable, but also added its own pair of single quotes around it, doubling them (highlighted in red - ‘’% Free space’’ instead of ‘% Free Space’).

Obviously I should be consistent, so if I remove both sets of single quotes in my query, then the first line (setting @ObjectName) now introduces its own syntax error, as the string should be surrounded by single quotes. But if I only drop the single quotes around ‘$PerfCounterNames’, then I can get data again.

I could make a special case out of it (and leave out the single quotes around $PerfCounterNames), but then I’d be breaking 11.x. I’d hate to make a special case based on the version of Grafana being used.

This is possibly important: My panel is repeated based on $PerfCounterNames (I repeat the panel for every value returned by the SQL query that populates $PerfCounterNames). As such, this is sounding to me similar to:

Repeating Rows based on a Variable breaks query by automatically adding single 'quote' marks

What Grafana version and what operating system are you using? Grafana 9.1.8 What are you trying to achieve? Repeating rows in dashboard based on variable How are you trying to achieve it? Variable type Custom with comma separated values It works fine for none repeating rows dashboard What happened? Single quote marks get inserted and query breaks When adding a row that is repeating on the variuable, then the expression used in the query where the variable is part of the query ge…

It’s not clear to me however how that solution might apply in my case.

FWIW, both $PerfCounterObjects and $PerfCounterNames variables are populated from SQL queries. $PerfCounterObjects is used to fill in a dropdown. The user selects a Performance Counter Object, and then the Perf Counter Names (for that object) are fetched, and then I end up with a separate panel for every name. Worked beautifully until v12.

What can I do here, that is not version-dependent, to solve this problem? If this requires a bug fix, I’m okay with telling my customer to wait for it.

[yosiasz]

bummer. Could you please share the sql query that builds PerfCounterNames

[dandy72]

Sure.

-- Performance Counters for the given object/computer
DECLARE @Computer NVARCHAR(128)
SET @Computer = '$Computer';
--SET @Computer = 'HYPERV04.mydomain.com';
--SET @Computer = 'ACER7.mydomain.com';
--SET @Computer = 'VSQL.mydomain.com';

DECLARE @ComputerOnly NVARCHAR(128)
SET @ComputerOnly = SUBSTRING(@Computer, 1, CHARINDEX( '.', @Computer) - 1)

DECLARE @ComputerDot NVARCHAR(128)
DECLARE @ComputerSemiCol NVARCHAR(128)
DECLARE @ComputerBS NVARCHAR(128)

SET @ComputerDot = @ComputerOnly + '.'
SET @ComputerSemiCol = @ComputerOnly + ';'
SET @ComputerBS = @ComputerOnly + '\';

DECLARE @ObjectName NVARCHAR(128)
SET @ObjectName = '$PerfCounterObjects';
--SET @ObjectName = 'Health Service';
--SET @ObjectName = 'SQL Database';

SELECT
    DISTINCT /*pcv.[ObjectName],*/ pcv.CounterName
FROM PerformanceCounterView pcv
INNER JOIN BaseManagedEntity bme 
ON pcv.ManagedEntityId = bme.BaseManagedEntityId
CROSS APPLY (
    SELECT TOP 1
        pdv.SampleValue,
        pdv.TimeSampled
    FROM PerformanceDataAllView pdv
    WHERE pdv.PerformanceSourceInternalId = pcv.PerformanceSourceInternalId
-----        AND bme.[Path] = @Computer
--AND bme.[Path] LIKE '%' + @Computer + '%'
     AND
        (bme.[Path] LIKE '%' + @Computer + '%'
         OR (bme.[Path] IS NULL AND
            (bme.[DisplayName] LIKE '%' + @ComputerDot + '%' OR
             bme.[DisplayName] LIKE '%' + @ComputerSemiCol + '%' OR
             bme.[DisplayName] LIKE '%' + @ComputerBS + '%')
            )
        )
        --AND pdv.TimeSampled >= DATEADD(HOUR, -12, GETUTCDATE())
        AND pcv.[ObjectName] = @ObjectName
AND $__timeFilter(pdv.TimeSampled)
    --ORDER BY pdv.TimeSampled DESC, pdv.SampleValue DESC
) AS [LatestData]
ORDER BY pcv.[CounterName]

[yosiasz]

what if you tried

${PerfCounterNames:singlequote}

Also I highly encourage you to use stored procedures instead of a lot of this inline querying.

Also this type of concatenation will come back to bite you

SET @ComputerDot = @ComputerOnly + '.'
SET @ComputerSemiCol = @ComputerOnly + ';'
SET @ComputerBS = @ComputerOnly + '\';

instead do it in stored procedures and the following way

SET @ComputerDot = concat(@ComputerOnly,'.')
SET @ComputerSemiCol = concat(@ComputerOnly,';')
SET @ComputerBS = concat(@ComputerOnly,'\')

also instead of AND pcv.[ObjectName] = @ObjectName could you use

AND pcv.[ObjectGuid] = @ObjectGuid is there is such a thing?

[dandy72]

I was not aware of this syntax → ${PerfCounterNames:singlequote}

I’m assuming this is NOT new to v12 and I can safely use this throughout all my queries.

It seems to take care of the problem, as long as I consistently remove the single quotes that are hardcoded in my query. So I used it in both locations:

SET @ObjectName = ${PerfCounterObjects:singlequote};
SET @CounterName = ${PerfCounterNames:singlequote};

Also I highly encourage you to use stored procedures instead of a lot of this inline querying.

I’m afraid this is not an option for me. The database is not mine, and for some of my customers all they can grant me is purely read-only access.

Also this type of concatenation will come back to bite you
[…]
instead do it in stored procedures and the following way

That, I do like, a lot. You’re right, I should’ve been doing this. Thanks for pointing it out.

As for the object GUID vs object name - no, those named objects don’t have corresponding GUIDs, I’m afraid.

Back to this :singlequote…are you suggesting I should commit to this, or (since I’m only now running into this) do you suspect this warrants a revision somewhere in the Grafana code?

[yosiasz]

@dandy72

you suspect this warrants a revision somewhere in the Grafana code

definitely worth bringing it up. but make sure to look in the breaking changes section also.

the other glaring issue is your own customers upgrading to v12. things should work yes and they do have that prerogative but … should have been tested.

[yosiasz]

@dandy72

As for the object GUID vs object name - no, those named objects don’t have corresponding GUIDs, I’m afraid

what is the primary key for those then a varchar column? what is the schema of PerformanceCounterView table

what is ManagedEntityId

[dandy72]

I’ve only had a cursory look at the breaking changes page, and it seemed like boilerplate text as of right now, saying there can always be problems in new major releases, but it didn’t bring anything specific. Still, I’ll keep an eye on it over the next few days.

I’ll have to think about using the $…:singlequote syntax or not. When I bring my queries back into something like SQL Server Management Studio, the more “Grafana things” are embedded in my queries, the more I have to massage them back so SQL can understand the query (among other things, I always have to adjust $__timeFilter() to some made-up value just so SQL can understand it, as you can see from the DATEADD() that I keep commented out).

I’m assuming the problem here is related to the fact that $PerfCounterNames is used to control the repeating panels - I can make a special case here for this unique situation, and leave the management of the quotes to Grafana (ONLY for that one variable, and not $PerfCounterObjects), and leave a comment in the SQL query to explain why that one variable is initialized in an inconsistent way.

I appreciate the quick feedback.

[yosiasz]

@dandy72

When I bring my queries back into something like SQL Server Management Studio, the more “Grafana things” are embedded in my queries, the more I have to massage them back so SQL can understand the query

you don’t need to do all that massaging (the more the need to massage the more indication of underlying issues ) , just hit Query Inspector and it will show you what it has done for you

[dandy72]

PerformanceCounterView is a view, and…well, I’ll remain polite and just say it gets messy. The database / schema is a third-party’s, and has evolved, literally, over at least 2 decades. There’s some nasty stuff in there that I’m pretty sure must be kept only for backwards compatibility reasons, and the chances it’ll ever get cleaned up are nil.

But for what it’s worth…

ManagedEntityId is a uniqueidentifier (a GUID, in MSSQL) identifying what could be a computer, or some other type of entity altogether.

The JOIN with Id from PerformanceCounterView should get rid of all non-computers, as only computers have perf counters. I have to include those messy strings combining computer names with various delimiters such as a dot, semi-colon and backslash to further filter out things that shouldn’t be there. These are tweaks that have been added over the course of months as edge cases kept surfacing, so it’s not something I’m going to rush in and redo. There’s unfortunately good reasons for those to be there, and performance right now is reasonable, and I’m going to have difficulty convincing others to re-open that Pandora’s box until a concrete problem is presented.

Especially when the only thing I’m afforded to look at right now is this quotation thing. So again, thanks.