otherTelephone export to CSV

This topic contains 4 replies, has 2 voices, and was last updated by Profile photo of Leon Ashton L Leon Ashton L 2 years ago.

  • Author
    Posts
  • #20907
    Profile photo of Leon Ashton L
    Leon Ashton L
    Participant

    Hi all,

    After watching some of the fantastic videos by Jason Helmick on the MVA I am now trying to use Powershell to make me more productive and save the hassle of boring, repetitive administrative tasks.

    We are changing numbers in our telephone system this weekend and I intended to export all of the information I require from AD to a csv file to edit, before then importing again to overwrite the changes.

    Well I have achieved the first part, well sort of by using the following command;

    [b]Get-ADUser -Filter * -Properties * -SearchBase "OU=Users,OU=LIT,DC=jdrcables,DC=com"| Select Name,EmailAddress,OfficePhone,@{n="otherTelephone";e={$_.otherTelephone}} | Sort-Object -Property Name | Export-Csv C:\Users\leon.ashton-leatherl\Desktop\Littleport.csv -NoTypeInformation[/b]

    I have two issues with this, fist of all I want to specify multiple searchbases for the different locations of users in AD (there are various "Users" OUs so maybe I could use that as an LDAP search filter?). Then main issue is though when I am exporting to a CSV file, the "otherTelephone" attribute is not showing the correct values in the cell it is something like 4.41E+11. When I highlight the cell it will show the correct value on the formula bar, but without a + symbol as a prefix. I need to be able to export these numbers in the correct format to a CSV and we must have the + symbol as the international prefix, for example +441234567890. I will then need to edit this data, along with the Office phone attribute before importing the changes back to AD again.

    I would appreciate any advise on both the two issues with the current command, also on how to import any changes back to AD

    Regards

    Leon

  • #20910
    Profile photo of tommymaynard
    tommymaynard
    Member

    In order to search multiple DNs, I would recommend that you store all of them as an array and then loop through each, running your Get-ADUser command once per DN. In order to add to the same CSV, you'll need to be using at least PowerShell 3.0 and using the -Append parameter of the Export-CSV cmdlet. You'd be better served to create a single object of everything and then export that to CSV, but you might want to save that for another day. It's important, so be certain to spend some time reading about custom objects!

    $DNs = 'OU=Users,OU=LIT,DC=jdrcables,DC=com','OU=FinanceGroup,OU=LIT,DC=jdrcables,DC=com','OU=SystemsGroup,OU=Users,OU=LIT,DC=jdrcables,DC=com' 
    
    Foreach ($DN in $DNs) {
        Get-ADUser -Filter * -Properties * -SearchBase $DN | Select-Object #etc.
    }
    

    Your otherTelephone attribute is exporting to the CSV just fine. The problem is the interpretation of the plus sign (+) by Excel. Like the equal sign (=), it indicates to Excel that you're going to enter a formula. I would recommend that while working on this project you use something other than Excel to view your CSVs. You can use Notepad, Programmer's Notepad (one of my favorites), and even the PowerShell console, if that's not too difficult to read (Get-Content -Path '...\Littleport.csv'). If using the console or a text editor isn't preferred, then search around for CSV viewers that will maintain the integrity of your data. I tried out CSV Viewer (http://csvviewer.com) and it seemed to work well.

    As far as bringing things into AD from a CSV, I would recommend you start small – a single user – and do some testing. You will need to use the Import-CSV and Set-ADUser cmdlet. Good luck!

  • #20921
    Profile photo of Leon Ashton L
    Leon Ashton L
    Participant

    Many thanks for your reply on this!

    I have carried out as you suggested with the array for the OU's and it works great. I just want to have a look at somehow filtering the information to try and improve the results further I believe this can be achieved with the -LDAPFilter or -Filter cmdlets?

    Regarding the import of information I will test on a few users, but what I will need to do is export the CSV, and then edit the OfficePhone and OtherTelephone attributes for most of the users before importing the changes.

  • #20947
    Profile photo of tommymaynard
    tommymaynard
    Member

    Currently the -Filter parameter in your command is, in effect, not filtering any of the users in your SearchBase. Instead, it returns all the users in the specified OU. If you want to add a filter, then you'll need to determine if there are certain users you don't want returned, and if they have a common attribute in which you can filter. My first thought was to use the -Filter parameter to only return users that have something in the otherTelephone attribute; that makes sense. You could do that with something like this: Get-ADUser -Filter {otherTelephone -like '*'} -SearchBase ... -Properties otherTelephone | Select-Object...

    Here's a quick example of your entire process running against a single DN.

    PS C:\> Get-ADUser -Filter {otherTelephone -like '*'} -SearchBase 'OU=Users,OU=LIT,DC=jdrcables,DC=com' -Properties otherTelephone | Select-Object SamAccountName,@{N='otherTelephone';E={$_.otherTelephone}} | Export-Csv c:\file.csv -NoTypeInformation

    PS C:\> Get-Content -Path .\file.csv
    "SamAccountName","otherTelephone"
    "TestUser","123456789"
    PS C:\> Import-Csv -Path c:\file.csv

    SamAccountName otherTelephone
    ————– ————–
    TestUser 123456789

    PS C:\> pn .\file.csv #pn is an alias I use to open Programmer's Notepad. I did this so I could manually edit file.csv, as you can see in the next command.
    PS C:\> Import-Csv -Path c:\file.csv

    SamAccountName otherTelephone
    ————– ————–
    TestUser 999999999

    PS C:\> $Users = Import-Csv -Path c:\file.csv
    PS C:\> $Users

    SamAccountName otherTelephone
    ————– ————–
    TestUser 999999999

    PS C:\> Set-ADUser -Identity $Users.SamAccountName -Replace @{otherTelephone=$users.otherTelephone}

    This isn't everything you'll need, but I think it should help you as you continue to work on your project.

  • #20984
    Profile photo of Leon Ashton L
    Leon Ashton L
    Participant

    Hi, I just wanted to say many thanks for the help, project complete, and I have realised how tidy I can make my Active Directory by using powershell with minimal effort!

You must be logged in to reply to this topic.