Tuesday, March 21, 2023

EMP Shield Review

EMP and Lightening Protection

As a quick disclaimer, I am not an electrician, however I am a software engineer and have been tinkering with things my entire life.  So I am reasonably well versed in figuring out how things work.

With various political tensions around the world, and the ever present threat of another Carrington event, I have been interested in some way to protect myself from the effects of various sizes of EMP events.  To that end I have been doing a good bit of research into what an EMP is, how it affects various things, and how to best protect against it.

The challenge is that not all EMPs are created equal, so we have to start with what it is, and what it does.  An electromagnetic wave is essentially just a radio signal, a very powerful one.  As such the frequency of the wave determines how it will affect things.  The length of the antenna also plays a large role in picking up such a wave and generating electrical current from it.

EMP Components

A nuclear EMP is made up of 3 major categories: the E1, E2, and E3 events.  They are characterized by wavelength and period after detonation that they occur.  The E1 event is a very high frequency event, my guess from the chart here and from an EMP Shield interview is that it will be in the gamma spectrum, despite the paper the chart is embedded in indicating it will only be a few hundred megahertz.  The E2 event appears to be a lower frequency event, yet still in the gamma spectrum according to the same chart. The E3 Event is a low frequency, below 1 Hz, starting at about the 1 second mark and can last for several minutes.

A geomagnetic storm only has the E3 component, and while it is generally estimated that it will not be as strong as the E3 event from a nuclear weapon, it will cover and affect a vastly larger geographic region.

Like all electromagnetic waves, including radio waves, an EMP is limited to line of site, or any objects the waves are able to penetrate.

So what does frequency and wavelength mean to us.  Let's start with a simple electromagnetic spectrum chart someone was nice enough to upload to quora.  You can see on the left hand side (long wave side) we have radio waves, then microwaves and light, and gamma rays on the far right (short wave side).  The longer the wavelength the longer than antenna required to generate a meaningful amount of current from the wave.

Most of us are familiar with car radios, truckers CBs, and walkie talkies.  Those all have relatively long antennas and fall on the far left of this chart.  Cell phones have much shorter antennas built into the body of the phones these days, they use the microwave spectrum.  So you can imagine that super high frequency waves up in the gamma region are going to bind to every tiny little antenna they can find, which pretty much means every circuit on a circuit board.

The caveat to this is that now we enter the realm of speculation.  Just how much current could a small wire generate before it got big enough to fry anything?  And just how much would such a wave have to be reduced to in order to protect against that?  There are examples on the Internet of people building small EMP devices that are capable of disrupting small electronics, but not frying them; and this is an important distinction when you're talking about the level of damage done.  Clearly unexpected current introduced into a modern electronic device is going to cause it to miss behave, usually a reboot fixes the problem.  What is not clear is how much current it would take to actually burn out a circuit, or if the wires on circuit boards are even capable of generating enough current to do that.

Once we get down to the E3 event or a geomagnetic storm we can see that the frequency of 1 Hz is super low.  To get an idea of just how low look at this chart of standard Ham Radio frequencies.  The Meter values next to the frequencies are the length of one full wave, and how long an optimal receive antenna would be to generate maximum voltage.  Most of those frequencies are measured in Megahertz, and the conversion by a factor of 1000 goes Hertz, Kilohertz, Megahertz.

So we're talking an extremely long wavelength.  What this means is it is unlikely to bind to anything smaller than an electrical line; which would in turn generate a massive surge into your house and fry everything plugged in.

EMP Protection

Most of us have heard of EMP hardening by putting things into a solid metal box.  Or some sort of wire mesh faraday cage.  It's important to note that such devices do not inherently block the EMP waves, they simply reduce them, if they get reduced to zero then they are effectively blocked.  The thicker the metal box the more reduction happens.

Living inside a metal box is not only not practical for most people, it also does not fully solve the problem as the metal box can become charged and transfer the charge to things inside of it.  So it is important for larger boxes to be grounded to allow that charge to flow to the ground.

And this is where logic starts to play a role.  There is very little evidence we can pull from as to what an EMP would really do.  The Carrington event is one of the most recent solar events we can look at.  And there was some nuclear testing that generated EMPs we can draw data from to speculate on.  It is also similar in effect to a lightening strike, so we can draw some data from lightening events as well.

Looking at all these data sources, it would seem that the highest risk items are long metal objects such as power lines that will provide great antennas to collect the energy and transform it into a power surge.  Obviously the closer you are to the event the smaller the antennas that will be affected.  And the easiest solution is if you could ground everything to get rid of the energy fast.

There are two problems with the grounding idea.  Not all devices can be practically grounded, and most surge protectors do not react fast enough to stop such a surge.  For reference a lightening strike lasts for around 1 second I believe, while the first part of an EMP surge, called an E1 event lasts for 1 nanosecond.  The breakers in a standard breaker panel do not even react fast enough to fully protect against a lightening strike.


Whole Home Surge Protectors

In about 2021 electrical code change requiring something called a whole home surge protector.  These are devices that react in about 2 nanoseconds shunting electrical surges and giving the breakers time to trip.  These devices can not handle current for very long due to their tiny 10 guage wires, but it is long enough for the breaker to trip.  While these devices can handle smaller surges, they sacrifice themselves if they end up protecting the house from a lightening strike.

There are multiple well known and trusted whole house surge protectors, a couple of them are:
Siemens FS140 it sounds like this is the highest rated one on the market.
Eaton SPD Type 2

These devices are great, which is why they are now code in all houses.  However they still do not react fast enough to protect against a surge from an E1 event.  It does sound as though you can use a modified ferrite bead to flatten the initial E1 event down to something one of these surge protectors can handle, but without digging into it, the details were not provided and that could just be snake oil.


Enter the EMP Shield

EMP shields are advertised as the first whole house surge protectors that react in less than 1 nanosecond and are therefore able to catch an E1 surge without any additional help.  Their house unit claims to be able to handle 228 kA, however it sounds as though this is 114 kA per leg, which is probably fine as logically a large surge this device is supposed to handle would probably be split across both incoming power lines anyway.

The EMP shield is also claimed to be able to handle 90k volts.  The amps and volts it can handle are the highest on the market that I have been able to find.  However, it is a brand new company started in 2017, so the question is, it is a scam?

The Negatives

In digging into the EMP shield things start out looking pretty negative for the device.  There is not a great deal of data about it being used in the real world, and I have been unable to verify most of their claims.  In fact one of the biggest negatives to the EMP Shield is their sales reps.

In talking with their sales reps the reps come across as being pretty ignorant and only capable of repeating marketing data.  They were unwilling or uncapable of sending me a picture of the inside of the device or even better of a third party opening it up and reviewing it.  They were even unable to find me third party reviews of their device in action, something any company should have at their finger tips.

While I did end up finding a couple of decent third party reviews, I was unable to verify the claim that any big energy names or the military were actually purchasing their devices as they claim.

I did find one very bitter customer who posted on amazon and a couple of other sites claiming that the generator version of the EMP Shield had not protected their generator from a lightening strike despite being installed by a professional electrician.  They also claimed the company would not honor the warranty guarantee to fix the damaged equipment.

I also found a review in which the customer had installed a solar panel version of the EMP Shield and the EMP Shield ended up draining the battery and damaging the battery and potentially the controller.  Responses from others on the internet to this review called into question whether the person had installed the correct version of the EMP Shield.  Not only are there multiple versions of the shield, there are even multiple versions for different kinds of solar arrays; this is because whole house surge protectors use different versions of metal oxide varistors to shunt energy to ground when it reaches a certain level, and you have to choose the correct one to ensure that the surge protector's resistance is high enough that it does not start shunting at normal usage levels.  Based on the lack of customer response to these queries, it seems reasonably possible this was the issue.

I also asked their rep why their device does not compete better with the Siemens FS140.  The rep responded with this:

Siemens has been around for a very long time, we have only been in business a few years. Also, we are in the EMP protection field, they are in the surge protector field. This is different fields so this is no competing.

Given that the EMP Shield is a whole home surge protector advertised to protect against lightening, they are in direct competition with the FS140.  And the fact that they are claiming faster reaction times, plus the ability to handle larger loads, and a more diverse line of products, means that they should be beating the FS140 at every corner; except brand name recognition.

In a Siemens FS140 review the electrician recommends only installing whole home surge protectors with at least a 10 guage wire.  He explained that this recommendation is due to larger guage wires being able to handle surges for longer periods before burning up and it is a mark of quality in the device.  So I asked the Shield rep about their device, and they responded with:

All of our models have 10 gauge wires except the vehicle model it is 12 gauge wire.

And yet when I read through their installation PDF for the house version, I found this:

The EMP Shield (RL – Version) comes with a terminal block 12 AWG COPPER WIRE. tighten to Torque 25 in-lbs. 

The EMPShield (W – version) comes with preconnected 12 AWG COPPER WIRE. Tighten to Torque 25 in-lbs.

Which makes me believe the wires are only 12 guage.  I did notice that all their advertising pictures show unmarked wires, however in one video review the wires do have their specifications stamped on them.  This could indicate lazy marketing, or a product that was upgraded at a later date.

Unfortunately after going back and forth with EMP Shield reps multiple times and getting answers that just weren't making sense I asked to speak with someone higher up.  They claimed they would have their marketing director reach out to me; however after a month and a couple of follow up emails I have not heard from him at all.

The Big Questions

Did it ever save a house from a lightening strike, and was there third party verification not on their website to support that fact?  After watching quite a few videos of people fawning over the device and not knowing anything about it, I finally found what I was looking for.  In this video the guy shows how the device shorted the lightening strike and in the process self destructed and even got blown off the wall it was mounted on.  Maybe in time we will see more videos like that, but at least we have one, so that is one point in EMP Shields favor.

Can it react in less than 1ns?  To backup their claim, EMP Shield has posted testing results from having their device tested at the Keystone Compliance facility.  Since I am not an electrician it took me quite some time to try and figure out what the results were saying.  I have two concerns with this document: the first is that there are reaction times listed from .24ns all the way up to 2.08ns.  And anything above 1ns would not protect against an E1 event and invalidates their marketing.  The second issue is that all the voltages and graphs list the testing power in volts and amps, not in kilovolts and kiloamps.  The caveat to this second issue is that the device does protect against lightening, so I must just not understand the graph labeling.

How would it function to protect a car when a car has no mechanism for grounding?  This one has been really hard to figure out, and the area where the EMP shield draws the most criticism online from people using common sense and saying it just isn't possible.  I watched this interview with the founder of EMP Shield, and he claimed that all they needed to do was draw the free electrons off the shell of the car itself; and he alludes to them accomplishing that by piping them into the battery.  I can get on board with the premise that if you can ground the shell then that will protect what is inside (you'd have to ground the antenna too).  But I have a harder time believing that you can just send all that energy straight into the battery and not have the battery blow up.  However, by happy accident, I ran across a video that appears to be the emp shield saving a car from a lightening strike.  The video felt authentic, and if it can accomplish this then that certainly makes the EMP claims much more believable.

Interesting Tidbits

In talking with the EMP Shield rep I got a few pieces of data I am not quite sure what to do with.  So i'll list them here and let you make up your mind.

I asked why the EMP Shield could handle multiple EMP strikes, but only one lightening strike.  The rep claimed it was because an EMP has far less voltage than a lightening strike.  A quick search of the Internet does not show a consistent consensus.  Estimates for lightening strikes include: 40 kV to 120 kV and 100,000 kV to 1,000,000 kV.  And EMP on the other hand is estimated to reach an atmospheric saturation level at 50 kV, with some evidence showing that modern devices might be able to overcome that saturation limit and take it up to 100 kV.  So we can assume that the Shield company is assuming an EMP will be limited to the 50 kV estimate.

The rep did verify a couple of common sense items.  An EMP is a repetitive and long lasting event which is why the devices needs to be able to handle multiple strikes.  A lightening strike is a single event, so it is ok for the device to sacrifice itself to handle it.  Should an event trip the EMP's breaker(s) then the system will be unprotected until the breaker has been manually reset.  In theory any event big enough to trip the breaker should also trip the main breaker which cuts the system off from the grid so it is protected.  I have heard some theories that lightening and EMPs can jump small gaps such as those found in breakers, however this is speculative at best.

An E1 and an E2 events generally both happen in less than a second, while the E3 event is generally assumed to last a few minutes, although EMP Shield believes it could last as long as several hours.  After research, I have no problem believing that 10 guage wires can shunt a large amount of electricity for less than a second without burning up.  But minutes or hours of high voltage would easily burn up such small wires.  The reps vague and illogical response to this:
 
no energy just "sits" in the wires It constantly shunts until all the over voltage is gone.

an E3 lasts for minutes to hours. It is continuous, and you are correct, without an EMP Shield is does cause problems for wires. that is why the E3 is what will destroy the grid. The E1 and E2 will not. 

Constantly "shunting" is a fancy term for energy constantly flowing through the wires to ground, which sounds like the very definition of "sitting" in the wires.  Especially if we are talking about high voltages over a several hour period.  The rep claims that an EMP will not trip the breakers.  So the rep admits that an hours long event would destroy the wiring in the grid, yet somehow the tiny wires in the Shield can handle it for an extended period because they are grounded; I am having trouble accepting this claim.

There is also the odd add on of the claim that the E1 and E2 events will not cause a problem for the grid.  My guess is since these are shorter wave events and can more easily bind to smaller wires the Rep was insinuating that the E3 event would be the one more likely to bind to the long electrical wires.  It felt poorly communicated to me.

When installing the EMP shield in a house, it is important that the 20 amp breaker(s) cover both rails, a double pole breaker does this naturally.  It is also best to have the breaker be as close to the main wires coming into the house as possible to create the fastest path to ground for the extra energy.

Conclusion

After waiting a month with several follow-up emails for EMP Shield to answer these troubling discrepancies in their product, advertising, and commutation, I am left to assume that I will not be hearing back or get these questions answered.

Ultimately I do not feel solid in the idea that the EMP shield will do what it claims.  However, with the two videos I found, and the conceptual research I have done I'd say there is a small possibility that the EMP Shield will function as advertised; or at least function as a basic whole home surge protector.  Given how sketchy things are around this company I will not personally be purchasing one at this time.

EDIT ( 3/13/2024 )

We now finally have a YouTube video of an independent party who knows what they are doing actually testing the EMP Shield device.  Despite all my misgivings prior, this video pushes it over the edge for me, and I now intend to get one as soon as finances allow.

In case the video does not exist by the time this post is being read, in summary a guy sets up a lab with the equivalent of a giant tesla coil type enclosed system on one end, a bunch of sensitive house hold electronics in the middle of the circuit, and the emp shield house device at the end of the circuit.  The whole system is powered by 120VDC.

The guy was doing his best to break the emp shield because he did not believe their claims.  He had an 8" spark gap setup to show the level of energy he was injecting into the system.  The capacitors on the firing end would charge using the 120VDC then release creating a giant surge crossing that spark gap and hitting the entire system.

As part of the demonstration he showed all the devices working, and talked about how the test was affecting the rest of his house despite his house being disconnected from the testing room aside from the 120V source current.

After hitting the circuit over 100 times the emp shield device was still working and no devices appeared damaged in anyway.  I believe he mentioned a couple of them might have rebooted, that was the extent of the damage.

He then showed a brief clip of him hitting the circuit with the emp shield device disconnected and every device on the circuit was badly destroyed.  He also described how the destruction was so bad that the majority of his closeup video footage was lost because the camera filming it had been destroyed as well.

So not only does the emp shield protect the circuit it is on, it prevents a lot of residual radiation from entering the air around the circuit.  This was a very well done test by a third party.  The test was being discussed and reviewed by a panel of 4 guys with various expertise's in the field, and electrician, a bomb tech, and a prepper were among them.

Friday, February 18, 2022

Installing VeraCrypt on Raspberry Pi4


Raspberry Pi 4 Project

I have been interested in small computing devices for awhile starting with various Arduino projects.  Recently I took on a new Raspberry project where I needed it to be able to read encrypted USB drives.  I ran into several issues along the way that I intend to document here to help others attempting similar things.

When working with a PI I generally use the Raspbian OS, it has been the one I have had the most success with.  For this project I tried both the Lite and the Full versions of the OS, and while they were both successful, the full version had one feature that caused me to go with it over Lite.

USB exFat Issue

The USB drives I was using were 64GB, which means they were too big for the older FAT32 format and were formatted exFat.  The full version of Raspbian auto mounted and read them perfectly, while the Lite version could not and needed the exFAT libraries installed using the following two commands:

sudo apt-get install exfat-fuse
sudo apt-get install exfat-utils

I didn't have any problem installing the libraries, but after install the drives would still not auto mount as the documentation indicated they should.  They did work correctly after performing a manual mount  using the following command:

sudo mount -t exfat /dev/sda1 /mnt/usb1

Part of this project was having an environment that wouldn't take me too long or too much work to re-create in the future if my micro SD card died.  So rather than continue to fight with why the USBs were not auto mounting, I ditched my preferred Lite version of Raspbian, and went with the Full version.

VeraCrypt

Now that my external USB drives were working correctly "out of the box", all I really needed to do was install the VeraCrypt software.  I use VeraCrypt because it is both free and one of the simplest and most portable encryption options available.

The first place I looked for a package was the recent downloads.  I grabbed the armhf version which is advertised as working on the 32bit Raspbian OS.  After installing and attempting to run it I was presented with the following errors:

veracrypt: /usr/lib/arm-linux-gnueabihf/libstdc++.so.6: version `GLIBCXX_3.4.26' not found (required by veracrypt) veracrypt: /usr/lib/arm-linux-gnueabihf/libwx_gtk3u_core-3.0.so.0: version `WXU_3.0.5' not found (required by veracrypt)

I founded one post that indicated the errors meant I had the wrong version of the libraries installed.  It took me awhile to figure out the actual names of the libraries the dependencies were referring to, however Code Yarns finally solved that problem for me telling me to run this command.

sudo apt install libfuse-dev libwxbase3.0-dev

Unfortunately I was still getting the same error.  Considering Ashwin from Code Yards was successful in getting it to work, and remembering the errors were supposed to be version issues, not just if the libraries were installed at all, I decided to see if the version of VeraCrypt Ashwin used would work.

But of course I didn't want to go all the way back to his version, so I started walking my way back trying each version to see if one would work.  Not a particularly fun task as VeraCrypt is not compiled for Raspian with each iteration, you have to go into each folder and see if you can find an armhf version compiled.  Several people solved this issue by simply compiling the latest version of VeraCrypt for themselves; but again, I was trying to go for the most easily reproducible build.

I finally found a working version in VeraCrypt 1.24 Update 7.  My guess is that if I had looked for newer versions of libfuse and libwxbase I might have been able to use a newer version of VeraCrypt as well.

After that it was all down to testing the install and making sure encrypted files could be created and opened.  I had no more issues afterwards, the Raspberry Pi device is really becoming quite the powerful tool these days.

Tuesday, February 15, 2022

SQL Server fast failover in High Availability Groups

 I recently had the somewhat dubious honor of helping migrate a company from a physical data center to the Azure cloud.  I say dubious because for as many positives as there are about Azure, there are at least the same number of negatives.  The only feature Azure has that makes it worth migrating, in my opinion, for most smaller companies is the ability to work and see the environment the same way a data center would see it; so much more visibility, not good visibility, just visibility.

However, my feelings about Azure are not the point of this post.  As a part of this migration we went from a traditional failover SQL cluster to an Always on High Availability group; primarily because the dba doing the migration could not figure out how to do a failover cluster in Azure.

In my short exposure to the high availability option it is both good and bad.  The automated failover in SQL server is faster than it was using the old method, however the visibility into the individual server databases is worse.

The DBA handling the migration was unable to figure out how to manually fail back the system in a reasonable time frame.  He was using a SQL script that failed back one availability group at a time.  This approach would leave our system down for up to a minute, a time frame that would increase as we added more databases to the system, and this was a big problem.

To solve this problem I decided to go with a multi-threaded approach so I could fail back every availability group simultaneously.  Unfortunately SQL does not seem to have any form of reasonable multi-threaded capability available for a script to use.  So I turned to scripting languages.

While this script could be written in most scripting languages, I chose the older VBscript this time.  The general idea is that the script loops through all found availability groups, and spins up an instance of itself in a separate thread to start a failover for each one.


Const sqlUser = "<username>" ' add your server admin login username here
Const sqlPass = "<password>" ' add the password for the username here
Const Server = "<ip address>" ' enter the ip address of the DESTINATION server to fail to here

Const adOpenStatic = 3
Const adLockOptimistic = 3

'
' Here is where we fail over the current AG
'
If (WScript.Arguments.Count > 0) Then
    'Wscript.Echo WScript.Arguments.Item(0)
Set failConnection = CreateObject("ADODB.Connection")
    failConnection.Open _
        "Provider=SQLOLEDB;Data Source=" + Server + ";" & _
            "Trusted_Connection=No;Initial Catalog=master;" & _
                 "User ID=" + sqlUser + ";Password=" + sqlPass + ";"
failConnection.Execute "ALTER AVAILABILITY GROUP " + WScript.Arguments.Item(0) + " FAILOVER;"

failConnection.Close
Set failConnection = Nothing
    WScript.Quit 0
End If



'
' Below is where we get the list of AGs to fail over
'


Dim FSO
Set FSO = CreateObject("Scripting.FileSystemObject")
GetCurrentFolder = FSO.GetAbsolutePathName(".")

Set objConnection = CreateObject("ADODB.Connection")
Set WshShell = WScript.CreateObject("WScript.Shell")


objConnection.Open _
    "Provider=SQLOLEDB;Data Source=" + Server + ";" & _
        "Trusted_Connection=No;Initial Catalog=master;" & _
             "User ID=" + sqlUser + ";Password=" + sqlPass + ";"

Set objRecordSet = objConnection.Execute("SELECT name FROM master.sys.availability_groups")

objRecordSet.MoveFirst

Do Until objRecordSet.EOF
    'Wscript.Echo objRecordSet.Fields("name")
WshShell.Run Wscript.ScriptFullName + " " + objRecordSet.Fields("name")
objRecordSet.MoveNext
Loop

objRecordSet.Close
objConnection.Close
Set objConnection = Nothing

Wscript.Echo "All failover commands fired"


With this approach the entire fail back is reduced to a couple of seconds, and the time does not increase noticeably as additional groups get added in the future.

Friday, December 10, 2021

Converting Google Spreadsheet App Script to Libre Office Macro

 I recently had a task to convert a small macro that did some data copying in a Google Spreadsheet, into an equivalent version in Libre Office.

Google has a very JavaScript like feel to it's scripting language, while Libre/Open Office uses either Python or Basic as their languages.  Basic seemed like a closer format to what I was coming from, and I've had some experience with VBA in the past, so I chose to go with that over the more powerful Python alternative.

My primary purpose in posting this is that there seems to be a lack of documentation around the Libre Office Basic language, which is understandable given the support of Python.  But in case someone else really feels like using Basic for a small project like I did, hopefully this will help them find some of the functions they need.

Here is the Google macro I was trying to convert

function fCopyData() {
  // get all the required sheets
  var shtSum = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Source");
  var shtFut = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Dest");

  // get the values we will be working with
  var vals = shtSum.getRange("A7:D45");
  var vIn = shtSum.getRange("I2:K10");

  // if we don't have enough rows, add more
  if((shtTran.getMaxRows()-shtTran.getLastRow()) < vals.getNumRows()) {
    shtTran.insertRows(shtTran.getMaxRows(),vals.getNumRows()-(shtTran.getMaxRows()-shtTran.getLastRow())+2);
  }

  if((shtFut.getMaxRows()-shtFut.getLastRow()) < vals.getNumRows()) {
    shtFut.insertRows(shtFut.getMaxRows(),vals.getNumRows()-(shtFut.getMaxRows()-shtFut.getLastRow())+2);
  }

  // populate dest data
  var today = new Date();
  today.setDate(today.getDate()+7);

  var x = 1;
  while(x <= vals.getNumRows()) {
    if(vals.getCell(x,1).getValue()=="ENV") {
      var lastRow = shtFut.getLastRow()+1;
      shtFut.getRange(lastRow,1).setValue(vals.getCell(x,4).getValue()*-1); // col 1
      shtFut.getRange(lastRow,2).setValue((today.getMonth()+1)+'/1/'+today.getYear()); // date
      shtFut.getRange(lastRow,3).setValue('=Year(B'+lastRow+')'); // year
      shtFut.getRange(lastRow,4).setValue('=Month(B'+lastRow+')'); // month
      shtFut.getRange(lastRow,5).setValue(vals.getCell(x,2).getValue()); // category
      shtFut.getRange(lastRow,6).setValue(vals.getCell(x,3).getValue()); // description
    }
    x++;
  }

  Browser.msgBox("Finished!");
}


Google has that special feature where you actually have to add new rows when you want to use them in their spreadsheets.  Moving to a desktop app no longer had that restriction, so part of the code could just be removed which was nice.

However, figuring out all the correction function names to take the place of the Google equivalents proved to be very difficult.

So here is the equivalent in Libre/Open Office Basic

REM  *****  BASIC  *****

Sub CopyData
  Dim Document As Object
  Dim Sheets As Object
  Document = ThisComponent  'assigns the current document to the variable document
  Sheets = Document.Sheets  'get the container of all Sheets

   ' get all the required sheets
  Dim shtTran As Object
  Dim shtSum As Object
  Dim shtFut As Object
  shtSum = Sheets.getByName("Source")
  shtFut = Sheets.getByName("Dest")

  ' get the values we will be working with
  Dim vals As Object
  Dim vIn As Object
  vals = shtSum.getCellRangebyName("A7:D45")
  vIn = shtSum.getCellRangebyName("I2:K10")

  ' populate dest data
  Dim thisYear As String
  Dim thisMonth As String
  Dim lastRow As Integer
  thisYear = Format(Now() + 7, "YYYY")
  thisMonth = Format(Now() + 7, "MM")

  oCursor= shtFut.createCursor
  oCursor.gotoEndOfUsedArea(False)
  lastRow = oCursor.RangeAddress.EndRow

  Dim Cell as Object
  Dim x As Integer
  For x = 0 To (vals.Rows.getCount() - 1)
    If vals.getCellByPosition(0,x).String = "ENV" Then
      lastRow = lastRow+1

      ' col 1
      Cell = shtFut.GetCellByPosition(0,lastRow)
      Cell.Value = vals.getCellByPosition(3,x).VALUE*-1
      Cell.NumberFormat = 125 ' secret code for Currency Format

      ' date
      Cell = shtFut.GetCellByPosition(1,lastRow)
      Cell.Value = DateValue(thisMonth & "/1/" & thisYear)
      Cell.NumberFormat = 36 ' secret code for Currency Format

      ' year
      Cell = shtFut.GetCellByPosition(2,lastRow)
      Cell.Formula = "=YEAR(B"+(lastRow+1)+")"
      Cell.NumberFormat = 0 ' Numeric Format

      ' month
      Cell = shtFut.GetCellByPosition(3,lastRow)
      Cell.Formula = "=Month(B"+(lastRow+1)+")"
      Cell.NumberFormat = 0 ' Numeric Format

      ' category
      Cell = shtFut.GetCellByPosition(4,lastRow)
      Cell.String = vals.getCellByPosition(1,x).String

      ' description
      Cell = shtFut.GetCellByPosition(5,lastRow)
      Cell.String = vals.getCellByPosition(2,x).String
    End If
  Next x

  msgbox "Finished!"
End Sub



Monday, August 10, 2020

Milestone Screen Recorder changes Default username

 I've been using the Milestone XProtect VMS system for several years now, and it has had various issues, many of which they have solved over time.

One of their addons, called the Screen Recorder, is a particularly handy piece of software, but has caused an outsized number of headaches given the simplicity of it.

It allows you to record a computer screen pretending as though the computer screen were a camera so all the recording footage is stored along side of all your other video footage in your VMS system.

There are multiple very helpful uses for it, one of the best is in recording customer facing demo systems to make sure people aren't doing things they should not be.

For many years it had a very odd bug in it where if the computer was turned off for too long (days), then the recording server would eventually stop trying to contact it, so when the computer was turned back on, the recording process would not be re-initiated.  However, a recent fix seems to have solved that bug.

A month ago I upgraded my demo system to the 2020R2 version of the recording server, and for the last couple of weeks I have been struggling trying to get a couple of screen recorders to come back online.  Traditionally in the past the big "gotcha" with the screen recorder has been that the username is hard coded to "Milestone" with a capital M.  If you don't know that then you will be trying all sorts of default usernames trying to figure out how to make it work; and of course it is not configurable on the client side, it was also not super obvious in the milestone documentation.  You had to go through their support sites to figure it out in most cases.

Their documentation has gotten significantly better the last couple of years, and I finally decided to re-read the screen recorder install documentation to see what I might have missed since all the old tricks I had learned over the years were not working.  Low and behold, it seems as though they change the hard coded username from "Milestone" with a capital M, to "videoos", as described here.

I had to laugh when I found this change, things worked so smoothly after figuring that out.  But the change is amusing because I can think of no good reason for it.  It is going to irritate all their clients who learned the old way, it will not increase security because it is still a hard coded username like before, and you still can't alter it on the client machine.  I am happy they at least thought to document the unannounced change though as it has not yet made it to any support forum I have found.

Tuesday, March 17, 2020

Syncing a similar code base between multiple client instances

For years one of the companies I worked for has juggled multiple copies of a very similar code base.  The majority of the code was (or could be) identical, but there was just enough of a difference that a single code base could not be used.  An additional complicating factor was that the company was known by its clients for the ability to quickly turn around feature change requests; which meant that when one client wanted a change, there was not time to test its impact for all clients before rolling it out.

As you can imagine, this was a very difficult process to manage, and very time consuming re-creating identical features when other clients decided they wanted something that had been developed.

Over the years multiple attempts have been made to solve this issue.
- Branching was scrapped because changes can really only be pushed from the base branch to the child/client branches and there was not generally time to test all feature updates when a client wanted just one single change pushed into their code base.
- A shared service architecture was scrapped because versioning quickly became unwieldy between the clients using it, and the shared services started to become fractured.  They also suffered from the same inability to easily test and regression test all combinations of the endpoints.  Also a shared database became a security concern.
- A shared dll was scrapped for similar reasons when one client updated the dll, and the other clients were forced to take all the updates on their next modification.
- Splitting the code into multiple projects by major feature area.  This allowed for smaller pushes when changes were made, and attempts could be made to keep the most similar projects in sync, but it was still unwieldy.

As the features became more numerous regression testing became a big issue.  So a fairly comprehensive automated unit test and UI testing system was developed.  This significantly reduced the danger of moving features between the client code bases, but it did nothing to reduce the time involved.

A lot of posts were reviewed, and a lot of tools tried in an effort to figure out how to have a human easily view and push changes around between all the code bases.

- SyncBackPro (great sync tool, but no human review during the process)
- Winmerge
- Vim
- Diffuse (amazing tool, but crashes on windows with three or more files open)
- Code Compare (best code compare tool found, but only supported up to three files)

Over time Code Compare became the hands down favorite in the company for comparing code files, it was a smoother compare process for code files than any other tool tried; and made pushing changes around much easier.

However, even though it was smoother, it was still a massively huge process; and getting bigger as more and more features were added.  Others have had similar problems, but no one had any amazing and workable general solutions; although many people had tried playing with various git branching type features.  One company even developed a piece of software attempting to tackle this problem, however it appears to be primarily for UI development rather than backend code.

In this ever evolving situation the next attempt build on the popular Code Compare.  A custom powershell script was developed that mapped code bases to either other, or back to a master allowing the code to be instantly compared using Code Compare between a "master" instance of the code so the developer could push new features back to master, and pull down any desired feature differences.

The custom script:
- installs/removes itself in the right click menus for files and folders
- includes directions for installing itself in those menus inside of Visual Studio
- requires the code bases being compared to have identical directory and file structures
- requires that you go through and modify the $parentProjects variable to map your project folders
- uses the $parentProjects mapping to detect the incoming file/folder path, and open a comparison with Code Compare to the file/folder of the corresponding mapped path
Here is the Custom Power Shell Script

#
# VISUAL STUDIO INSTALLATION INSTRUCTIONS:
# Menu: Tools / External Tools
# - Click Add
# - Title: Mas Code Compare
# - Command: C:\Windows\System32\WindowsPowerShell\v1.0\powershell.exe
# - Arguments: -File "D:\Documents\scripts\CompareProjects.ps1" "$(ItemPath)"
# - memorize the index number of the item you just created (how far down in the list it is)
# Menu: Tools / Customize
# - Click the Commands tab
# - choose Context menu: Project and Solution Context Menus | Item
# - - Add Command...
# - - Choose the category Tools
# - - Select External Command
# - choose Context menu: Project and Solution Context Menus | Folder
# - - Add Command...
# - - Choose the category Tools
# - - Select External Command
#





if ($args[0] -eq $null) {
msg *, "Run with the following parameters: -install, -remove, 'PathOfFileOrFolder'"
return
}

#
# Install or Remove the windows context menu item
#
if ($args[0] -eq "-install" -or $args[0] -eq "-remove")
{
# AllFilesystemObjects is the key folder here, it specifies that the "shell" sub folder will be applied
# to all file system objects.
# the "shell" sub folder indicates that we are dealing with the right click context menu
# and the final folder name becomes the name of the menu item itself
$registryPath = "HKCR:\AllFilesystemObjects\shell\MasCodeCompare"
$regAutoPath = "HKLM:\SOFTWARE\Microsoft\Windows\CurrentVersion\Explorer\CommandStore\shell\MasCodeCompare.Auto"
$regMasCodePath = "HKLM:\SOFTWARE\Microsoft\Windows\CurrentVersion\Explorer\CommandStore\shell\MasCodeCompare.MasterCode"
$regCli1CodePath = "HKLM:\SOFTWARE\Microsoft\Windows\CurrentVersion\Explorer\CommandStore\shell\MasCodeCompare.Client1Code"
$regCli2CodePath = "HKLM:\SOFTWARE\Microsoft\Windows\CurrentVersion\Explorer\CommandStore\shell\MasCodeCompare.Client2Code"
New-PSDrive -Name HKCR -PSProvider Registry -Root HKEY_CLASSES_ROOT
New-PSDrive -Name HKLM -PSProvider Registry -Root HKEY_LOCAL_MACHINE

if ($args[0] -eq "-install")
{
# attempts to get command windows to not show with (/Q and -windowstyle hidden) don't work
$Name = "(Default)"
$value = "CMD.EXE /Q /C Powershell.exe -windowstyle hidden -File "+$PSScriptRoot.replace("\", "\\")+"\\CompareProjects.ps1 %1"

IF(!(Test-Path $registryPath))
{
New-Item -Path $registryPath -Force | Out-Null
New-Item -Path ($registryPath + "\command") -Force | Out-Null
New-Item -Path $regAutoPath -Force | Out-Null
New-Item -Path ($regAutoPath + "\command") -Force | Out-Null
New-Item -Path $regMasCodePath -Force | Out-Null
New-Item -Path ($regMasCodePath + "\command") -Force | Out-Null
New-Item -Path $regCli1CodePath -Force | Out-Null
New-Item -Path ($regCli1CodePath + "\command") -Force | Out-Null
New-Item -Path $regCli2CodePath -Force | Out-Null
New-Item -Path ($regCli2CodePath + "\command") -Force | Out-Null
}

New-ItemProperty -Path ($registryPath + "\command") -Name $name -Value $value -PropertyType String -Force | Out-Null
New-ItemProperty -Path $registryPath -Name "MUIVerb" -Value "Mas Code Compare" -PropertyType String -Force | Out-Null
New-ItemProperty -Path $registryPath -Name "SubCommands" -Value "MasCodeCompare.Auto;MasCodeCompare.MasterCode;MasCodeCompare.Client1Code;MasCodeCompare.Client2Code;" -PropertyType String -Force | Out-Null
New-ItemProperty -Path $regAutoPath -Name "MUIVerb" -Value "Auto choose" -PropertyType String -Force | Out-Null
New-ItemProperty -Path ($regAutoPath + "\command") -Name $name -Value $value -PropertyType String -Force | Out-Null
New-ItemProperty -Path $regMasCodePath -Name "MUIVerb" -Value "Master Code" -PropertyType String -Force | Out-Null
New-ItemProperty -Path ($regMasCodePath + "\command") -Name $name -Value ($value + " Company\MasterCode") -PropertyType String -Force | Out-Null
New-ItemProperty -Path $regCli1CodePath -Name "MUIVerb" -Value "Client 1" -PropertyType String -Force | Out-Null
New-ItemProperty -Path ($regCli1CodePath + "\command") -Name $name -Value ($value + " Client\Client1Code") -PropertyType String -Force | Out-Null
New-ItemProperty -Path $regCli2CodePath -Name "MUIVerb" -Value "Client 2" -PropertyType String -Force | Out-Null
New-ItemProperty -Path ($regCli2CodePath + "\command") -Name $name -Value ($value + " Client\Client2Code") -PropertyType String -Force | Out-Null
return
}
if ($args[0] -eq "-remove")
{
if (test-path $registryPath) { remove-item $registryPath -Recurse }
if (test-path $regAutoPath) { remove-item $regAutoPath -Recurse }
if (test-path $regMasCodePath) { remove-item $regMasCodePath -Recurse }
if (test-path $regCli1CodePath) { remove-item $regCli1CodePath -Recurse }
if (test-path $regCli2CodePath) { remove-item $regCli2CodePath -Recurse }
return
}
}

#
# If we have gotten to here, then we are probably trying to do a compare
#
$itemPath = $args[0]
$compareProject = $args[1]



$parentProjects = @{
"Client\Client2Code" = "Company\MasterCode";
"Client\Client1Code" = "Company\MasterCode";
"Company\MasterCode" = "Client\Client1Code"
}
$parentProject = $null

# find mapping that applies
foreach ($proj in $parentProjects.GetEnumerator())
{
if ($itemPath -like "*" + $proj.Name + "*") {
$parentProject = $proj
break
}
}

# if no parent found, alert user
if ($parentProject -eq $null)
{
Msg * "Invalid project selection"
return
}

# if a parent wasn't requested, then find parent from mapping
if ($compareProject -eq $null)
{
$compareProject = $parentProject.Value
}

$parentPath = $itemPath.replace($parentProject.Name, $compareProject)

& "C:\\Program Files\\Devart\\Code Compare\\CodeCompare.exe" "/environment=auto" "$parentPath" "$itemPath"

Monday, February 24, 2020

Free 000WebHosting Gotcha

I run a small family genealogy website for my immediate relatives.  It is not large, and generates very little traffic, so I have always tried to host it somewhere for free.  Most of it's life it lived on Google's free pages.  However, with Google's recent upgrade to their free web pages system, that system lost so much functionality that I was forced to look somewhere else for hosting.

After comparing multiple offerings I settled on 000webhost.com.  They had site limiters and other restrictions on their free offering, but again, all I need was a basic place for my personal family to be able to read a few stories.  They also allowed WordPress hosting for free, and I am pretty comfortable with WordPress, so it is my CMS of choice.

Everything went very well for multiple months.  We (my family) all worked hard and got the content manually copied from Google's old static page system into the new WordPress system hosted by 000webhost.  After the initial copy several months were spent adding new data, and fixing some of the formatting issues that occurred during migration.

During this time period I was creating backups once every couple of months, which is the only thing that ended up saving us.

One day I got an email from a family member saying that the site was telling her it had been deleted.  I went to login and sure enough, my cPanel login was gone, the entire website was just gone.

I did a little research in the 000webhost KB system, and found an article that said because it was free hosting, if your site ever got deleted, too bad so sad, it was just gone.  I could understand this, it was a free site, that policy made sense; I just didn't know why my site had been deleted.

After contacting 000webhost to see what had happened, I received a very polite email back saying that because I had not logged into the site in a long time, they had deleted it.  I was a bit taken aback, we had been actively working on the site every week for months.  With a bit more clarification I learned that in order for activity on the site to count, it had to be a login specifically to the cPanel system.

At this point I was finally irritated.  It was a free hosting service so any policy they wanted to put in place was fine with me, as long as they were up front about it.  And that was my problem, many other sites had been up front about having a policy like that, but 000webhost had not.  It might be buried somewhere on their site, but at the time I signed up with them there was no warning that this would occur.

This is where the politeness ended from 00webhost, after expressing my displeasure that my site had been deleted with no warning, and no initial knowledge on my part, the response from them said I should have gotten a warning email, but it didn't always work; and they seemed rather excited to see me go.

Needless to say, I took my most recent backup and went to find another host.  There's a decent chance we will start paying for a host eventually as the site grows, but I will never give 000webhost or their parent company hostinger.com my business after an experience like this.