Export Active Directory users to MS SQL Server

Today's my script was written while i've been developing billing-system for Asterisk.

Every user in Active Directory has an attribute with phone number. We will take this attribute and some others into MS SQL Server to use in billing.

There are tons of scripts that can export AD users into scv-file, but none that will export users directly to SQL database. Of course we can export users into scv and then take this csv and import it into SQL database with the help of SSIS-scripts. But this method will generate two scripts, and if you have more than 5-10-15 AD domains, you will have 10-20-30 scripts, which is likely will lead to errors.

So I wrote a single script to manage my task.

To get the script working, you will need Quest ActiveRoles Managemet Shell for Active Directory. And you will need to provide an argument (parameter) to the script - FQDN-name of the Active Directory domain.
Also you will need to edit script on the 7th sctring - provide an address to SQL-server and the name of the database.

Add-PSSnapin Quest.ActiveRoles.ADManagement
if (!$args[0]) {
  Write-Host "Domain name was not defined!"
} else {
  $domain = $args[0];
  $users = Get-QADUser -Enabled -ou "$domain/" -Service "$domain" | Select-Object telephoneNumber, mail, mobile, email, logonname, description, displayname, UserPrincipalName;
  $conn = New-Object System.Data.SqlClient.SqlConnection("Data Source=<sql-server>; Initial Catalog=<database>; Integrated Security=SSPI");
  foreach ($item in $users) {
    $cmd = $conn.createcommand();
    $cmd.commandtext = "INSERT users (telephoneNumber, mail, mobile, email, logonname, description, displayname, UserPrincipalName)
        VALUES ('$($item.telephoneNumber)', '$($item.mail)', '$($item.mobile)', '$($item.email)', '$($item.logonname)',
        '$($item.description)', '$($item.displayname)', '$($item.UserPrincipalName)')";
    #try {
    #catch [system.exception] {
    #  "caught a system exception"


Tags: active directory (ru), script, powershell (ru)


Add comment

Security code