Labtech and Corrupted Scripts

Update: The latest patch fixes this, or at least handles it more gracefully. v110.387 (Patch 15) is what is needed, otherwise see below.

Labtech (or Connectwise Automate, which i refuse to say outloud), has a current bug where you can quite easily corrupt a script. Since the scripts are not traditional text files, and are stored encoded in the database, there is no easy way to fix it if something does get corrupted. I'll outline how this happens, and how you can fix it, since their support has indicated that this is a low priority issue to them.

The Issue

For various reasons a script can get corrupted. When i say corrupted, it's all still there, but something is preventing it from getting loaded. For example, the other day my coworker added a new EDF and then asked me to take a look at a script. When i loaded it, the script functions did not show the EDF name but just showed the ID. Though i advised against it, we ended up making some changes and saving the script. After that, the script would no longer load with the error "Error loading script:Syntax error: Missing operand after '=' operator." (see below):

Now, you'd think that Labtech would still load the script and force you to fix it... NOPE. Any work you've put into that script is now lost, you need to restore from backups. This means either restoring your entire database, or grabbing the nightly .sql backup files, editing to grab the right data, and replacing via SQL. But what if you just put hours of work into the script and backups haven't run yet? Well, you're SOL in this case. Or are you?

The Fixes

Restoring from backup

If you just want to go to last night's backup, then the fix is not too difficult. You will need adequate knowledge of running SQL queries against the Labtech server, and the text editor of your choice.

1. Take a new backup, in case you mess something up.
2. Grab the backup that you want to restore. These are usually located in C:\Program Files\LabTech\Backup. You can go into the Tablebase folder or extract one of the zip files if you need an older copy. The file you need is lt_scripts.sql. Make a copy of this in a spot where you can edit it.
3. Determine the ID of the script that is corrupted. Easiest method here is to show IDs in the Labtech Client (Tools > Show ID's). The ScriptID is the number in parenthesis next to the script in the Navigation Tree. For the sake of this guide, we will be using ID 5986.
4. Edit the .sql file, i used notepad++. Search for the ID of the script, you should see something that looks like this. The first red square is what you're looking for with the script ID, the second is the actual content that you need to use. You want everything between the apostrophes, in my example starting with H4sI and ending in AAA===. I had to blur a lot of this for obvious reasons, but you should get the gist of it.
5. Open your preferred SQL editor (Labtech staff usually install SQLyog on the server, you can also use the CLI or MySQL workbench)
6. Craft a query that reads:
   UPDATE lt_scripts SET ScriptData='H4sI......AAA==' WHERE ScriptID=5986;
   (replace 5986 with your ScriptID, and paste the FULL content of what you selected into the ScriptData portion of the statement.)
7. Run the query, and it will replace the body of the script with your backup copy.
8. At this point you should be able to edit the script, no Refresh/Reload needed
9. If not, you may need to go to an earlier backup

Fixing the script in-place

This is definitely the more difficult route. If your backup from last night is good enough then i'd use the previous section to restore from backup. Otherwise, read on. This requires knowledge of running SQL queries, minor XML knowledge, as well as the ability to perform Base64 encodes/decodes and gzip compressions/decompressions. I did this on a linux server, however you can also do it with other tools. I will gloss over various tasks that are covered in the previous section for the sake of brevity.

1. Take a new backup, in case you mess something up.
2. Extract the ScriptData for this script using this query:
   SELECT ScriptData FROM lt_scripts WHERE ScriptId=5986;
3. Base64 Decode the result. You can use an online tool, or on a Linux shell:
   # base64 -d > output.gz
   After running that command, paste the data in and press Ctrl+D. However you do it, you need to end up with a .gz file containing the base64 decoded content.
4. Unzip the file. On a Linux shell you can do:
   # gzip -d output.gz
   or
   # gunzip output.gz
5. output is now an XML file containing the script. Edit this with a text editor (i use Notepad++) and find the section with the error. If you note, when you open the script editor and it errors out, it does load at least a few lines before dying. The last line it loads is where you want to start looking. If this has a variable name or something you can search for, use that. In my case of a missing EDF, i ended up with a section that looked like this:

   <ScriptSteps>
       <Action>2</Action>
       <FunctionId>103</FunctionId>
       <Param1 />
       <Param2>%computerid%</Param2>
       <Param3>DuoDefaults</Param3>
       <Param4 />
       <Param5 />
       <Sort>8</Sort>
       <Continue>1</Continue>
       <OsLimit>0</OsLimit>
       <Indentation>1</Indentation>
     </ScriptSteps>
     <ScriptSteps>
       <Action>2</Action>
       <FunctionId>103</FunctionId>
       <Param1>611</Param1>
       <Param2>%computerid%</Param2>
       <Param3>APIHostnameComputer</Param3>
       <Param4 />
       <Param5 />
       <Sort>9</Sort>
       <Continue>1</Continue>
       <OsLimit>0</OsLimit>
       <Indentation>1</Indentation>
     </ScriptSteps>
   

6. If you notice, the second ScriptSteps block has an ID specified in Param1, while the first one does not. This is where LT stripped out the non-existent EDF and shot itself in the foot. In my case, i just needed to find the EDF ID and replace <Param1 /> with <Param1>1234</Param1>. If you're not up to it, just delete the entire ScriptSteps block.
7. Once done, save the file, and gzip it back up again:
   # gzip output
8. Then, base64 encode the file:
   # base64 output.gz > fixed.txt
9. Finally, copy the contents of fixed.txt and run an update to replace the ScriptData with the fixed version:
   UPDATE lt_scripts SET ScriptData='H4sI......AAA==' WHERE ScriptID=5986;
   (replace 5986 with your ScriptID, and paste the FULL content of what you selected into the ScriptData portion of the statement.)
10. Run the query, and it will replace the body of the script with your backup copy.
11. At this point you should be able to edit the script, no Refresh/Reload needed

Final Notes

• Do not update the ScriptData of one script with the contents of another. The ScriptData has the GUID of the script encoded in it so it will still be broken.
• Don't have access to a linux server, or don't know linux? Here are some alternatives:
  • For Base64 encoding/decoding, you can use your programming/scripting language of choice. Powershell has the ability to do Base64 Encoding/Decoding, a quick google can tell you how to do that. You can't really use websites for this portion because you are dealing with binary data.
  • For gzip compression, you can install 7zip to take care of this for you.
• It's a good practice to export your scripts after making large changes. If you can store these in version control (such as git), that's even better.