I was recently tasked with determining why a NAV 2018 installation was occasionally performing poorly.  I suspected that one or more users were running particular queries or functions but I needed to find a way to prove that.  The catch was I am not a SQL DBA and I know even less about NAV.  But that’s how it is sometimes.  It falls on you when no one else can figure it out.

I did a lot of Googling but the consensus seems to be that it’s not possible to find the specific NAV user running any given SQL query because from the perspective of SQL every query is run as the NAV service account.  I found posts that explain how to enable SQL/NAV debugging to try and capture the user name in real time but this puts a lot of extra load on an already poorly performing system and we didn’t know exactly when the issue would occur.

I decided to build a NAV 2018 lab environment with multiple users to see if I can find a way to determine which user was running which queries.  I came up with something that seems to work and wanted to share it in case it benefits others.

In the screenshot below, I have 2 users called ADMIN-RV and JSMITH.  I used the NAV Client to perform various NAV functions with each user.  The report below shows how long each query took to execute, the full SQL query (not limited to just 4,000 characters) and most importantly of all, the actual NAV user account that executed the query.  The rows that do not include a username are internal system queries and are not associated with any end user.  The report below shows all queries for testing but in production we would limit it to queries that ran for longer durations.

Continue reading

This HOWTO explains how to configure a completely fresh environment with Dynamics NAV 2018 and the Contact Insight Dynamics NAV Outlook Addin on-premises while using Azure AD for authentication.

The reason this HOWTO was created is a customer wanted to use the Contact Insights NAV plugin for Outlook. It was determined that this plugin does not support the default “Windows” based authentication NAV uses by default and instead must use either “NavUserPassword” authentication or AzureAD authentication. The latter provides a more single sign on experience and since the customer already uses Office 365, it was decided to implement the addin using AzureAD.

Unfortunately the documentation Microsoft provides is lacking in the implementation details and so there has been considerable banging my head against the wall. Now that I’ve gotten it working, I wanted to document my steps for the benefit of both others and for future me.

This HOWTO is partially based on the official Microsoft guides for configuring AzureAD and the Outlook addin and are available here:

https://docs.microsoft.com/en-us/dynamics-nav/authenticating-users-with-azure-active-directory
https://docs.microsoft.com/en-us/dynamics-nav/setting-up-office-add-ins-outlook-inbox

In order to proceed, you will need the NAV 2018 installation media. That can be downloaded at the link below and at the time of this writing the newest version available is Cumulative Update 20.

Note: This free download can be used to install the full application and includes a demo license and database that will be sufficient for testing

https://www.microsoft.com/en-us/download/details.aspx?id=58503&WT.mc_id=rss_alldownloads_all

Here is what our lab environment looks like. For your purposes, please replace any reference to company to the name of your Office 365 tenant or domain name as appropriate

Continue reading

UPDATE:  Since I made the waypoints anyway, I thought it might be fun to make a video flythrough of the entire 21.1KM of the course.  It’s quick and dirty and more than a little silly but it does serve to demonstrate that this is not going to be easy.  Check out the video here:

I’m as surprised as anyone but I have officially registered and paid to run a 21.1km half marathon this October.  Specifically I have entered the 2019 Okanagan Half Marathon which takes place in Kelowna, BC on October 20th.  For those keeping score at home, that’s just 6 months from the time of this writing.

I wanted to know what the route looked like so I could better visualize and mentally prepare during my training.  Unfortunately while the official website (available at https://www.okanaganmarathon.ca/route-maps-p183040) includes a “Route Map” for the “21K”, it actually only includes written directions.  That’s not terribly useful.

So I decided to manually map out all of the waypoints of the course in Google Earth Pro.  I figure I’d post this here in case it’s useful for anyone also participating in the same race or more generally are interested in what 21KM actually looks.

To start us off, here is what the course looks like when taken in as a whole.  The segments highlighted in yellow are those that have to be completed twice (once in either direction).

Continue reading

I heard tonight that a local radio station will celebrate International Women’s Day by playing songs exclusively by women for 24 straight hours.  This got me thinking — could I fill an entire day’s worth of music sung only by women and do so using only songs that I actually know and like?

I realized I have almost 1,000 songs in my MP3 collection that have been acquired over two decades.  I figure if I have it, it’s a safe bet to say I like the song so I wondered how many hours all those songs would add up to.

First I needed to set a couple of ground rules.  Since the objective is to fill “24 hours” of Radio airplay, I have to take into account commercials and DJ banter.  Some googling suggests that a typical radio station plays 80% music every hour which sounds about right.  That works out to 40 minutes each hour or 16 total hours of music over a 24 hour period.

Finally, to be eligible for this list, the song must be sung exclusively by a woman or women.  Duets or guest spots make the song ineligible.

To figure this out, I needed to scan all of my MP3s and dump the list into Excel along with each song Duration.

Continue reading

If you’ve ever had to write a Microsoft certification exam, you know that the exam syllabus is available from a URL that looks like this:

https://www.microsoft.com/en-us/learning/exam-##-###.aspx

Where the ##-### is the specific exam you are writing.  You likely also know that  the way Microsoft presents this information is not ideal from a study guide perspective.  It’s broken into categories and from there is just a jumble of words including many, many “filler” words like “plan and configure” or “configure and troubleshoot”.

I am writing a new certification exam and I wanted a simple means to know what specific concepts to study and play with in my lab.  I realized that I should be able to write a PowerShell script that would download the HTML from the URL above, extract the exam syllabus text and then cut out all of the unnecessary words.  This would leave me with a clean “checklist” of keywords I need to Google and understand.

So I wrote that.

Continue reading

I recently had my furnace fail while I was away on business.  Fortunately there were no lasting consequences but it could have been a very bad day.  To try and minimize the risk of such issues in the future, I decided I wanted a temperature monitoring solution that would alert me if the temperature in the house dropped below a pre-defined threshold.  I looked at things like a Nest and the TempStick among others but all of them were hundreds of dollars and or relied on third party cloud services.  Before I invested in something like that, I wanted to see if I could build my own solution for cheaper and that was designed to meet my exact needs.  I recently received a Raspberry Pi from a friend and decided this would be a great use for such a project. 

Specifically I set out to have the following goals:

• The Raspberry Pi would record the temperature from a dedicated external temperature sensor every 20 minutes and record that value into a SQL database along with the timestamp
• The Raspberry Pi would host a webserver that would allow me to review the current and historical temperatures from anywhere using my mobile phone
• The webpage would also include a generated graphical chart showing the historical temperatures so I can review for any odd behavior at a glance
• The Raspberry PI would automatically email me if the temperature dropped below a defined threshold so I could make phone calls to take immediate action
• The Raspberry PI would upload a file to my off-site website host during each scanning interval via FTP
• A cron job would run on my off-site webhost that would look for that file and if that file was not updated for more than one hour would alert me that either a power or Internet failure occurred at my house

I’m happy to report I achieved all of the goals above and figured I’d should share how I did it below.  Keep in mind I am not a software developer and so this solution is the result of grabbing things from the Internet and cobbling them together and tweaking until it does what I want.   With that out of the way, let’s begin with what you need to create something like this for yourself.

Continue reading

Have you ever needed to calculate how many business hours / working hours have elapsed between a specific date and now?  For example, you may have some metric that says something needs to happen within 4 business hours.  We define business hours in this case as 8AM to 5PM Monday to Friday and does not include statutory holidays.  How might you go about doing that?

I found myself in this situation and figured it would be common and spent far too much time Googling trying to find someone else who built a solution for this problem.  To my surprise, I was unable to find a single working example for PowerShell.  Lots of examples for other languages such as T-SQL but nothing for PowerShell.  I was therefore forced to solve this myself.

Hopefully this will help you if you ever find yourself in a similar situation.

Before we get into the code, I’d like to start with a quick demo.  In my situation, we have a ticketing system and we want to kick off a process any time a ticket remains untouched for more than 4 business hours so we need to determine how many business hours have elapsed since the last touchpoint on the ticket.

In our example, the last time the ticket was interacted with was on December 21st, 2018 at 14:43:11.  The current date in this example is Friday January 4th, 2019 at 13:41:56.

As we see in the screenshot below, after the ticket was last touched, 2 hours 16 minutes and 49 seconds elapsed until the end of that business day.  Since this was a Friday the next 2 days were Saturday and Sunday and so we don’t add up these times.  The next week is actually the Christmas break so we also don’t want to count any of these.

The first official day back is Wednesday January 2nd, 2019.  Since the current time is newer than this we can simply add in a full 8 hours.  We do the same for the 3rd.  Finally on the 4th, since the current time is 1:41PM, that means we have already elapsed an additional 5 hours 41 minutes since 8AM.

Continue reading

You're most likely here because you are an IT administrator and you have a network that deploys Windows Updates via Windows Server Update Services or WSUS.  Perhaps you're relatively new to WSUS or you're a veteran that has been using the product since its inception.  In either case, you are mostly frustrated because even in the latest release of WSUS that there is no reliable way to force clients to check in and report their status.  You know about wuauclt /reportnow and /detectnow.  You may even be aware of the .NET method  (New-Object -ComObject Microsoft.Update.AutoUpdate).DetectNow().

But despite having tried everything, you're at at a loss.  All you want is for your clients to report their current status into WSUS on demand.  Is that really too much to ask?  Actually, it might be.  If you google "force wsus client to check in to wsus server", you'll see almost 300,000 results.  And I swear I've read every single one of them and tried every single suggestion.  

I finally decided to take matters into my own hands. I built a lab environment consisting of a domain controller, a WSUS server and a client machine.  I then proceeded to deep dive with process monitor and packet analyzers to try and find a way to "trick" the WSUS client into thinking it's time to report in.  After many hours at this, I was just about to give up when I accidentally stumbled upon the magic command I was looking for.

Continue reading

This HOWTO is written primarily for my own future reference but hopefully it’ll help someone else too.

Let’s say you want to back up the entire contents of your Android device.  How do you do that?

1) Download the Android Debug Bridge (ADB) tool that comes with the Android SDK Platform Tools.  The link of which is available here: https://developer.android.com/studio/releases/platform-tools.html

2) You should end up with the ADB tool in the following location: c:\adb\adb.exe

3) On your mobile phone, go to Options / Developer Options / and enable USB Debugging

4) Connect your phone to your computer via a USB cable

5) Open a command prompt and type c:\adb\adb.exe backup -apk -shared -all -f c:/adb/backup.ab

6) On your phone, you’ll receive a prompt.  Enter your password and choose “Backup my Data”  You will be prompted to enter the decryption password.  Remember this password as you’ll need it to view the contents of the backup

7) Wait for this process to finish.  It will take a while depending on how much data you have on your device

Continue reading

Are you studying for a Microsoft certification exam?  If so, then you are probably familiar with the “Skills Measured” website that describes what will be covered on your exam.  If not, it looks something like this:

I’m currently studying for my 70-347 – Enabling Office 365 Services exam.  The skills measured list above is a great starting point to use to determine what to search for and study and experiment with in your lab.  However the layout of the website has never appealed to me.  I would much prefer the text in Excel where I can annotate and color code it.  Unfortunately you can’t just copy and paste the text from the website into Excel as the HTML formatting makes everything a mess.

Continue reading