以下代码仅作为参考之用
select md5, crc32, record->'UserModerAnalysis'->'base_info'->'file_malware' as file_malware
from reports
CREATE OR REPLACE FUNCTION py_get_file_malware(record TEXT)
RETURNS TEXT
AS $$
# pl/python functioin body
import json
plpy.notice('type of record is', type(record))
# plpy.notice('import json')
# plpy.notice('begin to loads()')
#if 'json' in SD:
# json = SD['json']
#else:
# import json
# SD['json'] = json
obj = json.loads(record)
plpy.notice('UserModerAnalysis = %s'%(str(obj['UserModerAnalysis'])))
try:
file_malware = obj['UserModerAnalysis']['base_info']['file_malware']
except Exception, e:
#plpy.error(record)
plpy.notice('ERROR!')
file_malware = ''
return file_malware
$$ LANGUAGE plpythonu
select md5, crc32, py_get_file_malware(record::TEXT)
from reports
limit 2
-- create table summary
CREATE TABLE summary_file_malware
(
description character varying(10) NOT NULL,
count integer,
CONSTRAINT summary_file_malware_pkey PRIMARY KEY (description)
)
DROP FUNCTION calculate_file_malware()
CREATE OR REPLACE FUNCTION calculate_file_malware()
RETURNS trigger AS $$
plpy.notice('calculate_file_malware invoked')
import json
event = TD['event']
if event == 'INSERT':
plpy.notice('insert triggered')
elif event == 'UPDATE':
plpy.notice('update triggered')
# parse parameter
old_obj = json.loads(TD['old']['record'])
new_obj = json.loads(TD['new']['record'])
plpy.notice('old = %s, new = %s'%(old_obj['UserModerAnalysis']['base_info']['file_malware'],
new_obj['UserModerAnalysis']['base_info']['file_malware']))
# sub old
try:
plpy.notice('begin')
plan = plpy.prepare('SELECT * FROM summary_file_malware WHERE description = $1', ['text'])
old_value = old_obj['UserModerAnalysis']['base_info']['file_malware']
plpy.notice("old_value = " + old_value)
rv = plpy.execute(plan, [old_value], 1)
old_count = int(rv[0]['count'])
plpy.notice('old_count = %s'%(old_count))
plan = plpy.prepare('UPDATE summary_file_malware SET count = $1 WHERE description = $2', ['int', 'text'])
plpy.execute(plan, [old_count - 1, old_value])
except Exception, e:
plpy.notice('exception occured, exception msg = '+str(e))
# add new
try:
plan = plpy.prepare('SELECT * FROM summary_file_malware WHERE description = $1', ['text'])
old_value = new_obj['UserModerAnalysis']['base_info']['file_malware']
rv = plpy.execute(plan, [old_value], 1)
old_count = int(rv[0]['count'])
plpy.notice('old_count = %s'%(old_count))
plan = plpy.prepare('UPDATE summary_file_malware SET count = $1 WHERE description = $2', ['int', 'text'])
plpy.execute(plan, [old_count + 1, old_value])
except Exception, e:
plpy.notice('exception occured, exception msg = '+str(e))
elif event == 'DELETE':
plpy.notice('delete triggered')
elif event == 'TRUNCATE':
plpy.notice('trancate triggered')
else:
plpy.notice('unknow event, event = ', event)
$$ LANGUAGE plpythonu
DROP TRIGGER IF EXISTS calculate on reports;
CREATE TRIGGER calculate AFTER UPDATE OF record
ON reports
FOR EACH ROW
EXECUTE PROCEDURE calculate_file_malware ();
SELECT * FROM summary_file_malware WHERE description ='OK'
INSERT INTO summary_file_malware VALUES('OK', 0)
UPDATE reports SET record = '{"Name": "000BD3A69E56CD5E8D998FEDA8EF3CA6.CCD2FFE1", "UserModerAnalysis": {"base_info": {"file_malware": "YES"}, "file_monitor": [], "virusname": null, "danger_behavior": [], "relation": {"processtree": [{"processid": "608", "process": "000BD3A69E56CD5E8D998FEDA8EF3CA6.CCD2FFE1", "module": "", "parentid": 0, "relationtype": "Root", "id": 1}]}, "other_behavior": [], "network_monitor": [], "process_monitor": [], "reg_monitor": []}, "KernelModelAnalysis": {"MaliciousActives": {"000BD3A69E56CD5E8D998FEDA8EF3CA6.CCD2FFE1": {"MemoryOperations": {}, "FileOperations": {"CREATE_FILE.DROP_PE_TO_SYSTEM_DIR": [{"COMMENT": "Create_File_In_SystemDirectory", "DETAILS": {"file_path": "c:\windows\.exe"}, "LEVEL": "LEVEL_3"}]}, "NetworkOperations": {}, "ProcessOperations": {}, "RegistryOperations": {}, "OtherOperations": {}}}, "ProcessFamily": {"000BD3A69E56CD5E8D998FEDA8EF3CA6.CCD2FFE1": {"Parent_Process": "", "Command_Line": "", "Type_Created": "Root"}}, "ProcessActives": {"000BD3A69E56CD5E8D998FEDA8EF3CA6.CCD2FFE1": {"MemoryOperations": {}, "FileOperations": {"DELETE_FILE": [{"COMMENT": "Delete_File_Found", "DETAILS": {"file_path": "C:\DOCUME~1\autoer\LOCALS~1\Temp\~DFCCF6.tmp"}, "LEVEL": "LEVEL_2"}], "CREATE_FILE": [{"COMMENT": "Create_File_Found", "DETAILS": {"file_path": "C:\DOCUME~1\autoer\LOCALS~1\Temp\~DFCCF6.tmp"}, "LEVEL": "LEVEL_2"}]}, "NetworkOperations": {}, "ProcessOperations": {}, "RegistryOperations": {"SET_KEY_VALUE": [{"COMMENT": "Set_Key_Value_Found", "DETAILS": {"value": "Drive", "type": "REG_SZ", "name": "BaseClass", "key": "HKEY_USERS\S-1-5-21-1708537768-287218729-1177238915-1003\Software\Microsoft\Windows\CurrentVersion\Explorer\MountPoints2\{7fb46850-baea-11e1-9890-806d6172696f}"}, "LEVEL": "LEVEL_2"}]}, "OtherOperations": {}}}, "TimeOfReportCreated": "2013-06-03 11:25:25:724 +0800", "Summary": ["CREATE_FILE", "CREATE_FILE.DROP_PE_TO_SYSTEM_DIR", "DELETE_FILE", "SET_KEY_VALUE"], "FileName": "000BD3A69E56CD5E8D998FEDA8EF3CA6.CCD2FFE1"}, "Result": "Success", "Time": "2013-06-03 11:25:25:724 +0800", "DESCRIPTION": "u64cdu4f5cu6210u529fu5b8cu6210u3002"}' WHERE md5 = '000BD3A69E56CD5E8D998FEDA8EF3CA6' and crc32 = 'CCD2FFE1'
select * from summary_file_malware