[CA Gateway] Audit 로그 조회

# audit list 조회 ========================
select
hex(a.goid) goid,
g.name node,
date_format(from_unixtime(a.time / 1000), '%Y-%m-%d %H:%i:%s.%f') time,
a.audit_level serverity,
a.name service,
a.message,
a.ip_address client_ip,
uncompress(b.request_zipxml) request, -- 옵션 사용중인 경우만
uncompress(b.response_zipxml) response -- 옵션 사용중인 경우만
from
  audit_main a
  left outer join audit_message b on a.goid = b.goid
  join cluster_info g on a.nodeid = g.nodeid
where
b.service_goid is not null -- 시스템,admin 로그 제외
and a.time > unix_timestamp('2022-02-17 11:38:57.274')*1000 -- 시작일시
and a.time < unix_timestamp('2022-02-17 11:48:57.274')*1000 -- 종료일시
-- and a.audit_level = 'WARNING'
-- and a.name like '%/openbanking/%' --api명
order by a.time desc;

 

# audit detail 조회 ========================
select
date_format(from_unixtime(a.time/1000),'%Y-%m-%d %H:%i:%s.%f') time,
a.message_id code,
case when b.position=1 then 'WARNING' else 'INFO' end Serverity,
b.value
from
  audit_detail a
  join audit_detail_params b on a.goid=b.audit_detail_goid
where hex(a.audit_goid)='85562EE4D72AA5EE2516C0D43F86EEFE' -- audit_main goid
order by a.time desc;