#Copyright 2011,2013 Mark Stingley (mark _AT_ altsec.info)
#Licensed under http://www.gnu.org/copyleft/gpl.html
#Original multi-purpose Palo Alto log converter code modified for just URL logs
#Designed to work with "scp export url" or "scheduled export" URL logs


This text file documents the schema of the combined PanLog MySQL table that can be used for threat, traffic, or url data.

Here is the table description, followed by a SQL statement suitable for pasting into MySQL to create the table:

Description -

+-----------+----------------------+------+-----+---------+-------+
| Field     | Type                 | Null | Key | Default | Extra |
+-----------+----------------------+------+-----+---------+-------+
| domain    | smallint(5) unsigned | YES  |     | NULL    |       |
| rcvtime   | datetime             | YES  |     | NULL    |       |
| sernum    | char(31)             | YES  |     | NULL    |       |
| type      | char(31)             | YES  |     | NULL    |       |
| subtype   | char(31)             | YES  |     | NULL    |       |
| config    | smallint(5) unsigned | YES  |     | NULL    |       |
| gentime   | datetime             | YES  | MUL | NULL    |       |
| srcaddr   | char(40)             | YES  | MUL | NULL    |       |
| dstaddr   | char(40)             | YES  | MUL | NULL    |       |
| nsrcaddr  | char(40)             | YES  |     | NULL    |       |
| ndstaddr  | char(40)             | YES  |     | NULL    |       |
| rule      | char(31)             | YES  | MUL | NULL    |       |
| srcuser   | char(63)             | YES  | MUL | NULL    |       |
| dstuser   | char(63)             | YES  |     | NULL    |       |
| app       | char(31)             | YES  | MUL | NULL    |       |
| vsys      | char(31)             | YES  |     | NULL    |       |
| srczone   | char(31)             | YES  | MUL | NULL    |       |
| dstzone   | char(31)             | YES  | MUL | NULL    |       |
| ibif      | char(15)             | YES  |     | NULL    |       |
| obif      | char(15)             | YES  |     | NULL    |       |
| logact    | char(31)             | YES  |     | NULL    |       |
| logtime   | datetime             | YES  |     | NULL    |       |
| sessid    | bigint(20) unsigned  | YES  |     | NULL    |       |
| rptcnt    | smallint(5) unsigned | YES  |     | NULL    |       |
| srcport   | smallint(5) unsigned | YES  |     | NULL    |       |
| dstport   | smallint(5) unsigned | YES  |     | NULL    |       |
| nsrcport  | smallint(5) unsigned | YES  |     | NULL    |       |
| ndstport  | smallint(5) unsigned | YES  |     | NULL    |       |
| flags     | char(8)              | YES  |     | NULL    |       |
| proto     | char(31)             | YES  | MUL | NULL    |       |
| action    | char(31)             | YES  | MUL | NULL    |       |
| bytes     | bigint(20) unsigned  | YES  |     | NULL    |       |
| bsent     | bigint(20) unsigned  | YES  |     | NULL    |       |
| brecv     | bigint(20) unsigned  | YES  |     | NULL    |       |
| packets   | bigint(20) unsigned  | YES  |     | NULL    |       |
| stime     | datetime             | YES  | MUL | NULL    |       |
| etime     | bigint(20) unsigned  | YES  |     | NULL    |       |
| category  | char(63)             | YES  | MUL | NULL    |       |
| padding   | smallint(5) unsigned | YES  |     | NULL    |       |
| miscurl   | text                 | YES  |     | NULL    |       |
| threat    | char(63)             | YES  | MUL | NULL    |       |
| severity  | char(31)             | YES  | MUL | NULL    |       |
| direction | char(31)             | YES  | MUL | NULL    |       |
| seqno     | bigint(20) unsigned  | YES  |     | NULL    |       |
| actflags  | char(20)             | YES  |     | NULL    |       |
| srccntry  | char(60)             | YES  |     | NULL    |       |
| dstcntry  | char(60)             | YES  |     | NULL    |       |
| cpadding  | smallint(5) unsigned | YES  |     | NULL    |       |
| contype   | char(20)             | YES  |     | NULL    |       |
+-----------+----------------------+------+-----+---------+-------+

Table creation MySQL statement -

CREATE DATABASE panlogs;
USE panlogs;
CREATE TABLE combined_template ( domain     SMALLINT UNSIGNED,
                        rcvtime    DATETIME,
                        sernum     CHAR(31),
                        type       CHAR(31),
                        subtype    CHAR(31),
                        config     SMALLINT UNSIGNED,
                        gentime    DATETIME,
                        srcaddr    CHAR(40),
                        dstaddr    CHAR(40),
                        nsrcaddr   CHAR(40),
                        ndstaddr   CHAR(40),
                        rule       CHAR(31),
                        srcuser    CHAR(63),
                        dstuser    CHAR(63),
                        app        CHAR(31),
                        vsys       CHAR(31),
                        srczone    CHAR(31),
                        dstzone    CHAR(31),
                        ibif       CHAR(15),
                        obif       CHAR(15),
                        logact     CHAR(31),
                        logtime    DATETIME,
                        sessid     BIGINT UNSIGNED,
                        rptcnt     SMALLINT UNSIGNED,
                        srcport    SMALLINT UNSIGNED,
                        dstport    SMALLINT UNSIGNED,
                        nsrcport   SMALLINT UNSIGNED,
                        ndstport   SMALLINT UNSIGNED,
                        flags      CHAR(8),
                        proto      CHAR(31),
                        action     CHAR(31),
                        bytes      BIGINT UNSIGNED,
                        bsent      BIGINT UNSIGNED,
                        brecv      BIGINT UNSIGNED,
                        packets    BIGINT UNSIGNED,
                        stime      DATETIME,
                        etime      BIGINT UNSIGNED,
                        category   CHAR(63),
                        padding    SMALLINT UNSIGNED,
                        miscurl    TEXT(1023),
                        threat     CHAR(63),
                        severity   CHAR(31),
                        direction  CHAR(31),
                        seqno      BIGINT UNSIGNED,
                        actflags   CHAR(20),
                        srccntry   CHAR(60),
                        dstcntry   CHAR(60),
                        cpadding   SMALLINT UNSIGNED,
                        contype    CHAR(20),
                        INDEX idx_stime(stime),
                        INDEX idx_gentime(gentime),
                        INDEX idx_srcaddr(srcaddr),
                        INDEX idx_dstaddr(dstaddr),
                        INDEX idx_rule(rule),
                        INDEX idx_srcuser(srcuser),
                        INDEX idx_app(app),
                        INDEX idx_srczone(srczone),
                        INDEX idx_dstzone(dstzone),
                        INDEX idx_proto(proto),
                        INDEX idx_action(action),
                        INDEX idx_category(category),
                        INDEX idx_threat(threat),
                        INDEX idx_severity(severity),
                        INDEX idx_direction(direction)
                        );

The table can also be created from the Linux command line by saving it to "panlog_template.sql" then executing: mysql -p -u root < panlog_template.sql