doc/prads.sql

   1 DROP DATABASE IF EXISTS prads;
   2 CREATE DATABASE prads;
   3
   4 -- use prads; -- mysql specific
   5 drop table if exists asset;
   6 -- autoincrements in postgres
   7 drop sequence asset_id_seq;
   8 create sequence asset_id_seq;
   9
  10 CREATE TABLE asset (
  11    -- assetID        INT NOT NULL AUTO_INCREMENT,
  12    assetID        INT NOT NULL default nextval('asset_id_seq'),
  13    hostname       TEXT default '',
  14    sensorID       INT NOT NULL default '0',
  15    timestamp      TIMESTAMP default NULL,
  16    --ipaddress      decimal(39,0) default NULL,
  17    ipaddress      inet default null,
  18    mac_address    VARCHAR(20) NOT NULL default '',
  19    mac_vendor     VARCHAR(50) NOT NULL default '',
  20    os             TEXT default '',
  21    os_details     TEXT default '',
  22    os_fingerprint TEXT default '',
  23    link           TEXT default '',
  24    distance       INT default '0',
  25    service        TEXT default '',
  26    application    TEXT default '',
  27    port           INT default '0',
  28    protocol       SMALLINT NOT NULL default '0',
  29    hex_payload    TEXT default '',
  30    --constraint uniq unique (ipaddress,port,protocol,service,application),
  31    --UNIQUE           KEY unique_row_key (ipaddress,port,protocol,service,application),
  32    --PRIMARY          KEY (sensorID,assetID),
  33    constraint prikey primary key (sensorID, assetID),
  34    CHECK (assetID>=0),
  35    CHECK (sensorID>=0),
  36    CHECK (distance>=0),
  37    CHECK (port>=0),
  38    CHECK (protocol >=0)
  39 );
  40
  41 /* mysql
  42 CREATE TABLE IF NOT EXISTS `asset` (
  43    `assetID`        INT UNSIGNED NOT NULL AUTO_INCREMENT,
  44    `hostname`       VARCHAR(255) NOT NULL default '',
  45    `sensorID`       INT UNSIGNED NOT NULL default '0',
  46    `timestamp`      DATETIME NOT NULL default '0000-00-00 00:00:00',
  47    `ipaddress`      decimal(39,0) unsigned default NULL,
  48    `mac_address`    VARCHAR(20) NOT NULL default '',
  49    `mac_vendor`     VARCHAR(50) NOT NULL default '',
  50    `os`             VARCHAR(20) NOT NULL default '',
  51    `os_details`     VARCHAR(255) NOT NULL default '',
  52    `os_fingerprint` VARCHAR(255) NOT NULL default '',
  53    `link`           VARCHAR(20) NOT NULL default '',
  54    `distance`       INT UNSIGNED NOT NULL default '0',
  55    `service`        VARCHAR(50) NOT NULL default '',
  56    `application`    VARCHAR(255) NOT NULL default '',
  57    `port`           INT UNSIGNED NOT NULL default '0',
  58    `protocol`       TINYINT UNSIGNED NOT NULL default '0',
  59    `hex_payload`    VARCHAR(255) default '',
  60    UNIQUE           KEY `unique_row_key` (`ipaddress`,`port`,`protocol`,`service`,`application`),
  61    PRIMARY          KEY (`sensorID`,`assetID`)
  62 ) TYPE=InnoDB;
  63
  64 -- INET_ATON6
  65 -- DELIMITER //
  66 CREATE FUNCTION INET_ATON6(n CHAR(39))
  67 RETURNS DECIMAL(39)
  68 BEGIN
  69     RETURN CAST(CONV(SUBSTRING(n FROM  1 FOR 4), 16, 10) AS DECIMAL(39))
  70                        * 5192296858534827628530496329220096 -- 65536 ^ 7
  71          + CAST(CONV(SUBSTRING(n FROM  6 FOR 4), 16, 10) AS DECIMAL(39))
  72                        *      79228162514264337593543950336 -- 65536 ^ 6
  73          + CAST(CONV(SUBSTRING(n FROM 11 FOR 4), 16, 10) AS DECIMAL(39))
  74                        *          1208925819614629174706176 -- 65536 ^ 5
  75          + CAST(CONV(SUBSTRING(n FROM 16 FOR 4), 16, 10) AS DECIMAL(39))
  76                        *               18446744073709551616 -- 65536 ^ 4
  77          + CAST(CONV(SUBSTRING(n FROM 21 FOR 4), 16, 10) AS DECIMAL(39))
  78                        *                    281474976710656 -- 65536 ^ 3
  79          + CAST(CONV(SUBSTRING(n FROM 26 FOR 4), 16, 10) AS DECIMAL(39))
  80                        *                         4294967296 -- 65536 ^ 2
  81          + CAST(CONV(SUBSTRING(n FROM 31 FOR 4), 16, 10) AS DECIMAL(39))
  82                        *                              65536 -- 65536 ^ 1
  83          + CAST(CONV(SUBSTRING(n FROM 36 FOR 4), 16, 10) AS DECIMAL(39))
  84          ;
  85 END;
  86 DELIMITER ;
  87 -- INET_NTOA6
  88
  89 DELIMITER //
  90 CREATE FUNCTION INET_NTOA6(n DECIMAL(39) UNSIGNED)
  91 RETURNS CHAR(39)
  92 DETERMINISTIC
  93 BEGIN
  94   DECLARE a CHAR(39)             DEFAULT '';
  95   DECLARE i INT                  DEFAULT 7;
  96   DECLARE q DECIMAL(39)          DEFAULT 0;
  97   DECLARE r INT                  DEFAULT 0;
  98   WHILE i DO
  99     -- DIV doesn't work with nubers > bigint
 100     SET q := FLOOR(n / 65536);
 101     SET r := n MOD 65536;
 102     SET n := q;
 103     SET a := CONCAT_WS(':', LPAD(CONV(r, 10, 16), 4, '0'), a);
 104
 105     SET i := i - 1;
 106   END WHILE;
 107
 108   SET a := TRIM(TRAILING ':' FROM CONCAT_WS(':',
 109                                             LPAD(CONV(n, 10, 16), 4, '0'),
 110                                             a));
 111
 112   RETURN a;
 113
 114 END;
 115 //
 116 DELIMITER ;
 117 */
 118
 119
 120
 121
 122 -- sqlight
 123 /* CREATE TABLE asset (
 124   ip TEXT,
 125   service TEXT,
 126   time TEXT,
 127   fingerprint TEXT,
 128   mac TEXT,
 129   os TEXT,
 130   details TEXT,
 131   link TEXT,
 132   distance TEXT,
 133   reporting TEXT
 134 )
 135 */
 136
 137 drop table protocol;
 138 CREATE TABLE protocol (
 139   protoID         SMALLINT,
 140   name            VARCHAR(100) NOT NULL default '',
 141   PRIMARY           KEY (protoID),
 142   CHECK(protoID >= 0)
 143 );
 144
 145 INSERT INTO protocol VALUES (0,   'HOPOPT');
 146 INSERT INTO protocol VALUES (1,   'ICMP');
 147 INSERT INTO protocol VALUES (2,   'IGMP');
 148 INSERT INTO protocol VALUES (3,   'GGP');
 149 INSERT INTO protocol VALUES (4,   'IP');
 150 INSERT INTO protocol VALUES (5,   'ST');
 151 INSERT INTO protocol VALUES (6,   'TCP');
 152 INSERT INTO protocol VALUES (7,   'CBT');
 153 INSERT INTO protocol VALUES (8,   'EGP');
 154 INSERT INTO protocol VALUES (9,   'IGP');
 155 INSERT INTO protocol VALUES (10,  'BBN-RCC-MON');
 156 INSERT INTO protocol VALUES (11,  'NVP-II');
 157 INSERT INTO protocol VALUES (12,  'PUP');
 158 INSERT INTO protocol VALUES (13,  'ARGUS');
 159 INSERT INTO protocol VALUES (14,  'EMCON');
 160 INSERT INTO protocol VALUES (15,  'XNET');
 161 INSERT INTO protocol VALUES (16,  'CHAOS');
 162 INSERT INTO protocol VALUES (17,  'UDP');
 163 INSERT INTO protocol VALUES (18,  'MUX');
 164 INSERT INTO protocol VALUES (19,  'DCN-MEAS');
 165
 166 INSERT INTO protocol VALUES (20,  'HMP');
 167 INSERT INTO protocol VALUES (21,  'PRM');
 168 INSERT INTO protocol VALUES (22,  'XNS-IDP');
 169 INSERT INTO protocol VALUES (23,  'TRUNK-1');
 170 INSERT INTO protocol VALUES (24,  'TRUNK-2');
 171 INSERT INTO protocol VALUES (25,  'LEAF-1');
 172 INSERT INTO protocol VALUES (26,  'LEAF-2');
 173 INSERT INTO protocol VALUES (27,  'RDP');
 174 INSERT INTO protocol VALUES (28,  'IRTP');
 175 INSERT INTO protocol VALUES (29,  'ISO-TP4');
 176
 177 INSERT INTO protocol VALUES (30,  'NETBLT');
 178 INSERT INTO protocol VALUES (31,  'MFE-NSP');
 179 INSERT INTO protocol VALUES (32,  'MERIT-INP');
 180 INSERT INTO protocol VALUES (33,  'DCCP');
 181 INSERT INTO protocol VALUES (34,  '3PC');
 182 INSERT INTO protocol VALUES (35,  'IDPR');
 183 INSERT INTO protocol VALUES (36,  'XTP');
 184 INSERT INTO protocol VALUES (37,  'DDP');
 185 INSERT INTO protocol VALUES (38,  'IDPR-CMTP');
 186 INSERT INTO protocol VALUES (39,  'TP++');
 187
 188 INSERT INTO protocol VALUES (40,  'IL');
 189 INSERT INTO protocol VALUES (41,  'IPv6');
 190 INSERT INTO protocol VALUES (42,  'SDRP');
 191 INSERT INTO protocol VALUES (43,  'IPv6-Route');
 192 INSERT INTO protocol VALUES (44,  'IPv6-Frag');
 193 INSERT INTO protocol VALUES (45,  'IDRP');
 194 INSERT INTO protocol VALUES (46,  'RSVP');
 195 INSERT INTO protocol VALUES (47,  'GRE');
 196 INSERT INTO protocol VALUES (48,  'MHRP');
 197 INSERT INTO protocol VALUES (49,  'BNA');
 198
 199 INSERT INTO protocol VALUES (50,  'ESP');
 200 INSERT INTO protocol VALUES (51,  'AH');
 201 INSERT INTO protocol VALUES (52,  'I-NLSP');
 202 INSERT INTO protocol VALUES (53,  'SWIPE');
 203 INSERT INTO protocol VALUES (54,  'NARP');
 204 INSERT INTO protocol VALUES (55,  'MOBILE');
 205 INSERT INTO protocol VALUES (56,  'TLSP');
 206 INSERT INTO protocol VALUES (57,  'SKIP');
 207 INSERT INTO protocol VALUES (58,  'IPv6-ICMP');
 208 INSERT INTO protocol VALUES (59,  'IPv6-NoNxt');
 209
 210 INSERT INTO protocol VALUES (60,  'IPv6-Opts');
 211 INSERT INTO protocol VALUES (61,  'Any host internal protocol');
 212 INSERT INTO protocol VALUES (62,  'CFTP');
 213 INSERT INTO protocol VALUES (63,  'Any local network');
 214 INSERT INTO protocol VALUES (64,  'SAT-EXPAK');
 215 INSERT INTO protocol VALUES (65,  'KRYPTOLAN');
 216 INSERT INTO protocol VALUES (66,  'RVD');
 217 INSERT INTO protocol VALUES (67,  'IPPC');
 218 INSERT INTO protocol VALUES (68,  'Any distributed file system');
 219 INSERT INTO protocol VALUES (69,  'SAT-MON');
 220
 221 INSERT INTO protocol VALUES (70,  'VISA');
 222 INSERT INTO protocol VALUES (71,  'IPCV');
 223 INSERT INTO protocol VALUES (72,  'CPNX');
 224 INSERT INTO protocol VALUES (73,  'CPHB');
 225 INSERT INTO protocol VALUES (74,  'WSN');
 226 INSERT INTO protocol VALUES (75,  'PVP');
 227 INSERT INTO protocol VALUES (76,  'BR-SAT-MON');
 228 INSERT INTO protocol VALUES (77,  'SUN-ND');
 229 INSERT INTO protocol VALUES (78,  'WB-MON');
 230 INSERT INTO protocol VALUES (79,  'WB-EXPAK');
 231
 232 INSERT INTO protocol VALUES (80,  'ISO-IP');
 233 INSERT INTO protocol VALUES (81,  'VMTP');
 234 INSERT INTO protocol VALUES (82,  'SECURE-VMTP');
 235 INSERT INTO protocol VALUES (83,  'VINES');
 236 INSERT INTO protocol VALUES (84,  'TTP');
 237 INSERT INTO protocol VALUES (85,  'NSFNET-IGP');
 238 INSERT INTO protocol VALUES (86,  'DGP');
 239 INSERT INTO protocol VALUES (87,  'TCF');
 240 INSERT INTO protocol VALUES (88,  'EIGRP');
 241 INSERT INTO protocol VALUES (89,  'OSPF');
 242
 243 INSERT INTO protocol VALUES (90,  'Sprite-RPC');
 244 INSERT INTO protocol VALUES (91,  'LARP');
 245 INSERT INTO protocol VALUES (92,  'MTP');
 246 INSERT INTO protocol VALUES (93,  'AX.25');
 247 INSERT INTO protocol VALUES (94,  'IPIP');
 248 INSERT INTO protocol VALUES (95,  'MICP');
 249 INSERT INTO protocol VALUES (96,  'SCC-SP');
 250 INSERT INTO protocol VALUES (97,  'ETHERIP');
 251 INSERT INTO protocol VALUES (98,  'ENCAP');
 252 INSERT INTO protocol VALUES (99,  'Any private encryption scheme');
 253
 254 INSERT INTO protocol VALUES (100,  'GMTP');
 255 INSERT INTO protocol VALUES (101,  'IFMP');
 256 INSERT INTO protocol VALUES (102,  'PNNI');
 257 INSERT INTO protocol VALUES (103,  'PIM');
 258 INSERT INTO protocol VALUES (104,  'ARIS');
 259 INSERT INTO protocol VALUES (105,  'SCPS');
 260 INSERT INTO protocol VALUES (106,  'QNX');
 261 INSERT INTO protocol VALUES (107,  'A/N');
 262 INSERT INTO protocol VALUES (108,  'IPComp');
 263 INSERT INTO protocol VALUES (109,  'SNP');
 264
 265 INSERT INTO protocol VALUES (110,  'Compaq-Peer');
 266 INSERT INTO protocol VALUES (111,  'IPX-in-IP');
 267 INSERT INTO protocol VALUES (112,  'VRRP');
 268 INSERT INTO protocol VALUES (113,  'PGM');
 269 INSERT INTO protocol VALUES (114,  'Any 0-hop protocol');
 270 INSERT INTO protocol VALUES (115,  'L2TP');
 271 INSERT INTO protocol VALUES (116,  'DDX');
 272 INSERT INTO protocol VALUES (117,  'IATP');
 273 INSERT INTO protocol VALUES (118,  'STP');
 274 INSERT INTO protocol VALUES (119,  'SRP');
 275
 276 INSERT INTO protocol VALUES (120,  'UTI');
 277 INSERT INTO protocol VALUES (121,  'SMP');
 278 INSERT INTO protocol VALUES (122,  'SM');
 279 INSERT INTO protocol VALUES (123,  'PTP');
 280 INSERT INTO protocol VALUES (124,  'IS-IS over IPv4');
 281 INSERT INTO protocol VALUES (125,  'FIRE');
 282 INSERT INTO protocol VALUES (126,  'CRTP');
 283 INSERT INTO protocol VALUES (127,  'CRUDP');
 284 INSERT INTO protocol VALUES (128,  'SSCOPMCE');
 285 INSERT INTO protocol VALUES (129,  'IPLT');
 286
 287 INSERT INTO protocol VALUES (130,  'SPS');
 288 INSERT INTO protocol VALUES (131,  'PIPE');
 289 INSERT INTO protocol VALUES (132,  'SCTP');
 290 INSERT INTO protocol VALUES (133,  'FC');
 291 INSERT INTO protocol VALUES (134,  'RSVP-E2E-IGNORE');
 292 INSERT INTO protocol VALUES (135,  'Mobility Header');
 293 INSERT INTO protocol VALUES (136,  'UDP Lite');
 294 INSERT INTO protocol VALUES (137,  'MPLS-in-IP');
 295 INSERT INTO protocol VALUES (138,  'MANET');
 296 INSERT INTO protocol VALUES (139,  'HIP');
 297
 298 INSERT INTO protocol VALUES (140,  'Shim6');
 299
 300 INSERT INTO protocol VALUES (141,  'UNASSIGNED');
 301 INSERT INTO protocol VALUES (142,  'UNASSIGNED');
 302 INSERT INTO protocol VALUES (143,  'UNASSIGNED');
 303 INSERT INTO protocol VALUES (144,  'UNASSIGNED');
 304 INSERT INTO protocol VALUES (145,  'UNASSIGNED');
 305 INSERT INTO protocol VALUES (146,  'UNASSIGNED');
 306 INSERT INTO protocol VALUES (147,  'UNASSIGNED');
 307 INSERT INTO protocol VALUES (148,  'UNASSIGNED');
 308 INSERT INTO protocol VALUES (149,  'UNASSIGNED');
 309
 310 INSERT INTO protocol VALUES (150,  'UNASSIGNED');
 311 INSERT INTO protocol VALUES (151,  'UNASSIGNED');
 312 INSERT INTO protocol VALUES (152,  'UNASSIGNED');
 313 INSERT INTO protocol VALUES (153,  'UNASSIGNED');
 314 INSERT INTO protocol VALUES (154,  'UNASSIGNED');
 315 INSERT INTO protocol VALUES (155,  'UNASSIGNED');
 316 INSERT INTO protocol VALUES (156,  'UNASSIGNED');
 317 INSERT INTO protocol VALUES (157,  'UNASSIGNED');
 318 INSERT INTO protocol VALUES (158,  'UNASSIGNED');
 319 INSERT INTO protocol VALUES (159,  'UNASSIGNED');
 320 --#... upto 254
 321
 322 --# INSERT INTO protocol VALUES (254,  'UNASSIGNED');
 323
 324 INSERT INTO protocol VALUES (255,  'Reserved');
 325