Coffee Break | More piping with Dynamics NAV


 Did you see our first coffee break about piping at Windows PowerShell and Piping? Let’s dig a bit further.

Coffee Break 6 – Return to piping

This time we will use more piping and other ways to look at a PowerShell object and format it in different ways. For the example here we will use Get-NAVServerInstance and the results from that cmdlet. But everything in this post would apply in the same way on other cmdlets, like

Get-NAVServerUser
Get-NAVWebService
Get-Process
Get-Service

 

Change how data is displayed

Import the NAV management module so we can use the NAV cmdlets

import-module ‘C:\Program Files\Microsoft Dynamics NAV\80\Service\Microsoft.Dynamics.Nav.Management.dll’

Run the following commands and check how the result differs:
Get-NAVServerInstance
Get-NAVServerInstance | Format-Table
Get-NAVServerInstance | Format-Table -AutoSize

 

Select parameters:

Select which columns to return

Get-NAVServerInstance | Select-Object ServerInstance, State

But… How do you know which columns are available? Simply pipe the cmdlet into Get-Member:

Get-NavServerInstance | Get-Member

This shows you a list of members, including these properties

Default                                                                                                      
DisplayName                                                                                                 
ServerInstance                                                                                              
ServiceAccount                                                                                            
State                                                                                                    
Version

Formatting Output

The most usual formats are list and table. Confusingly to a Dynamics NAV person, Format-List is like a card display, and Format-Table is just like a list. Run these to see the difference:
Get-NAVServerInstance | Select-Object ServerInstance, State | Format-List
Get-NAVServerInstance | Select-Object ServerInstance, State | Format-Table

Some of the most useful other formats (to replace the last bit of the pipe above):

Group-Object State
Sort-Object State
ConvertTo-Html
Export-Csv -Path c:\x\servers.txt
Out-gridview
Clip

Especially Clip is very useful – it sends the result directly to your clipboard so you can paste it into Notepad or somewhere else.

Note that formatting pipes may destroy the object in order to display it, so always do the formatting as the last part of a pipe. Except if you want to follow it by an Out-cmdlet.

 

Jasminka Thunes, Escalation Engineer Dynamics NAV EMEA

Lars Lohndorf-Larsen, Escalation Engineer Dynamics NAV EMEA

Bas Graaf, Senior Software Engineer Dynamics NAV

Comments (5)

  1. Jeremy says:

    Is it just me, or do these output options (piping in general) not work with Sync-Tenant when you're trying to get a list of the objects that will error out?

  2. Hi Jeremy,

    Thanks for asking the question here, so that others can comment too. I'm not sure about the exact scenario, but in reality the pipe-ability depends on how well each cmdlet's output matches another cmdlet's possible parameters. It's not PowerShell that enforces any rules here, its really down to each cmdlet. This command will show what parameters Sync-NAVTenant accepts:

    get-help Sync-NAVTenant -Parameter *

    But of course the real way is trial/error until it works…

    If you have a specific command or scenario, then feel free to post that here.

  3. Jeremy says:

    Well, the case scenario I was dealing with was during an upgrade, there were a bunch of tables with custom fields from a discontinued add-on.  This results (naturally) in Sync-NAVTenant giving errors about unhandled destructive changes.  But, the output of Sync-NAVTenant resisted all my efforts to pipe it anywhere – file, tables, anything.  I would always get a large chunk of text like so (ID's are made up, but format is the same):

    Table: 18, Customer

    Field: 12012310, Datathing: Deleted

    In my case, it was a long list.  The only useful way I could get the very long results outside of the powershell was simply to make sure to use the ISE, copy and paste the results to a text file, then manually format it.  What I would very much have preferred was some structure data so I could push the results into Excel for team review in a Sharepoint Sure Step site.

    I suspect there is some useful way in handling this with the Common Parameters, like OutVariable, PipelineVariable, or WarningVariable, but I admit I am simply too new to Powershell to see what is likely obvious.  I added this discussion here simply because I had taken the standard "Sync-NAVTenant -ServerInstance [myIsntance] -Mode CheckOnly" and added "| Format-Table" to see what it would get me, but it did nothing to the results.

  4. jthunes says:

    Hi Jeremy.

    Well, running

    sync-navtenant dynamicsnav80 -mode checkonly -errorvariable test

    should 'record' the details into test variable, then after that

    $test

    to show and allow you to store this info.

    However it seems to behave differently in 2015 (it will store these details into variable in 2013 R2). The variable will be blank in 2015. We will seek collaboration for this, as we don't know what causes the difference at this point. In the meantime, use:

    start-transcript -path c:tempfilename.txt

    then

    sync-navtenant dynamicsnav80 -mode checkonly

    to record the process.

  5. basgraaf says:

    Hi Jeremy,

    Turns out the sync errors are going to the error stream as expected. This means you can use PowerShell's $Error variable to get the sync errors as the most recent ($Error[0]). Note that $Error[0] might contain a previous error if the call to Sync-NavTenant did not find any sync errors. Therefore, it is better to rely on the -ErrorVariable common param, like

    Sync-NAVTenant MyService -Mode CheckOnly -ErrorVariable syncErr

    Admittedly, since all the sync errors are reported as a single error together with some other helpful text processing that automtically requires some fiddling. For instance, a function like below transforms the error output of Sync-NavTenant in comma-seperated-values that when output to a file could be imported into Excel.

    Perhaps this gets a little long for a comment.

    Hope this at least points you in the right direction and thanks for providing the idea for next week's blog post!

    Kind regards,

    Bas

    function ConvertSyncErrTo-Csv

    (

       $SyncErr

    )

    {

       # Header

       "TableId,TableName,FieldId,FieldName,ChangeType"

       foreach ($line in $syncErr -split "`n")

       {

           # Match 'Table: 123: SomeTable

           if ($line -match '^Table: (d+), (.+)') {

               $tableId = $Matches[1]

               $tableName = $Matches[2]

           }

           # Match 'Field: 123, SomeField, SomeChange

           if ($line -match '^Field: (d+), ([^:]+): (w+)') {        

               "$tableId,$tableName,$($Matches[1]),$($Matches[2]),$($Matches[3])"

           }

       }

    }