surveilr_osquery_ms_node

Column	Type	PK	Required	Default
surveilr_osquery_ms_node_id	VARCHAR	Yes	Yes
node_key	TEXT	No	Yes
host_identifier	TEXT	No	Yes
tls_cert_subject	TEXT	No	No
os_version	TEXT	No	Yes
platform	TEXT	No	Yes
last_seen	TIMESTAMP	No	Yes
status	TEXT	No	Yes	'active'
osquery_version	TEXT	No	No
osquery_build_platform	TEXT	No	Yes
device_id	VARCHAR	No	Yes
behavior_id	VARCHAR	No	No
created_at	TIMESTAMPTZ	No	No	CURRENT_TIMESTAMP
created_by	TEXT	No	No	'UNKNOWN'
updated_at	TIMESTAMPTZ	No	No
updated_by	TEXT	No	No
deleted_at	TIMESTAMPTZ	No	No
deleted_by	TEXT	No	No
activity_log	TEXT	No	No

Foreign Keys

Column Name	Foreign Key
behavior_id	behavior_id references behavior.behavior_id
device_id	device_id references device.device_id

Indexes

Column Name	Index Name
node_key	idx_surveilr_osquery_ms_node__node_key
node_key	sqlite_autoindex_surveilr_osquery_ms_node_3
host_identifier	sqlite_autoindex_surveilr_osquery_ms_node_2
os_version	sqlite_autoindex_surveilr_osquery_ms_node_2
surveilr_osquery_ms_node_id	sqlite_autoindex_surveilr_osquery_ms_node_1

SQL DDL

CREATE TABLE "surveilr_osquery_ms_node" (
    "surveilr_osquery_ms_node_id" VARCHAR PRIMARY KEY NOT NULL,
    "node_key" TEXT NOT NULL,
    "host_identifier" TEXT NOT NULL,
    "tls_cert_subject" TEXT,
    "os_version" TEXT NOT NULL,
    "platform" TEXT NOT NULL,
    "last_seen" TIMESTAMP NOT NULL,
    "status" TEXT NOT NULL DEFAULT 'active',
    "osquery_version" TEXT,
    "osquery_build_platform" TEXT NOT NULL,
    "device_id" VARCHAR NOT NULL,
    "behavior_id" VARCHAR,
    "created_at" TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    "created_by" TEXT DEFAULT 'UNKNOWN',
    "updated_at" TIMESTAMPTZ,
    "updated_by" TEXT,
    "deleted_at" TIMESTAMPTZ,
    "deleted_by" TEXT,
    "activity_log" TEXT,
    FOREIGN KEY("device_id") REFERENCES "device"("device_id"),
    FOREIGN KEY("behavior_id") REFERENCES "behavior"("behavior_id"),
    UNIQUE("host_identifier", "os_version"),
    UNIQUE("node_key")
)