Table: azuredevops_user - Query Azure DevOps Users using SQL

Azure DevOps is a Microsoft product that provides version control, reporting, requirements management, project management, automated builds, lab management, testing and release management capabilities. It covers the entire application lifecycle and enables DevOps capabilities. Azure DevOps can be used for any kind of application regardless of the framework, platform, or cloud.

The following types of users can join your Azure DevOps Services organization for free:

Five users who get Basic features, such as version control, tools for Agile, Java, build, release, and more.
Unlimited users who get Stakeholder features, such as working with your backlog, work items, and queries.
Unlimited Visual Studio subscribers who also get Basic or Basic + Test Plan features, depending on their subscription level.

Table Usage Guide

The azuredevops_user table provides insights into users within Azure DevOps. As a DevOps engineer or system administrator, explore user-specific details through this table, including roles, access levels, and associated metadata. Utilize it to uncover information about users, such as their access permissions, the projects they are associated with, and their activity patterns.

Examples

Basic info

Explore which users are active in your Azure DevOps environment. This can help in managing user access and understanding the distribution of users across different domains and origins.

select
  principal_name,
  display_name,
  membership_state,
  domain,
  origin
from
  azuredevops_user;

select
  principal_name,
  display_name,
  membership_state,
  domain,
  origin
from
  azuredevops_user;

List inactive users

Explore which users in your Azure DevOps environment are inactive. This can help maintain system security by identifying potential unused or unnecessary accounts.

select
  principal_name,
  display_name,
  membership_state,
  domain,
  origin
from
  azuredevops_user
where
  not membership_state;

select
  principal_name,
  display_name,
  membership_state,
  domain,
  origin
from
  azuredevops_user
where
  not membership_state;

List users from aad

Explore which Azure DevOps users have their origin set to 'aad', enabling you to understand their source and manage user access effectively. This is particularly useful in large organizations where user management can be complex.

select
  principal_name,
  display_name,
  membership_state,
  domain,
  origin
from
  azuredevops_user
where
  origin = 'aad';

select
  principal_name,
  display_name,
  membership_state,
  domain,
  origin
from
  azuredevops_user
where
  origin = 'aad';

List the users that have been deleted in the identity provider

Explore the users who have been removed from the identity provider. This query is useful for auditing and maintaining security compliance by tracking changes in user access.

select
  principal_name,
  display_name,
  membership_state,
  domain,
  origin
from
  azuredevops_user
where
  is_deleted_in_origin;

select
  principal_name,
  display_name,
  membership_state,
  domain,
  origin
from
  azuredevops_user
where
  is_deleted_in_origin = 1;

List users who are part of `Project Collection Administrators` group

Explore which users are part of a specific administrative group within a project collection. This is useful for auditing purposes, allowing you to ensure only authorized individuals have administrative access.

select
  principal_name,
  display_name,
  membership_state,
  domain,
  origin
from
  azuredevops_user,
  jsonb_array_elements(memberships) as m
where
  m ->> 'containerDescriptor' in (
    select
      descriptor
    from
      azuredevops_group
    where
      display_name = 'Project Collection Administrators'
  );

select
  principal_name,
  display_name,
  membership_state,
  domain,
  origin
from
  azuredevops_user,
  json_each(memberships) as m
where
  json_extract(m.value, '$.containerDescriptor') in (
    select
      descriptor
    from
      azuredevops_group
    where
      display_name = 'Project Collection Administrators'
  );

Schema for azuredevops_user

Name	Type	Operators	Description
_ctx	jsonb		Steampipe context in JSON form, e.g. connection_name.
descriptor	text	=	The descriptor is the primary way to reference the graph subject while the system is running. This field will uniquely identify the same graph subject across both Accounts and Organizations.
directory_alias	text		The short, generally unique name for the user in the backing directory. For AAD users, this corresponds to the mail nickname, which is often but not necessarily similar to the part of the user's mail address before the @ sign. For GitHub users, this corresponds to the GitHub user handle.
display_name	text		This is the non-unique display name of the graph subject. To change this field, you must alter its value in the source provider.
domain	text		This represents the name of the container of origin for a graph member. (For MSA this is Windows Live ID, for AD the name of the domain, for AAD the tenantID of the directory, for VSTS users the ScopeId, etc).
is_deleted_in_origin	text		When true, the user has been deleted in the identity provider.
legacy_descriptor	text		The legacy descriptor is here in case you need to access old version IMS using identity descriptor.
links	jsonb		This field contains zero or more interesting links about the graph subject. These links may be invoked to obtain additional relationships or more detailed information about this graph subject.
mail_address	text		The email address of record for a given graph member. This may be different than the principal name.
membership_state	boolean		When true, the membership is active.
memberships	jsonb		Get all the memberships where this descriptor is a member in the relationship.
meta_type	text		The meta type of the user in the origin, such as member, guest, etc.
origin	text		The type of source provider for the origin identifier (ex:AD, AAD, MSA).
origin_id	text		The unique identifier from the system of origin. Typically a sid, object id or Guid. Linking and unlinking operations can cause this value to change for a user because the user is not backed by a different provider and has a different unique id in the new provider.
principal_name	text		This is the PrincipalName of this graph member from the source provider. The source provider may change this field over time and it is not guaranteed to be immutable for the life of the graph member by VSTS.
subject_kind	text		This field identifies the type of the graph subject (ex: Group, Scope, User).
title	text		Title of the resource.
url	text		This url is the full route to the source resource of this graph subject.

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)" -- azuredevops

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

steampipe_export_azuredevops --config '<your_config>' azuredevops_user