add: cybersec-llmstxt

1 files changed, 37 insertions, 0 deletions

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;