Alert Log, count alarms by 1 hour interval

Archived Topics
1

So I am trying to make a mini alarm management system. I want to be able to take the selected alarm, and chart using a bar chart the alarm count vs hr interval. Now i was thinking of creating 24 sql queries and populate the data table that way, but is there an easier way to do this?

2

Absolutely! SQL databases are great a retrieving data like this. You would do something like:

SELECT EXTRACT(HOUR FROM active_time), COUNT(*) FROM AlarmLog WHERE item_name='...(something)...' AND active_time > DATE_SUB(NOW(), INTERVAL 24 HOUR) GROUP BY EXTRACT(HOUR FROM active_time)

This will select the count of a specific alarm in thelast 24 hours, grouped by hour of day. (note that if an hour has no alarms, you won’t get a 0 count for that hour, you just won’t get an entry for that hour. This can be fixed by doing a LEFT JOIN with a static table with one column and the entries 0-23 as rows.)

Hope this helps,

3

Hi Carl, i tried the following code, but it still on returns columns with non zero counts on it

SELECT hour,COUNT(*) FROM factorypmi.Hours LEFT JOIN factorypmi.Alert_log ON hour = EXTRACT(HOUR FROM active_time)  WHERE group_name='LT-100' AND active_time > DATE_SUB(NOW(), INTERVAL 24 HOUR) GROUP BY hour
4

try this:

[code]SELECT hour,COUNT(active_time)

FROM factorypmi.Hours LEFT JOIN factorypmi.Alert_log ON hour = EXTRACT(HOUR FROM active_time)

WHERE (group_name IS NULL OR group_name=‘LT-100’) AND (active_time IS NULL OR active_time > DATE_SUB(NOW(), INTERVAL 24 HOUR))

GROUP BY hour[/code]

Your where clause wasn’t matching the rows that the left join added in because the group_name and active_times were null.

5

I must be missing something. The query still doesnt return rows 0-23 all the time. only returns like… 7 rows, 1-4, 7, 12 and 23. Is there another way we can try this?

6

Hmm, do you have 24 rows, 0-23, in your factorypmi.Hours table? Give us a call - we could probably figure this out faster over GoToMeeting.

7

Not that I’m a big fan of sub-selects, but as a fall back, I got this to work:

SELECT h.hour, (SELECT count(*) FROM factorypmi.alert_log a WHERE a.hour=h.hour and a.group_name='LT-100') FROM factorypmi.hours h

There’s got to be a simple reason why adding the WHERE clause isn’t working in carl’s proposal, but it’s not coming to mind right now.

Later,[/code]

8

Ok, I think I just figured it out. The main trick to to move your WHERE clause into the join:

SELECT hour,COUNT(active_time) FROM factorypmi.Hours LEFT JOIN factorypmi.Alert_log ON hour = EXTRACT(HOUR FROM active_time) AND (group_name IS NULL OR group_name='LT-100') AND (active_time IS NULL OR active_time > DATE_SUB(NOW(), INTERVAL 24 HOUR)) GROUP BY hour

Hope that works,

9

Thanks guys, works like a charm