code_notebook_migration_sql (view) Content

code_notebook_migration_sql (view) Schema
BEGIN TRANSACTION; CREATE TEMP TABLE IF NOT EXISTS "session_state_ephemeral" ( "key" TEXT PRIMARY KEY NOT NULL, "value" TEXT NOT NULL ); -- ConstructionSqlNotebook.session_ephemeral_table -- Executing for the first time. CREATE TEMP TABLE IF NOT EXISTS "session_state_ephemeral" ( "key" TEXT PRIMARY KEY NOT NULL, "value" TEXT NOT NULL ); INSERT INTO code_notebook_state (code_notebook_state_id, code_notebook_cell_id, from_state, to_state, transition_reason, created_at) VALUES ('01JXADE9V3ARNW8RMY39GP0Y4H__20250907220507', '01JXADE9V3ARNW8RMY39GP0Y4H', 'MIGRATION_CANDIDATE', 'EXECUTED', 'Migration', CURRENT_TIMESTAMP) ON CONFLICT(code_notebook_cell_id, from_state, to_state) DO UPDATE SET updated_at = CURRENT_TIMESTAMP, transition_reason = 'Reapplied 2025-09-07 22:05:07'; -- ConstructionSqlNotebook.surveilr_table_size -- Executing for the first time. CREATE TABLE IF NOT EXISTS surveilr_table_size ( table_name TEXT PRIMARY KEY, table_size_mb REAL ); DELETE FROM surveilr_table_size; INSERT INTO surveilr_table_size (table_name, table_size_mb) SELECT name, ROUND(SUM(pgsize) / (1024.0 * 1024), 2) FROM dbstat GROUP BY name; INSERT INTO code_notebook_state (code_notebook_state_id, code_notebook_cell_id, from_state, to_state, transition_reason, created_at) VALUES ('01JXADE9V33XF4J780NF7D3YKG__20250907220507', '01JXADE9V33XF4J780NF7D3YKG', 'MIGRATION_CANDIDATE', 'EXECUTED', 'Migration', CURRENT_TIMESTAMP) ON CONFLICT(code_notebook_cell_id, from_state, to_state) DO UPDATE SET updated_at = CURRENT_TIMESTAMP, transition_reason = 'Reapplied 2025-09-07 22:05:07'; -- ConstructionSqlNotebook.v001_once_initialDDL not included because it is non-idempotent and was already executed on 2025-09-02 14:39:57 -- ConstructionSqlNotebook.v001_seedDML -- Executing for the first time. INSERT INTO "ur_ingest_resource_path_match_rule" ("ur_ingest_resource_path_match_rule_id", "namespace", "regex", "flags", "nature", "priority", "description", "elaboration", "created_at", "created_by", "updated_at", "updated_by", "deleted_at", "deleted_by", "activity_log") VALUES ('ignore .git and node_modules paths', 'default', '/(\.git|node_modules)/', 'IGNORE_RESOURCE', NULL, NULL, 'Ignore any entry with `/.git/` or `/node_modules/` in the path.', NULL, (CURRENT_TIMESTAMP), NULL, NULL, NULL, NULL, NULL, NULL) ON CONFLICT DO UPDATE SET ur_ingest_resource_path_match_rule_id = COALESCE(EXCLUDED.ur_ingest_resource_path_match_rule_id, ur_ingest_resource_path_match_rule_id), namespace = COALESCE(EXCLUDED.namespace, namespace), regex = COALESCE(EXCLUDED.regex, regex), flags = COALESCE(EXCLUDED.flags, flags), description = COALESCE(EXCLUDED.description, description), "updated_at" = CURRENT_TIMESTAMP, "updated_by" = (SELECT "value" FROM "session_state_ephemeral" WHERE "key" = 'current_user'); INSERT INTO "ur_ingest_resource_path_match_rule" ("ur_ingest_resource_path_match_rule_id", "namespace", "regex", "flags", "nature", "priority", "description", "elaboration", "created_at", "created_by", "updated_at", "updated_by", "deleted_at", "deleted_by", "activity_log") VALUES ('typical ingestion extensions', 'default', '\.(?P<nature>md|mdx|html|json|jsonc|puml|txt|toml|yml|xml|tap|csv|tsv|ssv|psv|tm7)$', 'CONTENT_ACQUIRABLE', '?P<nature>', NULL, 'Ingest the content for text and structured data extensions (md, mdx, html, json, jsonc, puml, txt, toml, yml, xml, tap, csv, tsv, ssv, psv, tm7). Assume the nature is the same as the extension.', NULL, (CURRENT_TIMESTAMP), NULL, NULL, NULL, NULL, NULL, NULL) ON CONFLICT DO UPDATE SET ur_ingest_resource_path_match_rule_id = COALESCE(EXCLUDED.ur_ingest_resource_path_match_rule_id, ur_ingest_resource_path_match_rule_id), namespace = COALESCE(EXCLUDED.namespace, namespace), regex = COALESCE(EXCLUDED.regex, regex), flags = COALESCE(EXCLUDED.flags, flags), description = COALESCE(EXCLUDED.description, description), "updated_at" = CURRENT_TIMESTAMP, "updated_by" = (SELECT "value" FROM "session_state_ephemeral" WHERE "key" = 'current_user'); INSERT INTO "ur_ingest_resource_path_match_rule" ("ur_ingest_resource_path_match_rule_id", "namespace", "regex", "flags", "nature", "priority", "description", "elaboration", "created_at", "created_by", "updated_at", "updated_by", "deleted_at", "deleted_by", "activity_log") VALUES ('surveilr-[NATURE] style capturable executable', 'default', 'surveilr\[(?P<nature>[^\]]*)\]', 'CAPTURABLE_EXECUTABLE', '?P<nature>', NULL, 'Any entry with `surveilr-[XYZ]` in the path will be treated as a capturable executable extracting `XYZ` as the nature', NULL, (CURRENT_TIMESTAMP), NULL, NULL, NULL, NULL, NULL, NULL) ON CONFLICT DO UPDATE SET ur_ingest_resource_path_match_rule_id = COALESCE(EXCLUDED.ur_ingest_resource_path_match_rule_id, ur_ingest_resource_path_match_rule_id), namespace = COALESCE(EXCLUDED.namespace, namespace), regex = COALESCE(EXCLUDED.regex, regex), flags = COALESCE(EXCLUDED.flags, flags), description = COALESCE(EXCLUDED.description, description), "updated_at" = CURRENT_TIMESTAMP, "updated_by" = (SELECT "value" FROM "session_state_ephemeral" WHERE "key" = 'current_user'); INSERT INTO "ur_ingest_resource_path_match_rule" ("ur_ingest_resource_path_match_rule_id", "namespace", "regex", "flags", "nature", "priority", "description", "elaboration", "created_at", "created_by", "updated_at", "updated_by", "deleted_at", "deleted_by", "activity_log") VALUES ('surveilr-SQL capturable executable', 'default', 'surveilr-SQL', 'CAPTURABLE_EXECUTABLE | CAPTURABLE_SQL', NULL, NULL, 'Any entry with surveilr-SQL in the path will be treated as a capturable SQL executable and allow execution of the SQL', NULL, (CURRENT_TIMESTAMP), NULL, NULL, NULL, NULL, NULL, NULL) ON CONFLICT DO UPDATE SET ur_ingest_resource_path_match_rule_id = COALESCE(EXCLUDED.ur_ingest_resource_path_match_rule_id, ur_ingest_resource_path_match_rule_id), namespace = COALESCE(EXCLUDED.namespace, namespace), regex = COALESCE(EXCLUDED.regex, regex), flags = COALESCE(EXCLUDED.flags, flags), description = COALESCE(EXCLUDED.description, description), "updated_at" = CURRENT_TIMESTAMP, "updated_by" = (SELECT "value" FROM "session_state_ephemeral" WHERE "key" = 'current_user'); INSERT INTO "ur_ingest_resource_path_match_rule" ("ur_ingest_resource_path_match_rule_id", "namespace", "regex", "flags", "nature", "priority", "description", "elaboration", "created_at", "created_by", "updated_at", "updated_by", "deleted_at", "deleted_by", "activity_log") VALUES ('pdf-docx-transform-metadata', 'default', '\.(?P<nature>pdf|docx)$', 'CONTENT_ACQUIRABLE | CONTENT_ACQUIRABLE_TRANSFORM_MARKITDOWN | CONTENT_ACQUIRABLE_METADATA', '?P<nature>', '1', 'PDF and DOCX documents with full transformation and metadata extraction', NULL, (CURRENT_TIMESTAMP), NULL, NULL, NULL, NULL, NULL, NULL) ON CONFLICT DO UPDATE SET ur_ingest_resource_path_match_rule_id = COALESCE(EXCLUDED.ur_ingest_resource_path_match_rule_id, ur_ingest_resource_path_match_rule_id), namespace = COALESCE(EXCLUDED.namespace, namespace), regex = COALESCE(EXCLUDED.regex, regex), flags = COALESCE(EXCLUDED.flags, flags), description = COALESCE(EXCLUDED.description, description), "updated_at" = CURRENT_TIMESTAMP, "updated_by" = (SELECT "value" FROM "session_state_ephemeral" WHERE "key" = 'current_user'); INSERT INTO "ur_ingest_resource_path_match_rule" ("ur_ingest_resource_path_match_rule_id", "namespace", "regex", "flags", "nature", "priority", "description", "elaboration", "created_at", "created_by", "updated_at", "updated_by", "deleted_at", "deleted_by", "activity_log") VALUES ('images-metadata', 'default', '\.(?P<nature>jpg|jpeg|png|gif|bmp|tiff|svg|webp)$', 'CONTENT_ACQUIRABLE | CONTENT_ACQUIRABLE_METADATA', '?P<nature>', '2', 'Images with metadata extraction', NULL, (CURRENT_TIMESTAMP), NULL, NULL, NULL, NULL, NULL, NULL) ON CONFLICT DO UPDATE SET ur_ingest_resource_path_match_rule_id = COALESCE(EXCLUDED.ur_ingest_resource_path_match_rule_id, ur_ingest_resource_path_match_rule_id), namespace = COALESCE(EXCLUDED.namespace, namespace), regex = COALESCE(EXCLUDED.regex, regex), flags = COALESCE(EXCLUDED.flags, flags), description = COALESCE(EXCLUDED.description, description), "updated_at" = CURRENT_TIMESTAMP, "updated_by" = (SELECT "value" FROM "session_state_ephemeral" WHERE "key" = 'current_user'); INSERT INTO "ur_ingest_resource_path_match_rule" ("ur_ingest_resource_path_match_rule_id", "namespace", "regex", "flags", "nature", "priority", "description", "elaboration", "created_at", "created_by", "updated_at", "updated_by", "deleted_at", "deleted_by", "activity_log") VALUES ('media-metadata', 'default', '\.(?P<nature>mp4|mp3)$', 'CONTENT_ACQUIRABLE | CONTENT_ACQUIRABLE_METADATA', '?P<nature>', '3', 'Media files with metadata extraction', NULL, (CURRENT_TIMESTAMP), NULL, NULL, NULL, NULL, NULL, NULL) ON CONFLICT DO UPDATE SET ur_ingest_resource_path_match_rule_id = COALESCE(EXCLUDED.ur_ingest_resource_path_match_rule_id, ur_ingest_resource_path_match_rule_id), namespace = COALESCE(EXCLUDED.namespace, namespace), regex = COALESCE(EXCLUDED.regex, regex), flags = COALESCE(EXCLUDED.flags, flags), description = COALESCE(EXCLUDED.description, description), "updated_at" = CURRENT_TIMESTAMP, "updated_by" = (SELECT "value" FROM "session_state_ephemeral" WHERE "key" = 'current_user'); INSERT INTO "ur_ingest_resource_path_match_rule" ("ur_ingest_resource_path_match_rule_id", "namespace", "regex", "flags", "nature", "priority", "description", "elaboration", "created_at", "created_by", "updated_at", "updated_by", "deleted_at", "deleted_by", "activity_log") VALUES ('jsonl-content-replace', 'default', '\.(?P<nature>jsonl)$', 'CONTENT_ACQUIRABLE | CONTENT_REPLACE_JSON_LINES', '?P<nature>', '4', 'JSONL files for line-by-line JSON ingestion', NULL, (CURRENT_TIMESTAMP), NULL, NULL, NULL, NULL, NULL, NULL) ON CONFLICT DO UPDATE SET ur_ingest_resource_path_match_rule_id = COALESCE(EXCLUDED.ur_ingest_resource_path_match_rule_id, ur_ingest_resource_path_match_rule_id), namespace = COALESCE(EXCLUDED.namespace, namespace), regex = COALESCE(EXCLUDED.regex, regex), flags = COALESCE(EXCLUDED.flags, flags), description = COALESCE(EXCLUDED.description, description), "updated_at" = CURRENT_TIMESTAMP, "updated_by" = (SELECT "value" FROM "session_state_ephemeral" WHERE "key" = 'current_user'); INSERT INTO "ur_ingest_resource_path_rewrite_rule" ("ur_ingest_resource_path_rewrite_rule_id", "namespace", "regex", "replace", "priority", "description", "elaboration", "created_at", "created_by", "updated_at", "updated_by", "deleted_at", "deleted_by", "activity_log") VALUES ('.plantuml -> .puml', 'default', '(\.plantuml)$', '.puml', NULL, 'Treat .plantuml as .puml files', NULL, (CURRENT_TIMESTAMP), NULL, NULL, NULL, NULL, NULL, NULL) ON CONFLICT DO NOTHING; INSERT INTO "ur_ingest_resource_path_rewrite_rule" ("ur_ingest_resource_path_rewrite_rule_id", "namespace", "regex", "replace", "priority", "description", "elaboration", "created_at", "created_by", "updated_at", "updated_by", "deleted_at", "deleted_by", "activity_log") VALUES ('.text -> .txt', 'default', '(\.text)$', '.txt', NULL, 'Treat .text as .txt files', NULL, (CURRENT_TIMESTAMP), NULL, NULL, NULL, NULL, NULL, NULL) ON CONFLICT DO NOTHING; INSERT INTO "ur_ingest_resource_path_rewrite_rule" ("ur_ingest_resource_path_rewrite_rule_id", "namespace", "regex", "replace", "priority", "description", "elaboration", "created_at", "created_by", "updated_at", "updated_by", "deleted_at", "deleted_by", "activity_log") VALUES ('.yaml -> .yml', 'default', '(\.yaml)$', '.yml', NULL, 'Treat .yaml as .yml files', NULL, (CURRENT_TIMESTAMP), NULL, NULL, NULL, NULL, NULL, NULL) ON CONFLICT DO NOTHING; INSERT INTO "party_type" ("party_type_id", "code", "value", "created_at", "created_by", "updated_at", "updated_by", "deleted_at", "deleted_by", "activity_log") VALUES ((ulid()), 'ORGANIZATION', 'Organization', NULL, NULL, NULL, NULL, NULL, NULL, NULL) ON CONFLICT DO NOTHING; INSERT INTO "party_type" ("party_type_id", "code", "value", "created_at", "created_by", "updated_at", "updated_by", "deleted_at", "deleted_by", "activity_log") VALUES ((ulid()), 'PERSON', 'Person', NULL, NULL, NULL, NULL, NULL, NULL, NULL) ON CONFLICT DO NOTHING; INSERT INTO "orchestration_nature" ("orchestration_nature_id", "nature", "elaboration", "created_at", "created_by", "updated_at", "updated_by", "deleted_at", "deleted_by", "activity_log") VALUES ('surveilr-transform-csv', 'Transform CSV', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL) ON CONFLICT DO NOTHING; INSERT INTO "orchestration_nature" ("orchestration_nature_id", "nature", "elaboration", "created_at", "created_by", "updated_at", "updated_by", "deleted_at", "deleted_by", "activity_log") VALUES ('surveilr-transform-xml', 'Transform XML', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL) ON CONFLICT DO NOTHING; INSERT INTO "orchestration_nature" ("orchestration_nature_id", "nature", "elaboration", "created_at", "created_by", "updated_at", "updated_by", "deleted_at", "deleted_by", "activity_log") VALUES ('surveilr-transform-html', 'Transform HTML', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL) ON CONFLICT DO NOTHING; INSERT INTO "uniform_resource_graph" ("name", "elaboration") VALUES ('filesystem', '{}') ON CONFLICT DO NOTHING; INSERT INTO "uniform_resource_graph" ("name", "elaboration") VALUES ('imap', '{}') ON CONFLICT DO NOTHING; INSERT INTO "uniform_resource_graph" ("name", "elaboration") VALUES ('plm', '{}') ON CONFLICT DO NOTHING; INSERT INTO "uniform_resource_graph" ("name", "elaboration") VALUES ('osquery-ms', '{}') ON CONFLICT DO NOTHING; DROP VIEW IF EXISTS "plm_graph"; CREATE VIEW IF NOT EXISTS "plm_graph" AS SELECT ure.graph_name, ure.nature, ur.uniform_resource_id, ur.uri, ur_ingest_plm.ur_ingest_session_plm_acct_project_issue_id, ur_ingest_plm.issue_id, ur_ingest_plm.ur_ingest_session_plm_acct_project_id as project_id, ur_ingest_plm.title, ur_ingest_plm.body FROM uniform_resource_edge ure JOIN uniform_resource ur ON ure.uniform_resource_id = ur.uniform_resource_id JOIN ur_ingest_session_plm_acct_project_issue ur_ingest_plm ON ure.node_id = ur_ingest_plm.ur_ingest_session_plm_acct_project_issue_id WHERE ure.graph_name = 'plm'; ; DROP VIEW IF EXISTS "imap_graph"; CREATE VIEW IF NOT EXISTS "imap_graph" AS SELECT ure.graph_name, ur.uniform_resource_id, ur.nature, ur.uri, ur.content, ur_ingest_imap.ur_ingest_session_imap_acct_folder_message_id, ur_ingest_imap.ingest_imap_acct_folder_id, ur_ingest_imap.message_id FROM uniform_resource_edge ure JOIN uniform_resource ur ON ure.uniform_resource_id = ur.uniform_resource_id JOIN ur_ingest_session_imap_acct_folder_message ur_ingest_imap ON ure.node_id = ur_ingest_imap.ur_ingest_session_imap_acct_folder_message_id WHERE ure.graph_name = 'imap'; ; DROP VIEW IF EXISTS "filesystem_graph"; CREATE VIEW IF NOT EXISTS "filesystem_graph" AS SELECT ure.graph_name, ur.uniform_resource_id, ur.nature, ur.uri, ur_ingest_fs_path.ur_ingest_session_fs_path_id, ur_ingest_fs_path.root_path FROM uniform_resource_edge ure JOIN uniform_resource ur ON ure.uniform_resource_id = ur.uniform_resource_id JOIN ur_ingest_session_fs_path ur_ingest_fs_path ON ure.node_id = ur_ingest_fs_path.ur_ingest_session_fs_path_id WHERE ure.graph_name = 'filesystem'; ; INSERT INTO "osquery_policy" ("osquery_policy_id", "policy_group", "policy_name", "osquery_code", "policy_description", "policy_pass_label", "policy_fail_label", "policy_pass_remarks", "policy_fail_remarks", "osquery_platforms", "created_at", "created_by", "updated_at", "updated_by", "deleted_at", "deleted_by", "activity_log") VALUES ((ulid()), NULL, 'Ad tracking is limited (macOS)', 'SELECT CASE WHEN EXISTS (SELECT 1 FROM managed_policies WHERE domain=''com.apple.AdLib'' AND name=''forceLimitAdTracking'' AND value=''1'' LIMIT 1) THEN ''true'' ELSE ''false'' END AS policy_result;', 'Checks that a mobile device management (MDM) solution configures the Mac to limit advertisement tracking.', 'Pass', 'Fail', NULL, 'Contact your IT administrator to ensure your Mac is receiving a profile that disables advertisement tracking.', '["darwin"]', NULL, NULL, NULL, NULL, NULL, NULL, NULL) ON CONFLICT DO NOTHING; INSERT INTO "osquery_policy" ("osquery_policy_id", "policy_group", "policy_name", "osquery_code", "policy_description", "policy_pass_label", "policy_fail_label", "policy_pass_remarks", "policy_fail_remarks", "osquery_platforms", "created_at", "created_by", "updated_at", "updated_by", "deleted_at", "deleted_by", "activity_log") VALUES ((ulid()), NULL, 'Antivirus healthy (Linux)', 'SELECT score FROM (SELECT CASE WHEN COUNT(*) = 2 THEN ''true'' ELSE ''false'' END AS score FROM processes WHERE (name = ''clamd'') OR (name = ''freshclam'')) WHERE score = ''true'';', 'Checks that both ClamAV''s daemon and its updater service (freshclam) are running.', 'Pass', 'Fail', NULL, 'Ensure ClamAV and Freshclam are installed and running.', '["linux","windows","darwin"]', NULL, NULL, NULL, NULL, NULL, NULL, NULL) ON CONFLICT DO NOTHING; INSERT INTO "osquery_policy" ("osquery_policy_id", "policy_group", "policy_name", "osquery_code", "policy_description", "policy_pass_label", "policy_fail_label", "policy_pass_remarks", "policy_fail_remarks", "osquery_platforms", "created_at", "created_by", "updated_at", "updated_by", "deleted_at", "deleted_by", "activity_log") VALUES ((ulid()), NULL, 'Antivirus healthy (macOS)', 'SELECT score FROM (SELECT case when COUNT(*) = 2 then 1 ELSE 0 END AS score FROM plist WHERE (key = ''CFBundleShortVersionString'' AND path = ''/Library/Apple/System/Library/CoreServices/XProtect.bundle/Contents/Info.plist'' AND value>=2162) OR (key = ''CFBundleShortVersionString'' AND path = ''/Library/Apple/System/Library/CoreServices/MRT.app/Contents/Info.plist'' and value>=1.93)) WHERE score == 1;', 'Checks the version of Malware Removal Tool (MRT) and the built-in macOS AV (Xprotect). Replace version numbers with the latest version regularly.', 'Pass', 'Fail', NULL, 'To enable automatic security definition updates, on the failing device, select System Preferences > Software Update > Advanced > Turn on Install system data files and security updates.', '["darwin"]', NULL, NULL, NULL, NULL, NULL, NULL, NULL) ON CONFLICT DO NOTHING; INSERT INTO "osquery_policy" ("osquery_policy_id", "policy_group", "policy_name", "osquery_code", "policy_description", "policy_pass_label", "policy_fail_label", "policy_pass_remarks", "policy_fail_remarks", "osquery_platforms", "created_at", "created_by", "updated_at", "updated_by", "deleted_at", "deleted_by", "activity_log") VALUES ((ulid()), NULL, 'Antivirus healthy (Windows)', 'SELECT 1 from windows_security_center wsc CROSS JOIN windows_security_products wsp WHERE antivirus = ''Good'' AND type = ''Antivirus'' AND signatures_up_to_date=1;', 'Checks the status of antivirus and signature updates from the Windows Security Center.', 'Pass', 'Fail', NULL, 'Ensure Windows Defender or your third-party antivirus is running, up to date, and visible in the Windows Security Center.', '["windows"]', NULL, NULL, NULL, NULL, NULL, NULL, NULL) ON CONFLICT DO NOTHING; INSERT INTO "osquery_policy" ("osquery_policy_id", "policy_group", "policy_name", "osquery_code", "policy_description", "policy_pass_label", "policy_fail_label", "policy_pass_remarks", "policy_fail_remarks", "osquery_platforms", "created_at", "created_by", "updated_at", "updated_by", "deleted_at", "deleted_by", "activity_log") VALUES ((ulid()), NULL, 'Automatic installation of application updates is enabled (macOS)', 'SELECT 1 FROM managed_policies WHERE domain=''com.apple.SoftwareUpdate'' AND name=''AutomaticallyInstallAppUpdates'' AND value=1 LIMIT 1;', 'Checks that a mobile device management (MDM) solution configures the Mac to automatically install updates to App Store applications.', 'Pass', 'Fail', NULL, 'Contact your IT administrator to ensure your Mac is receiving a profile that enables automatic installation of application updates.', '["darwin"]', NULL, NULL, NULL, NULL, NULL, NULL, NULL) ON CONFLICT DO NOTHING; INSERT INTO "osquery_policy" ("osquery_policy_id", "policy_group", "policy_name", "osquery_code", "policy_description", "policy_pass_label", "policy_fail_label", "policy_pass_remarks", "policy_fail_remarks", "osquery_platforms", "created_at", "created_by", "updated_at", "updated_by", "deleted_at", "deleted_by", "activity_log") VALUES ((ulid()), NULL, 'Automatic installation of operating system updates is enabled (macOS)', 'SELECT 1 FROM managed_policies WHERE domain=''com.apple.SoftwareUpdate'' AND name=''AutomaticallyInstallMacOSUpdates'' AND value=1 LIMIT 1;', 'Checks that a mobile device management (MDM) solution configures the Mac to automatically install operating system updates.', 'Pass', 'Fail', NULL, 'Contact your IT administrator to ensure your Mac is receiving a profile that enables automatic installation of operating system updates.', '["darwin"]', NULL, NULL, NULL, NULL, NULL, NULL, NULL) ON CONFLICT DO NOTHING; INSERT INTO "osquery_policy" ("osquery_policy_id", "policy_group", "policy_name", "osquery_code", "policy_description", "policy_pass_label", "policy_fail_label", "policy_pass_remarks", "policy_fail_remarks", "osquery_platforms", "created_at", "created_by", "updated_at", "updated_by", "deleted_at", "deleted_by", "activity_log") VALUES ((ulid()), NULL, 'Ensure ''Minimum password length'' is set to ''14 or more characters''', 'SELECT 1 FROM security_profile_info WHERE minimum_password_length >= 14;', 'This policy setting determines the least number of characters that make up a password for a user account.', 'Pass', 'Fail', NULL, 'Automatic method: Ask your system administrator to establish the recommended configuration via GP, set the following UI path to 14 or more characters ''Computer ConfigurationPoliciesWindows SettingsSecurity SettingsAccount PoliciesPassword PolicyMinimum password length''', '["windows"]', NULL, NULL, NULL, NULL, NULL, NULL, NULL) ON CONFLICT DO NOTHING; INSERT INTO code_notebook_state (code_notebook_state_id, code_notebook_cell_id, from_state, to_state, transition_reason, created_at) VALUES ('01JXADE9V3AYS65V03EPJWFEYY__20250907220507', '01JXADE9V3AYS65V03EPJWFEYY', 'MIGRATION_CANDIDATE', 'EXECUTED', 'Migration', CURRENT_TIMESTAMP) ON CONFLICT(code_notebook_cell_id, from_state, to_state) DO UPDATE SET updated_at = CURRENT_TIMESTAMP, transition_reason = 'Reapplied 2025-09-07 22:05:07'; COMMIT;