Tuesday, May 24, 2011

Documenting your Lync Voice Configuration

First off, all credit for this post goes to my colleague Chris Cook.  He provided all of the powershell scripting behind the tool.

UPDATED: Added download of file HERE

I have found it very difficult to easily document a voice configuration within Lync.  That is when I approached my scripting buddy Chris.  He developed this script to extract the export from the Lync Control Panel Voice Configuration:

LyncVoice - Export config - markup

When exporting the configuration, it saves as a *.vcfg file.  Using Chris’ handy powershell cmdlet, we can extract the contents of the *.vcfg file into an Excel spreadsheet with different tabs for each area: Dial Plan, Voice Policy, Route, PSTN Usage, and Trunk Configuration.

Copy the following powershell script as a .ps1 file and save in the same location as the exported *.vcfg file.

Download LyncVCFGConverter.ps1 here

##################################################
#
#  LyncVCFGConverter.ps1
#  Format Exported .vcfg file from Lync to Excel
#
#  Author: Chris Cook
#
#  Use at your own risk!
#
##################################################
 
 
cls
#Collect VCFG Filename from Command Line Arguments
$VCFGFileName = $args[0]
Write-Host "Importing XML Config..."
$VCFGXML = [xml](get-content $VCFGFileName)
Write-Host "Done"
 
#Get current working directory and convert it to a string.
$CurrentDirectory = Get-Location
$CurrentDirectory = $CurrentDirectory.ToString()
 
#Set the file name for the Excel Spreadsheet we're working with.
#Drop the VCFG extension and add " - Voice Config.XLSX" to the end.
Write-Host "Starting Excel..."
$ExcelFileName = $VCFGFileName.substring(0,$VCFGFileName.Length - 5) + " - Voice  Config.XLSX"
 
#Create the Excel Instance we will be working with.
$ExcelApp = New-Object -comobject Excel.Application
 
#Create a new Workbook and add 2 extra sheets for 5 total.
Write-Host "Configuring Worksheets..."
$Workbook = $ExcelApp.Workbooks.Add()
$Worksheets = $WorkBook.Worksheets.Add()
$Worksheets = $WorkBook.Worksheets.Add()
 
#Name the sheets in the Workbook and assign variables to them.
$LocationProfilesSheet = $Workbook.Worksheets.Item(1)
$LocationProfilesSheet.Name = "Location Profiles"
$PSTNUsagesSheet = $Workbook.Worksheets.Item(2)
$PSTNUsagesSheet.Name = "PSTN Usages"
$PSTNRoutesSheet = $Workbook.Worksheets.Item(3)
$PSTNRoutesSheet.Name = "PSTN Routes"
$VoicePolicySheet = $Workbook.Worksheets.Item(4)
$VoicePolicySheet.Name = "Voice Policies"
$TrunkSheet = $Workbook.Worksheets.Item(5)
$TrunkSheet.Name = "Trunk Configuration"
Write-Host "Done"
 
#Configure layout for Location Profile Sheet
Write-Host "**Building Location Profile Sheet**"
$LocationProfilesSheet.Cells.Item(1,1) = "Dial Plan"
$LocationProfilesSheet.Cells.Item(1,2) = "Scope"
$LocationProfilesSheet.Cells.Item(1,3) = "Dial Plan Description"
$LocationProfilesSheet.Cells.Item(1,4) = "Rule"
$LocationProfilesSheet.Cells.Item(1,5) = "Rule Description"
$LocationProfilesSheet.Cells.Item(1,6) = "Pattern"
$LocationProfilesSheet.Cells.Item(1,7) = "Translation"
$LocationProfilesSheet.Cells.Item(1,8) = "Dialin Conferencing Region"
 
$CurrentRow = 2
 
#Import XML data into Excel fields.
$LocationProfiles = @($VCFGXML.Datasource.LocationProfiles.Datasource)
ForEach ($DialPlan in $LocationProfiles){
Write-Host "Found Dial Plan: " $DialPlan.DialPlanName
ForEach ($Rule in $DialPlan.Rules.Datasource){
Write-Host "Importing Rule: " $Rule.Name
$LocationProfilesSheet.Cells.Item($CurrentRow,1) = $DialPlan.Name
$LocationProfilesSheet.Cells.Item($CurrentRow,2) = $DialPlan.Scope
$LocationProfilesSheet.Cells.Item($CurrentRow,3) = $DialPlan.Description
$LocationProfilesSheet.Cells.Item($CurrentRow,4) = $Rule.Name
$LocationProfilesSheet.Cells.Item($CurrentRow,5) = $Rule.Description
$LocationProfilesSheet.Cells.Item($CurrentRow,6) = $Rule.Pattern
$LocationProfilesSheet.Cells.Item($CurrentRow,7) = $Rule.Translation
$LocationProfilesSheet.Cells.Item($CurrentRow,8) = $DialPlan.DialinConferencingRegion
$CurrentRow = $CurrentRow + 1
}
}
Write-Host "**Done with Location Profile Sheet**"
 
#Configure layout for PSTN Usages Sheet
Write-Host "**Building PSTN Usages Sheet**"
$PSTNUsagesSheet.Cells.Item(1,1) = "Name"
$PSTNUsagesSheet.Cells.Item(1,2) = "Identity"
$PSTNUsagesSheet.Cells.Item(1,3) = "Scope"
$CurrentRow = 2
 
#Import XML data into Excel fields.
$PSTNUsages = @($VCFGXML.Datasource.PSTNUsages.Datasource)
ForEach ($Usages in $PSTNUsages){
ForEach ($Usage in $Usages.Usages.Datasource){
Write-Host "Importing Usage: " $Usage.Name
$PSTNUsagesSheet.Cells.Item($CurrentRow,1) = $Usage.Name
$PSTNUsagesSheet.Cells.Item($CurrentRow,2) = $Usages.Identity
$PSTNUsagesSheet.Cells.Item($CurrentRow,3) = $Usages.Scope
$CurrentRow = $CurrentRow + 1
}
}
Write-Host "**Done with PSTN Usages Sheet**"
 
#Configure layout for PSTN Route Settings Sheet
Write-Host "**Building PSTN Route Settings Sheet**"
$PSTNRoutesSheet.Cells.Item(1,1) = "Route Name"
$PSTNRoutesSheet.Cells.Item(1,2) = "Usage"
$PSTNRoutesSheet.Cells.Item(1,3) = "Route Description"
$PSTNRoutesSheet.Cells.Item(1,4) = "Number Pattern"
$PSTNRoutesSheet.Cells.Item(1,5) = "Supress Caller ID"
$PSTNRoutesSheet.Cells.Item(1,6) = "PSTN Gateway"
$CurrentRow = 2
 
#Import XML data into Excel fields.
$RouteSettings = @($VCFGXML.Datasource.PSTNRouteSettings.Datasource)
ForEach ($RouteGroup in $RouteSettings){
ForEach ($Route in $RouteGroup.Routes.Datasource){
Write-Host "Importing Route: " $Route.Name
$PSTNRoutesSheet.Cells.Item($CurrentRow,1) = $Route.Name
$PSTNRoutesSheet.Cells.Item($CurrentRow,3) = $Route.Description
$PSTNRoutesSheet.Cells.Item($CurrentRow,4) = $Route.NumberPattern
$PSTNRoutesSheet.Cells.Item($CurrentRow,5) = $Route.SuppressCallerId
ForEach ($Usage in $Route.Usages.Datasource){
$PSTNRoutesSheet.Cells.Item($CurrentRow,2) = $Usage.Name
}
ForEach ($Gateway in $Route.Gateways.Datasource){
$PSTNRoutesSheet.Cells.Item($CurrentRow,6) = $Gateway.ServiceId
}
$CurrentRow = $CurrentRow + 1
}
}
Write-Host "**Done with PSTN Route Settings Sheet**"
 
#Configure layout for Voice Policies Sheet
Write-Host "**Building Voice Policies Sheet**"
$VoicePolicySheet.Cells.Item(1,1) = "Voice Policy Name"
$VoicePolicySheet.Cells.Item(1,2) = "Description"
$VoicePolicySheet.Cells.Item(1,3) = "Scope"
$VoicePolicySheet.Cells.Item(1,4) = "Usage"
$VoicePolicySheet.Cells.Item(1,5) = "Allow Simultaneous Ring"
$VoicePolicySheet.Cells.Item(1,6) = "Allow Call Forwarding"
$VoicePolicySheet.Cells.Item(1,7) = "Allow PSTN Rerouting"
$VoicePolicySheet.Cells.Item(1,8) = "Enable Delegation"
$VoicePolicySheet.Cells.Item(1,9) = "Enable Team Call"
$VoicePolicySheet.Cells.Item(1,10) = "Enable Call Transfer"
$VoicePolicySheet.Cells.Item(1,11) = "Enable Call Park"
$VoicePolicySheet.Cells.Item(1,12) = "Enable Malicious Call tracing"
$VoicePolicySheet.Cells.Item(1,13) = "Enable Bandwidth Policy Override"
$CurrentRow = 2
 
#Import XML data into Excel fields.
$VoicePolicies = @($VCFGXML.Datasource.VoicePolicies.Datasource)
ForEach ($Policy in $VoicePolicies){
Write-Host "Importing Policies for: " $Policy.Name
ForEach ($Usage in $Policy.Usages.Datasource){
$VoicePolicySheet.Cells.Item($CurrentRow,1) = $Policy.Name
$VoicePolicySheet.Cells.Item($CurrentRow,2) = $Policy.Description
$VoicePolicySheet.Cells.Item($CurrentRow,3) = $Policy.Scope
$VoicePolicySheet.Cells.Item($CurrentRow,4) = $Usage.Name
$VoicePolicySheet.Cells.Item($CurrentRow,5) = $Policy.AllowSimulRing
$VoicePolicySheet.Cells.Item($CurrentRow,6) = $Policy.AllowCallForwarding
$VoicePolicySheet.Cells.Item($CurrentRow,7) = $Policy.AllowPSTNReRouting
$VoicePolicySheet.Cells.Item($CurrentRow,8) = $Policy.EnableDelegation
$VoicePolicySheet.Cells.Item($CurrentRow,9) = $Policy.EnableTeamCall
$VoicePolicySheet.Cells.Item($CurrentRow,10) = $Policy.EnableCallTransfer
$VoicePolicySheet.Cells.Item($CurrentRow,11) = $Policy.EnableCallPark
$VoicePolicySheet.Cells.Item($CurrentRow,12) = $Policy.EnableMaliciousCallTracing
$VoicePolicySheet.Cells.Item($CurrentRow,13) = $Policy.EnableBWPolicyOverride
$CurrentRow = $CurrentRow + 1
}
}
Write-Host "**Done with Voice Policies Sheet**"
 
#Configure layout for Trunks Sheet
Write-Host "**Building Trunk Sheet**"
$TrunkSheet.Cells.Item(1,1) = "Trunk Name"
$TrunkSheet.Cells.Item(1,2) = "Trunk Identity"
$TrunkSheet.Cells.Item(1,3) = "Scope"
$TrunkSheet.Cells.Item(1,4) = "Rule"
$TrunkSheet.Cells.Item(1,5) = "Rule Description"
$TrunkSheet.Cells.Item(1,6) = "Concentrated Topology"
$TrunkSheet.Cells.Item(1,7) = "Enable Bypass"
$TrunkSheet.Cells.Item(1,8) = "Enable Refer Support"
$TrunkSheet.Cells.Item(1,9) = "SRTP Mode"
$TrunkSheet.Cells.Item(1,10) = "Max. Early Dialogs"
$TrunkSheet.Cells.Item(1,11) = "Pattern"
$TrunkSheet.Cells.Item(1,12) = "Translation"
$CurrentRow = 2
 
#Import XML data into Excel fields.
$TrunkGroup = @($VCFGXML.Datasource.Trunks.Datasource)
ForEach ($Trunk in $TrunkGroup){
Write-Host "Found Trunk: " $Trunk.Name
ForEach ($Rule in $Trunk.Rules.Datasource){
Write-Host "Importing Rule: " $Rule.Name
$TrunkSheet.Cells.Item($CurrentRow,1) = $Trunk.Name
$TrunkSheet.Cells.Item($CurrentRow,2) = $Trunk.Identity
$TrunkSheet.Cells.Item($CurrentRow,3) = $Trunk.Scope
$TrunkSheet.Cells.Item($CurrentRow,4) = $Rule.Name
$TrunkSheet.Cells.Item($CurrentRow,5) = $Rule.Description
$TrunkSheet.Cells.Item($CurrentRow,6) = $Trunk.ConcentratedTopology
$TrunkSheet.Cells.Item($CurrentRow,7) = $Trunk.EnableBypass
$TrunkSheet.Cells.Item($CurrentRow,8) = $Trunk.EnableReferSupport
$TrunkSheet.Cells.Item($CurrentRow,9) = $Trunk.SRTPMode
$TrunkSheet.Cells.Item($CurrentRow,10) = $Trunk.MaxEarlyDialogs
$TrunkSheet.Cells.Item($CurrentRow,11) = $Rule.Pattern
$TrunkSheet.Cells.Item($CurrentRow,12) = $Rule.Translation
$CurrentRow = $CurrentRow + 1
}
}
Write-Host "**Done with Voice Policies Sheet**"
 
 
 
 
 
#Make Excel visible to the user.
$ExcelApp.Visible = $True
$Workbook.SaveAs($CurrentDirectory + "\" + $ExcelFileName)
 
#$ExcelApp.Quit()

Now open up a powershell command window and navigate to the directory with the .ps1 file and .vcfg file and run the following command:


.\LyncVCFGConverter.ps1 CustomerA_Lync.vcfg


LyncVoice - PS command


The script will extract the data and create an Excel spreadsheet.  The progress is shown as the script is running:


LyncVoice - PS command execute-markup


Once the script is complete, the Excel spreadsheet will be saved and launched.  You will notice that the spreadsheet is divided into different tabs:


LyncVoice - Excel - markup


Here are some examples from some of the other tabs…


Voice Policies:


LyncVoice - Excel -policies - markup


Trunk Configuration:


LyncVoice - TrunkConfig - markup


Hopefully this will help you out on documenting your Lync Voice configurations!  Again, thanks to Chris Cook for the heavy lifting on the powershell scripts.

31 comments:

  1. Tim & Chris -
    This ia great tool, one thing I noticed when looking at translation column on the dial plan tab is if the rule starts with +$ it strips off the +. I updated line 76 to read:
    $LocationProfilesSheet.Cells.Item($CurrentRow,7) = "'" + $Rule.Translation

    This should add a single ' before the translation rule which will save the cell as text.

    ReplyDelete
  2. Tim & Chris -
    This is great tool. The only problem i see is that the function in Line 122-123, 125-126 overwrite the Cell.Item and so in the XLS field only display the last value and not all.

    ReplyDelete
  3. Excellent tool, saved me a few hours of work!

    Thanks a bunch.

    ReplyDelete
  4. Hi are you planning on adding 2013 support for this excellent Tool. ?

    ReplyDelete
    Replies
    1. The author just updated his documentation tools here: http://emptymessage.com/?p=149

      Delete
    2. nice. I have not visited this script in a while. your download link is borked somehow. Also, the first tab in the spreadsheet output is labeled "location profiles"

      Delete
  5. Hi Guys,

    I am not able to get the result by running this command and get the error shown below in powershell:

    -----------------------------------

    PS C:\How to convert lync exported vcfg file to excel> .\LyncVCFGConverter.ps1 VoiceConfig.vcfg
    The term '.\LyncVCFGConverter.ps1' is not recognized as the name of a cmdlet, function, script file, or operable progra
    m. Check the spelling of the name, or if a path was included, verify that the path is correct and try again.
    At line:1 char:24
    + .\LyncVCFGConverter.ps1 <<<< VoiceConfig.vcfg
    + CategoryInfo : ObjectNotFound: (.\LyncVCFGConverter.ps1:String) [], CommandNotFoundException
    + FullyQualifiedErrorId : CommandNotFoundException

    PS C:\How to convert lync exported vcfg file to excel>

    ---------------------------

    Please let me know the solution

    Regards
    Vinod

    ReplyDelete
  6. I had the same issue and updated the areas of the script that has issues.

    https://itlogistix-my.sharepoint.com/personal/jlockett_itlogistix_net/Documents/Shared%20with%20Everyone/LyncVCFGConverter.zip

    ReplyDelete
  7. Opps, sorry that is an o365 link only. Here you go.

    https://onedrive.live.com/?cid=1021776e80d466e4&id=1021776E80D466E4%2147647&authkey=!AFlH3ijXctzFe4I

    ReplyDelete
    Replies
    1. great , no error by using this version.
      thanks.

      Delete
    2. This comment has been removed by the author.

      Delete
  8. By the way, thanks Chris Cook. I really like this format for documenting the Lync Voice components.

    ReplyDelete
  9. You cannot call a method on a null-valued expression.
    At C:\cucm\lyncvoice.ps1:135 char:29
    + $VoicePolicySheet.Cells.Item <<<< (1,1) = "Voice Policy Name"
    + CategoryInfo : InvalidOperation: (Item:String) [], RuntimeException
    + FullyQualifiedErrorId : InvokeMethodOnNull

    ReplyDelete
  10. This comment has been removed by the author.

    ReplyDelete
  11. This comment has been removed by the author.

    ReplyDelete
  12. Awesome article, it was exceptionally helpful! I simply began in this and I'm becoming more acquainted with it better! Cheers, keep doing awesome! English

    ReplyDelete
  13. Please continue this great work and I look forward to more of your awesome blog posts. https://lingbase.com/en/english/tests/grammar-test

    ReplyDelete
  14. I havent any word to appreciate this post.....Really i am impressed from this post....the person who create this post it was a great human..thanks for shared this with us. English

    ReplyDelete
  15. Last year my company was in a major business communication with our clients in China and we needed top-quality translation service to help secure the order. We search on Google and got ACE CHINESE TRANSLATION as our language partner, and they really did their job at consistent quality. Successful business, happy boss, nice rewards.

    ReplyDelete
  16. very nice blogs!!! i have to learning for lot of information for this sites...Sharing for wonderful information. Thanks for sharing this valuable information to our vision. You have posted a trust worthy blog keep sharing

    Digital Marketing In Telugu
    Digital Marketing In Hyderabad
    internet marketing
    Digital marketing
    Digital Marketing Strategy

    ReplyDelete
  17. Nice blog thank you for sharing I appreciate. In order to provide successful web development services expertise requires a deep understanding of the client's business. We help you plan and implementing the best method to bring your brand's image out into the world.

    ReplyDelete
  18. I am looking for this type of tool. Thanks for sharing the download link of this tool.
    best digital marketing agency in dubai

    ReplyDelete
  19. eDigital Consultants is a believed Best Digital marketing company in Hyderabad offering most reasonable Branding, SEO, SEM, PPC, Social Media Marketing , Content Analysis that ensure greatest leads for your business.The eDigital Consultants analyzes the business and distinguishes its situation on the lookout.

    ReplyDelete
  20. Great Post. I really appreciate the efforts you put into compiling and sharing this piece of content.
    Inovies

    ReplyDelete
  21. Impressionnant ! J'apprécie votre message. Ce profil vous présentera un outil qui rend l'étude du code morse plus intéressante, ce qui pourrait vous être d'une grande aide. Testez vos compétences rapides en decodeur morse.

    ReplyDelete