CloudQuery News

Introducing the new Microsoft Entra ID (Azure AD) Source Integration

• June 14, 2024

Many organizations use Microsoft Entra ID (Azure AD) to enhance security, for access management, and ensure compliance, however correlating data from Microsoft Entra ID with other services like AWS or Azure can be challenging. Our recently released Microsoft Entra ID Source Integration enables you to gain insights from Microsoft Entra ID across your organization and correlate it with other data sources.

Why Microsoft Entra ID + CloudQuery? #

The Microsoft Graph API is a powerful tool for accessing data from Microsoft Entra ID. However, querying and correlating data from Microsoft Entra ID with other data sources can be complex and time consuming, particularly if you want to combine this data with information from other sources. The Microsoft Entra ID source plugin simplifies this process by providing a unified interface to query and sync data from Microsoft Entra ID with other data sources.

Since you can use CloudQuery to send the data to multiple destinations, for example PostgreSQL, you don't have to learn the Microsoft Graph API query language and can use SQL instead.

Use cases #

Let's take a look at a couple of examples to help you get started with the Microsoft Entra ID source integration.

In all of these examples we assume the data was synced from Microsoft Entra ID source and the Azure source to a Postgres destination. You can find an example configuration below.

List all Azure roles an Entra ID group has #

The following query lists all the Azure roles that a specific Entra ID group (named Security) has:

select properties->>'roleName' as role_name from azure_authorization_role_definitions
where id in (
  select properties->>'roleDefinitionId' from azure_authorization_role_assignments
  where properties->>'principalId' in (
      select id from entraid_groups
      where display_name = 'Security'
      )
)

List all users with a specific Azure role #

The following query lists all the users that have the Azure AI Developer role:

select display_name from entraid_users
where id in (
  select properties->>'principalId' from azure_authorization_role_assignments
    where properties->>'roleDefinitionId' = (
      select id from azure_authorization_role_definitions
      where properties->>'roleName' = 'Azure AI Developer'
      )
)

Getting Started #

To get started syncing Entra ID, see the Entra ID Source Integration documentation for instructions. For the queries in this blog, you can use the example configuration below to sync the data needed.

Example Configuration #

kind: source
spec:
  name: 'entraid'
  path: 'cloudquery/entraid'
  version: 'v1.8.0'
  destinations: ['postgresql']
  tables: ['entraid_users', 'entraid_groups']
---
kind: source
spec:
  name: 'azure'
  path: 'cloudquery/azure'
  version: 'v15.5.0'
  destinations: ['postgresql']
  tables: ['azure_authorization_role_assignments', 'azure_authorization_role_definitions']
---
kind: destination
spec:
  name: 'postgresql'
  path: 'cloudquery/postgresql'
  version: 'v8.6.8'
  migrate_mode: forced
  spec:
    connection_string: 'postgresql://postgres:pass@localhost:5432/postgres?sslmode=disable'

Incremental syncing #

To prevent repeated syncing of the same data CloudQuery supports incremental tables. See more about incremental syncing in the Entra ID Source Integration documentation.

Ready to dive deeper?

Join the CloudQuery Community to connect with other users and experts.

You can also try out CloudQuery locally if you download ClourQuery and follow the instructions in our quick start guide or explore CloudQuery Cloud (currently in beta) for a more scalable solution.

Written by Erez Rokah

I'm a security oriented open source maintainer. I joined the CloudQuery team in April 2022 to focus on building a developer first, open source, high performance data integration platform for security and infrastructure teams.

On this page