aboutsummaryrefslogtreecommitdiffstats
path: root/sql
diff options
context:
space:
mode:
authorBryan Newbold <bnewbold@archive.org>2020-11-03 11:27:16 -0800
committerBryan Newbold <bnewbold@archive.org>2020-11-03 11:27:16 -0800
commitbd9075adef2733df046621ef799c3b29e00fac57 (patch)
treeaebd23370a06f2c676d67322e348058964277701 /sql
parentbc22da30e379221c31ce466f76e1e3f048d3bcc8 (diff)
downloadsandcrawler-bd9075adef2733df046621ef799c3b29e00fac57.tar.gz
sandcrawler-bd9075adef2733df046621ef799c3b29e00fac57.zip
monitoring: past-7-days summary query
Diffstat (limited to 'sql')
-rw-r--r--sql/monitoring_queries.md26
1 files changed, 26 insertions, 0 deletions
diff --git a/sql/monitoring_queries.md b/sql/monitoring_queries.md
index 1738731..1c872cc 100644
--- a/sql/monitoring_queries.md
+++ b/sql/monitoring_queries.md
@@ -39,6 +39,32 @@ Broken domains, past 30 days:
ORDER BY COUNT DESC
LIMIT 25;
+Summary of significant domains and status, past 7 days:
+
+ SELECT domain, status, count
+ FROM (
+ SELECT domain, status, COUNT((domain, status)) as count
+ FROM (
+ SELECT
+ ingest_file_result.ingest_type,
+ ingest_file_result.status,
+ substring(ingest_file_result.terminal_url FROM '[^/]+://([^/]*)') AS domain
+ FROM ingest_file_result
+ LEFT JOIN ingest_request
+ ON ingest_file_result.ingest_type = ingest_request.ingest_type
+ AND ingest_file_result.base_url = ingest_request.base_url
+ WHERE
+ ingest_file_result.updated >= NOW() - '7 day'::INTERVAL
+ AND ingest_request.ingest_type = 'pdf'
+ AND ingest_request.ingest_request_source = 'fatcat-changelog'
+ ) t1
+ WHERE t1.domain != ''
+ GROUP BY CUBE (domain, status)
+ ) t2
+ WHERE count > 500
+ ORDER BY domain ASC , count DESC;
+
+
Throughput per day, and success, for past 30 days:
SELECT ingest_request.ingest_type,