Skip to content

Instantly share code, notes, and snippets.

@flyck

flyck/analyzing-gitlab-metrics-sqlite-grafana-guide.md

Last active March 12, 2024 19:15
Show Gist options
  • Save flyck/e3deb7db07a5817dfb3a5c49b205a1c4 to your computer and use it in GitHub Desktop.
Save flyck/e3deb7db07a5817dfb3a5c49b205a1c4 to your computer and use it in GitHub Desktop.
Download ZIP
How to analyse gitlab job metrics via grafana and sqlite
Raw
analyzing-gitlab-metrics-sqlite-grafana-guide.md

How to analyse gitlab CI/CD job data with Grafana and SQLite

We're using bun for the local runtime, so we'll need a bun init:

bun init

Then we can copy the followign code into index.ts:

import SQLite from 'bun:sqlite';

// Environment variables for configuration
const REPO_PATH = 'path/to/repo';
const GITLAB_TOKEN = Bun.env.GITLAB_TOKEN;
const PAGE_SIZE = 100;
const JOB_LIMIT = 1000;
const DB_PATH = 'jobs_database.sqlite';
const GRAPHQL_ENDPOINT = 'https://gitlab.com/api/graphql';

async function fetchJobs(after?: string): Promise<{ jobs: JobData[]; nextCursor?: string }> {
  const query = `  query jobs($after: String, $first: Int) {
        project(fullPath: "${REPO_PATH}") {
          jobs(statuses: [SUCCESS, FAILED], after: $after, first: $first) {
            pageInfo {
              startCursor
              endCursor
            }
            nodes {
              id
              name
              status
              startedAt
              finishedAt
              duration
              queuedDuration
              pipeline {
                id
                status
                computeMinutes
                duration
                complete
              }
            }
          }
        }
      }`;
  const variables = after ? { after, first: PAGE_SIZE } : { first: PAGE_SIZE };

  const response = await fetch(GRAPHQL_ENDPOINT, {
    method: 'POST',
    headers: {
      'Content-Type': 'application/json',
      'Authorization': `Bearer ${GITLAB_TOKEN}`,
    },
    body: JSON.stringify({
      query,
      variables,
    }),
  });

  const { data } = (await response.json()) as any;
  const jobs: JobData[] = data.project.jobs.nodes;
  const nextCursor = data.project.jobs.pageInfo.endCursor;

  return { jobs, nextCursor };
}

interface JobData {
  id: string;
  name: string;
  status: string;
  startedAt: string;
  finishedAt: string;
  duration: number;
  queuedDuration: number;
  pipeline: {
    id: string;
    status: string;
    computeMinutes: number;
    duration: number;
    complete: boolean;
  };
}

// SQLite Database initialization
const db = new SQLite(DB_PATH);
db.query(`
  CREATE TABLE IF NOT EXISTS jobs (
    id TEXT PRIMARY KEY,
    name TEXT,
    status TEXT,
    startedAt TEXT,
    finishedAt TEXT,
    duration INTEGER,
    queuedDuration REAL,
    pipelineId TEXT,
    pipelineStatus TEXT,
    pipelineComputeMinutes REAL,
    pipelineDuration INTEGER,
    pipelineComplete INTEGER
  );
`).run();


function insertJobs(jobs: JobData[]) {
  jobs.forEach(job => {
    const query = db.query(`
      INSERT INTO jobs (id, name, status, startedAt, finishedAt, duration, queuedDuration, pipelineId, pipelineStatus, pipelineComputeMinutes, pipelineDuration, pipelineComplete)
      VALUES (?1, ?2, ?3, ?4, ?5, ?6, ?7, ?8, ?9, ?10, ?11, ?12)
`);
    query.run(
      job.id,
      job.name,
      job.status,
      job.startedAt,
      job.finishedAt,
      job.duration,
      job.queuedDuration,
      job.pipeline.id,
      job.pipeline.status,
      job.pipeline.computeMinutes,
      job.pipeline.duration,
      job.pipeline.complete ? 1 : 0,
    )

  });
}

async function runIngestionProcess() {
  let endCursor: string | undefined = undefined;
  let jobsCounter = 0;
  do {
    const { jobs, nextCursor } = await fetchJobs(endCursor);
    insertJobs(jobs);
    jobsCounter += PAGE_SIZE;
    console.log(`Retrieved ${jobsCounter} jobs`)
    endCursor = nextCursor;
  } while (endCursor && jobsCounter < JOB_LIMIT);
  db.close();
}

runIngestionProcess();

Next we can start grafana:

mkdir data
cp jobs_database.sqlite data/

docker run -d -p 3000:3000 --name=grafana \
  --user "$(id -u)" \
  --volume "$PWD/data:/var/lib/grafana" \
  -e "GF_INSTALL_PLUGINS=frser-sqlite-datasource" \
  grafana/grafana-enterprise

Once started we can add sqlite as a datasource. The path here is:

/var/lib/grafana/jobs_database.sqlite

Once this is done we can set up dashboards for analysis of the given job data.

SELECT * from jobs order by startedAt limit 10;

Our default grafana dashboard which can be easily imported via the grafana UI:

{
  "__inputs": [
    {
      "name": "DS_FRSER-SQLITE-DATASOURCE",
      "label": "frser-sqlite-datasource",
      "description": "",
      "type": "datasource",
      "pluginId": "frser-sqlite-datasource",
      "pluginName": "SQLite"
    }
  ],
  "__elements": {},
  "__requires": [
    {
      "type": "datasource",
      "id": "frser-sqlite-datasource",
      "name": "SQLite",
      "version": "3.4.0"
    },
    {
      "type": "grafana",
      "id": "grafana",
      "name": "Grafana",
      "version": "10.3.3"
    },
    {
      "type": "panel",
      "id": "stat",
      "name": "Stat",
      "version": ""
    },
    {
      "type": "panel",
      "id": "table",
      "name": "Table",
      "version": ""
    },
    {
      "type": "panel",
      "id": "text",
      "name": "Text",
      "version": ""
    },
    {
      "type": "panel",
      "id": "timeseries",
      "name": "Time series",
      "version": ""
    }
  ],
  "annotations": {
    "list": [
      {
        "builtIn": 1,
        "datasource": {
          "type": "grafana",
          "uid": "-- Grafana --"
        },
        "enable": true,
        "hide": true,
        "iconColor": "rgba(0, 211, 255, 1)",
        "name": "Annotations & Alerts",
        "type": "dashboard"
      }
    ]
  },
  "editable": true,
  "fiscalYearStartMonth": 0,
  "graphTooltip": 0,
  "id": null,
  "links": [],
  "liveNow": false,
  "panels": [
    {
      "collapsed": false,
      "gridPos": {
        "h": 1,
        "w": 24,
        "x": 0,
        "y": 0
      },
      "id": 8,
      "panels": [],
      "title": "Job Stats",
      "type": "row"
    },
    {
      "datasource": {
        "type": "frser-sqlite-datasource",
        "uid": "${DS_FRSER-SQLITE-DATASOURCE}"
      },
      "fieldConfig": {
        "defaults": {
          "color": {
            "mode": "palette-classic"
          },
          "custom": {
            "axisBorderShow": false,
            "axisCenteredZero": false,
            "axisColorMode": "text",
            "axisLabel": "",
            "axisPlacement": "auto",
            "barAlignment": 0,
            "drawStyle": "line",
            "fillOpacity": 0,
            "gradientMode": "none",
            "hideFrom": {
              "legend": false,
              "tooltip": false,
              "viz": false
            },
            "insertNulls": false,
            "lineInterpolation": "linear",
            "lineWidth": 1,
            "pointSize": 5,
            "scaleDistribution": {
              "type": "linear"
            },
            "showPoints": "auto",
            "spanNulls": false,
            "stacking": {
              "group": "A",
              "mode": "none"
            },
            "thresholdsStyle": {
              "mode": "off"
            }
          },
          "mappings": [],
          "thresholds": {
            "mode": "absolute",
            "steps": [
              {
                "color": "green",
                "value": null
              },
              {
                "color": "red",
                "value": 80
              }
            ]
          },
          "unitScale": true
        },
        "overrides": []
      },
      "gridPos": {
        "h": 8,
        "w": 12,
        "x": 0,
        "y": 1
      },
      "id": 14,
      "options": {
        "legend": {
          "calcs": [],
          "displayMode": "list",
          "placement": "bottom",
          "showLegend": true
        },
        "tooltip": {
          "mode": "single",
          "sort": "none"
        }
      },
      "pluginVersion": "10.3.3",
      "targets": [
        {
          "datasource": {
            "type": "frser-sqlite-datasource",
            "uid": "${DS_FRSER-SQLITE-DATASOURCE}"
          },
          "queryText": "SELECT \n    strftime('%Y-%m-%dT00:00:00Z', startedAt) AS time,\n    COUNT(*) as amount, \n    name as metric\nFROM jobs\nWHERE name in (\"unit_integration_test\", \"build\") AND status = \"SUCCESS\"\nGROUP BY name, time\nORDER BY time;",
          "queryType": "time series",
          "rawQueryText": "SELECT \n    strftime('%Y-%m-%dT00:00:00Z', startedAt) AS time,\n    COUNT(*) as amount, \n    name as metric\nFROM jobs\nWHERE name in (\"unit_integration_test\", \"build\") AND status = \"SUCCESS\"\nGROUP BY name, time\nORDER BY time;",
          "refId": "A",
          "timeColumns": [
            "time",
            "ts",
            "startedAt"
          ]
        }
      ],
      "title": "Executed Jobs",
      "type": "timeseries"
    },
    {
      "datasource": {
        "type": "frser-sqlite-datasource",
        "uid": "${DS_FRSER-SQLITE-DATASOURCE}"
      },
      "fieldConfig": {
        "defaults": {
          "color": {
            "mode": "palette-classic"
          },
          "custom": {
            "axisBorderShow": false,
            "axisCenteredZero": false,
            "axisColorMode": "text",
            "axisLabel": "Minutes",
            "axisPlacement": "auto",
            "barAlignment": 0,
            "drawStyle": "line",
            "fillOpacity": 0,
            "gradientMode": "none",
            "hideFrom": {
              "legend": false,
              "tooltip": false,
              "viz": false
            },
            "insertNulls": false,
            "lineInterpolation": "linear",
            "lineWidth": 1,
            "pointSize": 5,
            "scaleDistribution": {
              "type": "linear"
            },
            "showPoints": "auto",
            "spanNulls": false,
            "stacking": {
              "group": "A",
              "mode": "none"
            },
            "thresholdsStyle": {
              "mode": "off"
            }
          },
          "mappings": [],
          "thresholds": {
            "mode": "absolute",
            "steps": [
              {
                "color": "green",
                "value": null
              },
              {
                "color": "red",
                "value": 80
              }
            ]
          },
          "unitScale": true
        },
        "overrides": []
      },
      "gridPos": {
        "h": 8,
        "w": 12,
        "x": 12,
        "y": 1
      },
      "id": 11,
      "options": {
        "legend": {
          "calcs": [],
          "displayMode": "list",
          "placement": "bottom",
          "showLegend": true
        },
        "tooltip": {
          "mode": "single",
          "sort": "none"
        }
      },
      "pluginVersion": "10.3.3",
      "targets": [
        {
          "datasource": {
            "type": "frser-sqlite-datasource",
            "uid": "${DS_FRSER-SQLITE-DATASOURCE}"
          },
          "queryText": "SELECT \n    startedAt as time,\n    strftime('%Y-%m-%dT00:00:00Z', startedAt) AS hour_start,\n    SUM(queuedDuration / 60) AS job_queued_sum\nFROM jobs\nGROUP BY hour_start\nORDER BY hour_start;",
          "queryType": "table",
          "rawQueryText": "SELECT \n    startedAt as time,\n    strftime('%Y-%m-%dT00:00:00Z', startedAt) AS hour_start,\n    SUM(queuedDuration / 60) AS job_queued_sum\nFROM jobs\nGROUP BY hour_start\nORDER BY hour_start;",
          "refId": "A",
          "timeColumns": [
            "time",
            "ts",
            "startedAt"
          ]
        }
      ],
      "title": "Dead Waiting times",
      "type": "timeseries"
    },
    {
      "datasource": {
        "type": "frser-sqlite-datasource",
        "uid": "${DS_FRSER-SQLITE-DATASOURCE}"
      },
      "fieldConfig": {
        "defaults": {
          "color": {
            "mode": "palette-classic"
          },
          "custom": {
            "axisBorderShow": false,
            "axisCenteredZero": false,
            "axisColorMode": "text",
            "axisLabel": "Minutes",
            "axisPlacement": "auto",
            "barAlignment": 0,
            "drawStyle": "line",
            "fillOpacity": 0,
            "gradientMode": "none",
            "hideFrom": {
              "legend": false,
              "tooltip": false,
              "viz": false
            },
            "insertNulls": false,
            "lineInterpolation": "linear",
            "lineWidth": 1,
            "pointSize": 5,
            "scaleDistribution": {
              "type": "linear"
            },
            "showPoints": "auto",
            "spanNulls": false,
            "stacking": {
              "group": "A",
              "mode": "none"
            },
            "thresholdsStyle": {
              "mode": "off"
            }
          },
          "mappings": [],
          "thresholds": {
            "mode": "absolute",
            "steps": [
              {
                "color": "green",
                "value": null
              },
              {
                "color": "red",
                "value": 80
              }
            ]
          },
          "unitScale": true
        },
        "overrides": []
      },
      "gridPos": {
        "h": 8,
        "w": 12,
        "x": 0,
        "y": 9
      },
      "id": 13,
      "options": {
        "legend": {
          "calcs": [],
          "displayMode": "list",
          "placement": "bottom",
          "showLegend": true
        },
        "tooltip": {
          "mode": "single",
          "sort": "none"
        }
      },
      "pluginVersion": "10.3.3",
      "targets": [
        {
          "datasource": {
            "type": "frser-sqlite-datasource",
            "uid": "${DS_FRSER-SQLITE-DATASOURCE}"
          },
          "queryText": "SELECT \n    startedAt as time,\n    strftime('%Y-%m-%dT%H:00:00Z', startedAt) AS hour_start,\n    MAX(duration / 60.0) AS max_duration_minutes,\n    AVG(duration / 60.0) AS avg_duration_minutes,\n    MIN(duration / 60.0) AS min_duration_minutes\nFROM jobs\nWHERE name = \"build\" AND status = \"SUCCESS\"\nGROUP BY hour_start\nORDER BY hour_start;",
          "queryType": "table",
          "rawQueryText": "SELECT \n    startedAt as time,\n    strftime('%Y-%m-%dT%H:00:00Z', startedAt) AS hour_start,\n    MAX(duration / 60.0) AS max_duration_minutes,\n    AVG(duration / 60.0) AS avg_duration_minutes,\n    MIN(duration / 60.0) AS min_duration_minutes\nFROM jobs\nWHERE name = \"build\" AND status = \"SUCCESS\"\nGROUP BY hour_start\nORDER BY hour_start;",
          "refId": "A",
          "timeColumns": [
            "time",
            "ts",
            "startedAt"
          ]
        }
      ],
      "title": "Build Job Duration",
      "type": "timeseries"
    },
    {
      "datasource": {
        "type": "frser-sqlite-datasource",
        "uid": "${DS_FRSER-SQLITE-DATASOURCE}"
      },
      "fieldConfig": {
        "defaults": {
          "color": {
            "mode": "palette-classic"
          },
          "custom": {
            "axisBorderShow": false,
            "axisCenteredZero": false,
            "axisColorMode": "text",
            "axisLabel": "Minutes",
            "axisPlacement": "auto",
            "barAlignment": 0,
            "drawStyle": "line",
            "fillOpacity": 0,
            "gradientMode": "none",
            "hideFrom": {
              "legend": false,
              "tooltip": false,
              "viz": false
            },
            "insertNulls": false,
            "lineInterpolation": "linear",
            "lineWidth": 1,
            "pointSize": 5,
            "scaleDistribution": {
              "type": "linear"
            },
            "showPoints": "auto",
            "spanNulls": false,
            "stacking": {
              "group": "A",
              "mode": "none"
            },
            "thresholdsStyle": {
              "mode": "off"
            }
          },
          "mappings": [],
          "thresholds": {
            "mode": "absolute",
            "steps": [
              {
                "color": "green",
                "value": null
              },
              {
                "color": "red",
                "value": 80
              }
            ]
          },
          "unitScale": true
        },
        "overrides": []
      },
      "gridPos": {
        "h": 8,
        "w": 12,
        "x": 12,
        "y": 9
      },
      "id": 18,
      "options": {
        "legend": {
          "calcs": [],
          "displayMode": "list",
          "placement": "bottom",
          "showLegend": true
        },
        "tooltip": {
          "mode": "single",
          "sort": "none"
        }
      },
      "pluginVersion": "10.3.3",
      "targets": [
        {
          "datasource": {
            "type": "frser-sqlite-datasource",
            "uid": "${DS_FRSER-SQLITE-DATASOURCE}"
          },
          "queryText": "SELECT \n    startedAt as time,\n    strftime('%Y-%m-%dT%H:00:00Z', startedAt) AS hour_start,\n    MAX(duration / 60.0) AS max_duration_minutes,\n    AVG(duration / 60.0) AS avg_duration_minutes,\n    MIN(duration / 60.0) AS min_duration_minutes\nFROM jobs\nWHERE name = \"unit_integration_test\" AND status = \"SUCCESS\"\nGROUP BY hour_start\nORDER BY hour_start;",
          "queryType": "table",
          "rawQueryText": "SELECT \n    startedAt as time,\n    strftime('%Y-%m-%dT%H:00:00Z', startedAt) AS hour_start,\n    MAX(duration / 60.0) AS max_duration_minutes,\n    AVG(duration / 60.0) AS avg_duration_minutes,\n    MIN(duration / 60.0) AS min_duration_minutes\nFROM jobs\nWHERE name = \"unit_integration_test\" AND status = \"SUCCESS\"\nGROUP BY hour_start\nORDER BY hour_start;",
          "refId": "A",
          "timeColumns": [
            "time",
            "ts",
            "startedAt"
          ]
        }
      ],
      "title": "Test Job Duration",
      "type": "timeseries"
    },
    {
      "collapsed": false,
      "gridPos": {
        "h": 1,
        "w": 24,
        "x": 0,
        "y": 17
      },
      "id": 6,
      "panels": [],
      "title": "Pipeline Stats",
      "type": "row"
    },
    {
      "datasource": {
        "type": "frser-sqlite-datasource",
        "uid": "${DS_FRSER-SQLITE-DATASOURCE}"
      },
      "fieldConfig": {
        "defaults": {
          "color": {
            "mode": "thresholds"
          },
          "mappings": [],
          "thresholds": {
            "mode": "absolute",
            "steps": [
              {
                "color": "#000000bf",
                "value": null
              }
            ]
          },
          "unitScale": true
        },
        "overrides": []
      },
      "gridPos": {
        "h": 8,
        "w": 4,
        "x": 0,
        "y": 18
      },
      "id": 7,
      "options": {
        "colorMode": "value",
        "graphMode": "area",
        "justifyMode": "auto",
        "orientation": "auto",
        "reduceOptions": {
          "calcs": [
            "lastNotNull"
          ],
          "fields": "/.*/",
          "values": false
        },
        "showPercentChange": false,
        "textMode": "auto",
        "wideLayout": true
      },
      "pluginVersion": "10.3.3",
      "targets": [
        {
          "datasource": {
            "type": "frser-sqlite-datasource",
            "uid": "${DS_FRSER-SQLITE-DATASOURCE}"
          },
          "queryText": "SELECT COUNT() FROM (\nSELECT DISTINCT pipelineId from jobs \n)",
          "queryType": "table",
          "rawQueryText": "SELECT COUNT() FROM (\nSELECT DISTINCT pipelineId from jobs \n)",
          "refId": "A",
          "timeColumns": [
            "time",
            "ts"
          ]
        }
      ],
      "title": "Total Pipelines",
      "type": "stat"
    },
    {
      "datasource": {
        "type": "frser-sqlite-datasource",
        "uid": "${DS_FRSER-SQLITE-DATASOURCE}"
      },
      "fieldConfig": {
        "defaults": {
          "color": {
            "mode": "thresholds"
          },
          "mappings": [],
          "thresholds": {
            "mode": "absolute",
            "steps": [
              {
                "color": "semi-dark-blue",
                "value": null
              }
            ]
          },
          "unitScale": true
        },
        "overrides": []
      },
      "gridPos": {
        "h": 8,
        "w": 4,
        "x": 4,
        "y": 18
      },
      "id": 17,
      "options": {
        "colorMode": "value",
        "graphMode": "area",
        "justifyMode": "auto",
        "orientation": "auto",
        "reduceOptions": {
          "calcs": [
            "lastNotNull"
          ],
          "fields": "/.*/",
          "values": false
        },
        "showPercentChange": false,
        "textMode": "auto",
        "wideLayout": true
      },
      "pluginVersion": "10.3.3",
      "targets": [
        {
          "datasource": {
            "type": "frser-sqlite-datasource",
            "uid": "${DS_FRSER-SQLITE-DATASOURCE}"
          },
          "queryText": "SELECT COUNT() FROM (\nSELECT DISTINCT pipelineId, pipelineStatus from jobs \n)\nWHERE pipelineStatus == \"SUCCESS\"",
          "queryType": "table",
          "rawQueryText": "SELECT COUNT() FROM (\nSELECT DISTINCT pipelineId, pipelineStatus from jobs \n)\nWHERE pipelineStatus == \"SUCCESS\"",
          "refId": "A",
          "timeColumns": [
            "time",
            "ts"
          ]
        }
      ],
      "title": "Succeeded Pipelines",
      "type": "stat"
    },
    {
      "datasource": {
        "type": "frser-sqlite-datasource",
        "uid": "${DS_FRSER-SQLITE-DATASOURCE}"
      },
      "fieldConfig": {
        "defaults": {
          "color": {
            "mode": "thresholds"
          },
          "mappings": [],
          "thresholds": {
            "mode": "absolute",
            "steps": [
              {
                "color": "green",
                "value": null
              },
              {
                "color": "semi-dark-yellow",
                "value": 0
              }
            ]
          },
          "unitScale": true
        },
        "overrides": []
      },
      "gridPos": {
        "h": 8,
        "w": 4,
        "x": 8,
        "y": 18
      },
      "id": 16,
      "options": {
        "colorMode": "value",
        "graphMode": "area",
        "justifyMode": "auto",
        "orientation": "auto",
        "reduceOptions": {
          "calcs": [
            "lastNotNull"
          ],
          "fields": "/.*/",
          "values": false
        },
        "showPercentChange": false,
        "textMode": "auto",
        "wideLayout": true
      },
      "pluginVersion": "10.3.3",
      "targets": [
        {
          "datasource": {
            "type": "frser-sqlite-datasource",
            "uid": "${DS_FRSER-SQLITE-DATASOURCE}"
          },
          "queryText": "SELECT COUNT() FROM (\nSELECT DISTINCT pipelineId, pipelineStatus from jobs \n)\nWHERE pipelineStatus == \"FAILED\"",
          "queryType": "table",
          "rawQueryText": "SELECT COUNT() FROM (\nSELECT DISTINCT pipelineId, pipelineStatus from jobs \n)\nWHERE pipelineStatus == \"FAILED\"",
          "refId": "A",
          "timeColumns": [
            "time",
            "ts"
          ]
        }
      ],
      "title": "Failed Pipelines",
      "type": "stat"
    },
    {
      "datasource": {
        "type": "frser-sqlite-datasource",
        "uid": "${DS_FRSER-SQLITE-DATASOURCE}"
      },
      "gridPos": {
        "h": 8,
        "w": 12,
        "x": 12,
        "y": 18
      },
      "id": 12,
      "options": {
        "code": {
          "language": "plaintext",
          "showLineNumbers": false,
          "showMiniMap": false
        },
        "content": "Trying to answer:\n\n- Job waiting times\n- Core job duration trends (build & test)\n- Core job execution amounts\n- Total Pipeline time per day\n- Pipeline duration trends (difficult with manual jobs)\n  - should indicate general pipeline speed improvements\n- Amount of successful & failed pipelines (minor)",
        "mode": "markdown"
      },
      "pluginVersion": "10.3.3",
      "title": "Notes",
      "type": "text"
    },
    {
      "datasource": {
        "type": "frser-sqlite-datasource",
        "uid": "${DS_FRSER-SQLITE-DATASOURCE}"
      },
      "description": "Pipeline ran all the way to the end with no manual actions pending",
      "fieldConfig": {
        "defaults": {
          "color": {
            "mode": "palette-classic"
          },
          "custom": {
            "axisBorderShow": false,
            "axisCenteredZero": false,
            "axisColorMode": "text",
            "axisLabel": "",
            "axisPlacement": "auto",
            "barAlignment": 0,
            "drawStyle": "line",
            "fillOpacity": 0,
            "gradientMode": "none",
            "hideFrom": {
              "legend": false,
              "tooltip": false,
              "viz": false
            },
            "insertNulls": false,
            "lineInterpolation": "linear",
            "lineWidth": 1,
            "pointSize": 5,
            "scaleDistribution": {
              "type": "linear"
            },
            "showPoints": "auto",
            "spanNulls": false,
            "stacking": {
              "group": "A",
              "mode": "none"
            },
            "thresholdsStyle": {
              "mode": "off"
            }
          },
          "mappings": [],
          "thresholds": {
            "mode": "absolute",
            "steps": [
              {
                "color": "green"
              },
              {
                "color": "red",
                "value": 80
              }
            ]
          },
          "unitScale": true
        },
        "overrides": []
      },
      "gridPos": {
        "h": 8,
        "w": 12,
        "x": 0,
        "y": 26
      },
      "id": 20,
      "options": {
        "legend": {
          "calcs": [],
          "displayMode": "list",
          "placement": "bottom",
          "showLegend": true
        },
        "tooltip": {
          "mode": "single",
          "sort": "none"
        }
      },
      "pluginVersion": "10.3.3",
      "targets": [
        {
          "datasource": {
            "type": "frser-sqlite-datasource",
            "uid": "${DS_FRSER-SQLITE-DATASOURCE}"
          },
          "queryText": "SELECT \n    startedAt as time,\n    strftime('%Y-%m-%dT%H:00:00Z', startedAt) AS hour_start,\n    MAX(pipelineDuration / 60) AS max_duration_minutes,\n    AVG(pipelineDuration / 60) AS avg_duration_minutes,\n    MIN(pipelineDuration / 60) AS min_duration_minutes\nFROM (\nSELECT DISTINCT pipelineId, pipelineStatus, startedAt, pipelineDuration from jobs WHERE pipelineStatus==\"SUCCESS\"\n)\nGROUP BY hour_start\nORDER BY hour_start;",
          "queryType": "table",
          "rawQueryText": "SELECT \n    startedAt as time,\n    strftime('%Y-%m-%dT%H:00:00Z', startedAt) AS hour_start,\n    MAX(pipelineDuration / 60) AS max_duration_minutes,\n    AVG(pipelineDuration / 60) AS avg_duration_minutes,\n    MIN(pipelineDuration / 60) AS min_duration_minutes\nFROM (\nSELECT DISTINCT pipelineId, pipelineStatus, startedAt, pipelineDuration from jobs WHERE pipelineStatus==\"SUCCESS\"\n)\nGROUP BY hour_start\nORDER BY hour_start;",
          "refId": "A",
          "timeColumns": [
            "time",
            "ts",
            "startedAt"
          ]
        }
      ],
      "title": "Successfull Pipeline Duration",
      "type": "timeseries"
    },
    {
      "datasource": {
        "type": "frser-sqlite-datasource",
        "uid": "${DS_FRSER-SQLITE-DATASOURCE}"
      },
      "description": "Pipeline ran all the way to the end with no manual actions pending",
      "fieldConfig": {
        "defaults": {
          "color": {
            "mode": "palette-classic"
          },
          "custom": {
            "axisBorderShow": false,
            "axisCenteredZero": false,
            "axisColorMode": "text",
            "axisLabel": "",
            "axisPlacement": "auto",
            "barAlignment": 0,
            "drawStyle": "line",
            "fillOpacity": 0,
            "gradientMode": "none",
            "hideFrom": {
              "legend": false,
              "tooltip": false,
              "viz": false
            },
            "insertNulls": false,
            "lineInterpolation": "linear",
            "lineWidth": 1,
            "pointSize": 5,
            "scaleDistribution": {
              "type": "linear"
            },
            "showPoints": "auto",
            "spanNulls": false,
            "stacking": {
              "group": "A",
              "mode": "none"
            },
            "thresholdsStyle": {
              "mode": "off"
            }
          },
          "mappings": [],
          "thresholds": {
            "mode": "absolute",
            "steps": [
              {
                "color": "green"
              },
              {
                "color": "red",
                "value": 80
              }
            ]
          },
          "unitScale": true
        },
        "overrides": []
      },
      "gridPos": {
        "h": 8,
        "w": 12,
        "x": 12,
        "y": 26
      },
      "id": 21,
      "options": {
        "legend": {
          "calcs": [],
          "displayMode": "list",
          "placement": "bottom",
          "showLegend": true
        },
        "tooltip": {
          "mode": "single",
          "sort": "none"
        }
      },
      "pluginVersion": "10.3.3",
      "targets": [
        {
          "datasource": {
            "type": "frser-sqlite-datasource",
            "uid": "${DS_FRSER-SQLITE-DATASOURCE}"
          },
          "queryText": "SELECT \n    startedAt as time,\n    strftime('%Y-%m-%dT%H:00:00Z', startedAt) AS hour_start,\n    MAX(pipelineDuration / 60) AS max_duration_minutes,\n    AVG(pipelineDuration / 60) AS avg_duration_minutes,\n    MIN(pipelineDuration / 60) AS min_duration_minutes\nFROM (\nSELECT DISTINCT pipelineId, pipelineStatus, startedAt, pipelineDuration from jobs WHERE pipelineStatus==\"MANUAL\"\n)\nGROUP BY hour_start\nORDER BY hour_start;",
          "queryType": "table",
          "rawQueryText": "SELECT \n    startedAt as time,\n    strftime('%Y-%m-%dT%H:00:00Z', startedAt) AS hour_start,\n    MAX(pipelineDuration / 60) AS max_duration_minutes,\n    AVG(pipelineDuration / 60) AS avg_duration_minutes,\n    MIN(pipelineDuration / 60) AS min_duration_minutes\nFROM (\nSELECT DISTINCT pipelineId, pipelineStatus, startedAt, pipelineDuration from jobs WHERE pipelineStatus==\"MANUAL\"\n)\nGROUP BY hour_start\nORDER BY hour_start;",
          "refId": "A",
          "timeColumns": [
            "time",
            "ts",
            "startedAt"
          ]
        }
      ],
      "title": "Manual Pipeline Duration",
      "type": "timeseries"
    },
    {
      "datasource": {
        "type": "frser-sqlite-datasource",
        "uid": "${DS_FRSER-SQLITE-DATASOURCE}"
      },
      "description": "Pipeline ran all the way to the end with no manual actions pending",
      "fieldConfig": {
        "defaults": {
          "color": {
            "mode": "palette-classic"
          },
          "custom": {
            "axisBorderShow": false,
            "axisCenteredZero": false,
            "axisColorMode": "text",
            "axisLabel": "",
            "axisPlacement": "auto",
            "barAlignment": 0,
            "drawStyle": "line",
            "fillOpacity": 0,
            "gradientMode": "none",
            "hideFrom": {
              "legend": false,
              "tooltip": false,
              "viz": false
            },
            "insertNulls": false,
            "lineInterpolation": "linear",
            "lineWidth": 1,
            "pointSize": 5,
            "scaleDistribution": {
              "type": "linear"
            },
            "showPoints": "auto",
            "spanNulls": false,
            "stacking": {
              "group": "A",
              "mode": "none"
            },
            "thresholdsStyle": {
              "mode": "off"
            }
          },
          "mappings": [],
          "thresholds": {
            "mode": "absolute",
            "steps": [
              {
                "color": "green"
              },
              {
                "color": "red",
                "value": 80
              }
            ]
          },
          "unitScale": true
        },
        "overrides": []
      },
      "gridPos": {
        "h": 8,
        "w": 12,
        "x": 0,
        "y": 34
      },
      "id": 15,
      "options": {
        "legend": {
          "calcs": [],
          "displayMode": "list",
          "placement": "bottom",
          "showLegend": true
        },
        "tooltip": {
          "mode": "single",
          "sort": "none"
        }
      },
      "pluginVersion": "10.3.3",
      "targets": [
        {
          "datasource": {
            "type": "frser-sqlite-datasource",
            "uid": "${DS_FRSER-SQLITE-DATASOURCE}"
          },
          "queryText": "SELECT \n    startedAt as time,\n    strftime('%Y-%m-%dT%H:00:00Z', startedAt) AS hour_start,\n    MAX(pipelineDuration) AS max_duration_minutes,\n    AVG(pipelineDuration) AS avg_duration_minutes,\n    MIN(pipelineDuration) AS min_duration_minutes\nFROM (\nSELECT DISTINCT pipelineId, pipelineStatus, startedAt, pipelineDuration from jobs WHERE pipelineStatus==\"SUCCESS\"\n)\nGROUP BY hour_start\nORDER BY hour_start;",
          "queryType": "table",
          "rawQueryText": "SELECT \n    startedAt as time,\n    strftime('%Y-%m-%dT%H:00:00Z', startedAt) AS hour_start,\n    MAX(pipelineDuration) AS max_duration_minutes,\n    AVG(pipelineDuration) AS avg_duration_minutes,\n    MIN(pipelineDuration) AS min_duration_minutes\nFROM (\nSELECT DISTINCT pipelineId, pipelineStatus, startedAt, pipelineDuration from jobs WHERE pipelineStatus==\"SUCCESS\"\n)\nGROUP BY hour_start\nORDER BY hour_start;",
          "refId": "A",
          "timeColumns": [
            "time",
            "ts",
            "startedAt"
          ]
        }
      ],
      "title": "Successfull Pipeline Compute Minutes",
      "type": "timeseries"
    },
    {
      "datasource": {
        "type": "frser-sqlite-datasource",
        "uid": "${DS_FRSER-SQLITE-DATASOURCE}"
      },
      "description": "Pipeline ran all the way to the end with no manual actions pending",
      "fieldConfig": {
        "defaults": {
          "color": {
            "mode": "palette-classic"
          },
          "custom": {
            "axisBorderShow": false,
            "axisCenteredZero": false,
            "axisColorMode": "text",
            "axisLabel": "",
            "axisPlacement": "auto",
            "barAlignment": 0,
            "drawStyle": "line",
            "fillOpacity": 0,
            "gradientMode": "none",
            "hideFrom": {
              "legend": false,
              "tooltip": false,
              "viz": false
            },
            "insertNulls": false,
            "lineInterpolation": "linear",
            "lineWidth": 1,
            "pointSize": 5,
            "scaleDistribution": {
              "type": "linear"
            },
            "showPoints": "auto",
            "spanNulls": false,
            "stacking": {
              "group": "A",
              "mode": "none"
            },
            "thresholdsStyle": {
              "mode": "off"
            }
          },
          "mappings": [],
          "thresholds": {
            "mode": "absolute",
            "steps": [
              {
                "color": "green"
              },
              {
                "color": "red",
                "value": 80
              }
            ]
          },
          "unitScale": true
        },
        "overrides": []
      },
      "gridPos": {
        "h": 8,
        "w": 12,
        "x": 12,
        "y": 34
      },
      "id": 19,
      "options": {
        "legend": {
          "calcs": [],
          "displayMode": "list",
          "placement": "bottom",
          "showLegend": true
        },
        "tooltip": {
          "mode": "single",
          "sort": "none"
        }
      },
      "pluginVersion": "10.3.3",
      "targets": [
        {
          "datasource": {
            "type": "frser-sqlite-datasource",
            "uid": "${DS_FRSER-SQLITE-DATASOURCE}"
          },
          "queryText": "SELECT \n    startedAt as time,\n    strftime('%Y-%m-%dT%H:00:00Z', startedAt) AS hour_start,\n    MAX(pipelineComputeMinutes) AS max_duration_minutes,\n    AVG(pipelineComputeMinutes) AS avg_duration_minutes,\n    MIN(pipelineComputeMinutes) AS min_duration_minutes\nFROM (\nSELECT DISTINCT pipelineId, pipelineStatus, startedAt, pipelineComputeMinutes from jobs WHERE pipelineStatus==\"MANUAL\"\n)\nGROUP BY hour_start\nORDER BY hour_start;",
          "queryType": "table",
          "rawQueryText": "SELECT \n    startedAt as time,\n    strftime('%Y-%m-%dT%H:00:00Z', startedAt) AS hour_start,\n    MAX(pipelineComputeMinutes) AS max_duration_minutes,\n    AVG(pipelineComputeMinutes) AS avg_duration_minutes,\n    MIN(pipelineComputeMinutes) AS min_duration_minutes\nFROM (\nSELECT DISTINCT pipelineId, pipelineStatus, startedAt, pipelineComputeMinutes from jobs WHERE pipelineStatus==\"MANUAL\"\n)\nGROUP BY hour_start\nORDER BY hour_start;",
          "refId": "A",
          "timeColumns": [
            "time",
            "ts",
            "startedAt"
          ]
        }
      ],
      "title": "Manual Pipeline Compute Minutes",
      "type": "timeseries"
    },
    {
      "collapsed": false,
      "gridPos": {
        "h": 1,
        "w": 24,
        "x": 0,
        "y": 42
      },
      "id": 5,
      "panels": [],
      "title": "Ingested Data",
      "type": "row"
    },
    {
      "datasource": {
        "type": "frser-sqlite-datasource",
        "uid": "${DS_FRSER-SQLITE-DATASOURCE}"
      },
      "fieldConfig": {
        "defaults": {
          "color": {
            "mode": "thresholds"
          },
          "custom": {
            "align": "auto",
            "cellOptions": {
              "type": "auto"
            },
            "inspect": false
          },
          "mappings": [],
          "thresholds": {
            "mode": "percentage",
            "steps": [
              {
                "color": "green"
              },
              {
                "color": "orange",
                "value": 70
              },
              {
                "color": "red",
                "value": 85
              }
            ]
          },
          "unitScale": true
        },
        "overrides": []
      },
      "gridPos": {
        "h": 8,
        "w": 12,
        "x": 0,
        "y": 43
      },
      "id": 1,
      "options": {
        "cellHeight": "sm",
        "footer": {
          "countRows": false,
          "fields": "",
          "reducer": [
            "sum"
          ],
          "show": false
        },
        "showHeader": true
      },
      "pluginVersion": "10.3.3",
      "targets": [
        {
          "datasource": {
            "type": "frser-sqlite-datasource",
            "uid": "${DS_FRSER-SQLITE-DATASOURCE}"
          },
          "queryText": "SELECT * from jobs order by startedAt limit 10;",
          "queryType": "table",
          "rawQueryText": "SELECT * from jobs order by startedAt limit 10;",
          "refId": "A",
          "timeColumns": [
            "time",
            "ts"
          ]
        }
      ],
      "title": "Most Recent Jobs",
      "type": "table"
    },
    {
      "datasource": {
        "type": "frser-sqlite-datasource",
        "uid": "${DS_FRSER-SQLITE-DATASOURCE}"
      },
      "fieldConfig": {
        "defaults": {
          "color": {
            "mode": "thresholds"
          },
          "mappings": [],
          "thresholds": {
            "mode": "absolute",
            "steps": [
              {
                "color": "green"
              },
              {
                "color": "red",
                "value": 10000
              }
            ]
          },
          "unitScale": true
        },
        "overrides": []
      },
      "gridPos": {
        "h": 8,
        "w": 5,
        "x": 12,
        "y": 43
      },
      "id": 2,
      "options": {
        "colorMode": "value",
        "graphMode": "area",
        "justifyMode": "auto",
        "orientation": "auto",
        "reduceOptions": {
          "calcs": [
            "lastNotNull"
          ],
          "fields": "",
          "values": false
        },
        "showPercentChange": false,
        "textMode": "auto",
        "wideLayout": true
      },
      "pluginVersion": "10.3.3",
      "targets": [
        {
          "datasource": {
            "type": "frser-sqlite-datasource",
            "uid": "${DS_FRSER-SQLITE-DATASOURCE}"
          },
          "queryText": "select COUNT(*) from jobs",
          "queryType": "table",
          "rawQueryText": "select COUNT(*) from jobs",
          "refId": "A",
          "timeColumns": [
            "time",
            "ts"
          ]
        }
      ],
      "title": "Stored Jobs",
      "type": "stat"
    },
    {
      "datasource": {
        "type": "frser-sqlite-datasource",
        "uid": "${DS_FRSER-SQLITE-DATASOURCE}"
      },
      "fieldConfig": {
        "defaults": {
          "color": {
            "mode": "thresholds"
          },
          "mappings": [],
          "thresholds": {
            "mode": "absolute",
            "steps": [
              {
                "color": "green"
              },
              {
                "color": "red",
                "value": 80
              }
            ]
          },
          "unitScale": true
        },
        "overrides": []
      },
      "gridPos": {
        "h": 2,
        "w": 5,
        "x": 17,
        "y": 43
      },
      "id": 3,
      "options": {
        "colorMode": "value",
        "graphMode": "area",
        "justifyMode": "auto",
        "orientation": "auto",
        "reduceOptions": {
          "calcs": [
            "lastNotNull"
          ],
          "fields": "/.*/",
          "values": false
        },
        "showPercentChange": false,
        "textMode": "auto",
        "wideLayout": true
      },
      "pluginVersion": "10.3.3",
      "targets": [
        {
          "datasource": {
            "type": "frser-sqlite-datasource",
            "uid": "${DS_FRSER-SQLITE-DATASOURCE}"
          },
          "queryText": "SELECT startedAt from jobs order by startedAt DESC limit 1;",
          "queryType": "table",
          "rawQueryText": "SELECT startedAt from jobs order by startedAt DESC limit 1;",
          "refId": "A",
          "timeColumns": [
            "time",
            "ts"
          ]
        }
      ],
      "title": "Latest Job",
      "type": "stat"
    },
    {
      "datasource": {
        "type": "frser-sqlite-datasource",
        "uid": "${DS_FRSER-SQLITE-DATASOURCE}"
      },
      "fieldConfig": {
        "defaults": {
          "color": {
            "mode": "thresholds"
          },
          "mappings": [],
          "thresholds": {
            "mode": "absolute",
            "steps": [
              {
                "color": "green"
              },
              {
                "color": "red",
                "value": 80
              }
            ]
          },
          "unitScale": true
        },
        "overrides": []
      },
      "gridPos": {
        "h": 2,
        "w": 5,
        "x": 17,
        "y": 45
      },
      "id": 4,
      "options": {
        "colorMode": "value",
        "graphMode": "area",
        "justifyMode": "auto",
        "orientation": "auto",
        "reduceOptions": {
          "calcs": [
            "lastNotNull"
          ],
          "fields": "/.*/",
          "values": false
        },
        "showPercentChange": false,
        "textMode": "auto",
        "wideLayout": true
      },
      "pluginVersion": "10.3.3",
      "targets": [
        {
          "datasource": {
            "type": "frser-sqlite-datasource",
            "uid": "${DS_FRSER-SQLITE-DATASOURCE}"
          },
          "queryText": "SELECT startedAt from jobs order by startedAt ASC limit 1;",
          "queryType": "table",
          "rawQueryText": "SELECT startedAt from jobs order by startedAt ASC limit 1;",
          "refId": "A",
          "timeColumns": [
            "time",
            "ts"
          ]
        }
      ],
      "title": "Earliest Job",
      "type": "stat"
    }
  ],
  "refresh": "",
  "schemaVersion": 39,
  "tags": [],
  "templating": {
    "list": []
  },
  "time": {
    "from": "now/y",
    "to": "now"
  },
  "timepicker": {},
  "timezone": "",
  "title": "CI/CD Analysis",
  "uid": "d73e270d-4fa5-4025-9820-04345b3b7b10",
  "version": 35,
  "weekStart": ""
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment