Tutorial: Relatórios personalizados no Azure Data Explorer usando dados da ID do Microsoft Entra
Neste tutorial, você aprenderá a criar relatórios personalizados no Azure Data Explorer (ADX) usando dados dos serviços Microsoft Entra ID e Microsoft Entra ID Governance. Este tutorial complementa outras opções de relatório, como relatório de arquivamento & com o Azure Monitor egestão de direitos, que se concentra na exportação do log de auditoria para o Azure Monitor para retenção e análise. Em comparação, a exportação de dados de ID do Microsoft Entra para o Azure Data Explorer fornece flexibilidade para criar relatórios personalizados em objetos do Microsoft Entra, incluindo objetos históricos e excluídos. Além disso, o uso do Azure Data Explorer permite a agregação de dados de fontes adicionais, com escalabilidade massiva, esquema flexível e políticas de retenção. O Azure Data Explorer é especialmente útil quando você precisa reter dados de acesso por anos, executar investigações ad-hoc ou executar consultas personalizadas em dados de acesso do usuário.
Este artigo ilustra como mostrar a configuração, os usuários e os direitos de acesso exportados do Microsoft Entra juntamente com dados exportados de outras fontes, como aplicativos com direitos de acesso em seus próprios bancos de dados SQL. Em seguida, você pode usar a KQL (Kusto Query Language) no Azure Data Explorer para criar relatórios personalizados com base nos requisitos da sua organização.
Use as seguintes etapas para criar esses relatórios:
- Configurar o Azure Data Explorer em uma assinatura do Azure ou criar um cluster gratuito.
- Extrair dados do Microsoft Entra usando scripts do PowerShell e Microsoft Graph.
- Importe os dados para o Azure Data Explorer, um serviço de análise de dados rápido e escalável.
- Crie uma consulta personalizada usando Kusto Query Language.
Ao final deste tutorial, você poderá desenvolver exibições personalizadas dos direitos de acesso e permissões dos usuários. Essas vistas abrangem diferentes aplicativos que usam ferramentas suportadas pela Microsoft. Você também pode trazer dados de bancos de dados ou aplicativos de terceiros para gerar relatórios sobre eles também.
Pré-requisitos
Se você é novo no Azure Data Explorer e deseja aprender os cenários mostrados neste artigo, você pode obter um cluster gratuito do Azure Data Explorer. Para uso com suporte de produção com um contrato de nível de serviço para o Azure Data Explorer, você precisa de uma assinatura do Azure para hospedar um cluster completo do Azure Data Explorer.
Determine quais dados você deseja incluir em seus relatórios. Os scripts neste artigo fornecem exemplos com dados específicos de usuários, grupos e aplicativos do Microsoft Entra. Esses exemplos destinam-se a ilustrar os tipos de relatórios que você pode gerar com essa abordagem, mas suas necessidades específicas de relatórios podem variar e exigir dados diferentes ou adicionais. Você pode começar com esses objetos e trazer mais tipos de objetos do Microsoft Entra ao longo do tempo.
- Este artigo ilustra a recuperação de dados do Microsoft Entra como um usuário conectado. Para fazer isso, certifique-se de ter as atribuições de função necessárias para recuperar dados do Microsoft Entra. Você precisa das funções com as permissões certas para exportar o tipo de dados do Microsoft Entra com o qual gostaria de trabalhar.
- Dados do usuário: Administrador Global, Administrador de Função Privilegiada, Administrador de Usuário
- Dados de grupos: Administrador Global, Administrador de Função Privilegiada, Administrador de Grupo
- Atribuições de Funções de Aplicativos/Aplicativos: Administrador Global, Administrador de Função Privilegiada, Administrador de Aplicativos, Administrador de Aplicativos na Nuvem
- O Microsoft Graph PowerShell deve ser consentido para permitir a recuperação de objetos do Microsoft Entra por meio do Microsoft Graph. Os exemplos neste tutorial exigem as permissões delegadas User.Read.All, Group.Read.All, Application.Read.All e Directory.Read.All. Se você estiver planejando recuperar dados usando automação sem um usuário conectado, consinta com as permissões de aplicativo correspondentes. Consulte Referência de permissões do Microsoft Graph para obter informações adicionais. Se você ainda não consentiu o Microsoft Graph PowerShell com essas permissões, precisará ser um Administrador Global para executar essa operação de consentimento.
- Este tutorial não ilustra atributos de segurança personalizados. Por padrão, o Administrador Global e outras funções de administrador não incluem permissões para ler atributos de segurança personalizados de usuários do Microsoft Entra. Se você estiver planejando recuperar atributos de segurança personalizados, mais funções e permissões podem ser necessárias.
- No computador onde o Microsoft Graph PowerShell está instalado, verifique se você tem acesso de gravação ao diretório do sistema de arquivos. É aqui que você instala os módulos necessários do Microsoft Graph PowerShell e onde os dados exportados do Microsoft Entra são salvos.
- Verifique se você tem permissões para recuperar dados de outras fontes de dados além do Microsoft Entra.
1: Configurar o Azure Data Explorer
Se você não tiver usado anteriormente o Azure Data Explorer, precisará configurá-lo primeiro. Você pode criar um cluster gratuito sem precisar de uma assinatura do Azure ou de cartão de crédito, ou ainda um cluster completo que exija uma assinatura do Azure. Consulte Guia de início rápido: criar um cluster e um banco de dados do Azure Data Explorer para começar.
2: Conecte-se ao Microsoft Graph e extraia dados do Entra com o PowerShell
Nesta seção, você instala os módulos do Microsoft Graph PowerShell e conecta-se ao Microsoft Graph.
Na primeira vez que sua organização usar esses módulos para esse cenário, você precisará estar em uma função de Administrador Global para permitir que o Microsoft Graph PowerShell conceda consentimento para uso em seu locatário. As interações subsequentes podem usar uma função menos privilegiada.
- Abra o PowerShell.
- Se você não tiver todos os módulos do Microsoft Graph PowerShell instalados, instale os módulos necessários do Microsoft Graph. Os seguintes módulos são necessários para este tutorial:
Microsoft.Graph.Authentication,Microsoft.Graph.Users,Microsoft.Graph.Groups,Microsoft.Graph.Applications,Microsoft.Graph.DirectoryObjects.
$modules = @('Microsoft.Graph.Users', 'Microsoft.Graph.Groups', 'Microsoft.Graph.Applications', 'Microsoft.Graph.DirectoryObjects')
foreach ($module in $modules) {
Install-Module -Name $module -Scope CurrentUser -AllowClobber -Force
}
- Importe os módulos para a sessão atual do PowerShell.
$modules = @('Microsoft.Graph.Users', 'Microsoft.Graph.Groups', 'Microsoft.Graph.Applications', 'Microsoft.Graph.DirectoryObjects')
foreach ($module in $modules) {
Import-Module -Name $module
}
- Conecte-se ao Microsoft Graph.
Connect-MgGraph -Scopes "User.Read.All", "Group.Read.All", "Application.Read.All", "Directory.Read.All" -ContextScope Process
Este comando solicita que você entre com suas credenciais do Microsoft Entra. Depois de iniciar sessão, poderá ter de consentir as permissões necessárias se for a primeira vez que estabelece ligação ou se forem necessárias novas permissões.
Consultas do PowerShell para extrair dados necessários para criar relatórios personalizados no Azure Data Explorer
As consultas a seguir extraem dados do Microsoft Entra do Microsoft Graph usando o PowerShell e exportam os dados para arquivos JSON que são importados para o Azure Data Explorer na seção 3 subsequente. Pode haver vários cenários para gerar relatórios com esse tipo de dados:
- Um auditor gostaria de ver um relatório que liste os membros do grupo para 10 grupos, organizados pelo departamento dos membros.
- Um auditor gostaria de ver um relatório de todos os usuários que tiveram acesso a um aplicativo entre duas datas.
Você também pode trazer dados para o Azure Data Explorer de outras fontes além do Microsoft Entra. Isso permite cenários como:
- Um administrador gostaria de ver todos os usuários adicionados a um aplicativo a partir do ID do Microsoft Entra e seus direitos de acesso no próprio repositório do aplicativo, como bancos de dados SQL.
Esses tipos de relatórios não são incorporados ao Microsoft Entra ID. No entanto, você mesmo pode criar esses relatórios extraindo dados do Entra e combinando-os usando consultas personalizadas no Azure Data Explorer.
Para este tutorial, extraímos dados do Microsoft Entra de várias áreas:
- Informações do usuário, como nome para exibição, UPN e detalhes do trabalho
- Informações do grupo
- Atribuições de aplicativos e funções
Esse conjunto de dados nos permite realizar um amplo conjunto de consultas sobre quem recebeu acesso a um aplicativo, informações de função e o período de tempo associado. Observe que essas são consultas de exemplo, e seus dados e requisitos específicos podem variar do que é mostrado aqui.
Nota
Locatários maiores podem enfrentar limitações de taxa e erros de código 429 que são manipulados pelo módulo Microsoft Graph. O Azure Data Explorer também pode limitar os tamanhos de carregamento de ficheiros.
Nesses scripts do PowerShell, exportamos propriedades selecionadas dos objetos do Microsoft Entra para arquivos JSON. Os dados dessas propriedades exportadas são usados para gerar relatórios personalizados no Azure Data Explorer. As propriedades específicas a seguir foram incluídas nesses exemplos, porque estamos usando esses dados para ilustrar os tipos de relatórios que você pode criar no Azure Data Explorer. Como suas necessidades específicas de relatórios provavelmente variam do que é mostrado, você deve incluir as propriedades específicas nesses scripts que está interessado em exibir em seus relatórios. No entanto, você pode seguir o mesmo padrão mostrado para ajudar a criar seus scripts.
Selecione uma data de captura
Incluímos uma data de instantâneo codificada manualmente que identifica os dados num ficheiro JSON com uma data específica e permite-nos acompanhar ao longo do tempo conjuntos de dados semelhantes no Azure Data Explorer. A data do instantâneo também é útil para comparar alterações nos dados entre duas datas do instantâneo.
$SnapshotDate = "2024-01-11"
Obter dados de usuário do Entra
Esse script exporta propriedades selecionadas do objeto de usuário Entra para um arquivo JSON. Importaremos esses dados para o Azure Data Explorer em uma seção subsequente deste tutorial.
function Export-EntraUsersToJson {
# Define a hash table for property mappings
$propertyMappings = @{
"Id" = "ObjectID"
"DisplayName" = "DisplayName"
"UserPrincipalName" = "UserPrincipalName"
"EmployeeId" = "EmployeeId"
"UserType" = "UserType"
"CreatedDateTime" = "CreatedDateTime"
"JobTitle" = "JobTitle"
"Department" = "Department"
"AccountEnabled" = "AccountEnabled"
# Add custom properties as needed
"custom_extension" = "CustomExtension"
}
# Retrieve users with specified properties and create custom objects directly
$users = Get-MgUser -Select ($propertyMappings.Keys) -All | ForEach-Object {
$userObject = @{}
foreach ($key in $propertyMappings.Keys) {
if ($key -eq "CreatedDateTime") {
# Convert date string directly to DateTime and format it
$date = [datetime]::Parse($_.$key)
$userObject[$propertyMappings[$key]] = $date.ToString("yyyy-MM-dd")
} else {
$userObject[$propertyMappings[$key]] = $_.$key
}
}
# Additional properties or transformations
$userObject["SnapshotDate"] = $SnapshotDate
[pscustomobject]$userObject
}
# Convert the user data to JSON and save it to a file
$users | ConvertTo-Json -Depth 2 | Set-Content ".\EntraUsers.json"
}
# Execute the function
Export-EntraUsersToJson
Obter dados do Grupo
Gere um arquivo JSON com nomes de grupo e IDs que são usados para criar exibições personalizadas no Azure Data Explorer. O exemplo inclui todos os grupos, mas filtragem adicional pode ser incluída, se necessário. Se você estiver filtrando para incluir apenas determinados grupos, convém incluir lógica no script para verificar se há grupos aninhados.
# Get all groups and select Id and DisplayName
$groups = Get-MgGroup -All | Select-Object Id,DisplayName
# Export the groups to a JSON file
$groups | ConvertTo-Json | Set-Content ".\EntraGroups.json"
Obter dados de associação de grupo
Gere um arquivo JSON com associação de grupo que é usado para criar exibições personalizadas no Azure Data Explorer.
# Retrieve all groups from Microsoft Entra (Azure AD)
$groups = Get-MgGroup -All
# Initialize an array to store results
$results = @()
# Iterate over each group
foreach ($group in $groups) {
# Extract the group ID
$groupId = $group.Id
# Get members of the current group and select their IDs
$members = Get-MgGroupMember -GroupId $groupId | Select-Object -ExpandProperty Id
# Add a custom object with group ID and member IDs to the results array
$results += [PSCustomObject]@{
GroupId = $groupId
Members = $members
SnapshotDate = $SnapshotDate
}
# Pause for a short time to avoid rate limits
Start-Sleep -Milliseconds 200
}
# Convert the results array to JSON format and save it to a file
$results | ConvertTo-Json | Set-Content "EntraGroupMembership.json"
Obter dados da entidade de aplicativo e serviço
Gera o arquivo JSON com todos os aplicativos e as entidades de serviço correspondentes no locatário. Importaremos esses dados para o Azure Data Explorer em uma seção subsequente deste tutorial que nos permite gerar relatórios personalizados relacionados a aplicativos com base nesses dados.
# Fetch applications and their corresponding service principals, then export to JSON
Get-MgApplication -All | ForEach-Object {
$app = $_
$sp = Get-MgServicePrincipal -Filter "appId eq '$($app.AppId)'"
[pscustomobject]@{
Name = $app.DisplayName
ApplicationId = $app.AppId
ServicePrincipalId = $sp.Id
SnapshotDate = $SnapshotDate
}
} | ConvertTo-Json -Depth 10 | Set-Content "Applications.json"
Obter dados do AppRole
Gere um arquivo JSON de todos os appRoles para aplicativos corporativos no Microsoft Entra. Depois de importados para o Azure Data Explorer, utilizamos esses dados para gerar relatórios envolvendo atribuições de função de aplicativo para usuários.
# Get a list of all applications, handle pagination manually if necessary
$apps = Get-MgApplication -All
# Loop through each application to gather the desired information
$results = foreach ($app in $apps) {
# Get the service principal for the application using its appId
$spFilter = "appId eq '$($app.AppId)'"
$sp = Get-MgServicePrincipal -Filter $spFilter | Select-Object -First 1
# Process AppRoles, if any, for the application
$appRoles = if ($app.AppRoles) {
$app.AppRoles | Where-Object { $_.AllowedMemberTypes -contains "User" } |
Select-Object Id, Value, DisplayName
}
# Construct a custom object with application and service principal details
[PSCustomObject]@{
ApplicationId = $app.AppId
DisplayName = $app.DisplayName
ServicePrincipalId = $sp.Id
AppRoles = $appRoles
SnapshotDate = $SnapshotDate
}
}
# Export the results to a JSON file
$results | ConvertTo-Json -Depth 4 | Out-File 'AppRoles.json'
Obter dados de atribuição de AppRole
Gere um arquivo JSON de todas as atribuições de função do aplicativo no locatário.
$users = Get-MgUser -All
$result = @()
foreach ($user in $users) {
Get-MgUserAppRoleAssignment -UserId $user.Id | ForEach-Object {
# Use the same date formatting approach
$createdDateTime = $_.CreatedDateTime -replace "\\/Date\((\d+)\)\\/", '$1'
# Convert the milliseconds timestamp to a readable date format if needed
$result += [PSCustomObject]@{
AppRoleId = $_.AppRoleId
CreatedDateTime = $createdDateTime
PrincipalDisplayName = $_.PrincipalDisplayName
PrincipalId = $_.PrincipalId
ResourceDisplayName = $_.ResourceDisplayName
ResourceId = $_.ResourceId
SnapshotDate = $SnapshotDate
}
}
}
$result | ConvertTo-Json -Depth 10 | Out-File "AppRoleAssignments.json"
3: Importar dados de arquivo JSON para o Azure Data Explorer
Nesta seção, importamos os arquivos JSON recém-criados para análise posterior.
Depois de configurar um banco de dados em seu cluster ou cluster livre do Azure Data Explorer, conforme descrito na primeira seção deste documento, navegue até esse banco de dados.
- Entre na interface web do Azure Data Explorer.
- No menu à esquerda, selecione a opção Consulta.
Em seguida, siga estas etapas para cada arquivo JSON exportado, para obter seus dados exportados para esse banco de dados do Azure Data Explorer.
Selecione com o botão direito do mouse no nome do banco de dados onde você deseja ingerir os dados. Selecione Obter dados.
Selecione a fonte de dados na lista disponível. Neste tutorial, você está ingerindo dados de um arquivo local .
Selecione + Nova tabela e insira um nome de tabela, com base no nome do arquivo JSON que você está importando, Por exemplo, se você estiver importando EntraUsers.json, nomeie a tabela EntraUsers. Após a primeira importação, a tabela já existe e você pode selecioná-la como a tabela de destino para uma importação subsequente.
Selecione Procurar arquivos, selecione o arquivo JSON e selecione Avançar.
O Azure Data Explorer deteta automaticamente o esquema e fornece uma visualização na guia Inspecionar. Selecione Concluir para criar a tabela e importar os dados deste arquivo.
Repita cada uma das etapas anteriores para cada um dos arquivos JSON gerados na primeira seção.
4: Usar o Azure Data Explorer para criar relatórios personalizados
Com os dados agora disponíveis no Azure Data Explorer, você está pronto para começar a criar relatórios personalizados com base em seus requisitos de negócios.
O Azure Data Explorer é uma poderosa ferramenta de análise de dados altamente escalável e flexível, fornecendo um ambiente ideal para gerar relatórios de acesso de usuário personalizados. O Azure Data Explorer usa a KQL (Kusto Query Language).
As consultas a seguir fornecem exemplos de relatórios comuns, mas você pode personalizá-los para atender às suas necessidades e criar relatórios adicionais.
Exemplo 1: Gerar atribuições de função de aplicativo para atribuições diretas e de grupo para uma data de instantâneo específica
Este relatório fornece uma visão de quem teve que acesso e quando ao aplicativo de destino e pode ser usado para auditorias de segurança, verificação de conformidade e compreensão de padrões de acesso dentro da organização.
Esta consulta destina-se a uma aplicação específica no Microsoft Entra AD e analisa as atribuições de função a partir de uma determinada data. A consulta recupera atribuições de função diretas e baseadas em grupo, mesclando esses dados com detalhes do usuário da tabela EntraUsers e informações de função da tabela AppRoles.
/// Define constants
let targetServicePrincipalId = "<your service principal-id>"; // Target Service Principal ID
let targetSnapshotDate = datetime("2024-01-13"); // Target Snapshot Date for the data
// Extract role assignments for the target Service Principal and Snapshot Date
let roleAssignments = AppRoleAssignments
| where ResourceId == targetServicePrincipalId and startofday(SnapshotDate) == targetSnapshotDate
| extend AppRoleIdStr = tostring(AppRoleId); // Convert AppRoleId to string for easier comparison
// Prepare user data from EntraUsers table
let users = EntraUsers
| project ObjectID, UserPrincipalName, DisplayName, ObjectIDStr = tostring(ObjectID); // Include ObjectID as string for joining
// Prepare role data from AppRoles table
let roles = AppRoles
| mvexpand AppRoles // Expand AppRoles to handle multiple roles
| extend RoleName = AppRoles.DisplayName, RoleId = tostring(AppRoles.Id) // Extract Role Name and ID
| project RoleId, RoleName;
// Process direct assignments
let directAssignments = roleAssignments
| join kind=inner users on $left.PrincipalId == $right.ObjectID // Join with EntraUsers on PrincipalId
| join kind=inner roles on $left.AppRoleIdStr == $right.RoleId // Join with roles to get Role Names
| project UserPrincipalName, DisplayName, CreatedDateTime, RoleName, AssignmentType = "Direct", SnapshotDate;
// Process group-based assignments
let groupAssignments = roleAssignments
| join kind=inner EntraGroupMembership on $left.PrincipalId == $right.GroupId // Join with Group Membership
| mvexpand Members // Expand group members
| extend MembersStr = tostring(Members) // Convert member ID to string
| distinct MembersStr, CreatedDateTime, AppRoleIdStr, SnapshotDate // Get distinct values
| join kind=inner users on $left.MembersStr == $right.ObjectIDStr // Join with EntraUsers for user details
| join kind=inner roles on $left.AppRoleIdStr == $right.RoleId // Join with roles for role names
| project UserPrincipalName, DisplayName, CreatedDateTime, RoleName, AssignmentType = "Group", SnapshotDate;
// Combine results from direct and group-based assignments
directAssignments
| union groupAssignments
Exemplo 2: Criar Relatório de Auditor Básico com dados do Entra mostrando quem teve acesso a um aplicativo entre essas duas datas
Este relatório fornece uma visão de quem tinha que acesso ao aplicativo de destino entre duas datas e pode ser usado para auditorias de segurança, verificação de conformidade e compreensão de padrões de acesso dentro da organização.
Esta consulta destina-se a uma aplicação específica no Microsoft Entra ID e analisa as atribuições de função entre duas datas. A consulta recupera atribuições diretas de função da tabela AppRoleAssignments e mescla esses dados com detalhes do usuário da tabela EntraUsers e informações de função da tabela AppRoles.
// Set the date range and service principal ID for the query
let startDate = datetime('2024-01-01');
let endDate = datetime('2024-03-14');
let servicePrincipalId = "<your service principal-id>";
// Query AppRoleAssignments for the specified service principal within the date range
AppRoleAssignments
| where ResourceId == servicePrincipalId and
todatetime(CreatedDateTime) between (startDate .. endDate)
// Extend AppRoleId to a string for joining
| extend AppRoleIdStr = tostring(AppRoleId)
// Project the necessary fields for the join with EntraUsers and AppRoles
| project PrincipalId, AppRoleIdStr, CreatedDateTime
// Join with EntraUsers to get user details
| join kind=inner (EntraUsers | project UserPrincipalName, DisplayName, ObjectID) on $left.PrincipalId == $right.ObjectID
// Join with AppRoles to get the role display names
| join kind=inner (
AppRoles | mvexpand AppRoles | project RoleIdStr = tostring(AppRoles.Id), RoleDisplayName = tostring(AppRoles.DisplayName)
) on $left.AppRoleIdStr == $right.RoleIdStr
// Final projection of the report with the current date and time
| project UserPrincipalName, DisplayName, RoleDisplayName, CreatedDateTime, ReportDate = now()
Exemplo 3: Obter usuários adicionados a um aplicativo entre duas datas de instantâneo de dados
Esses relatórios fornecem uma exibição de quais usuários receberam uma atribuição de função de aplicativo para o aplicativo de destino entre duas datas. Esses relatórios podem ser usados para acompanhar as alterações no acesso ao aplicativo ao longo do tempo.
Esta consulta destina-se a uma aplicação específica no Microsoft Entra ID e altera as atribuições de função entre uma data de início e uma data de fim.
// Define the date range and service principal ID for the query
let startDate = datetime("2024-03-01");
let endDate = datetime("2024-03-14");
let servicePrincipalId = "<your service principal-id>";
let earlierDate = startDate; // Update this to your specific earlier date
AppRoleAssignments
| where SnapshotDate < endDate and ResourceId == servicePrincipalId
| project PrincipalId, AppRoleId2 = tostring(AppRoleId), CreatedDateTime
| join kind=anti (
AppRoleAssignments
| where SnapshotDate < earlierDate and ResourceId == servicePrincipalId
| project PrincipalId, AppRoleId1 = tostring(AppRoleId)
) on PrincipalId
| join kind=inner (EntraUsers) on $left.PrincipalId == $right.ObjectID
| join kind=inner (AppRoles
| mvexpand AppRoles
| project AppRoleId=tostring(AppRoles.Id), RoleDisplayName=tostring(AppRoles.DisplayName)
) on $left.AppRoleId2 == $right.AppRoleId
| project UserPrincipalName, DisplayName, RoleDisplayName, CreatedDateTime, PrincipalId, Change = "Added"
Trazer dados de outras fontes
Você também pode criar tabelas adicionais no Azure Data Explorer para ingerir dados de outras fontes. Se os dados estiverem num ficheiro JSON, semelhante aos exemplos acima, ou num ficheiro CSV, pode criar a tabela quando obtiver dados do ficheiro.
Para obter mais informações sobre ingestão de dados, consulte Visão geral da ingestão de dados do Azure Data Explorer.
Exemplo 4: Combinar atribuições de aplicativos de um Entra e uma segunda fonte para criar um relatório de todos os usuários que tiveram acesso a um aplicativo entre duas datas
Este relatório ilustra como você pode combinar dados de dois sistemas separados para criar relatórios personalizados no Azure Data Explorer. Ele agrega dados sobre usuários, suas funções e outros atributos de dois sistemas em um formato unificado para análise ou relatório.
Este exemplo pressupõe que há uma tabela chamada salesforceAssignments que foi preenchida trazendo dados de outro aplicativo.
// Define the date range and service principal ID for the query
let startDate = datetime("2023-06-01");
let endDate = datetime("2024-03-13");
let servicePrincipalId = "<your service principal-id>";
// Pre-process AppRoleAssignments with specific filters and projections
let processedAppRoleAssignments = AppRoleAssignments
| where ResourceId == servicePrincipalId and todatetime(CreatedDateTime) between (startDate .. endDate)
| extend AppRoleId = tostring(AppRoleId)
| project PrincipalId, AppRoleId, CreatedDateTime, ResourceDisplayName; // Exclude DeletedDateTime and keep ResourceDisplayName
// Pre-process AppRoles to get RoleDisplayName for each role
let processedAppRoles = AppRoles
| mvexpand AppRoles
| project AppRoleId = tostring(AppRoles.Id), RoleDisplayName = tostring(AppRoles.DisplayName);
// Main query: Process EntraUsers by joining with processed role assignments and roles
EntraUsers
| join kind=inner processedAppRoleAssignments on $left.ObjectID == $right.PrincipalId // Join with role assignments
| join kind=inner processedAppRoles on $left.AppRoleId == $right.AppRoleId // Join with roles to get display names
// Summarize to get the latest record for each unique combination of user and role attributes
| summarize arg_max(AccountEnabled, *) by UserPrincipalName, DisplayName, tostring(EmployeeId), Department, JobTitle, ResourceDisplayName, RoleDisplayName, CreatedDateTime
// Final projection of relevant fields including source indicator and report date
| project UserPrincipalName, DisplayName, EmployeeId=tostring(EmployeeId), Department, JobTitle, AccountEnabled=tostring(AccountEnabled), ResourceDisplayName, RoleDisplayName, CreatedDateTime, Source="EntraUsers", ReportDate = now()
// Union with processed salesforceAssignments to create a combined report
| union (
salesforceAssignments
// Project fields from salesforceAssignments to align with the EntraUsers data structure
| project UserPrincipalName = UserName, DisplayName = Name, EmployeeId = tostring(EmployeeId), Department, JobTitle, AccountEnabled = "N/A", ResourceDisplayName = AppName, RoleDisplayName = Role, CreatedDateTime, Source = "salesforceAssignments", ReportDate = now()
)