Implement a store procedure for authentication summarization

[outsmain]

Import new data of NE_AUTHACCT, NE_AUTH_SUM and NE_LIST. Create a MySQL's store procedure name "PROC_AUTHACCT_SUM", this procedure query data from table "NE_AUTHACCT" then insert to "NE_AUTHSUM".

Parameters

• start_date
• end_date Define range of data in"login_date"
• sum_dur Define grouping date interval, available options are
  • HOURLY HOUR(login_date)
  • DATE_HOURLY DATE(login_date), HOUR(login_date)
  • DAILY DATE(login_date)
  • WEEKLY WEEKOFYEAR(login_date)
  • YEARLY YEAR(login_date)
• sum_type Define additional grouping field, available options are
  • NODE_NAME NE_AUTHACCT.node_name
  • NODE_GROUP join with NE_LIST.level
  • USER_NAME NE_AUTHACCT.user_name
  • SITE_NAME join with NE_LIST.site_name
  • USER_IP NE_AUTHACCT.user_ip
  • USER_GROUP NE_AUTHACCT.group_name

    Count and calculate

After grouping data then count and calculate those fields are

• accept_num Count if NE_AUTHACCT.status = "AUTH-ACCEPT"
• reject_num Count if NE_AUTHACCT.status = "AUTH-REJECT"
• success_rate accept_num / (accept_num + reject_num)
• login_rate (accept_num + reject_num) / DURATION
• cmd_num Count if NE_AUTHACCT.status = "ACCT-START" or "ACCT-STOP"
• cmd_rate cmd_num / DURATION

[tanikul]

1. พี่ครับช่วยอธิบายเกี่ยวกับ parameter sum_dur, sum_type อีกทีครับ
2. ที่ table NE_AUTHSUM มี field update_date, last_login เอาข้อมูลจากไหนมาลงครับ ตอนนี้เป็นประมาณครับ ไม่แน่ใจว่าใช่อย่างที่ต้องการหรือเปล่า

DELIMITER $$ CREATE PROCEDURE PROC_AUTHACCT_SUM(IN start_date DATETIME, IN end_date DATETIME, IN sum_dur ENUM('HOURLY','DATE_HOURLY','DAILY','WEEKLY','YEARLY'), IN sum_type ENUM('NODE_NAME','NODE_GROUP','USER_NAME','SITE_NAME','USER_IP','USER_GROUP') ) BEGIN INSERT INTO NE_AUTHSUM (

update_date,

last_login,

sum_dur, sum_type,

node_ip,

node_name,

site_name,

node_group,

user_name,

user_ip,

user_group,

accept_num, reject_num, success_rate, login_rate, cmd_num, cmd_rate )SELECT sum_dur AS sum_dur, sum_type AS sum_type, c.accept_num, c.reject_num, (accept_num / (accept_num + reject_num)) AS success_rate, (c.accept_num + c.reject_num) / 60 AS login_rate , c.cmd_num, (c.cmd_num / 60) AS cmd_rate FROM( SELECT SUM(CASE WHEN a.status = "AUTH-ACCEPT" THEN 1 ELSE 0 END) AS accept_num , SUM(CASE WHEN a.status = "AUTH-REJECT" THEN 1 ELSE 0 END) AS reject_num , SUM(CASE WHEN a.status IN ('ACCT-START','ACCT-STOP') THEN 1 ELSE 0 END) AS cmd_num FROM NE_AUTHACCT a WHERE UNIX_TIMESTAMP(a.login_date) >= UNIX_TIMESTAMP(start_date) AND UNIX_TIMESTAMP(a.login_date) <= UNIX_TIMESTAMP(end_date) ) c; END$$ DELIMITER ;

CALL PROC_AUTHACCT_SUM('2013-11-21 05:00:01','2013-11-21 05:00:12','HOURLY','NODE_NAME');

[tanikul]

แล้วก็ DURATION นี่มา sec ยังไงนะครับ

ขอบคุณครับ

[outsmain]

procedure นี้จะใช้สำหรับนับจำนวณ record ของ NE_AUTHACCT และคำนวณจำนวณของ record/วินาที จากช่วงเวลาที่ระบุ

• sum_dur จะเป็นค่าที่ใช้กำหรับกำหนดว่าเวลา select จะ group by ด้วย function/column อะไรบ้าง
• sum_type บอกว่าจะใช้ column ที่จะใช้ group by เพิ่มบ้างนอกเหนือจากที่กำหนดโดย sum_dur
• update_date เป็นเวลาปัจจุบันที่ run procedure
• last_login เป็นค่า MAX ชอง login_date ในช่วงเวลาที่ select

ซึ่ง sum_dur และ sum_type จะมีผลต่อค่า *_rate ที่คำนวณได้

Note:

• ไม่ต้องใช้ UNIX_TIMESTAMP ใน where เพราะ datetime ใช้การเปรียบเทียบกันตรงๆ ได้เลย
• ตอนคำนวณ rate ค่าที่นำมาหาร (ค่า DURATION) จะไม่ใช้ 60 อย่างเดียว แต่ขึ้นอยู่กับค่า start/end date ว่าช่วงเวลาที่เลือกจะเป็นระยะเวลากว้างแค่ไหน

[tanikul]

รบกวนตรวจเช็คด้วยครับ

ขอบคุณครับ

DELIMITER $$ CREATE PROCEDURE PROC_AUTHACCT_SUM(IN start_date DATETIME, IN end_date DATETIME, IN sum_dur ENUM('HOURLY','DATE_HOURLY','DAILY','WEEKLY','YEARLY'), IN sum_type ENUM('NODE_NAME','NODE_GROUP','USER_NAME','SITE_NAME','USER_IP','USER_GROUP') ) BEGIN INSERT INTO NE_AUTHSUM ( update_date, last_login, sum_dur, sum_type, node_ip, node_name, site_name, node_group, user_name, user_ip, user_group, accept_num, reject_num, success_rate, login_rate, cmd_num, cmd_rate )SELECT NOW(), c.lasttime, sum_dur AS sum_dur, sum_type AS sum_type, c.node_ip, c.node_name, c.site_name, c.level, c.user_name, c.user_ip, c.group_name, c.accept_num, c.reject_num, IF((c.accept_num + c.reject_num) > 0,(c.accept_num / (c.accept_num + c.reject_num)), 0) AS success_rate, (c.accept_num + c.reject_num) / (UNIX_TIMESTAMP(end_date)-UNIX_TIMESTAMP(start_date)) AS login_rate , c.cmd_num, (c.cmd_num / (UNIX_TIMESTAMP(end_date)-UNIX_TIMESTAMP(start_date))) AS cmd_rate FROM( SELECT a.node_ip, a.node_name, b.site_name, b.level, a.user_name, a.user_ip, a.group_name, MAX(a.login_date) AS lasttime , SUM(CASE WHEN a.status = "AUTH-ACCEPT" THEN 1 ELSE 0 END) AS accept_num , SUM(CASE WHEN a.status = "AUTH-REJECT" THEN 1 ELSE 0 END) AS reject_num , SUM(CASE WHEN a.status IN ('ACCT-START','ACCT-STOP') THEN 1 ELSE 0 END) AS cmd_num FROM NE_AUTHACCT a JOIN NE_LIST b ON a.login_date >= start_date AND a.login_date <= end_date AND a.node_ip = b.ip_addr GROUP BY CASE sum_dur WHEN 'HOURLY' THEN HOUR(a.login_date) WHEN 'DATE_HOURLY' THEN DATE(a.login_date) WHEN 'DAILY' THEN DATE(a.login_date) WHEN 'WEEKLY' THEN WEEKOFYEAR(a.login_date) WHEN 'YEARLY' THEN YEAR(a.login_date) END, CASE sum_dur WHEN 'DATE_HOURLY' THEN HOUR(a.login_date) END, CASE sum_type WHEN 'NODE_NAME' THEN a.node_name WHEN 'NODE_GROUP' THEN b.level WHEN 'USER_NAME' THEN a.user_name WHEN 'SITE_NAME' THEN b.site_name WHEN 'USER_IP' THEN a.user_ip WHEN 'USER_GROUP' THEN a.group_name END ) c; END$$ DELIMITER ;

CALL PROC_AUTHACCT_SUM('2013-11-21 05:00:01','2013-11-21 05:00:10','HOURLY','NODE_NAME');

[outsmain]

ลองตรวจสอบ function ที่จะใช้สำหรับแต่ละ sum_dur อีกครั้ง ว่าตรงกับที่ระบุใน spec หรือไม่ครับ

[tanikul]

ตรงแล้วครับ จะเช็ค sum_dur 2 ครั้ง เนื่องจาก DATE_HOURLY DATE(login_date), HOUR(login_date) ครับ

CASE sum_dur WHEN 'HOURLY' THEN HOUR(a.login_date) WHEN 'DATE_HOURLY' THEN DATE(a.login_date) WHEN 'DAILY' THEN DATE(a.login_date) WHEN 'WEEKLY' THEN WEEKOFYEAR(a.login_date) WHEN 'YEARLY' THEN YEAR(a.login_date) END, CASE sum_dur WHEN 'DATE_HOURLY' THEN HOUR(a.login_date) END,

[outsmain]

เพิ่มเติมอีกดังนี้ครับ

ค่า *_rate ให้คูณ 100 เพื่อแปลงค่าเป็น % ด้วยครับ

ในส่วนของ sum_type เวลาระบุ sum_type เป็นค่าอะไร column ที่เหลือซึ่งไม่เกี่ยวของ sum_type นั้น column ที่ถูก insert จะต้องให้ค่าเป็น NULL

Field ที่เกี่ยวข้องกับ sum_type

• NODE_NAME node_name, node_ip
• NODE_GROUP node_group
• SITE_NAME site_name
• USER_NAME user_name
• USER_IP user_ip
• USER_GROUP user_group

ตัวอย่าง เช่น ให้ sum_type เป็น NODE_NAME ค่า site_name, node_group, user_name, user_ip และ user_group จะต้องเป็นค่า NULL

และในหน้า report เมื่อแสดงค่าจาก column เหล่านี้ คือ node_name, node_ip, node_group, site_name, user_name, user_ip และ user_group เมื่อพบค่า NULL ให้แสดงคำว่า All แทน

เมื่อปรับตามนี้แล้วให้บันทึก store procedure (ไม่รวม sql ที่ใช้ test) นี้ลงในชื่อเดียวกับชื่อ store procedure นามสกุล ".sql" ลงในไดเรกทอรี sql เช่น sql/PROC_AUTHACCT_SUM.sql

ส่วน sql ที่ใช้ test ให้บันทึกเป็นชื่อแบบเดียวกับ store procedure แต่เก็บในไดเรกทอรี test

[tanikul]

หน้า report นี้หมายถึงตรงไหนครับ หมายถึง หน้า http://localhost/authrep/?serv=online หรือเปล่าครับ

ขอบคุณครับ

[outsmain]

ใช่ครับ คือ report ที่ปั่นทำก่อนหน้านี้ครับ

[tanikul]

column เหล่านี้ node_name, node_ip, node_group, site_name, user_name, user_ip และ user_group

มันจะอยู่ในตาราง NE_AUTHSUM ซึ่งที่หน้า report ไม่ได้ดึงข้อมูลจากตารางนี้มาใช้เลย

เพราะฉะนั้น ผมเลยไปเปลี่ยนการแสดงผล ถ้ามีค่าเป็น NULL จากแสดงเป็น - เปลี่ยนเป็น All ดังนี้

node_name = node_name ของตาราง NE_AUTHACCT node_ip = node_ip ของตาราง NE_AUTHACCT user_name = user_name ของตาราง NE_AUTHACCT user_ip = user_ip ของตาราง NE_AUTHACCT

ส่วนของ popup site_name = site_name ของตาราง NE_LIST node_group = level ของตาราง NE_LIST

จะเหลือ user_group เพราะไม่ได้เอาออกมาโชว์ทั้งหน้า report และ popup

ขอบคุณครับ