AD - Exportar Usuários para o Google Sheets

Através de APIs o Google permite que você realize a integração com todos os Serviços disponíveis, sendo assim você pode desenvolver seus scripts um Python, PowerShell, Shell Script, etc. e realizar a integração através do um Token.

Neste artigo eu mostro todos os passos para exportar uma lista de usuários do Active Directory para o Google Sheets.

01 - Passo

Acesse o link : console.cloud.google.com e clique no menu :

  • APIs & Services > CREATE PROJECT

02 - Passo

Preencha todos os campos com seus dados para criar o Projeto, em seguida clique em CREATE.

  • Project name : active-directory
  • Project ID : active-directory-01
  • Organization : 100security.com.br
  • Location : 100security.com.br

03 - Passo

Assim que o projeto for criado clique em + ENABLE APIS AND SERVICES.

04 - Passo

Pesquise por Google Sheets API.

05 - Passo

Clique em ENABLE para habilitar a API.


API habilitada!

06 - Passo

Clique no menu Credentials.

07 - Passo

Clique em + CREATE CREDENTIALS e Service Account

08 - Passo

Preencha todos os campos para criar a Conta de Serviço, em seguida clique em CREATE.

  • Service account name : 100security
  • Service account ID : id-00security

09 - Passo

Atribua as Permissões no projeto.

  • Project > Owner

10 - Passo

Clique em CONTINUE.

11 - Passo

Clique em DONE.

12 - Passo

Copie o E-mail gerado, para utilizar no script.

13 - Passo

Clique em APIs & Services > Service Accounts

14 - Passo

Clique sobre o E-mail, para visualizar as Propriedades.


Detalhes da Conta.

15 - Passo

Clique em KEYS

  • ADD KEY > Create new key

16 - Passo

Selecione o tipo P12 e clique em CREATE.

17 - Passo

Defina uma Senha, em seguida clique em CLOSE.

  • Será gerado o Certificado : active-directory-01-2883f0047bc5.p12

Status Active.

18 - Passo

Copie o certificado active-directory-01-2883f0047bc5.p12 para o servidor do Active Directory ou para o computador que você utiliza para extrair os dados.

Execute o comando abaixo para instalar os módulos de integração com o Google :

  Windows 
PS C:\Users\Administrator> Install-Module UMN-Google

[ A ] Yes to All

19 - Passo

Após a instalação crie o arquivo API.ps1, com o seguinte conteúdo :

Import-Module UMN-Google

# Set security protocol to TLS 1.2 to avoid TLS errors
[Net.ServicePointManager]::SecurityProtocol = [Net.SecurityProtocolType]::Tls12

# Google API Authozation
$scope = "https://www.googleapis.com/auth/spreadsheets https://www.googleapis.com/auth/drive https://www.googleapis.com/auth/drive.file"
$certPath = "C:\Users\Administrator\Desktop\active-directory-01-2883f0047bc5.p12"
$iss = '[email protected]'
$certPswd = 'notasecret'
$accessToken = Get-GOAuthTokenService -scope $scope -certPath $certPath -certPswd $certPswd -iss $iss

$accessToken

20 - Passo

Execute o script API.ps1 para verificar se a conexão está sendo estabelecida, deve aparecer o Token como na imagem abaixo :

21 - Passo

Crie e Execute o arquivo Sheet.ps1 com o seguinte conteúdo para validar se a Planilha está sendo criada com sucesso!.

  • A variável $Title = 'Active Directory' define o nome da Planilha.
  • A variável Sheet = 'Users' define o nome da Aba na Planilha.
# Create new spreadsheet
$Title = 'Active Directory'
$SpreadsheetID = (New-GSheetSpreadSheet -accessToken $accessToken -title $Title).spreadsheetId
$SpreadsheetID

# Create new sheet
$Sheet = 'Users'
Add-GSheetSheet -accessToken $accessToken -sheetName $Sheet -spreadSheetID $SpreadsheetID

Observe que foi gerado um ID.

  • 1srVMs4ua7-GjwxxHO38-ccaGqiB-7DXYmeM-BJZuq_Y

22 - Passo

Tente acessar a Planilha informando o ID.

  • https://docs.google.com/spreadsheets/d/1srVMs4ua7-GjwxxHO38-ccaGqiB-7DXYmeM-BJZuq_Y

Se você receber a mensagem que Precisa de Acesso, significa que o Google Drive API não esta habilitado!.


O mesmo erro vai ocorrer se tentar executar via script.

Observe que adicionei as linhas a seguir no script API.ps1 atribuindo o acesso para o e-mail [email protected].

# Create Sheet
$SpreadsheetID = '1srVMs4ua7-GjwxxHO38-ccaGqiB-7DXYmeM-BJZuq_Y'
Set-GFilePermissions -accessToken $accessToken -fileID $SpreadsheetID -role writer -type user -emailAddress '[email protected]'

23 - Passo

Para ativar o Google Drive API acesse o menu :

  • APIs & Services > + ENABLE APIS AND SERVICES
  • Pesquise por : Google Drive API

24 - Passo

Clique em ENABLE para habilitar a API.


API Habilitada com sucesso!

25 - Passo

Execute o script API.ps1 novamente e observe que a permissão foi atribuída com sucesso!

26 - Passo

Acesse a Planilha e observe que a aba Users foi criada e o nome da planilha foi definido como Active Directory.

27 - Passo

Adicionei ao script API.ps1 o processo de extração da lista de Usuários do Active Directory, basta executar.

Import-Module UMN-Google

# Set security protocol to TLS 1.2 to avoid TLS errors
[Net.ServicePointManager]::SecurityProtocol = [Net.SecurityProtocolType]::Tls12

# Google API Authozation
$scope = "https://www.googleapis.com/auth/spreadsheets https://www.googleapis.com/auth/drive https://www.googleapis.com/auth/drive.file"
$certPath = "C:\Users\Administrator\Desktop\active-directory-01-2883f0047bc5.p12"
$iss = '[email protected]'
$certPswd = 'notasecret'
$accessToken = Get-GOAuthTokenService -scope $scope -certPath $certPath -certPswd $certPswd -iss $iss

# $accessToken

# Create Sheet
$SpreadsheetID = '1srVMs4ua7-GjwxxHO38-ccaGqiB-7DXYmeM-BJZuq_Y'
#Set-GFilePermissions -accessToken $accessToken -fileID $SpreadsheetID -role writer -type user -emailAddress '[email protected]'

#-- All Users
$directorypath = (Get-Item -Path ".\").FullName
$csv = "ad-users.csv"

#-- Import Module
Import-Module ActiveDirectory

#-- Filter
$users = @(Get-ADUser -filter * -Properties SamAccountName, Name, Mail)

$result = @($users | Select-Object SamAccountName, Name, Mail)

#-- Order by (A-Z)
$a = 'SamAccountName'
$order = ($a | Sort-Object)
$result = $result | Sort $order

#-- Export to CSV
$result | Sort Company | Export-Csv $csv -NoTypeInformation -Encoding Utf8

# Import CSV
$import = New-Object System.Collections.ArrayList($null)

# Build sheet header as it isn't included automatically
$import.Add( @("SamAccountName", "Name", "Mail"))


# Build ArrayList
$inputCsv = Import-Csv $csv
$inputCsv | ForEach-Object { 
    $import.Add(@($_.SamAccountName, $_.Name, $_.Mail))
}

#-- Remove Sheet1
Remove-GSheetSheet -accessToken $accessToken -sheetName Sheet1 -spreadSheetID $SpreadsheetID

#-- Import data
Set-GSheetData -accessToken $accessToken -rangeA1 "A1:C$($import.Count)" -sheetName "Users" -spreadSheetID $SpreadsheetID -values $import -Debug -Verbose

28 - Passo

Resultado final, contendo a lista de Usuários do Active Directory.



👍 Se este artigo te ajudou compartilhe!



  Autor

Marcos Henrique

 São Paulo/SP



  Windows Server

© 2024 - 100SECURITY

Contato