Regex on variables in a query

1

In a MS SQL (SQL Svr Std 2008 R2) database I have many IDs. But some people prefer to use the name instead of the ID. ID/Name like IP address and hostname.
So I want to offer both together as a selection (Variables -> Selection Options -> Multi-value).
It looks like this:
[ID]#[Name]

12345#Product1
67890#Product2

This also works as long as only one product is selected:
[…]
WHERE ID IN(Substring($product, 0, CHARINDEX(’#’, $product)))

But of course it fails if more than one selection is made, because now I can’t work with substring anymore. If I offer only the ID as selection, variables work great:
[…]
WHERE ID IN($product)

Does Grafana offer me a possibility to extract the ID in or before the query or can I give the values of a variable a label?

I’m using Version 6.7.3.

Thanks.

2

You could change you query in products variable to something like this

SELECT concat(id, '#', name) AS __text,
ID AS __value
FROM test
3

CONCAT is new to SQL Server 2012. It is not a function on Previous Versions, including my 2008 R2.

The product ID/name is a WHERE clause.
In Grafana the following are displayed: Good (1), Check(2), Error(3), Repaired(1.5) depending on the product and period:

SELECT DATEDIFF(second, '1970-01-01', DATEADD(hour,-$timezone,EndDateTime)) AS time, 
       CASE PCBResultAfter
        WHEN '11000000' THEN 1
        WHEN '12000000' THEN 2
        WHEN '13000000' THEN
          CASE PCBRepair
            WHEN '2' THEN 1.5
            ELSE 3
          END
       End AS value,
       PCBModel AS metric
FROM  dbo.test
WHERE  PCBModel IN(Substring($product, 0, CHARINDEX('#', $product)))
       AND Barcode <> 'canceled'
       AND (Checksum > 0 OR (Checksum = '0' AND (PCBResultAfter = '11000000' OR PCBResultAfter = '12000000')))
       AND EndDateTime IS NOT NULL
       AND PCBResultAfter IS NOT NULL
       AND PCBModel IS NOT NULL
ORDER  BY EndDateTime ASC;

SPC
SPC1068×653 56.1 KB

STRING_SPLIT is also only available for SQL Server 2016 and higher.

4

HI mnyut, maybe I could be clearer,

my suggestion is based on the following grafana feature,
if you have a variable (templeating) you can have a column '__value' that can be the ID and another one that is '__text' that will be the label in the dropdown menu. so the idea is to concat the id and the model.

so, you have to update your query in the products variable to something like this:

SELECT (cast('PCBModel ' as varchar) + cast('PCBName' as varchar)) AS __text,
PCBModel AS __value
FROM dbo.test2

and the graph query like this:

SELECT DATEDIFF(second, '1970-01-01', DATEADD(hour,-$timezone,EndDateTime)) AS time, 
       CASE PCBResultAfter
        WHEN '11000000' THEN 1
        WHEN '12000000' THEN 2
        WHEN '13000000' THEN
          CASE PCBRepair
            WHEN '2' THEN 1.5
            ELSE 3
          END
       End AS value,
       PCBModel AS metric
FROM  dbo.test
WHERE  PCBModel IN($product)
       AND Barcode <> 'canceled'
       AND (Checksum > 0 OR (Checksum = '0' AND (PCBResultAfter = '11000000' OR PCBResultAfter = '12000000')))
       AND EndDateTime IS NOT NULL
       AND PCBResultAfter IS NOT NULL
       AND PCBModel IS NOT NULL
ORDER  BY EndDateTime ASC;
1 Like
5

I think I got it. The feature is also described here?

All right, I’ll try it out directly on Monday. I was hoping for this feature, but I couldn’t find it.
Thank you very much :slight_smile: .