{"cells":[{"cell_type":"markdown","execution_count":null,"metadata":{"tags":[]},"outputs":[],"source":["RUN600 - Monitor infrastructure (SQL)\n","=====================================\n","\n","Description\n","-----------\n","\n","### Use the `runner` database"]},{"cell_type":"code","execution_count":null,"metadata":{"tags":[]},"outputs":[],"source":["use [runner]"]},{"cell_type":"markdown","execution_count":null,"metadata":{"tags":[]},"outputs":[],"source":["### Get the runner pass rates for the last hour"]},{"cell_type":"code","execution_count":null,"metadata":{"tags":[]},"outputs":[],"source":["WITH last_hour([name], [passed], [failed])\n","AS\n","(\n","    SELECT  [name],\n","        (SELECT count(*) FROM [metrics] r2 WHERE r1.name = r2.name AND r2.error_level = 0 AND session_start \u003e DATEADD(hour, -1, GETDATE())) AS [passed],\n","        (SELECT count(*) FROM [metrics] r2 WHERE r1.name = r2.name AND r2.error_level \u003c\u003e 0 AND session_start \u003e DATEADD(hour, -1, GETDATE())) AS [failed]\n","    FROM [metrics] r1\n","    WHERE session_start \u003e DATEADD(hour, -1, GETDATE())\n","    GROUP BY [name]\n",")\n","SELECT [name], [passed], [failed], [passed] + [failed] as total, CONVERT(DECIMAL(5, 2), ([passed] * 1.0) / ([passed] + [failed]) * 100.0) as [percent]\n","FROM last_hour"]},{"cell_type":"markdown","execution_count":null,"metadata":{"tags":[]},"outputs":[],"source":["### Get the runner pass rates over last 24 hours"]},{"cell_type":"code","execution_count":null,"metadata":{"tags":[]},"outputs":[],"source":["WITH last_24_hours([name], [passed], [failed])\n","AS\n","(\n","    SELECT  [name],\n","        (SELECT count(*) FROM [metrics] r2 WHERE r1.name = r2.name AND r2.error_level = 0 AND session_start \u003e GETDATE() - 1) AS [passed],\n","        (SELECT count(*) FROM [metrics] r2 WHERE r1.name = r2.name AND r2.error_level \u003c\u003e 0 AND session_start \u003e GETDATE() - 1) AS [failed]\n","    FROM [metrics] r1\n","    WHERE session_start \u003e GETDATE() - 1\n","    GROUP BY [name]\n",")\n","SELECT [name], [passed], [failed], [passed] + [failed] as total, CONVERT(DECIMAL(5, 2), ([passed] * 1.0) / ([passed] + [failed]) * 100.0) as [percent]\n","FROM last_24_hours"]},{"cell_type":"markdown","execution_count":null,"metadata":{"tags":[]},"outputs":[],"source":["### Freshness check (when did the last runner reading happen). Check both the data pool, and the copy in master-pool (used for verificaiton purposes)"]},{"cell_type":"code","execution_count":null,"metadata":{"tags":[]},"outputs":[],"source":["SELECT [name], DATEDIFF(s, max(session_start), GETDATE()) as seconds_since_last_reading\n","FROM metrics\n","GROUP BY [name]\n","ORDER BY 2 DESC"]},{"cell_type":"markdown","execution_count":null,"metadata":{"tags":[]},"outputs":[],"source":["### When was the last failures and success for each runner"]},{"cell_type":"code","execution_count":null,"metadata":{"tags":[]},"outputs":[],"source":["SELECT [name], DATEDIFF(s, max(session_start), GETDATE()) as seconds_since_last_failure\n","FROM metrics\n","WHERE error_level \u003c\u003e 0\n","GROUP BY [name]\n","ORDER BY 2 ASC\n","\n","SELECT [name], DATEDIFF(s, max(session_start), GETDATE()) as seconds_since_last_success\n","FROM metrics\n","WHERE error_level = 0\n","GROUP BY [name]\n","ORDER BY 2 ASC"]},{"cell_type":"markdown","execution_count":null,"metadata":{"tags":[]},"outputs":[],"source":["### View the failed runner results (for last 24 hours)"]},{"cell_type":"code","execution_count":null,"metadata":{"tags":[]},"outputs":[],"source":["SELECT datediff(s, session_start, [end]) as durarion, datediff(s, session_start,  GETDATE()) as seconds_ago, * FROM metrics \n","WHERE error_level \u003c\u003e 0 AND session_start \u003e GETDATE() - 1\n","ORDER BY session_start DESC"]},{"cell_type":"code","execution_count":null,"metadata":{"tags":[]},"outputs":[],"source":["print 'Notebook execution is complete.'"]}],"nbformat":4,"nbformat_minor":5,"metadata":{"kernelspec":{"name":"sql","display_name":"SQL"},"pansop":{"related":"","test":{"strategy":"","types":null,"disable":{"reason":"","workitems":null,"types":null}},"target":{"current":"","final":""},"internal":{"parameters":null,"symlink":false},"timeout":"0"},"language_info":{"codemirror_mode":"{ Name: \"\", Version: \"\"}","file_extension":"","mimetype":"","name":"","nbconvert_exporter":"","pygments_lexer":"","version":""},"widgets":[]}}