본문 바로가기
카테고리 없음

[D] 각 시스템에 등록된 SQL로 알람 요청

오아름 샘 2025. 4. 24. 02:37
반응형
Oracle ERP에서 각 시스템에서 SQL를 만들어서 문제가 발생 할 수 있는지 녹색, 노랑색, 빨강색으로 
나누어 메일로 알림을 보내주는 시스템을 만들었는데 React, Typescript, Spring, ibatis, Mariadb 
환경에서 구축 할 수 있도록 코드 생성해주세요.

아래는 Oracle ERP 시스템에서 SQL 실행 상태를 모니터링하고 문제 발생 시 경고 이메일을 보내는 시스템을 구현하는 전체 아키텍처와 주요 코드입니다.

시스템 아키텍처

[Frontend: React+TypeScript] ← REST API → [Backend: Spring Boot] ↔ [MyBatis] ↔ [MariaDB]
                                      ↓
                                  [Oracle ERP DB] (주기적 모니터링)
                                      ↓
                                  [Email Server]

 

1. 데이터베이스 설계 (MariaDB)

-- 모니터링 대상 시스템 테이블
CREATE TABLE monitored_systems (
  system_id INT PRIMARY KEY AUTO_INCREMENT,
  system_name VARCHAR(100) NOT NULL,
  oracle_connection VARCHAR(200) NOT NULL,
  is_active BOOLEAN DEFAULT TRUE
);

-- 모니터링 SQL 쿼리 테이블
CREATE TABLE monitoring_queries (
  query_id INT PRIMARY KEY AUTO_INCREMENT,
  system_id INT NOT NULL,
  query_name VARCHAR(100) NOT NULL,
  sql_query TEXT NOT NULL,
  warning_threshold INT,
  critical_threshold INT,
  check_interval_minutes INT DEFAULT 60,
  last_checked DATETIME,
  FOREIGN KEY (system_id) REFERENCES monitored_systems(system_id)
);

-- 모니터링 결과 기록 테이블
CREATE TABLE monitoring_results (
  result_id INT PRIMARY KEY AUTO_INCREMENT,
  query_id INT NOT NULL,
  execution_time DATETIME NOT NULL,
  result_value DECIMAL(20,4) NOT NULL,
  status ENUM('GREEN', 'YELLOW', 'RED') NOT NULL,
  details TEXT,
  FOREIGN KEY (query_id) REFERENCES monitoring_queries(query_id)
);

-- 이메일 알림 설정 테이블
CREATE TABLE notification_settings (
  setting_id INT PRIMARY KEY AUTO_INCREMENT,
  query_id INT NOT NULL,
  email_recipients TEXT NOT NULL,
  notify_on_green BOOLEAN DEFAULT FALSE,
  notify_on_yellow BOOLEAN DEFAULT TRUE,
  notify_on_red BOOLEAN DEFAULT TRUE,
  FOREIGN KEY (query_id) REFERENCES monitoring_queries(query_id)
);

 

2. 백엔드 구현 (Spring Boot)

application.properties

# MariaDB 설정
spring.datasource.url=jdbc:mariadb://localhost:3306/erp_monitoring
spring.datasource.username=your_username
spring.datasource.password=your_password
spring.datasource.driver-class-name=org.mariadb.jdbc.Driver

# Oracle ERP 연결 (예시)
oracle.erp.datasource.url=jdbc:oracle:thin:@//erp-server:1521/ORCL
oracle.erp.datasource.username=erp_user
oracle.erp.datasource.password=erp_password

# 이메일 설정
spring.mail.host=smtp.yourmail.com
spring.mail.port=587
spring.mail.username=your_email@yourmail.com
spring.mail.password=your_email_password
spring.mail.properties.mail.smtp.auth=true
spring.mail.properties.mail.smtp.starttls.enable=true

 

MonitoringService.java

@Service
@RequiredArgsConstructor
public class MonitoringService {
    private final MonitoringQueryMapper queryMapper;
    private final MonitoringResultMapper resultMapper;
    private final NotificationService notificationService;
    
    @Scheduled(fixedDelayString = "${monitoring.interval:3600000}")
    public void monitorAllSystems() {
        List<MonitoringQuery> queries = queryMapper.findAllActiveQueries();
        queries.forEach(this::executeAndEvaluateQuery);
    }
    
    private void executeAndEvaluateQuery(MonitoringQuery query) {
        try {
            // Oracle ERP에서 쿼리 실행
            double resultValue = queryMapper.executeOracleQuery(
                query.getSystem().getOracleConnection(), 
                query.getSqlQuery());
            
            // 상태 평가
            Status status = evaluateStatus(resultValue, query);
            
            // 결과 저장
            MonitoringResult result = new MonitoringResult();
            result.setQueryId(query.getQueryId());
            result.setExecutionTime(LocalDateTime.now());
            result.setResultValue(resultValue);
            result.setStatus(status);
            result.setDetails("Query executed successfully");
            resultMapper.insertResult(result);
            
            // 알림 전송
            notificationService.sendNotificationIfNeeded(query, result);
            
        } catch (Exception e) {
            // 오류 처리
            MonitoringResult result = new MonitoringResult();
            result.setQueryId(query.getQueryId());
            result.setExecutionTime(LocalDateTime.now());
            result.setResultValue(-1);
            result.setStatus(Status.RED);
            result.setDetails("Error: " + e.getMessage());
            resultMapper.insertResult(result);
            
            notificationService.sendErrorNotification(query, e);
        }
    }
    
    private Status evaluateStatus(double value, MonitoringQuery query) {
        if (query.getCriticalThreshold() != null && value >= query.getCriticalThreshold()) {
            return Status.RED;
        } else if (query.getWarningThreshold() != null && value >= query.getWarningThreshold()) {
            return Status.YELLOW;
        } else {
            return Status.GREEN;
        }
    }
}

NotificationService.java

@Service
@RequiredArgsConstructor
public class NotificationService {
    private final JavaMailSender mailSender;
    private final NotificationSettingMapper settingMapper;
    
    public void sendNotificationIfNeeded(MonitoringQuery query, MonitoringResult result) {
        NotificationSetting setting = settingMapper.findByQueryId(query.getQueryId());
        if (setting == null) return;
        
        boolean shouldSend = (result.getStatus() == Status.GREEN && setting.isNotifyOnGreen()) ||
                          (result.getStatus() == Status.YELLOW && setting.isNotifyOnYellow()) ||
                          (result.getStatus() == Status.RED && setting.isNotifyOnRed());
        
        if (shouldSend) {
            sendEmail(setting.getEmailRecipients(), 
                     buildSubject(query, result), 
                     buildContent(query, result));
        }
    }
    
    public void sendErrorNotification(MonitoringQuery query, Exception e) {
        NotificationSetting setting = settingMapper.findByQueryId(query.getQueryId());
        if (setting == null) return;
        
        String subject = "[URGENT] Monitoring Error - " + query.getQueryName();
        String content = "An error occurred while executing monitoring query:\n\n" +
                        "Query: " + query.getQueryName() + "\n" +
                        "System: " + query.getSystem().getSystemName() + "\n\n" +
                        "Error Details:\n" + e.getMessage();
        
        sendEmail(setting.getEmailRecipients(), subject, content);
    }
    
    private void sendEmail(String recipients, String subject, String content) {
        SimpleMailMessage message = new SimpleMailMessage();
        message.setTo(recipients.split(";"));
        message.setSubject(subject);
        message.setText(content);
        mailSender.send(message);
    }
    
    private String buildSubject(MonitoringQuery query, MonitoringResult result) {
        return "[" + result.getStatus() + "] " + query.getQueryName() + 
              " - " + query.getSystem().getSystemName();
    }
    
    private String buildContent(MonitoringQuery query, MonitoringResult result) {
        return "Monitoring Result:\n\n" +
              "System: " + query.getSystem().getSystemName() + "\n" +
              "Query: " + query.getQueryName() + "\n" +
              "Executed at: " + result.getExecutionTime() + "\n" +
              "Result Value: " + result.getResultValue() + "\n" +
              "Status: " + result.getStatus() + "\n\n" +
              "Thresholds:\n" +
              "Warning: " + query.getWarningThreshold() + "\n" +
              "Critical: " + query.getCriticalThreshold() + "\n\n" +
              "Details: " + result.getDetails();
    }
}

 

3. 프론트엔드 구현 (React + TypeScript)

types.ts

export type Status = 'GREEN' | 'YELLOW' | 'RED';

export interface MonitoringSystem {
  systemId: number;
  systemName: string;
  oracleConnection: string;
  isActive: boolean;
}

export interface MonitoringQuery {
  queryId: number;
  systemId: number;
  system?: MonitoringSystem;
  queryName: string;
  sqlQuery: string;
  warningThreshold?: number;
  criticalThreshold?: number;
  checkIntervalMinutes: number;
  lastChecked?: Date;
}

export interface MonitoringResult {
  resultId: number;
  queryId: number;
  query?: MonitoringQuery;
  executionTime: Date;
  resultValue: number;
  status: Status;
  details: string;
}

export interface NotificationSetting {
  settingId: number;
  queryId: number;
  emailRecipients: string;
  notifyOnGreen: boolean;
  notifyOnYellow: boolean;
  notifyOnRed: boolean;
}

Dashboard.tsx

import React, { useEffect, useState } from 'react';
import { MonitoringResult, Status } from './types';
import { fetchRecentResults } from './api';

const Dashboard: React.FC = () => {
  const [results, setResults] = useState<MonitoringResult[]>([]);
  const [loading, setLoading] = useState(true);

  useEffect(() => {
    const loadData = async () => {
      try {
        const data = await fetchRecentResults();
        setResults(data);
      } catch (error) {
        console.error('Failed to fetch results:', error);
      } finally {
        setLoading(false);
      }
    };
    loadData();
  }, []);

  const getStatusColor = (status: Status) => {
    switch (status) {
      case 'GREEN': return 'bg-green-500';
      case 'YELLOW': return 'bg-yellow-500';
      case 'RED': return 'bg-red-500';
      default: return 'bg-gray-500';
    }
  };

  if (loading) return <div>Loading...</div>;

  return (
    <div className="container mx-auto p-4">
      <h1 className="text-2xl font-bold mb-6">Oracle ERP Monitoring Dashboard</h1>
      
      <div className="grid grid-cols-1 md:grid-cols-2 lg:grid-cols-3 gap-4">
        {results.map(result => (
          <div key={result.resultId} className="border rounded-lg p-4 shadow">
            <div className={`${getStatusColor(result.status)} text-white p-2 rounded-md mb-2`}>
              {result.query?.queryName} - {result.status}
            </div>
            <p>System: {result.query?.system?.systemName}</p>
            <p>Value: {result.resultValue}</p>
            <p>Executed: {new Date(result.executionTime).toLocaleString()}</p>
            <p className="mt-2 text-sm">{result.details}</p>
          </div>
        ))}
      </div>
    </div>
  );
};

export default Dashboard;

 

QueryForm.tsx

import React, { useState } from 'react';
import { MonitoringQuery, MonitoringSystem } from './types';
import { saveQuery } from './api';

interface QueryFormProps {
  systems: MonitoringSystem[];
  onSuccess?: () => void;
}

const QueryForm: React.FC<QueryFormProps> = ({ systems, onSuccess }) => {
  const [query, setQuery] = useState<Partial<MonitoringQuery>>({
    systemId: systems[0]?.systemId,
    checkIntervalMinutes: 60
  });

  const handleSubmit = async (e: React.FormEvent) => {
    e.preventDefault();
    try {
      await saveQuery(query as MonitoringQuery);
      if (onSuccess) onSuccess();
    } catch (error) {
      console.error('Failed to save query:', error);
    }
  };

  return (
    <form onSubmit={handleSubmit} className="space-y-4">
      <div>
        <label className="block">System</label>
        <select 
          value={query.systemId} 
          onChange={(e) => setQuery({...query, systemId: parseInt(e.target.value)})}
          className="w-full p-2 border rounded"
          required
        >
          {systems.map(system => (
            <option key={system.systemId} value={system.systemId}>
              {system.systemName}
            </option>
          ))}
        </select>
      </div>
      
      <div>
        <label className="block">Query Name</label>
        <input
          type="text"
          value={query.queryName || ''}
          onChange={(e) => setQuery({...query, queryName: e.target.value})}
          className="w-full p-2 border rounded"
          required
        />
      </div>
      
      <div>
        <label className="block">SQL Query</label>
        <textarea
          value={query.sqlQuery || ''}
          onChange={(e) => setQuery({...query, sqlQuery: e.target.value})}
          className="w-full p-2 border rounded h-32"
          required
        />
      </div>
      
      <div className="grid grid-cols-2 gap-4">
        <div>
          <label className="block">Warning Threshold</label>
          <input
            type="number"
            value={query.warningThreshold || ''}
            onChange={(e) => setQuery({...query, warningThreshold: parseFloat(e.target.value)})}
            className="w-full p-2 border rounded"
          />
        </div>
        
        <div>
          <label className="block">Critical Threshold</label>
          <input
            type="number"
            value={query.criticalThreshold || ''}
            onChange={(e) => setQuery({...query, criticalThreshold: parseFloat(e.target.value)})}
            className="w-full p-2 border rounded"
          />
        </div>
      </div>
      
      <div>
        <label className="block">Check Interval (minutes)</label>
        <input
          type="number"
          value={query.checkIntervalMinutes || 60}
          onChange={(e) => setQuery({...query, checkIntervalMinutes: parseInt(e.target.value)})}
          className="w-full p-2 border rounded"
          required
        />
      </div>
      
      <button type="submit" className="bg-blue-500 text-white px-4 py-2 rounded">
        Save Query
      </button>
    </form>
  );
};

export default QueryForm;

 

4. MyBatis Mapper 예제

MonitoringQueryMapper.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" 
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<mapper namespace="com.example.monitoring.repository.MonitoringQueryMapper">
    <select id="findAllActiveQueries" resultType="MonitoringQuery">
        SELECT q.*, s.system_name as "system.systemName", s.oracle_connection as "system.oracleConnection"
        FROM monitoring_queries q
        JOIN monitored_systems s ON q.system_id = s.system_id
        WHERE s.is_active = TRUE
    </select>
    
    <select id="executeOracleQuery" resultType="double">
        <!-- 실제로는 Oracle JDBC를 사용하여 실행 -->
        <!-- 이 예제에서는 간단히 표현 -->
        ${oracleConnection} /* 연결 정보 활용 */
        ${sqlQuery} /* 실제 쿼리 실행 */
    </select>
    
    <insert id="insertResult" parameterType="MonitoringResult" useGeneratedKeys="true" keyProperty="resultId">
        INSERT INTO monitoring_results (query_id, execution_time, result_value, status, details)
        VALUES (#{queryId}, #{executionTime}, #{resultValue}, #{status}, #{details})
    </insert>
</mapper>

 

5. 시스템 실행 방법

  1. MariaDB 데이터베이스 설정
    • 위의 SQL 스크립트로 데이터베이스 생성
    • 테스트 데이터 삽입
  2. 백엔드 애플리케이션 실행
cd backend
mvn spring-boot:run

 

3.프론트엔드 애플리케이션 실행

cd frontend
npm install
npm start

 

6. 추가 고려 사항

  1. Oracle JDBC 연결 풀 설정
    • HikariCP 등을 사용하여 Oracle 연결 풀 구성
    • 여러 ERP 시스템 연결을 효율적으로 관리
  2. 보안 강화
    • 데이터베이스 자격 증명 암호화
    • API 인증/인가 추가 (JWT 등)
  3. 고급 기능
    • 모니터링 결과 시각화 (차트 등)
    • 알림 기록 및 추적 시스템
    • 자동 복구 작업 설정

이 시스템은 Oracle ERP에서 실행되는 SQL 쿼리들의 상태를 지속적으로 모니터링하고, 설정된 임계값을 기준으로 녹색(정상), 노란색(경고), 빨간색(위험) 상태를 판단하여 해당하는 담당자에게 이메일 알림을 전송합니다.

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

반응형

티스토리툴바