Diagnosing LabTech Report Center Scheduling E-mail/File Problems – an in-depth Analysis using Procmon, SQL, and Powershell

For a long time now, my LabTech has not been able to run scheduled reports. I’ve had a support case open since August 2016 for this issue. Independent to this, I’ve recently setup a LabTech Lab at home, a completely fresh install running LT 11 Patch 9, setup with exactly the same configuration of my live environment.

I tested the scheduled report creation on my LabTech dev server, having just the reporting center plugin enabled and a completely untouched Labtech, and it worked. On my live environment, with just the reporting center enabled and all the other plugins disabled it didn’t work. On my drive home from work tonight, I had a brain wave. Why don’t I use Powershell to compare the two servers, and find out where they differ? I figured the problem had to lie in the LabTech server side somewhere (as the scheduled items run as part of the DBAgent service on the server) so I focused my efforts on “C:\Program Files\LabTech”

The first thing to do was export a reference object from the dev server. I decided to use a custom PSObject and gather FilePath, FileSize, FileVersion and MD5Hash.

This loops through every file in the LabTech directory specified in Line 1, generates an object for each file and inserts that into an array. It then uses Export-Clixml to generate an XML reference with the objects in which I can import on the other server.

I then went to my live LabTech environment and ran:

The key parts here are at the end. The Compare-Object (also known as diff) is the important output here. Once this was done I played about with the best way to display my data and came up with:

The results were interesting. I ignored some of the folders where I’d expect a change (Backup/Logs/Plugins) and I focused initially on seeing if any report center DLLs were different – they werent. What I did notice though were a number of significant differences with the DevExpress DLLs. The “Side Indicator here” indicates which side the change is on, depending on what you used in the order of the previous cmdlet:

I thought this was odd, so went to check on the LabTech Live server to have a look at the DLLs in the folder.

Compare that to my LabTech Dev Server:

Definitely something not quite right here. It was at this point I took a backup. I took a full backup of the LabTech live – server, including a snapshot. I backed up the database for good measure, and also took a copy outside of my LabTech live server because I was about to make some ‘changes’!

My running theory at this point was the older DLLs (which I am assumed at the time were largely useless) were causing a problem with the reporting center. I half expected a plugin was messing with the DevExpress DLLs. After all, my dev server DOESN’T have them and it worked fine.

It turns out that my LabTech Dev server has all v14 DevExpress DLLs, yet my LabTech live server had V11, V12 and V14 DLLs.

TEST 1 – Delete the DLLs

First, I disabled all of my plugins in Live apart from the report center. I then deleted the DLLs from the folder; It’s worth pointing out that though this is a desperate measure I felt that desperate times called for it. I’ve waited 6 months to get this working and it STILL wasn’t working. I was confident in my backup, so I deleted them then restarted the LabTech Database Agent Service.

The DLLs recreated as soon as the LabTech Database Agent started. Shit.

THEORY 1 – The DLLs were being copied from somewhere on the local disk. I did a search for one of the DLLs across the entire C:\ drive and only got one result (the file in the main folder). Theory busted.

So entered the bold and beautiful Procmon (Process Monitor). Possibly the best tool I have in my arsenal as a sysadmin.

I deleted the files again after stopping the service, started capturing, started the service and then stopped capturing then searched through the results for the DevExpress.XtraCharts keyword (an example of one of the older versions DLL that was recreating). A few things popped out at me.

Specifically, the file creation was there, as was not a couple of milliseconds behind it a call to SQL.

THEORY 2 – The DLLs were stored in SQL somewhere. I went into SQLYog and searched for some keywords. DLL? Nope. Devexpress? Nope. Dependency? Wait, what popped up in the tree view when I was typing depen? Oh there is a table,

That’s not how you spell dependency, LabTech. Anyway – that looks promising, so I opened it up:

Jackpot! I also noticed there was a binary column called FileSource. This has to be where the DLLs are being generated from. (Note the screenshot above is AFTERWARDS as I didn’t have a screenshot of this table full of v12 and v11 DLLs but they were there).

I also noticed something else odd. There were multiple entries for certain DLLs that “should” have been exactly the same version. It was at this point I looked back at my comparison from before and noticed a significant change in “C:\Program Files\LabTech\DevExpress.XtraCharts.v14.2.dll” – the file size on my dev server was around 3.6MB. The file size on my LabTech server for the same DLL was around 500KB. Something clearly not right there. It also had two entries in Dependancy with exactly the same details apart from the GUID and the binary blob.

I went on my second deleting spree (remember I had a backup done previously!) – I deleted every row in here that had a devexpress reference to v11, v12 and I also deleted the incorrect entry for DevExpress.XtraCharts.v14.2.dll, using the GUID on the same table in my dev server to make sure I deleted the correct one.

I deleted the DLLs from the last run from “C:\Program Files\LabTech\” that were V11 and V12 DevExpress DLLs, then restarted the LabTech Database Agent Service. The service (thankfully) started. I opened the LabTech CC (which thankfully also started) and opened the Report Center and eagerly put a schedule of 15 minutes in the future to test.

I immediately noticed something in the scheduling window that had changed. The AM/PM had disappeared from the schedule selection.

What it looked like before hand:

What it looked like after my DLL changes:

Notice how the AM/PM is missing? This told me that the DLL changes had indeed had an effect. My working theory is that one of the DevExpress V11 or V12 DLLs that the DB Agent was loading were causing an older DLL to be active which the Report Center was using. I already know the Report Center runs entirely on V14 DLLs because I can see this in my Dev environment which has ONLY v14 DLLs.

I submitted my schedule changes and waited.

Every. Single. Scheduled. Report. Ran. Both my two e-mailed ones and the scheduled file creation. I almost cried.

THEORY 3 – One of the Plugins was registering directly into dependancy table bad DLLs. The testing for this was basically to reload all 38 plugins individually and restart the database agent each time. This took an absolute age. None of them created any entries in the dependancy table, nor did any V11 or V12 DLLs get created. Hmm.

THEORY 4 – In the past, a plugin that has since been updated has been responsible for the errant entries and corrupted/incorrect versioned DLLs. Unfortunately there is no easy way to prove this.

A job for tomorrow – see if this has fixed other things in LabTech that were broken for me (like the Patch Manager!). One can only hope. That’s been ongoing since July 2016.

I hope you found this useful and can take strategies away from it on how to deal with problems in what is hopefully and structured and logical problem solving manner.

Special thanks to @Hikato and @SomeCallMe, the former for assisting me in confirming DLL versions on his LabTech as well as getting me information from his Report Center and to the latter for answering a question on how the DBAgent loaded DLLs at start.