aboutsummaryrefslogtreecommitdiffstats
path: root/sql
diff options
context:
space:
mode:
Diffstat (limited to 'sql')
-rw-r--r--sql/monitoring_queries.md34
1 files changed, 29 insertions, 5 deletions
diff --git a/sql/monitoring_queries.md b/sql/monitoring_queries.md
index 35eef8f..fd0ce51 100644
--- a/sql/monitoring_queries.md
+++ b/sql/monitoring_queries.md
@@ -27,7 +27,9 @@ Broken domains, past 3 days:
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_request.created >= NOW() - '3 day'::INTERVAL
+ WHERE
+ -- ingest_request.created >= NOW() - '3 day'::INTERVAL
+ ingest_file_result.updated >= NOW() - '3 day'::INTERVAL
AND ingest_request.ingest_type = 'pdf'
AND ingest_request.ingest_request_source = 'fatcat-changelog'
) t1
@@ -50,8 +52,8 @@ Throughput per day, and success, for past month:
WHERE ingest_request.created >= NOW() - '1 month'::INTERVAL
AND ingest_request.ingest_type = 'pdf'
AND ingest_request.ingest_request_source = 'fatcat-changelog'
- GROUP BY ingest_request.ingest_type, ingest_file_result.ingest_type, date(ingest_file_result.updated)
- ORDER BY date(ingest_file_result.updated) DESC;
+ GROUP BY ingest_request.ingest_type, ingest_file_result.ingest_type, date(ingest_request.created)
+ ORDER BY date(ingest_request.created) DESC;
## fatcat-ingest
@@ -67,7 +69,9 @@ Broken domains, past 7 days:
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_request.created >= NOW() - '7 day'::INTERVAL
+ WHERE
+ -- ingest_request.created >= NOW() - '7 day'::INTERVAL
+ ingest_file_result.updated >= NOW() - '24 hour'::INTERVAL
AND ingest_request.ingest_type = 'pdf'
AND ingest_request.ingest_request_source = 'fatcat-ingest'
) t1
@@ -87,8 +91,28 @@ Throughput per day, and success, for past 7 days:
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_request.created >= NOW() - '7 day'::INTERVAL
+ WHERE
+ -- ingest_request.created >= NOW() - '7 day'::INTERVAL
+ ingest_file_result.updated >= NOW() - '24 hour'::INTERVAL
AND ingest_request.ingest_type = 'pdf'
AND ingest_request.ingest_request_source = 'fatcat-ingest'
GROUP BY ingest_request.ingest_type, ingest_file_result.ingest_type, date(ingest_file_result.updated)
ORDER BY date(ingest_file_result.updated) DESC;
+
+Overall status, updated requests past 3 days:
+
+ SELECT ingest_request.ingest_type,
+ ingest_file_result.status,
+ COUNT(*)
+ 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() - '3 day'::INTERVAL
+ ingest_file_result.updated >= NOW() - '48 hour'::INTERVAL
+ AND ingest_request.ingest_type = 'pdf'
+ AND ingest_request.ingest_request_source = 'fatcat-ingest'
+ GROUP BY ingest_request.ingest_type, ingest_file_result.status
+ ORDER BY COUNT(*) DESC;
+