Table: github_organization - Query GitHub Organizations using SQL

GitHub Organizations is a feature within GitHub that allows users to collaborate across many projects at once. Organizations include features such as unified billing, access control, and multiple repositories. It is a way for businesses and open-source projects to manage their projects and teams.

Table Usage Guide

The github_organization table provides insights into Organizations within GitHub. As a developer or project manager, explore organization-specific details through this table, including profile information, public repository count, and associated metadata. Utilize it to uncover information about organizations, such as their location, public repository count, and other profile details.

Important Notes

You must specify the login column in where or join clause to query the table.
To list organizations that you are a member of, use the github_my_organization table.

Examples

Basic info for a GitHub Organization

Explore essential details about a specific GitHub organization to understand its structure and activity. This is useful for gaining insights into the organization's verification status, team and member counts, and repository count.

select
  login as organization,
  name,
  twitter_username,
  created_at,
  updated_at,
  is_verified,
  teams_total_count as teams_count,
  members_with_role_total_count as member_count,
  repositories_total_count as repo_count
from
  github_organization
where
  login = 'postgres';

select
  login as organization,
  name,
  twitter_username,
  created_at,
  updated_at,
  is_verified,
  teams_total_count as teams_count,
  members_with_role_total_count as member_count,
  repositories_total_count as repo_count
from
  github_organization
where
  login = 'postgres';

List members of an organization

This query is used to identify members of a specific organization and check if they have two-factor authentication enabled. This can be useful for organizations looking to enforce security measures and ensure all members have additional protection for their accounts.

select
  o.login as organization,
  m.login as user_login,
  m.has_two_factor_enabled as mfa_enabled
from
  github_organization o,
  github_organization_member m
where
  o.login = 'turbot'
  and o.login = m.organization;

select
  o.login as organization,
  m.login as user_login,
  m.has_two_factor_enabled as mfa_enabled
from
  github_organization o
  join github_organization_member m on o.login = m.organization
where
  o.login = 'turbot';

select
  organization,
  login as user_login,
  has_two_factor_enabled as mfa_enabled
from
  github_organization_member
where
  organization = 'turbot';

select
  organization,
  login as user_login,
  has_two_factor_enabled as mfa_enabled
from
  github_organization_member
where
  organization = 'turbot';

Schema for github_organization

Name	Type	Operators	Description
_ctx	jsonb		Steampipe context in JSON form.
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.
login_id	text	=, !=, ~~, ~~, !~~, !~~	Unique identifier for the user login.
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.
sp_connection_name	text	=, !=, ~~, ~~, !~~, !~~	Steampipe connection name.
sp_ctx	jsonb		Steampipe context in JSON form.
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_organization