日志數據分析之主帳號登錄、登出時間過於集中

日志數據分析之主帳號登錄、登出時間過於集中

1、一個分析場景，分析某公司主賬號登錄的集中情況，數據源為一個月登錄原始日志：

1. ID MASTERLOGINID USERNAME PACKAGETIME CLIENTIP SERVERIP APPLICATIONID OPERATENAME OPERATETYPE RETURNCODE LOG_TYPE ORGNAME CONTENT
   
   
2. 
   

2、各個字段的意思：
日志id，主賬號，用戶姓名，操作時間，客戶端ip，服務器ip，應用系統id，操作名稱，操作類型，返回碼，日志類型，組織結構，操作內容
表結構：

1. SQL> desc int_main_acct_login_100;
   
2. Name Type Nullable Default Comments
   
3. ------------- -------------- -------- ------- --------
   
4. ID VARCHAR2(64)
   
5. MASTERLOGINID VARCHAR2(50) Y
   
6. USERNAME VARCHAR2(64) Y
   
7. PACKAGETIME VARCHAR2(64) Y
   
8. CLIENTIP VARCHAR2(32) Y
   
9. SERVERIP VARCHAR2(256) Y
   
10. APPLICATIONID NUMBER Y
    
11. OPERATENAME VARCHAR2(1024) Y
    
12. OPERATETYPE VARCHAR2(1024) Y
    
13. RETURNCODE VARCHAR2(1024) Y
    
14. LOG_TYPE VARCHAR2(32) Y
    
15. ORGNAME VARCHAR2(1024) Y
    
16. CONTENT VARCHAR2(4000) Y
    
17. 
    
18. SQL>

3、執行分析，查看每分鐘、每天的登錄數量：

1. select to_char(to_date(t.packagetime,'yyyy-mm-dd hh24:mi:ss'), 'yyyy-mm-dd hh24'),count(t.operatetype) from int_main_acct_login_100 t group by to_char(to_date(t.packagetime,'yyyy-mm-dd hh24:mi:ss'), 'yyyy-mm-dd hh24')
   
2. order by to_char(to_date(t.packagetime,'yyyy-mm-dd hh24:mi:ss'), 'yyyy-mm-dd hh24');
   
3. TO_CHAR(TO_DATE(T.PACKAGETIME,COUNT(T.OPERATETYPE)
   
4. 12016-05-01 0062
   
5. 22016-05-01 0141
   
6. 32016-05-01 0234
   
7. 42016-05-01 0336
   
8. 52016-05-01 0440
   
9. 62016-05-01 0537
   
10. 72016-05-01 0670
    
11. 82016-05-01 07469
    
12. 92016-05-01 081001

4、存在的問題和解決方案：
由於部分時間（某些小時、某些分鐘、某些天）沒人登錄，最終產生的數據的時間不連續，圖表的效果不好，根據和廠商一個開發人員溝通，創建一個常量的表，通過這個表進行兩個表關聯左連接查詢就可以了：
創建該表的話，通過存儲過程來處理：
首先創建表結構：

1. create table yk_tb_time(mtime timestamp,pid number);

#oracle時間運算：
當前系統日期加1天：
select to_date(sysdate,'YYYY-MM-DD') +1 from dual;
當前系統日期加1分鐘：
select sysdate + 1/1440 from dual;
其中1440＝1/(24*60)是一天之一分鐘

對varchar類型的“時間”轉成時間時（oracle中varchar類型的日期格式轉換date類型 ）必須寫全：to_date(t.packagetime,'yyyy-mm-dd hh24:mi:ss'），要添加時分秒，不能寫成to_date(t.packagetime,'yyyy-mm-dd hh24'），然後再進行to_char處理：to_char(to_date(t.packagetime,'yyyy-mm-dd hh24:mi:ss'), 'yyyy-mm-dd hh24')
創建存儲過程：

1. create or replace procedure pro_yk_tb_time is
   
2. i number :=0;
   
3. begin
   
4. while i <744 loop
   
5. --select to_date('2016-5-1 0:0:1','yyyy-mm-dd hh24:mi:ss')+i/24 into ttime from dual;
   
6. insert into yk_tb_time values(to_date('2016-5-1 0:0:1','yyyy-mm-dd hh24:mi:ss')+i/24,yk_seq.nextval);
   
7. i:=i+1;
   
8. end loop;
   
9. --24 * 30 = 720,31day；
   
10. --dbms_output.put_line(to_char(sysdate,'yyyy-mm-dd HH24:mi:ss'));
    
11. commit;
    
12. end pro_yk_tb_time

檢查：
select to_char(s.mtime,'yyyy-mm-dd hh24') from yk_tb_time s；
TO_CHAR(S.MTIME,'YYYY-MM-DDHH2
1 2016-05-01 00
2 2016-05-01 01
3 2016-05-01 02
4 2016-05-01 03
5 2016-05-01 04
6 2016-05-01 05
7 2016-05-01 06
8 2016-05-01 07
9 2016-05-01 08

最終版的sql腳本：
我的全量、常量信息表yk_tb_time要放到前面，因為我用的是左連接，如果放後面的話，下面提到的13號那行就出不來了：

1. select to_char(s.mtime,'yyyy-mm-dd'),count(t.packagetime) from yk_tb_time s left join int_main_acct_login_100 t
   
2. on to_char(to_date(t.packagetime,'yyyy-mm-dd hh24:mi:ss'), 'yyyy-mm-dd')=to_char(s.mtime,'yyyy-mm-dd') group by
   
3. to_char(s.mtime,'yyyy-mm-dd') order by to_char(s.mtime,'yyyy-mm-dd');
   
4. TO_CHAR(S.MTIME,'YYYY-MM-DD')COUNT(T.PACKAGETIME)
   
5. 12016-05-01197640
   
6. 22016-05-02166488
   
7. 32016-05-03428592
   
8. 42016-05-04306288
   
9. 52016-05-05494160
   
10. 62016-05-06481416
    
11. 72016-05-07216816

生成圖表來看5月份登錄數量（13號那天沒人登錄數據，可能其他天某些分鐘也沒有；如果不這麼弄，那麼可以12、14號就是連續的，很難看出來中間有一天沒人登錄，這可能是有問題的）：

之前寫的以分鐘為單位的圖表：

根據圖表，就可以看出登錄頻次多的時間了，設置一個阈值，就可以進一步生成分析結構了。