Using an existing connection variable ($cm) you could try the following to retrieve unassigned numbers:

PS > $cm | Get-CMDirectoryNumber -Full | ? { $_.AssociatedDevices -eq $null }

The 'Full' parameter joins relationships on the data (i.e. 'AssociatedDevices'). Returned object(s) may be stored in a variable ($dn) which can be used to perform some action. For example, export the results and purge the unassigned numbers.

PS > $csv = "UnassignedNumbers.{0}.csv" -f [datetime]::now.ToString("yyyyMMdd")
PS > $dn | Export-Csv $csv
PS > $dn | % { $cm | Remove-CMDirectoryNumber $_.Name $_.Partition }

Optionally improve performance to retrieve unassigned patterns for large deployments.

PS > $cm | Get-CMSqlData "select numplan.dnorpattern Number, routepartition.name Partition from numplan left join devicenumplanmap on devicenumplanmap.fknumplan = numplan.pkid left join routepartition on numplan.fkroutepartition = routepartition.pkid where devicenumplanmap.fkdevice is null"

Hi Mike,

What is taking a long time exactly? Establishing the initial connection or the actual retrieval operation? In the latter case the size of your deployment might be a factor, namely because the first method retrieves all DN and and then filters the results client-side.

The second method leaves filtering on the cucm host and retrieves only unassigned DN or patterns. To tweak the query and constrain the return:

PS > $query = @"
select
numplan.dnorpattern number,
routepartition.name partition
from numplan
left join devicenumplanmap on devicenumplanmap.fknumplan = numplan.pkid
left join routepartition on numplan.fkroutepartition = routepartition.pkid
left join typepatternusage on numplan.tkpatternusage = typepatternusage.enum
where devicenumplanmap.fkdevice is null
and typepatternusage.enum = 2
"@
PS > $cm | Get-CMSqlData $query

If the issue is in fact the initial connection time please consider the following:

• Apply the 'CMVersion' parameter if possible. Otherwise CMShell will try to connect using the most suitable adapter, starting with the latest one. That process can take a while.
• Hang on until 2.0 release. There have been drastic improvements in the serialization process resulting in a much faster initial connection for all adapters.