Парсер python suricata eve.json и перенос данных в mssql server

Linux > Парсер python suricata eve.json и перенос данных в mssql server
17.12.2015 13:26:19


Наиболее часто встречающиеся слова в статье:

[varchar] [parsed_string] [timestamp] [suricata] [src_port] [in_iface] [dest_port] [dest_ip] [signature] [category]


Статья:

CREATE TABLE [dbo].[suricata](
[id] [int] IDENTITY(1,1) NOT NULL,
[src_port] [varchar](10) NULL,
[proto] [varchar](20) NULL,
[in_iface] [varchar](20) NULL,
[src_ip] [varchar](20) NULL,
[dest_port] [varchar](20) NULL,
[dest_ip] [varchar](20) NULL,
[signature] [varchar](400) NULL,
[category] [varchar](400) NULL,
[signature_id] [varchar](20) NULL,
[timestamp] [nvarchar](350) NULL,
[d] [varchar](20) NULL
) ON [PRIMARY]

GO

 

Парсер suricata eve.json и перенос данных в   mssql server

import pymssql
import json

conn = pymssql.connect(server='i7.iocsha', user='iocsha', password='5311453', database='ti')
cursor = conn.cursor()
cursor.execute("truncate table suricata")
myfile = open('/usr/local/var/log/suricata/eve.json', 'r')
for line in myfile.readlines():
    parsed_string = json.loads(line)
    #print parsed_string
    #ROOT
    timestamp= parsed_string["timestamp"]
    src_port= parsed_string["src_port"]
    proto= parsed_string["proto"]
    in_iface= parsed_string["in_iface"]
    src_ip=parsed_string["src_ip"]
    dest_port=parsed_string["dest_port"]
    dest_ip=parsed_string["dest_ip"]
    #Children
    alert= parsed_string["alert"]
    signature=alert["signature"]
    category=alert["category"]
    signature_id=alert["signature_id"]


    #print str(timestamp)+"|"+str(src_port)+"|"+str(proto)+"|"+str(in_iface)+" >> "+str(category)+"|"+str(signature)+"|"+str(src_ip)+">"+str(dest_ip)+":"+str(dest_port)
    cursor.execute("insert into [ti].[dbo].[suricata] (timestamp,src_port,proto,in_iface,src_ip,dest_port,dest_ip,signature,category,signature_id) values ('"+str(timestamp)+"','"+str(src_port)+"','"+str(proto)+"','"+str(in_iface)+"','"+str(src_ip)         +"','"+str(dest_port)+"','"+str(dest_ip)+"','"+str(signature)+"','"+str(category)+"','"+str(signature_id)+"')")


cursor.execute("update [ti].[dbo].[suricata] set [d]=LEFT([timestamp], CHARINDEX('T',[timestamp])-1)")
conn.commit()
conn.close()
myfile.close()