From 35ebee00c38436ea8c8a075689d987d80841255a Mon Sep 17 00:00:00 2001
From: Bryan Newbold <bnewbold@archive.org>
Date: Mon, 30 Mar 2020 16:34:54 -0700
Subject: more monitoring queries

---
 sql/monitoring_queries.md | 34 +++++++++++++++++++++++++++++-----
 1 file changed, 29 insertions(+), 5 deletions(-)

(limited to 'sql')

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;
+
-- 
cgit v1.2.3