plpythonu_postgresql plpythonu例子

以下代码仅作为参考之用

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": "\u64cd\u4f5c\u6210\u529f\u5b8c\u6210\u3002"}' WHERE md5 = '000BD3A69E56CD5E8D998FEDA8EF3CA6' and crc32 = 'CCD2FFE1'

select * from summary_file_malware