Search | Register | Login 
Forum
Nederlands English
HomeHomeCMShellCMShellGeneralGeneralUnassigned DNsUnassigned DNs
Previous
 
Next
New Post
8/17/2012 7:39 PM
 
How can I pull unassigned DNs using CMShell?
 
New Post
8/18/2012 2:52 AM
 

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"

 
New Post
8/18/2012 4:34 PM
 
Helpdesk -

So I tried both of these methods. The first worked as I would have expected. It provided al the unassigned DNs. It however took a long time to complete. I don't think I could use this as I would need something more instantaneous.

The second method you presented using the SQL query didn't have the expected results. It looks to me like it included route patterns as well.

Thanks for your help however! Any ideas? Why does the first method take so long?

Thanks,

Mike
 
New Post
8/20/2012 1:53 PM
 

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.
 
Previous
 
Next
HomeHomeCMShellCMShellGeneralGeneralUnassigned DNsUnassigned DNs


Privacy Statement
Foxnet BV 2011