DX NetOps

I'd like to query the alarminfo to get active "DEVICE HAS STOPPED RESPONDING TO POLLS" alarms. Anyone know how to do that ?

You need to look in 2 tables for all the info you need ... 

mysql> select * from alarmtitle where title like '%DEVICE HAS STOPPED RESPONDING TO POLLS%';

+----------------+----------------------------------------+----------+

| alarm_title_id | title                                  | cause_id |

+----------------+----------------------------------------+----------+

|             74 | DEVICE HAS STOPPED RESPONDING TO POLLS |    65545 |

+----------------+----------------------------------------+----------+

1 row in set (0.00 sec)

mysql> select count(*) from alarminfo where alarm_title_id = 74 and clear_time is NULL;

+----------+

| count(*) |

+----------+

|     62 |

+----------+

1 row in set (1.44 sec)

Hi Rick,

John has provided the required steps to achieve your query.  the question was 

"I'd like to query the alarminfo to get active "DEVICE HAS STOPPED RESPONDING TO POLLS" alarms."

the alarminfo table does not contain the alarm title text.  the following query shows the tables that contain column names similar to title and these are the tables we can query for your alarm using the title.

mysql> SELECT TABLE_NAME, COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE column_name LIKE 'title';
+-------------------------+-------------+
| TABLE_NAME | COLUMN_NAME |
+-------------------------+-------------+
| alarmtitle | title |

the alarmtitle table is described as  follows;

mysql> describe alarmtitle;
+----------------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------------+------------------+------+-----+---------+----------------+
| alarm_title_id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| title | varchar(255) | YES | | NULL | |
| cause_id | int(10) unsigned | NO | MUL | NULL | |
+----------------+------------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

we can verify what other tables contain the alarm_title_id field with:

mysql> SELECT TABLE_NAME, COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE column_name LIKE 'alarm_title_id';
+-----------------------------------+----------------+
| TABLE_NAME | COLUMN_NAME |
+-----------------------------------+----------------+
| alarminfo_mrg | alarm_title_id |
| alarminfo_staging | alarm_title_id |
| alarminfo | alarm_title_id |
| alarmreconcile_history | alarm_title_id |
| alarmtitle | alarm_title_id |
| v_alarm_activity | alarm_title_id |
| v_bi_topnalarmtypesmain | alarm_title_id |
| v_bi_topnassetswithmostalarmsmain | alarm_title_id |
| v_dim_alarm_title | alarm_title_id |
| v_fact_alarm_info | alarm_title_id |
+-----------------------------------+----------------+
10 rows in set (0.10 sec)

or verify by looking at the description of the alarminfo table.

top - 14:21:49 up 197 days, 4:26, 1 user, load average: 0.81, 0.43, 0.25

mysql> describe alarminfo;
+----------------------------+---------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------------------------+---------------------+------+-----+---------+----------------+
| alarm_key | int(11) unsigned | NO | PRI | NULL | auto_increment |
| alarm_id | char(36) | NO | UNI | NULL | |

I hope this answers your questions

Shane