We move information about users in Active Directory and Sharepoint using PowerShell

It so happens that in a large organization subject to complex corporate rules, there are several points of penetration of information from the real world into the world of production! For example - a small trading company kept a list of its employees in 1C - with the help of a hired employee from the personnel department - and then - once, and grew into a large company - with an extensive network of branches and a large IT infrastructure! A large number of employees were hired - in haste (with the involvement of integrators, of course) the AD infrastructure was built - users were set up, websites were configured, group policies were working, etc ... and suddenly - it turns out that there is zero information in AD about users! Well, that is, the name - last name, OU, group membership, password, logon script - and that's it! Neither your name, nor your room, nor your telephone — where he is and who he is are empty fields. What to do???



Or take my situation — come to work as a small cog in a large corporate system! It is distributed throughout the country - and was not organized - but during my work - I pulled myself up to the realities of the modern world, and instead of taking a single domain in each branch not connected with the others - I built a big AD for the whole country! And my users went to the new progressive future through ADMT, and their workstations went, and they themselves! Both OU and group policies with logon scripts, operating WMI filters, and so on were quickly organized. But only here their old domain did not keep any knowledge about them - not about the office where they were sitting, nor about their names, nor about the department where they work! And so they went, sorry, naked to a new domain, which in itself is indecent! And from here the first task has grown:

1) Fill user data in AD using powershell

It has to be said that, despite the fact that we had a branch located far from the center of both Russia and the organization, but we also have enthusiastic people! Well, where to go without them! And therefore, at one time - one of the good companions was written utility to the local personnel 1C: Enterprise, which allowed unloading everything that the personnel department leads to an employee - into a regular Excel table. There is Excel - there is a powershell - it means you can cram data into AD! Go:

')

Here I’ll retreat a bit and tell you what is necessary in order to start communicating with AD using powershell.



The first way to start communicating with AD from Powershell is to install the Microsoft Remote Server Administration Tolls (RSAT) package on a workstation. In addition, in order for everything to work, the active directory web services must be installed on your domain controller, which is not always possible. For example, in my branch I can administer my OU - but I cannot, put anything on the domain controller - there is not enough rights.



But there is a way out of this situation. Good Quest Software has developed its free solution for addressing AD from PowerShell - Active Roles Management Shell for Active Directory (link). The package is also installed on a workstation with an operating system of at least Windows XP. In this case, nothing is needed to put on the domain controller - everything works out of the box.



So back to the task of obtaining information from the excel table. The table itself looks like this:







All data is made-up - we don’t have any problems with the disclosure of personal data.

At the same time, in our AD there is only data by last name, first name and patronymic - therefore, this will be the key field. Begin to write.

First you need to load the Active Roles Management Shell library for Active Directory. In the powershell open console, you can do this with the following command:

Add-pssnapin Quest.ActiveRoles.ADManagement



The same should be written in the script file, so as not to connect the module by hand.

Further I will give all code with comments:

 #   cls #     $TelSPR="C:\info2AD\.xls" #  (WorkSheet)   Excel $SheetName="1" #"" Excel ( COM- Excel.Application) $objExcel=New-Object -comobject Excel.Application #   (" ")  Excel $objWorkbook=$objExcel.Workbooks.Open($TelSPR) # ,   $ColumnName=1 # ,   $ColumnTitle=2 #  $ColumnHomePhone=3 $ColumnPhone=4 $ColumnMobPhone=5 # $ColumnOffice=6 $ColumnMail=7 # $ColumnDep=8 # #     SpecialCells $xlCellTypeLastCell = 11 # #       #$TotalsRow=$objWorkbook.Worksheets.Item($SheetName).UsedRange.SpecialCells($xlCellTypeLastCell).CurrentRegion.Row #      Excel for ($Row=1;$Row -le $TotalsRow; $Row++) { #      $UserName=$objWorkbook.ActiveSheet.Cells.Item($Row, $ColumnName).Value() $Title=$objWorkbook.ActiveSheet.Cells.Item($Row, $ColumnTitle).Value() $HomePhone=$objWorkbook.ActiveSheet.Cells.Item($Row, $ColumnHomePhone).Value() $Phone=$objWorkbook.ActiveSheet.Cells.Item($Row, $ColumnPhone).Value() $MobPhone=$objWorkbook.ActiveSheet.Cells.Item($Row, $ColumnMobPhone).Value() $Office=$objWorkbook.ActiveSheet.Cells.Item($Row, $ColumnOffice).Value() $Mail=$objWorkbook.ActiveSheet.Cells.Item($Row, $ColumnMail).Value() $Department=$objWorkbook.ActiveSheet.Cells.Item($Row, $ColumnDep).Value() # ,    AD    $arrfio =-split $UserName $arrfio[2]=$arrfio[2].Substring(0,1) #      if ($Phone -ne $null) { $Phone="(888) "+$Phone } if ($MobPhone -ne $null) { $MobPhone="(888) "+$MobPhone } #   AD,    (enabled),    try { Get-QADUser -DisplayName $UserName -enabled | Set-QADUser -FirstName $arrfio[1] -Initials $arrfio[2] -LastName $arrfio[0] -Department $Department -HomePhone $HomePhone -Phone $Phone -MobilePhone $MobPhone -Office $Office -Title $Title -Company " " -Mail $Mail } catch { $ReportString=("{0,-50} <-> {1,50}" -f $UserName, " ") } write-Host $reportString $reportString=" " } #  Excel $objExcel.Workbooks.Close() #  Excel (      Excel) $objExcel.Quit() #  $objExcel = $null #           [gc]::collect() [gc]::WaitForPendingFinalizers() 

Here is a script. It works quite quickly. Let's say a list of 300 people is processed for no longer than a minute. The script can also be hung up in scheduled tasks and ask the personnel department to upload the file with the data of employees somewhere in the network folder when the staffing schedule changes. Then in AD you will have a structure corresponding to reality.

2) Take data from AD to Sharepoint list

After implementation in our Microsoft Sharepoint office, it became necessary to use the lists of employees inside this monstrous brainchild of Microsoft. And again Powershell comes to the rescue.

To begin with, we will take data from AD into a text file of the csv format - this is done in two lines:

Add-pssnapin Quest.ActiveRoles.ADManagement

$ user = get-qaduser -SearchRoot "OU = Accounts, OU = comenta, DC = domen, DC = local" | Select-Object GivenName, DN, DisplayName, mail, LogonName, telephoneNumber, Office, Department, Title, HomePhone, MobilePhone , sid | Export-CSV-limiter ";" -path "C: \ PowershellScripts \ ADUsers.csv" -Encoding UTF8



It would be possible to ship all the data on users to csv - but we noticed that the running time of the script then increases significantly.

And by the next script we will load this file into the Sharepoint list. I will give the whole text, but under the cut - I think it will be useful:







As a result of the work of the last script, we get a ready list in sharepoint, with the current one.

If you have any questions on the topic of the article - I am ready to answer. Thanks for attention.