steampipe plugin install github

Table: github_organization_external_identity - Query GitHub Organization External Identities using SQL

GitHub Organization External Identities represent the mapping between a GitHub user and their linked identity at an Identity Provider. It provides information about the external identity of users in a GitHub organization. This data is particularly useful for organizations that use SAML single sign-on (SSO) in conjunction with GitHub.

Table Usage Guide

The github_organization_external_identity table provides insights into the external identities of users within a GitHub organization. As a GitHub organization administrator, this table can be used to gain a comprehensive understanding of the linked identities of users at your Identity Provider. This can be particularly useful when managing users in organizations that use SAML single sign-on (SSO) with GitHub.

Important Notes

  • You must specify the organization column in where or join clause to query the table.

Examples

List external identities of an organization

This query is useful for gaining insights into the external identities associated with a specific organization. It allows you to identify the roles and usernames of these external identities, which can help in assessing the organization's security and access management structure.

select
guid,
user_login,
saml_identity ->> 'username' as saml_user,
scim_identity ->> 'username' as scim_user,
organization_invitation ->> 'role' as invited_role
from
github_organization_external_identity
where
organization = 'turbot';
select
guid,
user_login,
json_extract(saml_identity, '$.username') as saml_user,
json_extract(scim_identity, '$.username') as scim_user,
json_extract(organization_invitation, '$.role') as invited_role
from
github_organization_external_identity
where
organization = 'turbot';

List external identities for all your organizations

This query is useful for gaining insights into the external identities linked to your organizations. It provides a comprehensive view of each user's login details, email, and role, which can be particularly beneficial for managing access and understanding user behavior across different platforms.

select
o.login as org,
o.saml_identity_provider ->> 'sso_url' as sso_url,
e.user_login,
e.user_detail ->> 'email' as user_email,
e.saml_identity ->> 'username' as saml_user,
e.scim_identity ->> 'username' as scim_user,
e.organization_invitation ->> 'role' as invited_role
from
github_my_organization o
join github_organization_external_identity e on o.login = e.organization;
select
o.login as org,
json_extract(o.saml_identity_provider, '$.sso_url') as sso_url,
e.user_login,
json_extract(e.user_detail, '$.email') as user_email,
json_extract(e.saml_identity, '$.username') as saml_user,
json_extract(e.scim_identity, '$.username') as scim_user,
json_extract(e.organization_invitation, '$.role') as invited_role
from
github_my_organization o
join github_organization_external_identity e on o.login = e.organization;

Schema for github_organization_external_identity

NameTypeOperatorsDescription
_ctxjsonbSteampipe context in JSON form.
guidtextGuid identifier for the external identity.
login_idtext=, !=, ~~, ~~*, !~~, !~~*Unique identifier for the user login.
organizationtext=The organization the external identity is associated with.
organization_invitationjsonbThe invitation to the organization.
saml_identityjsonbThe external SAML identity.
scim_identityjsonbThe external SCIM identity.
sp_connection_nametext=, !=, ~~, ~~*, !~~, !~~*Steampipe connection name.
sp_ctxjsonbSteampipe context in JSON form.
user_detailjsonbThe GitHub user details.
user_logintextThe GitHub user login.

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_external_identity