Microsoft Flow, Twitter and Dynamics NAV

Standard

As C/AL is my number one language to code, I wanted since last summer to give it a try to Microsoft Flow. And as Twitter is one of the top 3 applications I use on my phone I wanted to see if I can get an MS Flow bring the tweets in my favorite environment, Dynamics NAV.

After a few trials and tweaks my flow brings twits in NAV:

Tweet Table

If you want to try it out this is what you need:

  • a Dynamics NAV instance with public IP. I used an azure machine loaded with Dynamics NAV 2017
    • web services for entities you want to create or update via MS flow
  • a Microsoft or work account to connect to flow.microsoft.com
  • a Twitter account

To allow MS Flow to talk to both Twitter and MS NAV we need to set up appropriate Connections in MS Flow:

Conn

The connection to NAV looks like this:

NAVConn

For usename and password create a NAV user and set a password. On the instance you want to connect set a credential type = NAVUserPassword.

For twitter connection user your twitter user id.

To support the Flow, I needed 2 tables and 2 pages:

  • a table Tweet and a page Tweets (exposed as web service) to support the tweets
  • a table Last Tweet to record the last tweet id coming into NAV and a page to be able to update this last tweet id, so the flow does not collect again the same tweets but only new tweets published after the last tweet brought to NAV

NAV tables

And this is what the flow is doing:

  1. Start with a recurrence action:Recurrence
  2. Get the last tweet id by using the NAV connection:Search Tweets
  3. Set up the search for tweets action:Search Tweets
  4. Insert a new record in the NAV Tweet table by using a NAV connection and mapping twitter fields to NAV fields:Net NAVTweet
  5. Update “Last Tweet” table with the last action in the flow:UpdateLastTweet

And this is how the flow looks like:

WholeFlow

The C/AL code is included here.

Thanks for reading. Enjoy Flowing!

 

 

 

 

Advertisements

Building a NAV server performance baseline using Logman and Perfmon Windows utilities

Standard

More and more NAV servers are being deployed to the cloud, yet a large number of NAV customers are keeping their NAV installations on premise (either on a physical server or a VM). Those that choose cloud(and if you didn’t please read this) get the benefit of cloud specific tools for measuring the hardware and database performance.

Unless you have a specialized tool, to monitor the performance of your server you need to establish a baseline. During server’s deployment, administrators can  still tweak (especially if it’s a VM or part of a hyper-v infrastructure) the server hardware until the server reaches a satisfactory performance level.

Regular recordings of server’s performance can uncover problems in their early stages, sometimes long before they become obvious in production.

Windows Server 2008 and later server versions as well as Windows 8/8.1/10 come with two easy to use tools for measuring (and scheduling) the performance of the system: perfmon and logman.

With perfmon we get a graphical interface in which we can manually create our alerts or counters, run them and analyze the results.

With logman we can manage the counters from command line.

On my system I created two counters, one for measuring SQL server counters, the other was targeted at the hardware performance:perfmon

Double-clicking on HardwareBaseline we can manage the counters:

hardware

To create these two counters I ran the following script:

install-counters

To start and stop the counters, run:

start-counters

Or manually, in Performance Monitor, right click on the counter and choose Start or Stop.

A few “logman” command switches I use:

-b and -e switches to allow the performance counters to run in a specific time period.

-v switch to add a timestamp for when the output file is created

-f specifies the file format used for collecting data

After a few minutes the performance counters graph will look like this:

graph

With a Task Scheduler entry you can control when to start and stop the performance counters collection.

As for the analysis of collected data, there are lots of places online where you can find valuable information on counter’s results interpretation.

Download the package with hardware and SQL counters and  a sample script from here.

A  baseline case study

One of the processes that is pressing all resources on a NAV server instance in our solution is the running of a report that posts sales invoices for all customers for a specific Due Date. I’ll run the report and record and discuss the counters recorded.

With the installation of MS Dynamics server you get out of the box a few counters NAV specific:

nav-specific-counters I created a new data collector with the following counters:

my-nav-counters

In Performance Monitor right click on your new Data Collector Set and Start.

Next I went to RTC and ran the report.

After the report finished I came back to Performance monitor and stopped the Data Collector Set.

reporton-counters

Microsoft Dynamics NAV\.NET CLR\% Time  in GC:

If RAM is insufficient for MS Dynamics NAV Server instance, you might see a spike in the “% Time in GC” – which measures the .NET memory garbage collection activity. My process shows a maximum of 7% nd an average of a bit over 2%  – numbers that do not show that NAV is looking for more RAM.

Microsoft Dynamics NAV\% Primary key cache hit rate:

This counter should be above 90% – otherwise your cache might be too small because either your cache settings are set too low or the cache is shared between tenants and might need to be increased. I my case is above 99%:

cachehits

% Calculated fields cache hit rate  averages 63% which means that in 63% of the cases when we launched a CALCFIELDS command we hit the cache – decent number!

calcfieldsjpg

# Open Connections is 5, and refers to the number of open connections made from the service tier to the database hosted on SQL Server. You might be interested in the counter “# Active Sessions” which keeps track of the number of concurrent connections  the service tier manages.

openconn

The rest of the counters give numbers of rows, which might or might not be too relevant considering that in time the number of rows increases.

Having a baseline and comparing regular performance counters log against it, is not just a proactive measure in order to keep a NAV server healthy. It is fairly easy to use and cheap (logman and perfmon are both builtin Windows), qualities that appeal to NAV customers and Dynamics VARs.