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?
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;
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;