Using PowerShell to export Active Directory information


By:   |   Updated: 2016-02-22   |   Comments (1)   |   Related: More > PowerShell


Problem

You need to export the information from Active Directory in automated way, one way to do that is using PowerShell.

Solution

With PowerShell it's possible to export the data to 3 types of files (TXT, CSV and XML).

To get Active Directory information using PowerShell, first, it's necessary to install the PowerShell module into the server. Open Server Manager, select Features and select "Add Features" then navigate as shown below and select "Active Directory module for Windows PowerShell".

powershellmodule

Next, run the command Import-Module in PowerShell.

importmodule

I've tried to export the information into CSV and TXT, but some data would truncate or change characters, so I choose XML and that works very well. To export the XML file just put | Export-Clixml after the command below along with a file name to capture the data. I just selected 5 fields to export, but Active Directory has many more attributes.

xmlexport

The result file:

XML

To automate the execution, open Notepad, copy and paste the below commands and save the file as C:\ADInfo.ps1 extension.

Import-Module ActiveDirectory;

Get-ADUser -Filter * -Properties GivenName,Surname,Title,Department,City | Export-Clixml R:\Ad.xml;

The Windows Task Scheduler is used to run the script with a date and time configured. You can see below the program used is PowerShell and the argument is -file along with the script file and path.

XMLtask

If you try to run the task it may show an error like "cannot be loaded because running scripts is disabled on this system". To run the script without error, you need to set the execution policy to RemoteSigned, run the command Set-ExecutionPolicy with administrator rights.

psscript
Next Steps


Last Updated: 2016-02-22


get scripts

next tip button



About the author
MSSQLTips author Douglas Correa Douglas Correa is a database professional, focused on tuning, high-availability and infrastructure.

View all my tips
Related Resources





Comments For This Article




Monday, February 22, 2016 - 2:15:56 PM - Jeremy Kadlec Back To Top (40747)

Douglas,

Congrats on your first tip and welcome to the team!

Thank you,
Jeremy Kadlec
Community Co-Leader



download





Recommended Reading

Setting the PowerShell Execution Policy

How to find a specific text string in a SQL Server Stored Procedure, Function, View or Trigger

Using PowerShell to Work with Directories and Files

Bulk Copy Data from Oracle to SQL Server

Execute SQL Server Stored Procedures from PowerShell








get free sql tips
agree to terms


Learn more about SQL Server tools