Find Commits by Departed Employees Across All Repos with SQL

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 Integration syncs commit history into your data warehouse. Combined with identity provider data from Okta or Entra ID, you can query commits by employment status with SQL.
Required Plugins
This use case requires two CloudQuery source integrations:
- Git Source Integration - Syncs commit history
- Okta Source Integration (or Azure AD) - Provides employee status
Configuration
Configure the Git source integration to sync commits:
Query Commits by Departed Employees
Find commits authored by inactive Okta users:
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:
Prioritize review for authors with high commit counts or recent activity.
Recent Commits Only
Focus on commits from the past 90 days:
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:
Export to CSV for audit documentation or import into your GRC platform.
Get Started
The Git Source Integration and Okta Source Integration 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 Entra ID 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 Integration 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]).
Try CloudQuery yourself
Self-hosted, open source, and free to start. Or schedule a demo to see the managed version.

