How to generate a report that includes columns from alarminfo table

Products

• Network Observability
• Spectrum

Issue/Introduction

The management team needs a report containing alarm and ticket data from the integration between Spectrum and Service NOW.

• set_time - Originated time of the alarm date time
• clear_time - When the alarm is cleared from DX NetOps Spectrum
• set_troubleticket_time - Time of the trouble ticket assigned
• set_troubleticket_id - Trouble ticket ID of the alarm

Environment

Any Spectrum version

Resolution

Here are the detailed steps to run the MySQL query on the SRM (Spectrum Report Manager) machine and get the output in a .CSV file format:

1. Open a bash shell (bash -login) logged as Spectrum Install Owner account

2. Navigate to the $SPECROOT/mysql/bin/ directory

cd mysql/bin

3. Run the following MySQL query to generate a .CSV file format.

The following syntax will export all data:

./mysql.exe -uroot -p<PassWord> -e 'select ai.set_time,ai.clear_time,ai.set_troubleticket_time,ai.ack_time,au.alarm_user_name as ack_user_name,ai.clear_user,ai.set_troubleticket_id,m.model_name,HEX(ai.model_h),at.title from reporting.alarminfo ai, reporting.model m, reporting.alarmtitle at, reporting.alarm_user au where ai.model_key=m.model_key and ai.alarm_title_id=at.alarm_title_id and au.alarm_user_key=ai.ack_user_key order by ai.set_time;' | sed 's/\t/,/g' > alarminfo_ticket.csv

The following syntax will export the data from yesterday only:

./mysql.exe -uroot -p<PassWord> -e 'select ai.set_time,ai.clear_time,ai.set_troubleticket_time,ai.ack_time,au.alarm_user_name as ack_user_name,ai.clear_user,ai.set_troubleticket_id,m.model_name,HEX(ai.model_h),at.title from reporting.alarminfo ai, reporting.model m, reporting.alarmtitle at, reporting.alarm_user au where ai.model_key=m.model_key and ai.alarm_title_id=at.alarm_title_id and au.alarm_user_key=ai.ack_user_key and ai.set_time >= (CURDATE() - INTERVAL 1 DAY) and ai.set_time < CURDATE() order by ai.set_time;' | sed 's/\t/,/g' > alarminfo_ticket.csv

Where:

MySQL username: root

MySQL password: <password>

MySQL query: select ai.set_time,ai.clear_time,ai.set_troubleticket_time,ai.ack_time,au.alarm_user_name as ack_user_name,ai.clear_user,ai.set_troubleticket_id,m.model_name,HEX(ai.model_h),at.title from reporting.alarminfo ai, reporting.model m, reporting.alarmtitle at, reporting.alarm_user au where ai.model_key=m.model_key and ai.alarm_title_id=at.alarm_title_id and au.alarm_user_key=ai.ack_user_key and ai.set_time >= (CURDATE() - INTERVAL 1 DAY) and ai.set_time < CURDATE() order by ai.set_time;

MySQL date/time filters:ai.set_time >= (CURDATE() - INTERVAL 1 DAY) and ai.set_time < CURDATE()

Output file in .CSV format: alarminfo_ticket.csv

4. Open the $SPECROOT/msyql/binalarminfo_ticket.csv file in MS Excel.