I’ve posted stuff about some of the slick ways to migrate data from one place to another before. Scripts, linked tables, scheduled tasks, migrant farm workers, robots, carrier pigeons, and so on. I can’t remember where or when I did that, but trust me, I did. Or at least, I think I did.
So, what if you have a few basic tools at hand, but are afraid to monkey with their internal goodies? Or, what if you just don’t have time to learn one of the more “proper” ways, but simply need to “get it done” because you have an angry boss standing over you with a rolled-up newspaper, waiting to beat you as soon as you pause to breathe?
What if? I’m glad you asked. Well, if you need to pull a custom query result from Active Directory into a SQL database (or Excel, for those of you still dragging your knuckles), here’s how. You’ll need two basic ingredients:
- Active Directory Users and Computers (or “ADUC”)
- SQL Server Express (2012 or later. I prefer 2014)
Part 1 – Create and Export an AD Saved Query
- Right-click on Saved Queries, select New / Query
- Do your magical query stuff (e.g. “(&(&(&(objectCategory=computer)(msTPM-OwnerInformation=*))))” )
- Give it a Name and (optional) Description, and Save it.
- Run the query to see if you get the desired results.
- Right-click on the Saved Query, and select “Export List”
- Save the output to a suitable location (and be sure to review the “Save as type” drop-down list options!). For this example, I leave the tab-delimited text option as the default.
Part 2 – Import it into SQL Server
(note: this works just increditastically with SQL Express as well. I recommend SQL Server Express 2014)
- Open the SQL Server Management Studio console.
- Expand Databases and right-click on the desired Database.
- Select Tasks / Import.
- Select the Data Source option “Flat File Source”.
- Select the File to import and confirm the other Format options.
- Click the “Columns” and “Preview” entries in the left-hand panel to preview the column results, then click Next.
- Select the destination Data Source (e.g. “SQL Server Native Client XX.x”) and enter the server name. (note: If you click “Browse” and are working in an Active Directory environment, you might grow old waiting for it to enumerate every server).
- Set the desired Authentication options (if needed).
- Select or enter the destination Database name, and click Next.
- Review the “Edit Mappings” and “Preview” options (if desired), and click Next.
- Check “Run immediately” and click Next.
- Click Finish.
- Navigate to the new/updated Table within SQL Management Studio and relish the glory of it all.
Bonus points: Turn around and proclaim “I! Yes, I! I have imported data!! I am a god among humans! Bow before me, mortals! I desire doughnuts and coffee.“