Uw Software Development Lifecycle Data Template

query getProjectDevData(
  $projectPath: ID!,
  $createdAfter: Time,
  $issuesFirst: Int = 10,
  $issuesAfter: String
) {
  project(fullPath: $projectPath) {
    name
    issues(first: $issuesFirst, after: $issuesAfter, createdAfter: $createdAfter, state: all) {
      pageInfo {
        hasNextPage
        endCursor
      }
      nodes {
        iid
        createdAt
        closedAt
        author {
          username
        }
        assignees(first: 10) {
          nodes {
            username
          }
        }
        labels(first: 20) {
          nodes {
            title
          }
        }
        timelineEvents(first: 100) {
          nodes {
            ... on LabeledEvent {
              createdAt
              user {
                username
              }
            }
            ... on UnlabeledEvent {
              createdAt
              user {
                username
              }
            }
          }
        }
        relatedMergeRequests(first: 10) {
          nodes {
            iid
            createdAt
            author {
              username
            }
            assignees(first: 10) {
              nodes {
                username
              }
            }
            mergedAt
            approvedBy(first: 50) {
              nodes {
                username
                createdAt
              }
            }
            discussions(first: 50) {
              nodes {
                notes(first: 50) {
                  nodes {
                    author {
                      username
                    }
                    createdAt
                  }
                }
              }
            }
            commits(first: 1) {
              nodes {
                authoredDate
              }
            }
            pipelines(first: 20) {
              nodes {
                createdAt
                finishedAt
                status
                jobs(first: 100) {
                  nodes {
                    name
                    startedAt
                    finishedAt
                    status
                    environment {
                      name
                    }
                  }
                }
              }
            }
          }
        }
      }
    }
  }
}

WITH vars AS (
    SELECT
        '2023-01-01'::timestamp AS start_date,
        '2024-01-01'::timestamp AS end_date,
        ARRAY['your-group/your-project'] AS project_paths
),
issue_base AS (
    SELECT i.id, i.iid, i.project_id, i.author_id, i.assignee_id, i.created_at
    FROM issues i
    JOIN projects p ON i.project_id = p.id
    JOIN namespaces n ON p.namespace_id = n.id
    WHERE p.path_with_namespace = ANY((SELECT project_paths FROM vars))
    AND i.created_at BETWEEN (SELECT start_date FROM vars) AND (SELECT end_date FROM vars)
)

-- 1. Issue Created
SELECT
    p.path_with_namespace || '!' || i.iid AS "DevelopmentItem",
    'Issue Created' AS "Activity",
    i.created_at AS "StartTime",
    i.created_at AS "EndTime",
    u_assignee.username AS "Assignee",
    l.name AS "DevelopmentItemType",
    NULL AS "Severity",
    p.name AS "ProjectName"
FROM issues i
JOIN issue_base ib ON i.id = ib.id
JOIN projects p ON i.project_id = p.id
LEFT JOIN users u_assignee ON i.assignee_id = u_assignee.id
LEFT JOIN label_links ll ON ll.target_id = i.id AND ll.target_type = 'Issue'
LEFT JOIN labels l ON l.id = ll.label_id AND l.name IN ('Feature', 'Bug', 'Task', 'Maintenance')

UNION ALL

-- 2. Issue Assigned
SELECT
    p.path_with_namespace || '!' || i.iid AS "DevelopmentItem",
    'Issue Assigned' AS "Activity",
    n.created_at AS "StartTime",
    n.created_at AS "EndTime",
    u_assignee.username AS "Assignee",
    NULL AS "DevelopmentItemType",
    NULL AS "Severity",
    p.name AS "ProjectName"
FROM notes n
JOIN system_note_metadata snm ON n.id = snm.note_id
JOIN issues i ON n.noteable_id = i.id AND n.noteable_type = 'Issue'
JOIN issue_base ib ON i.id = ib.id
JOIN projects p ON i.project_id = p.id
LEFT JOIN users u_assignee ON i.assignee_id = u_assignee.id
WHERE snm.action = 'assignee'
AND n.created_at BETWEEN (SELECT start_date FROM vars) AND (SELECT end_date FROM vars)

UNION ALL

-- 3. Development Started (First commit on a related MR)
SELECT
    sub.DevelopmentItem,
    'Development Started' AS "Activity",
    sub.first_commit_date AS "StartTime",
    sub.first_commit_date AS "EndTime",
    sub.Assignee,
    NULL AS "DevelopmentItemType",
    NULL AS "Severity",
    sub.ProjectName
FROM (
    SELECT 
        p.path_with_namespace || '!' || i.iid AS DevelopmentItem,
        c.committed_date as first_commit_date,
        u_assignee.username AS Assignee,
        p.name as ProjectName,
        ROW_NUMBER() OVER(PARTITION BY i.id ORDER BY c.committed_date ASC) as rn
    FROM merge_requests_closing_issues mrc
    JOIN merge_requests mr ON mrc.merge_request_id = mr.id
    JOIN issues i ON mrc.issue_id = i.id
    JOIN issue_base ib ON i.id = ib.id
    JOIN projects p ON i.project_id = p.id
    JOIN merge_request_diffs mrd ON mr.latest_merge_request_diff_id = mrd.id
    JOIN merge_request_diff_commits mrdc ON mrd.id = mrdc.merge_request_diff_id
    JOIN commits c ON mrdc.sha = c.sha
    LEFT JOIN users u_assignee ON mr.assignee_id = u_assignee.id
) sub
WHERE sub.rn = 1

UNION ALL

-- 4. Merge Request Created
SELECT
    p.path_with_namespace || '!' || i.iid AS "DevelopmentItem",
    'Merge Request Created' AS "Activity",
    mr.created_at AS "StartTime",
    mr.created_at AS "EndTime",
    u_assignee.username AS "Assignee",
    NULL AS "DevelopmentItemType",
    NULL AS "Severity",
    p.name AS "ProjectName"
FROM merge_requests_closing_issues mrc
JOIN merge_requests mr ON mrc.merge_request_id = mr.id
JOIN issues i ON mrc.issue_id = i.id
JOIN issue_base ib ON i.id = ib.id
JOIN projects p ON mr.target_project_id = p.id
LEFT JOIN users u_assignee ON mr.assignee_id = u_assignee.id
WHERE mr.created_at BETWEEN (SELECT start_date FROM vars) AND (SELECT end_date FROM vars)

UNION ALL

-- 5. Pipeline Started
SELECT
    p.path_with_namespace || '!' || i.iid AS "DevelopmentItem",
    'Pipeline Started' AS "Activity",
    pipe.created_at AS "StartTime",
    pipe.finished_at AS "EndTime",
    u_assignee.username AS "Assignee",
    NULL AS "DevelopmentItemType",
    NULL AS "Severity",
    p.name AS "ProjectName"
FROM ci_pipelines pipe
JOIN merge_requests mr ON pipe.merge_request_id = mr.id
JOIN merge_requests_closing_issues mrc ON mr.id = mrc.merge_request_id
JOIN issues i ON mrc.issue_id = i.id
JOIN issue_base ib ON i.id = ib.id
JOIN projects p ON mr.target_project_id = p.id
LEFT JOIN users u_assignee ON mr.assignee_id = u_assignee.id
WHERE pipe.created_at BETWEEN (SELECT start_date FROM vars) AND (SELECT end_date FROM vars)

UNION ALL

-- 6. Pipeline Failed
SELECT
    p.path_with_namespace || '!' || i.iid AS "DevelopmentItem",
    'Pipeline Failed' AS "Activity",
    pipe.finished_at AS "StartTime",
    pipe.finished_at AS "EndTime",
    u_assignee.username AS "Assignee",
    NULL AS "DevelopmentItemType",
    NULL AS "Severity",
    p.name AS "ProjectName"
FROM ci_pipelines pipe
JOIN merge_requests mr ON pipe.merge_request_id = mr.id
JOIN merge_requests_closing_issues mrc ON mr.id = mrc.merge_request_id
JOIN issues i ON mrc.issue_id = i.id
JOIN issue_base ib ON i.id = ib.id
JOIN projects p ON mr.target_project_id = p.id
LEFT JOIN users u_assignee ON mr.assignee_id = u_assignee.id
WHERE pipe.status = 'failed'
AND pipe.finished_at BETWEEN (SELECT start_date FROM vars) AND (SELECT end_date FROM vars)

UNION ALL

-- 7. Code Review Started (First comment on MR by a non-author)
SELECT
    sub.DevelopmentItem,
    'Code Review Started' AS "Activity",
    sub.first_comment_time AS "StartTime",
    sub.first_comment_time AS "EndTime",
    sub.Assignee,
    NULL AS "DevelopmentItemType",
    NULL AS "Severity",
    sub.ProjectName
FROM (
    SELECT 
        p.path_with_namespace || '!' || i.iid AS DevelopmentItem,
        n.created_at AS first_comment_time,
        u_assignee.username AS Assignee,
        p.name AS ProjectName,
        ROW_NUMBER() OVER(PARTITION BY mr.id ORDER BY n.created_at ASC) as rn
    FROM notes n
    JOIN merge_requests mr ON n.noteable_id = mr.id AND n.noteable_type = 'MergeRequest'
    JOIN merge_requests_closing_issues mrc ON mr.id = mrc.merge_request_id
    JOIN issues i ON mrc.issue_id = i.id
    JOIN issue_base ib ON i.id = ib.id
    JOIN projects p ON mr.target_project_id = p.id
    LEFT JOIN users u_assignee ON mr.assignee_id = u_assignee.id
    WHERE n.author_id != mr.author_id AND n.system = false
    AND n.created_at BETWEEN (SELECT start_date FROM vars) AND (SELECT end_date FROM vars)
) sub
WHERE sub.rn = 1

UNION ALL

-- 8. Approval Added
SELECT
    p.path_with_namespace || '!' || i.iid AS "DevelopmentItem",
    'Approval Added' AS "Activity",
    a.created_at AS "StartTime",
    a.created_at AS "EndTime",
    u_assignee.username AS "Assignee",
    NULL AS "DevelopmentItemType",
    NULL AS "Severity",
    p.name AS "ProjectName"
FROM approvals a
JOIN merge_requests mr ON a.merge_request_id = mr.id
JOIN merge_requests_closing_issues mrc ON mr.id = mrc.merge_request_id
JOIN issues i ON mrc.issue_id = i.id
JOIN issue_base ib ON i.id = ib.id
JOIN projects p ON mr.target_project_id = p.id
LEFT JOIN users u_assignee ON mr.assignee_id = u_assignee.id
WHERE a.created_at BETWEEN (SELECT start_date FROM vars) AND (SELECT end_date FROM vars)

UNION ALL

-- 9. Merge Request Merged
SELECT
    p.path_with_namespace || '!' || i.iid AS "DevelopmentItem",
    'Merge Request Merged' AS "Activity",
    mr.merged_at AS "StartTime",
    mr.merged_at AS "EndTime",
    u_merger.username AS "Assignee",
    NULL AS "DevelopmentItemType",
    NULL AS "Severity",
    p.name AS "ProjectName"
FROM merge_requests_closing_issues mrc
JOIN merge_requests mr ON mrc.merge_request_id = mr.id
JOIN issues i ON mrc.issue_id = i.id
JOIN issue_base ib ON i.id = ib.id
JOIN projects p ON mr.target_project_id = p.id
LEFT JOIN users u_merger ON mr.merge_user_id = u_merger.id
WHERE mr.merged_at IS NOT NULL
AND mr.merged_at BETWEEN (SELECT start_date FROM vars) AND (SELECT end_date FROM vars)

UNION ALL

-- 10. Deployment Started
SELECT
    p.path_with_namespace || '!' || i.iid AS "DevelopmentItem",
    'Deployment Started' AS "Activity",
    d.created_at AS "StartTime",
    d.finished_at AS "EndTime",
    u_deployer.username AS "Assignee",
    NULL AS "DevelopmentItemType",
    NULL AS "Severity",
    p.name AS "ProjectName"
FROM deployments d
JOIN projects p ON d.project_id = p.id
JOIN environments e ON d.environment_id = e.id
JOIN issues i ON d.deployable_id = i.id AND d.deployable_type = 'Issue'
JOIN issue_base ib ON i.id = ib.id
LEFT JOIN users u_deployer ON d.user_id = u_deployer.id
WHERE d.created_at BETWEEN (SELECT start_date FROM vars) AND (SELECT end_date FROM vars)

UNION ALL

-- 11. Deployed To Production
SELECT 
    p.path_with_namespace || '!' || i.iid AS "DevelopmentItem",
    'Deployed To Production' AS "Activity",
    d.finished_at AS "StartTime",
    d.finished_at AS "EndTime",
    u_deployer.username AS "Assignee",
    NULL AS "DevelopmentItemType",
    NULL AS "Severity",
    p.name AS "ProjectName"
FROM deployments d
JOIN projects p ON d.project_id = p.id
JOIN environments e ON d.environment_id = e.id
JOIN issues i ON d.deployable_id = i.id AND d.deployable_type = 'Issue'
JOIN issue_base ib ON i.id = ib.id
LEFT JOIN users u_deployer ON d.user_id = u_deployer.id
WHERE e.name = 'production' AND d.status = 2 -- success
AND d.finished_at BETWEEN (SELECT start_date FROM vars) AND (SELECT end_date FROM vars)

UNION ALL

-- 12. Issue Closed
SELECT
    p.path_with_namespace || '!' || i.iid AS "DevelopmentItem",
    'Issue Closed' AS "Activity",
    i.closed_at AS "StartTime",
    i.closed_at AS "EndTime",
    u_assignee.username AS "Assignee",
    NULL AS "DevelopmentItemType",
    NULL AS "Severity",
    p.name AS "ProjectName"
FROM issues i
JOIN issue_base ib ON i.id = ib.id
JOIN projects p ON i.project_id = p.id
LEFT JOIN users u_assignee ON i.assignee_id = u_assignee.id
WHERE i.closed_at IS NOT NULL
AND i.closed_at BETWEEN (SELECT start_date FROM vars) AND (SELECT end_date FROM vars);

import gitlab
import pandas as pd
import os
from datetime import datetime

class=class="hl-string">"hl-comment"># --- Configuration ---
class=class="hl-string">"hl-comment"># Replace with your GitLab instance URL, Private Access Token, and Project IDs
GITLAB_URL = os.environ.get(class="hl-string">'GITLAB_URL', class="hl-string">'[Your GitLab URL, e.g., https://gitlab.com]')
GITLAB_PRIVATE_TOKEN = os.environ.get(class="hl-string">'GITLAB_PRIVATE_TOKEN', class="hl-string">'[Your Personal Access Token]')
PROJECT_IDS = [12345, 67890]  class=class="hl-string">"hl-comment"># Replace with your target project IDs
SINCE_DATE = class="hl-string">'2023-01-01T00:00:00Z'  class=class="hl-string">"hl-comment"># Fetch data created after this date
PRODUCTION_JOB_NAME = class="hl-string">'deploy_production' class=class="hl-string">"hl-comment"># The exact name of your production deployment job

events = []

def add_event(case_id, activity, timestamp, end_timestamp=None, assignee=None, item_type=None, severity=None, project_name=None):
    class="hl-string">"""Helper function to add a new event to the list."""
    if timestamp:
        events.append({
            class="hl-string">'DevelopmentItem': case_id,
            class="hl-string">'Activity': activity,
            class="hl-string">'StartTime': pd.to_datetime(timestamp).isoformat() + class="hl-string">'Z',
            class="hl-string">'EndTime': pd.to_datetime(end_timestamp or timestamp).isoformat() + class="hl-string">'Z',
            class="hl-string">'Assignee': assignee,
            class="hl-string">'DevelopmentItemType': item_type,
            class="hl-string">'Severity': severity,
            class="hl-string">'ProjectName': project_name
        })

class=class="hl-string">"hl-comment"># Initialize GitLab API client
try:
    gl = gitlab.Gitlab(GITLAB_URL, private_token=GITLAB_PRIVATE_TOKEN)
    gl.auth()
    print(class="hl-string">"Successfully authenticated with GitLab.")
except Exception as e:
    print(fclass="hl-string">"Authentication failed: {e}")
    exit()

class=class="hl-string">"hl-comment"># --- Main Extraction Logic ---
for project_id in PROJECT_IDS:
    try:
        project = gl.projects.get(project_id)
        print(fclass="hl-string">"\nProcessing project: {project.name_with_namespace}")

        class=class="hl-string">"hl-comment"># Process Issues first to establish the case ID
        issues = project.issues.list(all=True, created_after=SINCE_DATE)
        for issue in issues:
            print(fclass="hl-string">"  Processing Issue class="hl-commentclass="hl-string">">#{issue.iid}")
            case_id = fclass="hl-string">"{project.name_with_namespace}-I-{issue.iid}"
            assignee_name = issue.assignee[class="hl-string">'name'] if issue.assignee else None
            item_type = class="hl-string">'Bug' if class="hl-string">'bug' in [label.lower() for label in issue.labels] else class="hl-string">'Feature'

            class=class="hl-string">"hl-comment"># Activity: Issue Created
            add_event(case_id, class="hl-string">'Issue Created', issue.created_at, assignee=assignee_name, item_type=item_type, project_name=project.name)

            class=class="hl-string">"hl-comment"># Activity: Issue Assigned
            try:
                state_events = issue.resourcestateevents.list(all=True)
                for event in sorted(state_events, key=lambda x: x.created_at):
                    if event.action == class="hl-string">'add' and hasattr(event, class="hl-string">'user'):
                        add_event(case_id, class="hl-string">'Issue Assigned', event.created_at, assignee=event.user[class="hl-string">'name'], item_type=item_type, project_name=project.name)
                        break class=class="hl-string">"hl-comment"># Capture first assignment only
            except Exception as e:
                print(fclass="hl-string">"    Could not fetch state events for issue {issue.iid}: {e}")

            class=class="hl-string">"hl-comment"># Activity: Issue Closed
            if issue.closed_at:
                add_event(case_id, class="hl-string">'Issue Closed', issue.closed_at, assignee=assignee_name, item_type=item_type, project_name=project.name)

        class=class="hl-string">"hl-comment"># Process Merge Requests and link them to issues
        mrs = project.mergerequests.list(all=True, created_after=SINCE_DATE)
        for mr in mrs:
            print(fclass="hl-string">"  Processing MR !{mr.iid}")
            class=class="hl-string">"hl-comment"># Find the closing issue to use as the case_id
            closing_issues = mr.closes_issues()
            if not closing_issues:
                continue class=class="hl-string">"hl-comment"># Skip MRs not linked to an issue

            class=class="hl-string">"hl-comment"># Assuming one MR closes one issue for simplicity
            linked_issue_iid = closing_issues[0][class="hl-string">'iid']
            case_id = fclass="hl-string">"{project.name_with_namespace}-I-{linked_issue_iid}"
            assignee_name = mr.assignee[class="hl-string">'name'] if mr.assignee else None
            item_type = class="hl-string">'Bug' if class="hl-string">'bug' in [label.lower() for label in mr.labels] else class="hl-string">'Feature'

            class=class="hl-string">"hl-comment"># Activity: Merge Request Created
            add_event(case_id, class="hl-string">'Merge Request Created', mr.created_at, assignee=assignee_name, item_type=item_type, project_name=project.name)

            class=class="hl-string">"hl-comment"># Activity: Development Started (first commit)
            try:
                commits = mr.commits()
                if commits:
                    first_commit = commits[-1] class=class="hl-string">"hl-comment"># Commits are listed newest first
                    add_event(case_id, class="hl-string">'Development Started', first_commit.created_at, assignee=mr.author[class="hl-string">'name'], item_type=item_type, project_name=project.name)
            except Exception as e:
                print(fclass="hl-string">"    Could not fetch commits for MR {mr.iid}: {e}")

            class=class="hl-string">"hl-comment"># Activity: Code Review Started (first comment by non-author)
            try:
                notes = mr.notes.list(all=True, sort=class="hl-string">'asc')
                for note in notes:
                    if not note.system and note.author[class="hl-string">'id'] != mr.author[class="hl-string">'id']:
                        add_event(case_id, class="hl-string">'Code Review Started', note.created_at, assignee=note.author[class="hl-string">'name'], item_type=item_type, project_name=project.name)
                        break
            except Exception as e:
                print(fclass="hl-string">"    Could not fetch notes for MR {mr.iid}: {e}")

            class=class="hl-string">"hl-comment"># Activity: Approval Added
            try:
                approvals = mr.approvals.get()
                for approver in approvals.approved_by:
                    add_event(case_id, class="hl-string">'Approval Added', approver[class="hl-string">'approved_at'], assignee=approver[class="hl-string">'user'][class="hl-string">'name'], item_type=item_type, project_name=project.name)
            except Exception as e:
                print(fclass="hl-string">"    Could not fetch approvals for MR {mr.iid}: {e}")
            
            class=class="hl-string">"hl-comment"># Activity: Merge Request Merged
            if mr.merged_at:
                add_event(case_id, class="hl-string">'Merge Request Merged', mr.merged_at, assignee=mr.merged_by[class="hl-string">'name'], item_type=item_type, project_name=project.name)

            class=class="hl-string">"hl-comment"># Activities from Pipelines
            try:
                pipelines = mr.pipelines()
                for p in pipelines:
                    pipeline = project.pipelines.get(p[class="hl-string">'id'])
                    add_event(case_id, class="hl-string">'Pipeline Started', pipeline.created_at, assignee=pipeline.user[class="hl-string">'name'], item_type=item_type, project_name=project.name)
                    if pipeline.status == class="hl-string">'failed':
                        add_event(case_id, class="hl-string">'Pipeline Failed', pipeline.finished_at, assignee=pipeline.user[class="hl-string">'name'], item_type=item_type, project_name=project.name)
                    
                    class=class="hl-string">"hl-comment"># Activities from Jobs in the pipeline
                    jobs = pipeline.jobs.list(all=True)
                    for job in jobs:
                        if class="hl-string">'deploy' in job.name.lower() and job.started_at:
                            add_event(case_id, class="hl-string">'Deployment Started', job.started_at, assignee=job.user[class="hl-string">'name'], item_type=item_type, project_name=project.name)
                        if job.name == PRODUCTION_JOB_NAME and job.status == class="hl-string">'success' and job.finished_at:
                            add_event(case_id, class="hl-string">'Deployed To Production', job.finished_at, assignee=job.user[class="hl-string">'name'], item_type=item_type, project_name=project.name)
            except Exception as e:
                print(fclass="hl-string">"    Could not fetch pipelines/jobs for MR {mr.iid}: {e}")

    except gitlab.exceptions.GitlabError as e:
        print(fclass="hl-string">"Error processing project {project_id}: {e}")

class=class="hl-string">"hl-comment"># --- Create and Save DataFrame ---
if events:
    df = pd.DataFrame(events)
    df_sorted = df.sort_values(by=[class="hl-string">'DevelopmentItem', class="hl-string">'StartTime'], ascending=[True, True])
    df_sorted.to_csv(class="hl-string">'gitlab_event_log.csv', index=False)
    print(class="hl-string">"\nExtraction complete. Event log saved to gitlab_event_log.csv")
else:
    print(class="hl-string">"\nExtraction complete. No events found for the given projects and date range.")

{
  "url": "https://[your-listening-service-url.com]/webhook-endpoint",
  "secret_token": "[your-secure-secret-token]",
  "merge_requests_events": true,
  "push_events": true,
  "issues_events": true,
  "note_events": true,
  "job_events": true,
  "pipeline_events": true,
  "wiki_page_events": false,
  "releases_events": false,
  "confidential_issues_events": true,
  "confidential_note_events": true,
  "enable_ssl_verification": true
}