Hi all,
Hoping someone can assist me with some scripting to query our alarm table.
Basically we have alarm groups & I can put them into a table with the following query;
*"-- Select only distinct Equipment*
*SELECT DISTINCT alarm_event_data.strvalue*
*FROM alarm_event_data*
*WHERE alarm_event_data.propname = 'Equipment';"*
This will deliver a table with 1 row and x columns e.g
Alarm Group 0
Alarm Group 1
Alarm Group 2
........ 12
Now the part I'm struggling with is; I would like an expression that will query this table and return the number of active alarms in a specific equipment group I am querying as an integer. Or even as a dataset showing group/event_count.
Might be asking a bit much here but any help would be appreciated.
Cheers,
As written, this is purely a SQL question, and the answer is to use aggregation:
SELECT
aed.strvalue,
count(aed.id)
FROM alarm_event_data aed
WHERE aed.propname = 'Equipment'
GROUP BY alarm_event_data.strvalue
This will return a two column dataset with group name and count.
Thanks for the reply. Could you take a look at the attached pic and tell me what I missed?
If you hover over 'Error_Configuration' what does it say?
You might need to change your return format to JSON, and it may need a transform or adjustment in your query to line up with the format the radar chart expects to display.
Yeah I was aware that if it's in a dataset I'll need to transform to get the required column/row as I need a real number at the end, however I don't think that's the issue here. Below is the error details;
com.microsoft.sqlserver.jdbc.SQLServerException: The multi-part identifier "alarm_event_data.strvalue" could not be bound.
Here is a query currently in use to query total alarm counts within a time period if that helps;
SELECT
DATEADD(hour, DATEDIFF(hour, 0, alarm_events.eventtime), 0) AS hour_start,
COUNT(*) AS alarm_count
FROM
alarm_event_data
INNER JOIN
alarm_events ON alarm_event_data.id = alarm_events.id
WHERE
alarm_event_data.propname = 'label'
AND alarm_events.eventtime >= DATEADD(HOUR, :timePeriod, GETDATE()) -- Filter alarms within the last 8 hours
AND alarm_events.eventtype = 0
GROUP BY
DATEADD(hour, DATEDIFF(hour, 0, alarm_events.eventtime), 0)
ORDER BY
hour_start;
Are you missing the query parameters?
Tip: resize windows before taking a screengrab. Yours is 50% whitespace. Often the reduction in size means they are displayed at 100% and avoids an extra step in reading your post and flipping between the post and a 100% popup.
2 Likes
