Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Using PowerShell to export Active Directory information


By:   |   Last Updated: 2016-02-22   |   Comments (1)   |   Related Tips: 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


next webcast button


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




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



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

Douglas,

Congrats on your first tip and welcome to the team!

Thank you,
Jeremy Kadlec
Community Co-Leader


Learn more about SQL Server tools