GitHub is a source-control provider that helps developers store and manage their code, as well as track and control changes to their code.
In this tutorial, we will install CloudQuery and use it to fetch GitHub resources. Then, we will use SQL to get visibility into security, compliance and cost-management in GitHub.
Setup
- Download CloudQuery.
- Acquire a GitHub personal access token
with the scopes:read:org
read:project
public_repo - Create a
cloudquery.ymlfile, similar to the following:kind: source
spec:
name: github
path: cloudquery/github
registry: cloudquery
version: 'v17.0.0'
tables: ['*']
destinations: ['postgresql']
spec:
access_token:
orgs: ['cloudquery']
---
kind: destination
spec:
name: 'postgresql'
path: cloudquery/postgresql
registry: cloudquery
version: 'v8.16.0'
write_mode: 'overwrite-delete-stale'
spec:
connection_string: 'postgresql://postgres:pass@localhost:5432/postgres?sslmode=disable' - Run
cloudquery sync cloudquery.yml
Use Cases
After we finish fetching our config data we can make queries for security, compliance, cost management and other purposes.
Find all Public Repositories
-- Query all repositories in the organizations that are public
SELECT id, org, name, description FROM github_repositories
WHERE private = false
Organization Billing
-- Get billing information state for actions, packages and storage
SELECT * FROM github_billing_action
SELECT * FROM github_billing_package
SELECT * FROM github_billing_storage
Long Standing Open Issues
-- Find all open issues that have been open for more than 7 days
SELECT gr.org, gr.name, gi.id, gi.state, gi.created_at, gi.updated_at
FROM github_issues gi
INNER JOIN github_repositories gr ON gr.id = (gi.repository->'id')::bigint
WHERE state = 'open' AND NOW() - interval '7 days' > gi.created_at
What's next
We are going to continue expanding the GitHub Provider, adding support for more resources. Interested in seeing another plugin? Check out Developing New plugin and/or open an issue on our GitHub.
Want to see CloudQuery in action? Schedule a demo with our team or check out the platform documentation to learn more.
Want help getting started? Join the CloudQuery community to connect with other users and experts, or message our team directly here if you have any questions.
Written by
Try CloudQuery yourself
Self-hosted, open source, and free to start. Or schedule a demo to see the managed version.
