comparing a csv instead of an array in powershell

Question

I'm trying to edit a script that does a few things: it compares two arrays and removes known unavailable strings from the first array it polls AD and prints all the strings that are not found in AD.

The problem is, I don't want to initially use an array, I want to use a csv. Well, the other problem is I'm feeling my way around with Powershell. I've edited this so I'm not using real numbers, but here's what I've got. Please do help.

# Find free numbers in AD
$numberRanges = @(
  @(6667778001,6667778999), 
  @(6667776001,6667777999)
)
[System.Collections.ArrayList]$knownUnavailable = 6667778203,6667777212,6667777213

[System.Collections.ArrayList]$knownUnavailableU64 = @()
foreach ( $knownUnavailableItem in $knownUnavailable ) {
  $knownUnavailableU64 += [UInt64]$knownUnavailableItem
}

$strFilter = '(&(objectCategory=User)(telephonenumber=*-*-*))'
$objDomain = New-Object System.DirectoryServices.DirectoryEntry
$objSearcher = New-Object System.DirectoryServices.DirectorySearcher
$objSearcher.SearchRoot = $objDomain
$objSearcher.PageSize = 1000
$objSearcher.Filter = $strFilter
$objSearcher.SearchScope = "Subtree" 
$colResults = $objSearcher.FindAll()
$aDNumbers = @()
foreach ($objResult in $colResults) {
  $objItem = $objResult.Properties
  $aDNumbers+=[UInt64]($objItem.telephonenumber.Replace("-",""))
}
$availableNumbers = @()
foreach ($numberSet in $numberRanges) {
  $lowerBound=[UInt64]$numberSet[0]
  $upperBound=[UInt64]$numberSet[1]
  for($i = $lowerBound; $i -le $upperBound; $i++) { 
    if ( !( $aDNumbers.Contains($i)) -and !( $knownUnavailableU64.Contains($i) )
    ) {
      $availableNumbers+=$i
    }
  }
  [array]::sort($availableNumbers)
}
$availableNumbers | % { "{0:##########}" -f $_ }

write-host "Press any key to close..."
[void][System.Console]::ReadKey($true)

Ideally I'd use this type of csv and skip the arrays altogether:

knownDID,usable
6667778001,
6667778002,
6667778003,FALSE
6667778004,
6667778005,

Thanks for reading!

If I do Import-Csv (file path) would I replace the array in $numberRanges? I've tried that and I get errors. — Forrest
– Forrest, Commented Sep 14, 2016 at 19:02
So, the csv in question has the header knownNumbers and each phone number I own formatted like ########## — Forrest
– Forrest, Commented Sep 14, 2016 at 19:04
Now I am a little confused. Is number ranges what you would have in a CSV or no? Can you show a CSV example with how it would work? — Matt
– Matt, Commented Sep 14, 2016 at 19:05
Ideally, I'd compare the csv to AD with the second column populated with FALSE if the number can't be used. That's ultimately what I want to do. But I also want to learn how to do it this way first. — Forrest
– Forrest, Commented Sep 14, 2016 at 19:06

woxxom · Accepted Answer · 2016-09-14 20:05:12Z

+= on an Array/ArrayList recreates it each time. Use ArrayList's Add method and redirect its output (the new item index) to $null
Instead of processing array elements in a loop, process the entire array via piping through ForEach{} and assign the output to a variable so that it will receive the new array in one go.
To convert arrays just use typecasting: $b = [uint64[]]@(1,2,3) (note [] after the type).
Instead of manually enumerating large arrays, use .NET3.5+ HashSet.ExceptWith which is orders of magnitude faster. Both objects should be converted to HashSet first (use typecasting).

$numberRanges = @(
    [UInt64[]]@(6667778001,6667778999)
    [UInt64[]]@(6667776001,6667777999)
)

$knownUnavailable = [Collections.Generic.HashSet[UInt64]](
    Import-Csv c:\knownDID.csv |
        Where { $_.usable -eq 'false' } |
        Select -expand knownDID
)

$objSearcher = [DirectoryServices.DirectorySearcher]::new(
    [DirectoryServices.DirectoryEntry]::new(),
    '(&(objectCategory=User)(telephonenumber=*-*-*))',
    [Collections.Specialized.StringCollection]@('telephonenumber'),
    'Subtree'
)
$objSearcher.PageSize = 1000

$aDNumbers = [Collections.Generic.HashSet[UInt64]](
    $objSearcher.FindAll() | ForEach { $_.Properties.telephonenumber -replace '-','' }
)

$availableNumbers = $numberRanges | ForEach {
    $numberSet = [Collections.Generic.HashSet[UInt64]]@($_[0]..$_[1])
    $numberSet.ExceptWith($aDNumbers)
    $numberSet.ExceptWith($knownUnavailable)
    $numberSet
}

$availableNumbers | %{ '{0:##########}' -f $_ }

Write-Host "Press any key to close..."
[void][System.Console]::ReadKey($true)

The code is untested, so use it as a possible approach example.

Collectives™ on Stack Overflow

comparing a csv instead of an array in powershell

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related