?
Solved

Powershell Script issue

Posted on 2014-10-28
9
Medium Priority
?
228 Views
Last Modified: 2015-03-04
Trying to pull data from a SQL Database and add to AD as a contacts. The Display name comes over but nothing else. Any ideas on what's wrong with this script?

Import-Module ActiveDirectory
Import-Module SQLPS
Invoke-Sqlcmd -ServerInstance dataServer -Database Database1 -u "sa" -password "password" -Query `
 "select Top 10 FirstName, LastName, Title, Email, Phone from dbo.CustomerContacts" |
select @{l='Name';e={$_.firstname+" "+$_.lastname}},
 @{l='SamAccountName';e={$_.firstname.tolower().substring(0,1)+$_.lastname.tolower()}},
 @{l='UserPrincipalName';e={$_.firstname.tolower().substring(0,1)+$_.lastname.tolower()}},
 @{l='DisplayName';e={$_.firstname+" "+$_.lastname}},
 @{l='GivenName';e={$_.firstname}},
 @{l='Surname';e={$_.lastname}},
 @{l='telephoneNumber';e={$_.phone}},
 title, email |
New-ADObject -Type Contact -Path "OU=DealerContacts,OU=users - gpo,DC=korgusa,DC=com" -PassThru |
select Name, SamAccountName, UserPrincipalName, DisplayName, GivenName, Surname, telephoneNumber, title, email | ft -auto
0
Comment
Question by:jcroy727
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
9 Comments
 
LVL 71

Expert Comment

by:Chris Dent
ID: 40410142
Because only DisplayName is acceptable down through the pipeline. You'll have to pass the rest as a HashTable using the OtherAttributes parameter.
Import-Module ActiveDirectory
Import-Module SQLPS

Invoke-Sqlcmd -ServerInstance dataServer -Database Database1 -u "sa" -password "password" -Query `
   "select Top 10 FirstName, LastName, Title, Email, Phone from dbo.CustomerContacts" |
  ForEach-Object {
    $OtherAttributes = @{
      Name              = "$($_.firstname) $($_.lastname)"
      SamAccountName    = "$($_.firstname.ToLower()[0])$($_.lastname.ToLower())"
      UserPrincipalName = "$($_.firstname.ToLower()[0])$($_.lastname.ToLower())@korgusa.com"
      GivenName         = $_.firstname
      sn                = $_.lastname
      telephoneNumber   = $_.phone
      title             = $_.title
      mail              = $_.email
    }
  
    New-ADObject -Type Contact -DisplayName $_.DisplayName -OtherAttributes $OtherAttributes -Path "OU=DealerContacts,OU=users - gpo,DC=korgusa,DC=com" -PassThru
  } |
  Select-Object Name, SamAccountName, UserPrincipalName, DisplayName, GivenName, sn, telephoneNumber, title, mail |
  ft -auto

Open in new window

I hvaen't tested it, can't here I'm afraid. It may need further modification but I'm sure it'll shout at you if it's not quite right.

Chris
0
 

Author Comment

by:jcroy727
ID: 40410726
Thanks for the reply. On the initial try, it is asking me to enter a name when I run the script. So there must be a syntax error in there somewhere. I will play around with it. Thanks again for the guidance. If you de see anything that stands out, please let me know. I pasted the output below so you can see it for yourself.

PS SQLSERVER:\> C:\Users\Administrator.KORGUSANT\Documents\NEW-CreateAD-User-Test.ps1
WARNING: The names of some imported commands from the module 'SQLPS' include unapproved verbs that might make them less discoverable. To find th
e commands with unapproved verbs, run the Import-Module command again with the Verbose parameter. For a list of approved verbs, type Get-Verb.
cmdlet New-ADObject at command pipeline position 1
Supply values for the following parameters:
Name:
0
 
LVL 71

Accepted Solution

by:
Chris Dent earned 2000 total points
ID: 40410871
Ahh we just need to move Name from the other attributes set back into the main CmdLet call:
Import-Module ActiveDirectory
Import-Module SQLPS

Invoke-Sqlcmd -ServerInstance dataServer -Database Database1 -u "sa" -password "password" -Query `
   "select Top 10 FirstName, LastName, Title, Email, Phone from dbo.CustomerContacts" |
  ForEach-Object {
    $OtherAttributes = @{
      SamAccountName    = "$($_.firstname.ToLower()[0])$($_.lastname.ToLower())"
      UserPrincipalName = "$($_.firstname.ToLower()[0])$($_.lastname.ToLower())@korgusa.com"
      GivenName         = $_.firstname
      sn                = $_.lastname
      telephoneNumber   = $_.phone
      title             = $_.title
      mail              = $_.email
    }
  
    New-ADObject -Name "$($_.firstname) $($_.lastname)" -Type Contact -DisplayName $_.DisplayName -OtherAttributes $OtherAttributes -Path "OU=DealerContacts,OU=users - gpo,DC=korgusa,DC=com" -PassThru
  } |
  Select-Object Name, SamAccountName, UserPrincipalName, DisplayName, GivenName, sn, telephoneNumber, title, mail |
  ft -auto

Open in new window

Chris
0
WatchGuard's M Series Appliances - Miecom Approved

WatchGuard's newest M series appliances were put to the test by Miercom.  We had great results and outperformed all of our competitors in both stateless and stateful traffic throghput scenarios! Ready to see how your UTM appliance stacked up? Download the Miercom Report!

 

Author Comment

by:jcroy727
ID: 40410973
Now getting this message:

New-ADObject : Illegal modify operation. Some aspect of the modification is not permitted
At C:\Users\Administrator.KORGUSANT\Documents\NEW-CreateAD-User-Test.ps1:17 char:5
+     New-ADObject -Name "$($_.firstname) $($_.lastname)" -Type Contact -DisplayNa ...
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidOperation: (cn=Alan Alan,OU...=korgusa,DC=com:String) [New-ADObject], ADIllegalModifyOperationException
    + FullyQualifiedErrorId : ActiveDirectoryServer:8311,Microsoft.ActiveDirectory.Management.Commands.NewADObject
0
 
LVL 71

Expert Comment

by:Chris Dent
ID: 40411013
Hmm drop the items out of the OtherAttributes list one at a time until it goes away?

We might be exceeding a maximum length, or writing an attribute which doesn't exist... which brings to mind SamAccountName and UserPrincipalName, are you sure you mean to create contacts?
0
 

Author Comment

by:jcroy727
ID: 40411150
Yes, Contacts. And you are correct. Did not those two. It still generates an error but the Contacts are created in AD with all the requested properties. The error is below if you are interested but I can't thank you enough for all your help!

New-ADObject : The server is unwilling to process the request
At C:\Users\Administrator.KORGUSANT\Documents\NEW-CreateAD-User-Test.ps1:16 char:5
+     New-ADObject -Name "$($_.firstname) $($_.lastname)" -Type Contact -DisplayNa ...
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (cn=Paul McDermo...=korgusa,DC=com:String) [New-ADObject], ADException
    + FullyQualifiedErrorId : ActiveDirectoryServer:0,Microsoft.ActiveDirectory.Management.Commands.NewADObject
0
 
LVL 71

Expert Comment

by:Chris Dent
ID: 40411205
Getting there :)

Things that can cause that error include:

Name conflicts (within the OU), empty or unexpected values, etc, etc.

Perhaps let's start with the latter, is anything in the CSV empty?

Chris
0
 

Author Comment

by:jcroy727
ID: 40411651
Not using a CSV. Pulling the data directly from the database.
0
 
LVL 71

Expert Comment

by:Chris Dent
ID: 40411769
Of course, sorry. Any of the fields likely to be DbNull?
0

Featured Post

Four New Appliances. Same Industry-leading Speeds.

But don't take it from us.  The Firebox M370 is Miercom tested and Miercom approved, outperforming its competitors for stateless and stateful traffic throughput scenarios.  Learn more about the M370, M470, M570 and M670 and find the right solution for your organization today!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Had a business requirement to store the mobile number in an environmental variable. This is just a quick article on how this was done.
This tutorial will walk an individual through the process of transferring the five major, necessary Active Directory Roles, commonly referred to as the FSMO roles from a Windows Server 2008 domain controller to a Windows Server 2012 domain controlle…
This tutorial will walk an individual through the process of transferring the five major, necessary Active Directory Roles, commonly referred to as the FSMO roles to another domain controller. Log onto the new domain controller with a user account t…
Suggested Courses

764 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question