question

leadtheway avatar image
leadtheway asked

Populate table with AD attributes

What we are trying to accomplish is we are working in GP and need to pull user into into specific tables in SQL. Was hoping to get help with that process. As below


For the email address

SY01200.INET1


For phone number

UPR00102.PHONE1 –Cell

UPR00102.PHONE3 –Work


Its a sub form on user details record in GP

sqlserver2012alter-tableactive-directory
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

anthony.green avatar image
anthony.green answered

Powershell is the tool for this. Use the get-ad* cmdlets and pipe the data into a sql connection and store it in the table in the right format.

10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

leadtheway avatar image
leadtheway answered

the table already exists with data in other fields, how can i make sure the data matches the right record? also do you have an example possibly how that would look?

1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write it to a staging table then use an upsert methodology to do an insert or update depending on the requirement.

don’t have an example to hand at the moment as on leave, so no access to my script repos.

best off installing dbatools from the psgallery and the ad tools from rsat

then using a combination of get-aduser, convert-dbadatatable and write-dbadatatable.

0 Likes 0 ·
Jeff Moden avatar image
Jeff Moden answered

@anthony.green

Thank you, good Sir. I learned something new today.

2 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

I think I may need to add that to the resume now.


Used to do all my AD stuff via traditional LDAP queries but then it just got to complicated as the domain grew.

Must of been a simpler approach and with one line in powershell it replaced over 100 lines of t-sql.

Think Microsoft need to give powershell the love of deserves



0 Likes 0 ·

Totally agree on giving PoSH some love...just not as much as some people give it. I remember when it first came out and it was all the rage to build a central system to control all the backups on all the servers... without considering what happens to all those servers if that central system went offline ever for a couple of hours. My point is, too many people lean on PowerShell to do things that can and should be done in T-SQL or even at the command line.

As a bit of a sidebar, I'll probably never use convert-dbadatatable or write-dbadatatable because I have no problem calling PoSH from T-SQL using xp_CmdShell and simply processing the output of the PoSH from the Cmd returns.

Heh... for me, it's like everything else with computers... "It Depends".

Thank you again, though, for the hints in using PoSH with AD. That's good stuff and I really appreciate it. And thank you for the compliment... I didn't miss it. I'm humbled.


0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.