Reference

• https://grafana.com/docs/grafana/latest/datasources/mysql/
• https://docs.aws.amazon.com/ja_jp/grafana/latest/userguide/using-mysql-in-AMG.html
• https://ludwig125.hatenablog.com/entry/2020/12/05/064958

1. Understand the Schema

• Explore Tables: First, understand the schema of the Icinga MySQL database. Identify the key tables such as icinga_hosts, icinga_services, icinga_statehistory, and icinga_contacts.
• Review Relationships: Look at how tables are related, especially how host and service tables interact, which is vital for understanding dependencies and notifications.

2. Check Configuration Data

• Hosts and Services: Query the icinga_hosts and icinga_services tables to see all the monitoring hosts and services. These tables will provide you with the configurations and current states of each host and service.
• Command Configuration: Look at the command configuration to understand what checks are being performed, typically stored in icinga_commands.

3. Analyze State and History

• Current State: Check the current state of hosts and services by querying the icinga_state table, which can provide real-time status data.
• Historical Data: Analyze historical data in the icinga_statehistory table to see trends and past issues. This can be crucial for identifying recurring problems.

4. Review Notifications and Contacts

• Notifications: Investigate the icinga_notifications table to understand notification behaviors, such as who was notified, when, and why.
• Contacts: Examine the icinga_contacts table to review who receives alerts and their contact methods.

5. Query Performance Data

• Performance Metrics: Utilize tables like icinga_perfdata to extract performance metrics. This data is essential for performance tuning and ensuring that the monitored systems meet their required service levels.
• Custom Queries: Develop custom queries to correlate performance data with events and notifications for deeper insights.

6. Integrate with Grafana

• Create Dashboards: Use the data source you added in Grafana to create insightful dashboards. Focus on real-time monitoring, historical trends, and alert summaries.
• Visualize Trends: Build visualizations in Grafana to represent the health over time of various components, anomaly detections, and SLA compliance.

7. Optimize and Clean Up

• Database Maintenance: Regularly check for database optimizations, such as indexing frequent queries and cleaning up old entries that are no longer necessary.
• Security Checks: Ensure that only authorized users have access to sensitive data and perform regular audits to maintain security.