7.4 カスタマイゼーション ​

#!/usr/bin/env python3
# /opt/zabbix/scripts/db_monitor.py

import sys
import json
import logging
import argparse
from datetime import datetime
import mysql.connector
import psycopg2
import cx_Oracle

class DatabaseMonitor:
    def __init__(self, db_type, connection_params):
        self.db_type = db_type.lower()
        self.connection_params = connection_params
        self.setup_logging()
    
    def setup_logging(self):
        """ログ設定"""
        logging.basicConfig(
            filename='/var/log/zabbix_db_monitor.log',
            level=logging.INFO,
            format='%(asctime)s - %(levelname)s - %(message)s'
        )
        self.logger = logging.getLogger(__name__)
    
    def get_connection(self):
        """データベース接続の取得"""
        try:
            if self.db_type == 'mysql':
                return mysql.connector.connect(**self.connection_params)
            elif self.db_type == 'postgresql':
                return psycopg2.connect(**self.connection_params)
            elif self.db_type == 'oracle':
                dsn = f"{self.connection_params['host']}:{self.connection_params['port']}/{self.connection_params['database']}"
                return cx_Oracle.connect(
                    self.connection_params['user'],
                    self.connection_params['password'],
                    dsn
                )
            else:
                raise ValueError(f"Unsupported database type: {self.db_type}")
        
        except Exception as e:
            self.logger.error(f"Database connection failed: {e}")
            return None
    
    def execute_query(self, query):
        """クエリ実行"""
        conn = self.get_connection()
        if not conn:
            return None
        
        try:
            cursor = conn.cursor()
            cursor.execute(query)
            result = cursor.fetchall()
            cursor.close()
            conn.close()
            return result
        
        except Exception as e:
            self.logger.error(f"Query execution failed: {e}")
            return None
    
    def get_connection_count(self):
        """接続数取得"""
        queries = {
            'mysql': "SHOW STATUS LIKE 'Threads_connected'",
            'postgresql': "SELECT count(*) FROM pg_stat_activity",
            'oracle': "SELECT count(*) FROM v$session WHERE status='ACTIVE'"
        }
        
        query = queries.get(self.db_type)
        if not query:
            return 0
        
        result = self.execute_query(query)
        if result:
            if self.db_type == 'mysql':
                return int(result[0][1])
            else:
                return int(result[0][0])
        return 0
    
    def get_slow_queries(self):
        """スロークエリ数取得"""
        queries = {
            'mysql': "SHOW STATUS LIKE 'Slow_queries'",
            'postgresql': """
                SELECT count(*) FROM pg_stat_statements 
                WHERE mean_time > 1000
            """,
            'oracle': """
                SELECT count(*) FROM v$sql 
                WHERE elapsed_time/executions > 1000000
            """
        }
        
        query = queries.get(self.db_type)
        if not query:
            return 0
        
        result = self.execute_query(query)
        if result:
            if self.db_type == 'mysql':
                return int(result[0][1])
            else:
                return int(result[0][0])
        return 0
    
    def get_database_size(self, database_name=None):
        """データベースサイズ取得"""
        queries = {
            'mysql': f"""
                SELECT ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) 
                FROM information_schema.tables 
                WHERE table_schema = '{database_name or "zabbix"}'
            """,
            'postgresql': f"""
                SELECT pg_size_pretty(pg_database_size('{database_name or "zabbix"}'))
            """,
            'oracle': """
                SELECT ROUND(SUM(bytes)/1024/1024, 2) 
                FROM user_segments
            """
        }
        
        query = queries.get(self.db_type)
        if not query:
            return 0
        
        result = self.execute_query(query)
        if result and result[0][0]:
            if self.db_type == 'postgresql':
                # PostgreSQLの場合、サイズ文字列から数値を抽出
                size_str = result[0][0]
                if 'MB' in size_str:
                    return float(size_str.replace(' MB', ''))
                elif 'GB' in size_str:
                    return float(size_str.replace(' GB', '')) * 1024
            else:
                return float(result[0][0])
        return 0
    
    def get_table_stats(self):
        """テーブル統計情報取得"""
        queries = {
            'mysql': """
                SELECT table_name, table_rows, 
                       ROUND((data_length + index_length) / 1024 / 1024, 2) as size_mb
                FROM information_schema.tables 
                WHERE table_schema = DATABASE()
                ORDER BY size_mb DESC LIMIT 10
            """,
            'postgresql': """
                SELECT schemaname, tablename, n_tup_ins, n_tup_upd, n_tup_del
                FROM pg_stat_user_tables 
                ORDER BY n_tup_ins DESC LIMIT 10
            """,
            'oracle': """
                SELECT table_name, num_rows, 
                       ROUND(avg_row_len * num_rows / 1024 / 1024, 2) as size_mb
                FROM user_tables 
                WHERE num_rows > 0
                ORDER BY size_mb DESC
                FETCH FIRST 10 ROWS ONLY
            """
        }
        
        query = queries.get(self.db_type)
        if not query:
            return []
        
        result = self.execute_query(query)
        if result:
            return [list(row) for row in result]
        return []
    
    def discover_databases(self):
        """データベース一覧発見（LLDで使用）"""
        queries = {
            'mysql': "SHOW DATABASES",
            'postgresql': "SELECT datname FROM pg_database WHERE datistemplate = false",
            'oracle': "SELECT name FROM v$database"
        }
        
        query = queries.get(self.db_type)
        if not query:
            return {"data": []}
        
        result = self.execute_query(query)
        if result:
            databases = []
            for row in result:
                databases.append({
                    "{#DATABASE}": row[0],
                    "{#DBTYPE}": self.db_type.upper()
                })
            return {"data": databases}
        
        return {"data": []}

def main():
    parser = argparse.ArgumentParser(description="Database Monitor for Zabbix")
    parser.add_argument("--database", required=True, help="Database name")
    parser.add_argument("--metric", required=True, 
                       choices=['connections', 'slow_queries', 'size', 'discovery', 'table_stats'])
    parser.add_argument("--host", default="localhost", help="Database host")
    parser.add_argument("--port", type=int, help="Database port")
    parser.add_argument("--user", default="zabbix", help="Database user")
    parser.add_argument("--password", default="", help="Database password")
    parser.add_argument("--db-type", default="mysql", 
                       choices=['mysql', 'postgresql', 'oracle'], help="Database type")
    
    args = parser.parse_args()
    
    # デフォルトポート設定
    if not args.port:
        port_defaults = {'mysql': 3306, 'postgresql': 5432, 'oracle': 1521}
        args.port = port_defaults.get(args.db_type, 3306)
    
    # 接続パラメータ設定
    connection_params = {
        'host': args.host,
        'port': args.port,
        'user': args.user,
        'password': args.password,
        'database': args.database
    }
    
    monitor = DatabaseMonitor(args.db_type, connection_params)
    
    try:
        if args.metric == 'connections':
            print(monitor.get_connection_count())
        elif args.metric == 'slow_queries':
            print(monitor.get_slow_queries())
        elif args.metric == 'size':
            print(monitor.get_database_size())
        elif args.metric == 'discovery':
            print(json.dumps(monitor.discover_databases(), indent=2))
        elif args.metric == 'table_stats':
            print(json.dumps(monitor.get_table_stats(), indent=2))
    
    except Exception as e:
        logging.error(f"Monitor execution failed: {e}")
        print("0")

if __name__ == "__main__":
    main()

#!/usr/bin/env python3
# /opt/zabbix/scripts/system_info.py

import json
import psutil
import subprocess
import platform
import socket
from datetime import datetime

class SystemInfoCollector:
    def __init__(self):
        self.info = {
            'timestamp': datetime.now().isoformat(),
            'hostname': socket.gethostname(),
            'platform': platform.platform()
        }
    
    def collect_cpu_info(self):
        """CPU情報収集"""
        return {
            'physical_cores': psutil.cpu_count(logical=False),
            'logical_cores': psutil.cpu_count(logical=True),
            'cpu_percent': psutil.cpu_percent(interval=1),
            'load_average': list(psutil.getloadavg()) if hasattr(psutil, 'getloadavg') else [],
            'cpu_freq': {
                'current': psutil.cpu_freq().current if psutil.cpu_freq() else 0,
                'min': psutil.cpu_freq().min if psutil.cpu_freq() else 0,
                'max': psutil.cpu_freq().max if psutil.cpu_freq() else 0
            }
        }
    
    def collect_memory_info(self):
        """メモリ情報収集"""
        memory = psutil.virtual_memory()
        swap = psutil.swap_memory()
        
        return {
            'total': memory.total,
            'available': memory.available,
            'used': memory.used,
            'free': memory.free,
            'percent': memory.percent,
            'buffers': getattr(memory, 'buffers', 0),
            'cached': getattr(memory, 'cached', 0),
            'swap': {
                'total': swap.total,
                'used': swap.used,
                'free': swap.free,
                'percent': swap.percent
            }
        }
    
    def collect_disk_info(self):
        """ディスク情報収集"""
        disk_info = {}
        
        # ディスク使用量
        for partition in psutil.disk_partitions():
            try:
                usage = psutil.disk_usage(partition.mountpoint)
                disk_info[partition.mountpoint] = {
                    'device': partition.device,
                    'fstype': partition.fstype,
                    'total': usage.total,
                    'used': usage.used,
                    'free': usage.free,
                    'percent': (usage.used / usage.total) * 100
                }
            except PermissionError:
                continue
        
        # ディスクI/O統計
        disk_io = psutil.disk_io_counters()
        if disk_io:
            disk_info['io_stats'] = {
                'read_count': disk_io.read_count,
                'write_count': disk_io.write_count,
                'read_bytes': disk_io.read_bytes,
                'write_bytes': disk_io.write_bytes,
                'read_time': disk_io.read_time,
                'write_time': disk_io.write_time
            }
        
        return disk_info
    
    def collect_network_info(self):
        """ネットワーク情報収集"""
        network_info = {}
        
        # ネットワークインターフェース情報
        for interface, addresses in psutil.net_if_addrs().items():
            interface_info = []
            for addr in addresses:
                interface_info.append({
                    'family': str(addr.family),
                    'address': addr.address,
                    'netmask': addr.netmask,
                    'broadcast': addr.broadcast
                })
            network_info[interface] = interface_info
        
        # ネットワークI/O統計
        net_io = psutil.net_io_counters()
        if net_io:
            network_info['io_stats'] = {
                'bytes_sent': net_io.bytes_sent,
                'bytes_recv': net_io.bytes_recv,
                'packets_sent': net_io.packets_sent,
                'packets_recv': net_io.packets_recv,
                'errin': net_io.errin,
                'errout': net_io.errout,
                'dropin': net_io.dropin,
                'dropout': net_io.dropout
            }
        
        return network_info
    
    def collect_process_info(self):
        """プロセス情報収集"""
        processes = []
        
        for proc in psutil.process_iter(['pid', 'name', 'username', 'memory_percent', 'cpu_percent']):
            try:
                processes.append({
                    'pid': proc.info['pid'],
                    'name': proc.info['name'],
                    'username': proc.info['username'],
                    'memory_percent': proc.info['memory_percent'],
                    'cpu_percent': proc.info['cpu_percent']
                })
            except (psutil.NoSuchProcess, psutil.AccessDenied):
                continue
        
        # メモリ使用量でソート（上位20プロセス）
        processes.sort(key=lambda x: x['memory_percent'] or 0, reverse=True)
        
        return {
            'total_processes': len(processes),
            'top_memory_processes': processes[:20]
        }
    
    def collect_service_info(self):
        """サービス状態収集"""
        services = {}
        
        # systemctl でサービス状態を取得
        try:
            result = subprocess.run(
                ['systemctl', 'list-units', '--type=service', '--all', '--no-pager'],
                capture_output=True, text=True, timeout=30
            )
            
            if result.returncode == 0:
                lines = result.stdout.strip().split('\n')[1:]  # ヘッダーをスキップ
                for line in lines:
                    if '.service' in line:
                        parts = line.split()
                        if len(parts) >= 4:
                            service_name = parts[0].replace('.service', '')
                            services[service_name] = {
                                'loaded': parts[1],
                                'active': parts[2],
                                'sub': parts[3],
                                'description': ' '.join(parts[4:]) if len(parts) > 4 else ''
                            }
        except Exception as e:
            services['error'] = str(e)
        
        return services
    
    def collect_all_info(self):
        """全情報の収集"""
        self.info.update({
            'cpu': self.collect_cpu_info(),
            'memory': self.collect_memory_info(),
            'disk': self.collect_disk_info(),
            'network': self.collect_network_info(),
            'processes': self.collect_process_info(),
            'services': self.collect_service_info()
        })
        
        return self.info

def main():
    collector = SystemInfoCollector()
    system_info = collector.collect_all_info()
    print(json.dumps(system_info, indent=2))

if __name__ == "__main__":
    main()

#!/usr/bin/env python3
# /usr/lib/zabbix/externalscripts/auto_remediation.py

import sys
import json
import subprocess
import logging
import smtplib
from email.mime.text import MIMEText
from email.mime.multipart import MIMEMultipart
from datetime import datetime
import requests

class AutoRemediation:
    def __init__(self):
        self.setup_logging()
        self.config = self.load_config()
    
    def setup_logging(self):
        """ログ設定"""
        logging.basicConfig(
            filename='/var/log/zabbix_remediation.log',
            level=logging.INFO,
            format='%(asctime)s - %(levelname)s - %(message)s'
        )
        self.logger = logging.getLogger(__name__)
    
    def load_config(self):
        """設定読み込み"""
        try:
            with open('/etc/zabbix/remediation_config.json', 'r') as f:
                return json.load(f)
        except Exception as e:
            self.logger.error(f"Config load failed: {e}")
            return {}
    
    def parse_zabbix_params(self, args):
        """Zabbixパラメータの解析"""
        if len(args) < 6:
            raise ValueError("Insufficient parameters")
        
        return {
            'event_id': args[1],
            'event_date': args[2],
            'event_time': args[3],
            'trigger_name': args[4],
            'host_name': args[5],
            'trigger_severity': args[6] if len(args) > 6 else 'Unknown',
            'item_name': args[7] if len(args) > 7 else '',
            'item_value': args[8] if len(args) > 8 else ''
        }
    
    def disk_space_cleanup(self, host_name, threshold=90):
        """ディスク容量不足の自動対応"""
        self.logger.info(f"Starting disk cleanup for {host_name}")
        
        cleanup_commands = [
            # 一時ファイルクリーンアップ
            f"ssh {host_name} 'find /tmp -type f -atime +7 -delete'",
            # ログローテーション強制実行
            f"ssh {host_name} 'logrotate -f /etc/logrotate.conf'",
            # パッケージキャッシュクリーンアップ
            f"ssh {host_name} 'apt-get clean || yum clean all'",
            # ジャーナルログクリーンアップ
            f"ssh {host_name} 'journalctl --vacuum-time=7d'"
        ]
        
        results = []
        for cmd in cleanup_commands:
            try:
                result = subprocess.run(
                    cmd.split(), 
                    capture_output=True, 
                    text=True, 
                    timeout=300
                )
                results.append({
                    'command': cmd,
                    'success': result.returncode == 0,
                    'output': result.stdout[:500],  # 出力を制限
                    'error': result.stderr[:500] if result.stderr else None
                })
                self.logger.info(f"Command executed: {cmd} - Success: {result.returncode == 0}")
            except Exception as e:
                self.logger.error(f"Command failed: {cmd} - Error: {e}")
                results.append({
                    'command': cmd,
                    'success': False,
                    'error': str(e)
                })
        
        return results
    
    def service_restart(self, host_name, service_name):
        """サービス再起動"""
        self.logger.info(f"Restarting service {service_name} on {host_name}")
        
        # サービス状態確認
        check_cmd = f"ssh {host_name} 'systemctl is-active {service_name}'"
        
        try:
            # 現在の状態をチェック
            check_result = subprocess.run(
                check_cmd.split(),
                capture_output=True,
                text=True,
                timeout=30
            )
            
            current_status = check_result.stdout.strip()
            self.logger.info(f"Current service status: {current_status}")
            
            # サービス再起動
            restart_cmd = f"ssh {host_name} 'sudo systemctl restart {service_name}'"
            restart_result = subprocess.run(
                restart_cmd.split(),
                capture_output=True,
                text=True,
                timeout=60
            )
            
            # 再起動後の状態確認
            time.sleep(5)  # 少し待つ
            final_check = subprocess.run(
                check_cmd.split(),
                capture_output=True,
                text=True,
                timeout=30
            )
            
            final_status = final_check.stdout.strip()
            success = restart_result.returncode == 0 and final_status == 'active'
            
            self.logger.info(f"Service restart result: {success}, Final status: {final_status}")
            
            return {
                'success': success,
                'previous_status': current_status,
                'final_status': final_status,
                'restart_output': restart_result.stdout,
                'restart_error': restart_result.stderr
            }
            
        except Exception as e:
            self.logger.error(f"Service restart failed: {e}")
            return {'success': False, 'error': str(e)}
    
    def high_cpu_investigation(self, host_name):
        """高CPU使用率の調査"""
        self.logger.info(f"Investigating high CPU on {host_name}")
        
        investigation_commands = [
            # トップ10プロセス
            f"ssh {host_name} 'ps aux --sort=-%cpu | head -11'",
            # システム負荷
            f"ssh {host_name} 'uptime'",
            # メモリ使用状況
            f"ssh {host_name} 'free -h'",
            # ディスクI/O状況
            f"ssh {host_name} 'iostat -x 1 1'",
            # ネットワーク接続
            f"ssh {host_name} 'netstat -tuln | wc -l'"
        ]
        
        investigation_results = {}
        for cmd in investigation_commands:
            try:
                result = subprocess.run(
                    cmd.split(),
                    capture_output=True,
                    text=True,
                    timeout=30
                )
                
                cmd_name = cmd.split("'")[1].split()[0]  # コマンド名を抽出
                investigation_results[cmd_name] = {
                    'output': result.stdout,
                    'error': result.stderr,
                    'success': result.returncode == 0
                }
                
            except Exception as e:
                self.logger.error(f"Investigation command failed: {cmd} - {e}")
        
        return investigation_results
    
    def send_notification(self, event_data, remediation_result):
        """通知送信"""
        try:
            # Slack通知
            if 'slack_webhook' in self.config:
                self.send_slack_notification(event_data, remediation_result)
            
            # メール通知
            if 'email' in self.config:
                self.send_email_notification(event_data, remediation_result)
            
        except Exception as e:
            self.logger.error(f"Notification failed: {e}")
    
    def send_slack_notification(self, event_data, remediation_result):
        """Slack通知"""
        webhook_url = self.config['slack_webhook']
        
        color = "good" if remediation_result.get('success') else "danger"
        
        payload = {
            "attachments": [{
                "color": color,
                "title": f"Auto-Remediation: {event_data['trigger_name']}",
                "fields": [
                    {"title": "Host", "value": event_data['host_name'], "short": True},
                    {"title": "Event ID", "value": event_data['event_id'], "short": True},
                    {"title": "Status", "value": "Success" if remediation_result.get('success') else "Failed", "short": True},
                    {"title": "Time", "value": f"{event_data['event_date']} {event_data['event_time']}", "short": True}
                ],
                "text": f"Details: {json.dumps(remediation_result, indent=2)}"
            }]
        }
        
        requests.post(webhook_url, json=payload, timeout=10)
    
    def execute_remediation(self, event_data):
        """自動復旧の実行"""
        trigger_name = event_data['trigger_name'].lower()
        host_name = event_data['host_name']
        
        # トリガー名に基づく自動復旧ロジック
        if 'disk space' in trigger_name or 'filesystem' in trigger_name:
            result = self.disk_space_cleanup(host_name)
            result['remediation_type'] = 'disk_cleanup'
            
        elif 'service' in trigger_name and 'down' in trigger_name:
            # サービス名を抽出（例：httpd service is down）
            service_name = self.extract_service_name(trigger_name)
            if service_name:
                result = self.service_restart(host_name, service_name)
                result['remediation_type'] = 'service_restart'
                result['service_name'] = service_name
            else:
                result = {'success': False, 'error': 'Could not determine service name'}
                
        elif 'high cpu' in trigger_name or 'cpu utilization' in trigger_name:
            result = self.high_cpu_investigation(host_name)
            result['remediation_type'] = 'cpu_investigation'
            result['success'] = True  # 調査は常に成功
            
        else:
            result = {
                'success': False,
                'error': 'No remediation rule found for this trigger',
                'remediation_type': 'none'
            }
        
        # 通知送信
        self.send_notification(event_data, result)
        
        return result
    
    def extract_service_name(self, trigger_name):
        """トリガー名からサービス名を抽出"""
        common_services = ['httpd', 'nginx', 'mysql', 'postgresql', 'ssh', 'postfix']
        
        for service in common_services:
            if service in trigger_name.lower():
                return service
        
        return None

def main():
    if len(sys.argv) < 6:
        print("Usage: auto_remediation.py event_id date time trigger_name host_name [severity] [item_name] [item_value]")
        sys.exit(1)
    
    remediation = AutoRemediation()
    
    try:
        event_data = remediation.parse_zabbix_params(sys.argv)
        result = remediation.execute_remediation(event_data)
        
        # 結果をJSON形式で出力
        print(json.dumps({
            'event_data': event_data,
            'remediation_result': result,
            'timestamp': datetime.now().isoformat()
        }, indent=2))
        
    except Exception as e:
        error_result = {
            'success': False,
            'error': str(e),
            'timestamp': datetime.now().isoformat()
        }
        print(json.dumps(error_result, indent=2))
        logging.error(f"Auto-remediation failed: {e}")

if __name__ == "__main__":
    main()