From c283bcc507b0a7946a8660677a04da6f53cc77e1 Mon Sep 17 00:00:00 2001
From: hrbrmstr <bob@rud.is>
Date: Fri, 4 Sep 2026 10:47:32 -0400
Subject: add: cybersec-llmstxt

---
 2026/cybersec-llmstxt/extract.sql | 37 +++++++++++++++++++++++++++++++++++++
 1 file changed, 37 insertions(+)
 create mode 100644 2026/cybersec-llmstxt/extract.sql

(limited to '2026/cybersec-llmstxt/extract.sql')

diff --git a/2026/cybersec-llmstxt/extract.sql b/2026/cybersec-llmstxt/extract.sql
new file mode 100644
index 0000000..92d4dd1
--- /dev/null
+++ b/2026/cybersec-llmstxt/extract.sql
@@ -0,0 +1,37 @@
+WITH raw AS (
+  SELECT content AS html FROM read_text('page.html')
+),
+anchors AS (
+  SELECT unnest(regexp_extract_all(html, '<a [^>]*href="(https?://[^"]+)"[^>]*>(.*?)</a>', ['1','2'])) AS pair
+  FROM raw
+),
+split AS (
+  SELECT pair['1'] AS url, pair['2'] AS name FROM anchors
+),
+clean AS (
+  SELECT
+    trim(regexp_replace(url, '&amp;', '&', 'g')) AS url,
+    trim(regexp_replace(regexp_replace(name, '<[^>]*>', '', 'g'), '&amp;', '&', 'g')) AS name
+  FROM split
+),
+companies AS (
+  SELECT * FROM clean
+  WHERE url NOT LIKE '%sandiego.edu%'
+    AND url NOT LIKE '%facebook.com%'
+    AND url NOT LIKE '%twitter.com%'
+    AND name NOT SIMILAR TO '(Skip to content|Home|Request Info|Back to Blog.*|Computer Science|Engineering|Technology|Show Me.*|Apply Now|100 Most Influential.*|protect sensitive information|."Best Cybersecurity Company".|"Partner of the Year"|RELATED.*)'
+    AND length(name) BETWEEN 2 AND 60
+    AND url NOT LIKE '%time.com%' AND url NOT LIKE '%liaisoncas.com%' AND url NOT LIKE '%nordvpn.com%' AND url NOT LIKE '%excellence-awards.com%' AND url NOT LIKE '%careers.trellix.com%' AND url NOT LIKE '%cyderes.com/news-feed%'
+)
+SELECT name, url, regexp_replace(host, '^www\.', '') AS domain
+FROM (
+  SELECT name, url2 AS url,
+         regexp_extract(url2, 'https?://([^/]+)', 1) AS host
+  FROM (
+    SELECT name,
+           CASE WHEN url LIKE '%herjavecgroup.com%' THEN 'https://www.cyderes.com/' ELSE url END AS url2
+    FROM companies
+  ) a
+) t
+QUALIFY row_number() OVER (PARTITION BY regexp_replace(regexp_extract(url, 'https?://([^/]+)', 1), '^www\.', '') ORDER BY length(url)) = 1
+ORDER BY name;
-- 
cgit v1.2.3

