diff options
| -rw-r--r-- | sql/monitoring_queries.md | 34 |
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; + |