code_notebook_sql_cell_migratable_not_executed (view) Content
| 01JQHA2GMV1BFGD680E78XD0KY | SQL | ConstructionSqlNotebook | session_ephemeral_table | CREATE TEMP TABLE IF NOT EXISTS "session_state_ephemeral" ( "key" TEXT PRIMARY KEY NOT NULL, "value" TEXT NOT NULL ); | b739acd000cf37091bbb365085506f975345351d | 2025-04-04 10:08:42 | 1 | ||||
| 01JQHA2GMVZZWD8Y5M7BZMCR60 | SQL | ConstructionSqlNotebook | surveilr_table_size | 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; | 0213397f35c9eaf525cce0fa6276a02243f5d877 | 2025-04-04 10:08:42 | 1 | ||||
| 01JQHA2GMVBNJQ7JDD6JVF3N8E | SQL | ConstructionSqlNotebook | v001_seedDML | 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|pdf|docx|doc|pptx|ppt|xlsx|xls)$', 'CONTENT_ACQUIRABLE', '?P<nature>', NULL, 'Ingest the content for md, mdx, html, json, jsonc, puml, txt, toml, and yml extensions. 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_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.', '["macos"]', 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","macos"]', 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.', '["macos"]', 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.', '["macos"]', 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.', '["macos"]', 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; | 5feb5b3fc2a9da3baf805c97a0901d89ef76d0c0 | 2025-04-04 10:08:42 | 1 |
(Page 1 of 1)