Split phone numbers from AD into separate columns using PowerShell-CodePudding

I am using the powershell script below:

Get-ADUser -SearchBase "OU=Domain Users,DC=nwvp,DC=local" -filter * -properties TelephoneNumber,DisplayName,mobile | select DisplayName,TelephoneNumber,mobile

and it gives me an output like this:

DisplayName               TelephoneNumber         mobile               
-----------               ---------------         ------               
Paul Strong                1 (902) 444-4444,6405   1 (506) 111-1111    
Katie White                1 (416) 333-3333,2204   1 (905) 222-2222

I am looking for updating the format of the output like below:

LastName FirstName InternationalCode AreaCode PhoneNumber Extension DeviceType
Strong   Paul      1                 902      4444444     6405      Work
Strong   Paul      1                 506      1111111               Mobile
White    Katie     1                 416      3333333     2204      Work
White    Katie     1                 905      2222222               Mobile

How can I format the original output to get the desired output shown?

Thanks!

CodePudding user response：

Check to see if the number exists and if it does then create the desired object

Get-ADUser -SearchBase 'OU=Domain Users,DC=nwvp,DC=local' -Filter * -Properties TelephoneNumber, DisplayName, mobile | 
    ForEach-Object {
        if ($_.TelephoneNumber) {
            [PSCustomObject]@{
                LastName          = ($_.DisplayName.Split(' '))[1]
                FirstName         = ($_.DisplayName.Split(' '))[0]
                InternationalCode = if ($_.TelephoneNumber -match '\ (\d )') { $Matches[1] }
                AreaCode          = if ($_.TelephoneNumber -match '\((\d{3})\)') { $Matches[1] }
                PhoneNumber       = if ($_.TelephoneNumber -match '[\d-]{7,}') { $Matches[0] -replace '\s|\-' }
                Extension         = if ($_.TelephoneNumber -match ',(\d )') { $Matches[1] }
                DeviceType        = 'Work'
            }
        }
        if ($_.Mobile) {
            [PSCustomObject]@{
                LastName          = ($_.DisplayName.Split(' '))[1]
                FirstName         = ($_.DisplayName.Split(' '))[0]
                InternationalCode = if ($_.Mobile -match '\ (\d )') { $Matches[1] }
                AreaCode          = if ($_.Mobile -match '\((\d{3})\)') { $Matches[1] }
                PhoneNumber       = if ($_.Mobile -match '[\d-]{7,}') { $Matches[0] -replace '\s|\-' }
                Extension         = ''
                DeviceType        = 'Mobile'
            }
        }
    }

CodePudding user response：

I think you can use PowerShell Name, Expression to customize your output in required format First store your output to an array (I think AD query give output in different data type so better to specifically define data type to array)

[array]$myArray=Get-ADUser -SearchBase "OU=Domain Users,DC=nwvp,DC=local" -filter * -properties TelephoneNumber,DisplayName,mobile | select DisplayName,TelephoneNumber,mobile

Then select the data by using n & e customization like below

$myArray|select @{n="LastName";e={([string]($_.DisplayName)).Split(" ")[1]}},
                @{n="FirstName";e={([string]($_.DisplayName)).Split(" ")[0]}},
                @{n="InternationalCode";e={([string]($_.TelephoneNumber)).Split(" ")[0]}},
                @{n="AreaCode";e={(([string]($_.TelephoneNumber)).Split(")")[0]).Split("(")[1]}},
                @{n="PhoneNumber";e={((([string]($_.TelephoneNumber)).Split(")")[1]).Split(",")[0]).Trim()}},
                @{n="Extension";e={([string]($_.TelephoneNumber)).Split(",")[1]}}

I could not get how you determining the DeviceType ! check is something it is available in AD query output itself
you can check it by running -properties *
also I think First and Last name is available as a property , just check it out.