Table: github_my_organization - Query GitHub Organizations using SQL

GitHub Organizations are a shared workspace where businesses and open-source projects can collaborate across many projects at once. Owners and administrators can manage member access to the organization's data and projects with sophisticated security and administrative features. GitHub Organizations help streamline project management and boost productivity in your organization.

Table Usage Guide

The github_my_organization table provides insights into a user's primary organization on GitHub. As a project manager or team lead, explore organization-specific details through this table, including member access, repository details, and administrative features. Utilize it to manage and streamline your organization's projects and enhance team productivity.

Important Notes

The github_my_organization table will list the organization that you are a member of. To view details of ANY organization, use the github_organization table.

Examples

Basic info for the GitHub Organizations to which you belong

Determine the areas in which you hold membership across various GitHub organizations. This query is useful in understanding your involvement and role within these organizations, including details such as the number of private and public repositories, team counts, and member counts.

select
  login as organization,
  name,
  twitter_username,
  private_repositories_total_count as private_repos,
  public_repositories_total_count as public_repos,
  created_at,
  updated_at,
  is_verified,
  teams_total_count as teams_count,
  members_with_role_total_count as member_count,
  url
from
  github_my_organization;

select
  login as organization,
  name,
  twitter_username,
  private_repositories_total_count as private_repos,
  public_repositories_total_count as public_repos,
  created_at,
  updated_at,
  is_verified,
  teams_total_count as teams_count,
  members_with_role_total_count as member_count,
  url
from
  github_my_organization;

Show all members for the GitHub Organizations to which you belong

Determine the areas in which you are a member of a GitHub organization, offering insights into your collaborative coding environments and affiliations. This can be useful in managing and understanding your participation in various coding projects and teams.

select
  o.login as organization,
  m.login as member_login
from
  github_my_organization o
  join github_organization_member m on o.login = m.organization;

select
  o.login as organization,
  m.login as member_login
from
  github_my_organization o
  join github_organization_member m on o.login = m.organization;

Show your permissions on the Organization

Explore your access level and permissions within your GitHub organization. This can help in understanding what actions you are authorized to perform, such as administering the organization, changing pinned items, creating projects, repositories, or teams, and whether you are currently a member.

select
  login as organization,
  members_with_role_total_count as members_count,
  can_administer,
  can_changed_pinned_items,
  can_create_projects,
  can_create_repositories,
  can_create_teams,
  is_a_member as current_member
from
  github_my_organization;

select
  login as organization,
  members_with_role_total_count as members_count,
  can_administer,
  can_changed_pinned_items,
  can_create_projects,
  can_create_repositories,
  can_create_teams,
  is_a_member as current_member
from
  github_my_organization;

Show Organization security settings

Gain insights into your organization's security settings, such as member permissions and two-factor authentication requirements. This can help ensure your organization's GitHub repositories and pages are appropriately protected.

select
  login as organization,
  members_with_role_total_count as members_count,
  members_allowed_repository_creation_type,
  members_can_create_internal_repos,
  members_can_create_pages,
  members_can_create_private_repos,
  members_can_create_public_repos,
  members_can_create_repos,
  default_repo_permission,
  two_factor_requirement_enabled
from
  github_my_organization;

select
  login as organization,
  members_with_role_total_count as members_count,
  members_allowed_repository_creation_type,
  members_can_create_internal_repos,
  members_can_create_pages,
  members_can_create_private_repos,
  members_can_create_public_repos,
  members_can_create_repos,
  default_repo_permission,
  two_factor_requirement_enabled
from
  github_my_organization;

List organization hooks that are insecure

Explore which organization hooks are potentially insecure due to specific settings, such as lack of SSL security, absence of a secret, or non-HTTPS URLs. This is particularly useful in identifying and mitigating potential security vulnerabilities within your organization's GitHub configuration.

select
  login as organization,
  hook
from
  github_my_organization,
  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
  login as organization,
  hook.value as hook
from
  github_my_organization,
  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:%';

Query examples

Control examples

Schema for github_my_organization

Name	Type	Description
_ctx	jsonb	Steampipe context in JSON form, e.g. connection_name.
announcement	text	The text of the announcement.
announcement_expires_at	timestamp with time zone	The expiration date of the announcement, if any.
announcement_user_dismissible	boolean	If true, the announcement can be dismissed by the user.
any_pinnable_items	boolean	If true, this organization has items that can be pinned to their profile.
avatar_url	text	URL pointing to the organization's public avatar.
billing_email	text	The email address for billing.
can_administer	boolean	If true, you can administer the organization.
can_changed_pinned_items	boolean	If true, you can change the pinned items on the organization's profile.
can_create_projects	boolean	If true, you can create projects for the organization.
can_create_repositories	boolean	If true, you can create repositories for the organization.
can_create_teams	boolean	If true, you can create teams within the organization.
can_sponsor	boolean	If true, you can sponsor this organization.
collaborators	bigint	The number of collaborators for the organization.
created_at	timestamp with time zone	Timestamp when the organization was created.
default_repo_permission	text	The default repository permissions for the organization.
description	text	The description of the organization.
email	text	The email address associated with the organization.
estimated_next_sponsors_payout_in_cents	bigint	The estimated next GitHub Sponsors payout for this organization in cents (USD).
followers	bigint	The number of users following the organization.
following	bigint	The number of users followed by the organization.
has_organization_projects	boolean	If true, the organization can use organization projects.
has_repository_projects	boolean	If true, the organization can use repository projects.
has_sponsors_listing	boolean	If true, this organization has a GitHub Sponsors listing.
hooks	jsonb	The Hooks of the organization.
id	bigint	The ID number of the organization.
interaction_ability	jsonb	The interaction ability settings for this organization.
is_a_member	boolean	If true, you are an active member of the organization.
is_following	boolean	If true, you are following the organization.
is_sponsoring	boolean	If true, you are sponsoring the organization.
is_sponsoring_you	boolean	If true, you are sponsored by this organization.
is_verified	boolean	If true, the organization has verified its profile email and website.
location	text	The organization's public profile location.
login	text	The login name of the organization.
members_allowed_repository_creation_type	text	Specifies which types of repositories non-admin organization members can create
members_can_create_internal_repos	boolean	If true, members can create internal repositories.
members_can_create_pages	boolean	If true, members can create pages.
members_can_create_private_repos	boolean	If true, members can create private repositories.
members_can_create_public_repos	boolean	If true, members can create public repositories.
members_can_create_repos	boolean	If true, members can create repositories.
members_can_fork_private_repos	boolean	If true, members can fork private organization repositories.
members_with_role_total_count	bigint	Count of members with a role within the organization.
monthly_estimated_sponsors_income_in_cents	bigint	The estimated monthly GitHub Sponsors income for this organization in cents (USD).
name	text	The display name of the organization.
new_team_url	text	URL for creating a new team.
node_id	text	The node ID of the organization.
packages_total_count	bigint	Count of packages within the organization.
pinnable_items_total_count	bigint	Count of pinnable items within the organization.
pinned_items_remaining	bigint	Returns how many more items this organization can pin to their profile.
pinned_items_total_count	bigint	Count of itesm pinned to the organization's profile.
plan_filled_seats	bigint	The number of used seats for the plan.
plan_name	text	The name of the GitHub plan.
plan_private_repos	bigint	The number of private repositories for the plan.
plan_seats	bigint	The number of available seats for the plan
plan_space	bigint	The total space allocated for the plan.
private_repositories_total_count	bigint	Count of private repositories within the organization.
projects_total_count	bigint	Count of projects within the organization.
projects_url	text	URL listing organization's projects.
projects_v2_total_count	bigint	Count of V2 projects within the organization.
public_repositories_total_count	bigint	Count of public repositories within the organization.
repositories_total_count	bigint	Count of all repositories within the organization.
repositories_total_disk_usage	bigint	Total disk usage for all repositories within the organization.
saml_identity_provider	jsonb	The Organization's SAML identity provider. Visible to (1) organization owners, (2) organization owners' personal access tokens (classic) with read:org or admin:org scope, (3) GitHub App with an installation token with read or write access to members, else null.
sponsoring_total_count	bigint	Count of users the organization is sponsoring.
sponsors_listing	jsonb	The GitHub sponsors listing for this organization.
sponsors_total_count	bigint	Count of sponsors the organization has.
teams_total_count	bigint	Count of teams within the organization.
teams_url	text	URL listing organization's teams.
total_sponsorship_amount_as_sponsor_in_cents	bigint	The amount in United States cents (e.g., 500 = $5.00 USD) that this entity has spent on GitHub to fund sponsorships. Only returns a value when viewed by the user themselves or by a user who can manage sponsorships for the requested organization.
twitter_username	text	The organization's Twitter username.
two_factor_requirement_enabled	boolean	If true, all members in the organization must have two factor authentication enabled.
updated_at	timestamp with time zone	Timestamp when the organization was last updated.
url	text	The URL for this organization.
web_commit_signoff_required	boolean	If true, contributors are required to sign off on web-based commits for repositories in this organization.
website_url	text	URL for the organization's public website.

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_organization