0

I have succesfully used the new SQL (Line Graph) Tile to show Events and Alerts.

Running into a funny issue. I have an Alert History perspective scoped to All Alerts. It shows the number of alerts (Scalar) and also the alerts in a list (Grid).

Now I tried to add a Line Graph tile using the Query below.

DECLARE @60dago DateTime;SET @60dago = DATEADD(DAY, -60, GETUTCDATE())
SELECT COUNT(a.AlertName),
CAST(CONVERT(VARCHAR(20), [RaisedDateTime], 102) As DateTime) as DateTime
FROM
Alert.vAlert a
JOIN
vManagedEntity AS me ON a.ManagedEntityRowId = me.ManagedEntityRowId
WHERE
me.ManagedEntityGuid = {{monitoringObjectId}} AND RaisedDateTime >=@60dago AND
(
a.AlertProblemGuid = {{id}} or
a.AlertName = {{name}}
)
GROUP BY CAST(CONVERT(VARCHAR(20), [RaisedDateTime], 102) As DateTime)

For some reason it ignores the DataTime@60dago in the WHERE Clause.

Could someone try this and see if you get the same result. Or even better, find out if I have something wrong in my query 🙂

Peter Svensson answered
    • When I copy/paste your query I get an error: Error: Incorrect syntax near 'SET@60dago'. Must declare the scalar variable "@60dago". (102) - Not sure if this helps at all?
    • There was missing spaces between DECLARE @60dago and SET @60dago which must have happened when I pasted it. Have edited the query but it seems like its waiting for moderation.
    • The Query workst just fine when running it in SQL Management Studio on the Datawarehouse DB (if you insert proper values for the mustache variables). Its just not showing things correctly in the Line Graph Tile.
    • Oh yeah, it works now - thank you! (Can you tell I'm no SQL guru?)! I think this is a bug Peter - I'd recommend you reach out to Support 🙂