CCTV_ver.3. DB에서 이상값 탐지 후 푸시알람(socket.io, trigger)

과제 목표

DB 이상값 탐지
Socket.io 연결
사용자에게 푸시 알림

➡ 위 이미지를 클릭하면 Repository로 이동합니다.

DB Outlier Detection

by. 서현

1. MySQL Trigger 생성

지금까지의 데이터 평균값 + 1.5 * 표준편차 이상이면 탐지하여 backup table에 msg와 함께 저장되도록 함

DELIMITER //
CREATE TRIGGER detection 
	AFTER INSERT 
    ON test 
	FOR EACH ROW
BEGIN
    IF  NEW.people > (SELECT AVG(people)+1.5*STDDEV(people) FROM test) THEN
    
		IF  NEW.vehicle > (SELECT AVG(vehicle)+1.5*STDDEV(vehicle) FROM test) THEN
			INSERT INTO backup VALUES(NEW.id, NEW.start_date ,NEW.end_date, NEW.people, NEW.vehicle, 'Outlier People & Vehicle');
		ELSE 
			INSERT INTO backup VALUES(NEW.id, NEW.start_date ,NEW.end_date, NEW.people, NEW.vehicle, 'Outlier People');
		END IF;
        
	ELSEIF NEW.vehicle > (SELECT AVG(vehicle)+1.5*STDDEV(vehicle) FROM test) THEN
		INSERT INTO backup VALUES(NEW.id, NEW.start_date ,NEW.end_date, NEW.people, NEW.vehicle, 'Outlier Vehicle');
    END IF;
END //
DELIMITER ;

2. 백업 데이터 결과

Socket.io 연결

by. 태훈

1. socket 연결

backup table에서 5분마다 id값의 변화를 관찰하여 변화가 있는 경우 emit

// <app.js>  
const socketio = require('socket.io');
const io = socketio(server);

io.on("connection", (socket) => {
    console.log(`connect: ${socket.id}`);

    var lastRowID = -1;
    var job = schedule.scheduleJob(
        '*/5 * * * * *', // 주기 (5분마다)
    function() {
        console.log(1);
        var sql = 'select * from backup order by id desc limit 1';
        conn.query(sql, (err, row) => {
            if (err) {
                console.log(err);
            } else {
                if (lastRowID != row[0].id) {
                    lastRowID = row[0].id;
                    console.log(row[0]);
                    socket.emit("outlierData", row[0]);
                } else {
                    console.log('not update database');
                }
            }
        });
    });
});

2. client에서 수신한 데이터 처리

<script>
    var port = JSON.parse('<%- JSON.stringify(port) %>');
    console.log(port);
    var socket = io.connect("http://localhost:" + port);
    socket.on('outlierData', function(data) {
        console.log(data);
        notify(data.id, data.msg);
    })
</script>

사용자에게 푸시 알림

by. 가희

1. 이상값 데이터를 Notify

detection이 이뤄진 데이터의 id값과 msg를 띄워 보여줌

<script>
    Notification.requestPermission();
    function notify(id, msg) {
        if (Notification.permission !== "denied") {
            new Notification("id="+id + ": " + msg);
        }
        else if (Notification.permission === "denied") {
            Notification.requestPermission();
        }
    }
</script>

2. 결과