steampipe plugin install github

Table: github_my_repository - Query GitHub Repositories using SQL

GitHub is a version control system that allows developers to collaborate on projects. Repositories in GitHub contain all of the project files and each file's revision history. They also contain a README file which provides information about the project. Repositories can be public or private, and they can be accessed and managed on GitHub's website.

Table Usage Guide

The github_my_repository table provides insights into personal GitHub repositories. As a developer or project manager, explore repository-specific details through this table, including repository name, description, owner, and visibility status. Utilize it to manage and monitor your repositories, such as checking the visibility status, reviewing the repository description, and identifying the repository owner.

Important Notes

  • You can own repositories individually, or you can share ownership of repositories with other people in an organization. The github_my_repository table will list tables you own, you collaborate on, or that belong to your organizations.
  • To query ANY repository, including public repos, use the github_repository table.

Examples

List of repositories that you or your organizations own or contribute to

Explore which repositories you or your organizations have ownership of or contribute to, in order to better understand your coding involvement and collaborations. This could be particularly useful for tracking project participation or evaluating the spread of your contributions.

select
name,
owner_login,
name_with_owner
from
github_my_repository
order by
name_with_owner;
select
name,
owner_login,
name_with_owner
from
github_my_repository
order by
name_with_owner;

Show repository stats

Explore your GitHub repositories' statistics to gain insights into popularity and user engagement. This query is useful in understanding the overall performance and reach of your repositories, including the primary language used, number of forks, stars, subscribers, watchers, and the last updated date.

select
name,
owner_login,
primary_language ->> 'name' as language,
fork_count,
stargazer_count,
subscribers_count,
watchers_total_count,
updated_at as last_updated,
description
from
github_my_repository;
select
name,
owner_login,
json_extract(primary_language, '$.name') as language,
fork_count,
stargazer_count,
subscribers_count,
watchers_total_count,
updated_at as last_updated,
description
from
github_my_repository;

List your public repositories

Explore which of your repositories on GitHub are publicly accessible. This is useful for ensuring your private work remains confidential while sharing the projects you wish to showcase.

select
name,
is_private,
visibility,
owner_login
from
github_my_repository
where
not is_private;
select
name,
is_private,
visibility,
owner_login
from
github_my_repository
where
not is_private;

OR

select
name,
is_private,
visibility
from
github_my_repository
where
visibility = 'PUBLIC';
select
name,
is_private,
visibility
from
github_my_repository
where
visibility = 'PUBLIC';

List all your repositories and their collaborators

Gain insights into the collaboration dynamics of your GitHub repositories by identifying the collaborators and their respective permissions. This can be useful in assessing access levels and ensuring proper project management.

select
r.name_with_owner as repository_full_name,
c.user_login,
c.permission
from
github_my_repository r,
github_repository_collaborator c
where
r.name_with_owner = c.repository_full_name;
select
r.name_with_owner as repository_full_name,
c.user_login,
c.permission
from
github_my_repository r,
github_repository_collaborator c
where
r.name_with_owner = c.repository_full_name;

List all your repository collaborators with admin or maintainer permissions

Identify individuals with elevated access rights within your repositories. This can help enhance security by ensuring only necessary permissions are granted.

select
r.name_with_owner as repository_full_name,
c.user_login,
c.permission
from
github_my_repository r,
github_repository_collaborator c
where
r.name_with_owner = c.repository_full_name
and permission in ('ADMIN', 'MAINTAIN');
select
r.name_with_owner as repository_full_name,
c.user_login,
c.permission
from
github_my_repository r
join github_repository_collaborator c on r.name_with_owner = c.repository_full_name
where
c.permission in ('ADMIN', 'MAINTAIN');

List repository hooks that are insecure

Discover the segments that have insecure settings in your GitHub repository hooks. This query is useful to identify potential security vulnerabilities in your repositories' hooks configuration.

select
name as repository,
hook
from
github_my_repository,
jsonb_array_elements(hooks) as hook
where
hook -> 'config' ->> 'insecure_ssl' = '1'
or hook -> 'config' ->> 'secret' is null
or hook -> 'config' ->> 'url' not like '%https:%';
select
name as repository,
hook.value as hook
from
github_my_repository,
json_each(hooks) as hook
where
json_extract(hook.value, '$.config.insecure_ssl') = '1'
or json_extract(hook.value, '$.config.secret') is null
or json_extract(hook.value, '$.config.url') not like '%https:%';

Control examples

Schema for github_my_repository

NameTypeOperatorsDescription
_ctxjsonbSteampipe context in JSON form.
allow_update_branchbooleanIf true, a pull request head branch that is behind its base branch can always be updated even if it is not required to be up to date before merging.
archived_attimestamp with time zoneTimestamp when repository was archived.
auto_merge_allowedbooleanIf true, auto-merge can be enabled on pull requests in this repository.
can_administerbooleanIf true, you can administer this repository.
can_create_projectsbooleanIf true, you can create projects in this repository.
can_subscribebooleanIf true, you can subscribe to this repository.
can_update_topicsbooleanIf true, you can update topics on this repository.
code_of_conductjsonbThe code of conduct for this repository.
contact_linksjsonbList of contact links associated to the repository.
created_attimestamp with time zoneTimestamp when the repository was created.
default_branch_refjsonbDefault ref information.
delete_branch_on_mergebooleanIf true, branches are automatically deleted when merged in this repository.
descriptiontextThe description of the repository.
disk_usagebigintNumber of kilobytes this repository occupies on disk.
fork_countbigintNumber of forks there are of this repository in the whole network.
forking_allowedbooleanIf true, repository allows forks.
funding_linksjsonbThe funding links for this repository.
has_discussions_enabledbooleanIf true, the repository has the Discussions feature enabled.
has_downloadsbooleanIf true, the GitHub Downloads feature is enabled on the repository.
has_issues_enabledbooleanIf true, the repository has issues feature enabled.
has_pagesbooleanIf true, the GitHub Pages feature is enabled on the repository.
has_projects_enabledbooleanIf true, the repository has the Projects feature enabled.
has_starredbooleanIf true, you have starred this repository.
has_vulnerability_alerts_enabledbooleanIf true, vulnerability alerts are enabled for the repository.
has_wiki_enabledbooleanIf true, the repository has wiki feature enabled.
homepage_urltextThe external URL of the repository if set.
hooksjsonbThe API Hooks URL.
idbigintThe numeric ID of the repository.
interaction_abilityjsonbThe interaction ability settings for this repository.
is_archivedbooleanIf true, the repository is unmaintained (archived).
is_blank_issues_enabledbooleanIf true, blank issue creation is allowed.
is_disabledbooleanIf true, this repository disabled.
is_emptybooleanIf true, this repository is empty.
is_forkbooleanIf true, the repository is a fork.
is_in_organizationbooleanIf true, repository is either owned by an organization, or is a private fork of an organization repository.
is_lockedbooleanIf true, repository is locked.
is_mirrorbooleanIf true, the repository is a mirror.
is_privatebooleanIf true, the repository is private or internal.
is_security_policy_enabledbooleanIf true, repository has a security policy.
is_templatebooleanIf true, the repository is a template that can be used to generate new repositories.
is_user_configuration_repositorybooleanIf true, this is a user configuration repository.
issue_templatesjsonbA list of issue templates associated to the repository.
license_infojsonbThe license associated with the repository.
lock_reasontextThe reason the repository has been locked.
login_idtext=, !=, ~~, ~~*, !~~, !~~*Unique identifier for the user login.
merge_commit_allowedbooleanIf true, PRs are merged with a merge commit on this repository.
merge_commit_messagetextHow the default commit message will be generated when merging a pull request.
merge_commit_titletextHow the default commit title will be generated when merging a pull request.
mirror_urltextThe repository's original mirror URL.
nametextThe name of the repository.
name_with_ownertextThe repository's name with owner.
network_countbigintThe number of member repositories in the network.
node_idtextThe node ID of the repository.
open_graph_image_urltextThe image used to represent this repository in Open Graph data.
open_issues_total_countbigintCount of issues open on the repository.
owner_logintextLogin of the repository owner.
possible_commit_emailsjsonbA list of emails you can commit to this repository with.
primary_languagejsonbThe primary language of the repository's code.
projects_urltextThe URL listing the repository's projects.
pull_request_templatesjsonbReturns a list of pull request templates associated to the repository.
pushed_attimestamp with time zoneTimestamp when the repository was last pushed to.
rebase_merge_allowedbooleanIf true, rebase-merging is enabled on this repository.
repository_topics_total_countbigintCount of topics associated with the repository.
security_policy_urltextThe security policy URL.
sp_connection_nametext=, !=, ~~, ~~*, !~~, !~~*Steampipe connection name.
sp_ctxjsonbSteampipe context in JSON form.
squash_merge_allowedbooleanIf true, squash-merging is enabled on this repository.
squash_merge_commit_messagetextHow the default commit message will be generated when squash merging a pull request.
squash_merge_commit_titletextHow the default commit title will be generated when squash merging a pull request.
ssh_urltextThe SSH URL to clone this repository.
stargazer_countbigintReturns a count of how many stargazers there are on this repository.
subscribers_countbigintThe number of users who have subscribed to the repository.
subscriptiontextIdentifies if the current user is watching, not watching, or ignoring the repository.
topicsjsonbThe topics (similar to tags or labels) associated with the repository.
updated_attimestamp with time zoneTimestamp when repository was last updated.
urltextThe URL of the repository.
uses_custom_open_graph_imagebooleanif true, this repository has a custom image to use with Open Graph as opposed to being represented by the owner's avatar.
visibilitytextIndicates the repository's visibility level.
watchers_total_countbigintCount of watchers on the repository.
web_commit_signoff_requiredbooleanIf true, contributors are required to sign off on web-based commits in this repository.
your_permissiontextYour permission level on the repository. Will return null if authenticated as an GitHub App.

Export

This table is available as a standalone Exporter CLI. Steampipe exporters are stand-alone binaries that allow you to extract data using Steampipe plugins without a database.

You can download the tarball for your platform from the Releases page, but it is simplest to install them with the steampipe_export_installer.sh script:

/bin/sh -c "$(curl -fsSL https://steampipe.io/install/export.sh)" -- github

You can pass the configuration to the command with the --config argument:

steampipe_export_github --config '<your_config>' github_my_repository