Security
Tutorials
Find Commits by Departed Employees Across All Repos with SQL
TL;DR: The Git Source Plugin syncs commit history. Join with Okta or Azure AD to identify commits by former employees for compliance audits and security reviews.
An engineer leaves your company. Their access gets revoked, their laptop returned. But their commits remain across dozens of repositories. For compliance audits, security reviews, or simply understanding code ownership, you need to identify which code was authored by former employees. The manual approach: export HR data, clone repos, run
git log, correlate email addresses, compile spreadsheets. Repeat quarterly.The Git Source Plugin syncs commit history into your data warehouse. Combined with identity provider data from Okta or Azure AD, you can query commits by employment status with SQL.
Required Plugins #
This use case requires two CloudQuery source plugins:
- Git Source Plugin - Syncs commit history
- Okta Source Plugin (or Azure AD) - Provides employee status
Configuration #
Configure the Git Source Plugin to sync commits:
kind: source
spec:
name: git
path: cloudquery/git
version: 'v1.0.0'
tables:
- git_commits
- git_repositories
destinations:
- postgresql
Query Commits by Departed Employees #
Find commits authored by inactive Okta users:
SELECT
gc.sha,
gc.repository_url,
gc.author_name,
gc.author_email,
gc.author_date,
gc.message,
COALESCE(u.status, 'NOT_FOUND') AS okta_status
FROM git_commits gc
LEFT JOIN okta_users u ON lower(gc.author_email) = lower(u.email)
WHERE u.status IS NULL -- No matching Okta account
OR u.status != 'ACTIVE' -- Okta account inactive
ORDER BY gc.author_date DESC
The query:
- Joins commit authors with Okta user records by email
- Filters for missing or inactive accounts
- Returns recent commits first for prioritization
Results #
The query returns:
sha- Commit hash for referencerepository_url- Repository containing the commitauthor_name,author_email- Who made the commitauthor_date- When the commit was mademessage- Commit message for contextokta_status-ACTIVE,DEPROVISIONED,SUSPENDED, orNOT_FOUND
Aggregate by Author #
See which former employees have the most commits:
SELECT
gc.author_email,
gc.author_name,
COUNT(*) AS commit_count,
MIN(gc.author_date) AS first_commit,
MAX(gc.author_date) AS last_commit,
COUNT(DISTINCT gc.repository_url) AS repos_touched
FROM git_commits gc
LEFT JOIN okta_users u ON lower(gc.author_email) = lower(u.email)
WHERE u.status IS NULL OR u.status != 'ACTIVE'
GROUP BY gc.author_email, gc.author_name
ORDER BY commit_count DESC
Prioritize review for authors with high commit counts or recent activity.
Recent Commits Only #
Focus on commits from the past 90 days:
SELECT
gc.sha,
gc.repository_url,
gc.author_name,
gc.author_email,
gc.author_date,
gc.message
FROM git_commits gc
LEFT JOIN okta_users u ON lower(gc.author_email) = lower(u.email)
WHERE (u.status IS NULL OR u.status != 'ACTIVE')
AND gc.author_date > NOW() - INTERVAL '90 days'
ORDER BY gc.author_date DESC
Recent commits from departed employees may indicate timing issues with offboarding or warrant immediate security review.
Compliance Reporting #
Generate a report for SOC 2 or security audits:
SELECT
gr.full_name AS repository,
gc.author_name,
gc.author_email,
gc.author_date,
gc.sha,
LEFT(gc.message, 100) AS commit_summary,
CASE
WHEN u.status = 'DEPROVISIONED' THEN 'Former Employee'
WHEN u.status = 'SUSPENDED' THEN 'Suspended Account'
WHEN u.status IS NULL THEN 'Unknown - Not in IdP'
ELSE u.status
END AS employee_status
FROM git_commits gc
JOIN git_repositories gr ON gr.url = gc.repository_url
LEFT JOIN okta_users u ON lower(gc.author_email) = lower(u.email)
WHERE u.status IS NULL OR u.status != 'ACTIVE'
ORDER BY gr.full_name, gc.author_date DESC
Export to CSV for audit documentation or import into your GRC platform.
Get Started #
The Git Source Plugin and Okta Source Plugin are available on CloudQuery Hub. See the Git tables documentation for the full schema. Download the CloudQuery CLI to start auditing commit history.
Related:
FAQ #
What if employees use personal email addresses for commits?
Commits with personal emails won't match Okta records. Query for commits where
author_email doesn't match your corporate domain pattern, then correlate manually or maintain an email alias mapping table.Can I use Azure AD instead of Okta?
Yes. Replace the Okta plugin with Azure AD and join on the appropriate user email field. The SQL pattern remains the same.
How do I handle contractors and external contributors?
External contributors won't appear in your IdP. Filter by email domain first, or maintain a separate table of known external contributors to exclude from the audit.
How often should I run this audit?
Running monthly catches recently departed employees. For compliance, align with your audit schedule (quarterly for SOC 2, annually for some frameworks). Automate with scheduled CloudQuery syncs.
What about commits made before the employee joined?
The query returns all commits by email, regardless of employment dates. Join with HR data containing hire/termination dates for more precise filtering.
Can I track commits by both author and committer?
Yes. Git distinguishes between author (who wrote the code) and committer (who applied the commit). Query both
author_email and committer_email columns if your workflow includes commit rebasing or cherry-picking.How do I identify commits that need code review?
Join with the GitHub Source Plugin to check if commits are part of merged PRs that had reviews. Commits pushed directly to main without PR review are higher risk.
What if the same person has multiple email addresses?
Create an email alias mapping table or use SQL pattern matching. Many organizations have historical email format changes ([email protected] vs [email protected]).
