Intelmq

----------

Postgres databases
sql
ALTER TABLE events
ADD COLUMN "feed.provider" text

----------

Syntax of runtime.conf has changed

Postgres databases
sql
ALTER TABLE events
ADD COLUMN "misp.attribute_uuid" varchar(36),
ADD COLUMN "malware.hash.sha256" text,
ALTER COLUMN "misp.event_uuid" SET DATA TYPE varchar(36);

ALTER TABLE events RENAME COLUMN "misp_uuid" TO "misp.event_uuid";

UPDATE events
SET "protocol.application" = lower("protocol.application")
WHERE "protocol.application" IS NOT NULL;
UPDATE events
SET "source.abuse_contact" = lower("source.abuse_contact")
WHERE "source.abuse_contact" IS NOT NULL;
UPDATE events
SET "destination.abuse_contact" = lower("destination.abuse_contact")
WHERE "destination.abuse_contact" IS NOT NULL;
UPDATE events
SET "event_hash" = lower("event_hash")
WHERE "event_hash" IS NOT NULL;
UPDATE events
SET "malware.hash.md5" = lower("malware.hash.md5");
UPDATE events
SET "malware.hash.sha1" = lower("malware.hash.sha1");
UPDATE events
SET "malware.hash.sha256" = lower("malware.hash.sha256");
UPDATE events
SET "malware.hash.md5" = lower(substring("malware.hash" from 4))
WHERE substring("malware.hash" from 1 for 3) = '$1$';
UPDATE events
SET "malware.hash.sha1" = lower(substring("malware.hash" from 7))
WHERE substring("malware.hash" from 1 for 6) = '$sha1$';
UPDATE events
SET "malware.hash.sha256" = lower(substring("malware.hash" from 4))
WHERE substring("malware.hash" from 1 for 3) = '$5$';
UPDATE events
SET "malware.hash.md5" = lower("malware.hash.md5")
WHERE "malware.hash.md5" IS NOT NULL;
UPDATE events
SET "malware.hash.sha1" = lower("malware.hash.sha1")
WHERE "malware.hash.sha1" IS NOT NULL;

----------

Postgres databases

sql
ALTER TABLE events
ADD COLUMN "classification.identifier" text,
ADD COLUMN "feed.accuracy" text,
ADD COLUMN "feed.code" text,
ADD COLUMN "malware.hash.md5" text,
ADD COLUMN "malware.hash.sha1" text,
ADD COLUMN "protocol.transport" text,
ALTER COLUMN "extra" SET DATA TYPE json,
RENAME COLUMN "additional_information" TO "extra",
RENAME COLUMN "description.target" TO "event_description.target",
RENAME COLUMN "description.text" TO "event_description.text",
RENAME COLUMN "destination.bgp_prefix" TO "destination.network" text,
RENAME COLUMN "destination.cc" TO "destination.geolocation.cc" text,
RENAME COLUMN "destination.email_address" TO "destination.account" text,
RENAME COLUMN "destination.reverse_domain_name" TO "destination.reverse_dns" text,
RENAME COLUMN "misp_id" TO "misp_uuid",
RENAME COLUMN "source.bgp_prefix" TO "source.network" text,
RENAME COLUMN "source.cc" TO "source.geolocation.cc" text,
RENAME COLUMN "source.email_address" TO "source.account" text,
RENAME COLUMN "source.reverse_domain_name" TO "source.reverse_dns" text,
RENAME COLUMN "webshot_url" TO "screenshot_url" text;

UPDATE events
SET "extra"=json_build_object('os.name', "os.name", 'os.version', "os.version", 'user_agent', "user_agent")
WHERE "os.name" IS NOT NULL AND "os.version" IS NOT NULL AND "user_agent" IS NOT NULL AND "extra" IS NULL;

ALTER TABLE events
DROP COLUMN "os.name",
DROP COLUMN "os.version",
DROP COLUMN "user_agent",
DROP COLUMN "malware.hash";