Month: January 2020

Technical support

Technical support. Not quite “Vanilla Sky” level stuff but still thought provoking.

I provide some limited technical support on Apache OpenOffice and LibreOffice forums.

Why not Microsoft Office? Because that’s what I do at work. At home I use, and have used for many years, LibreOffice and OpenOffice. Why? Because they save me money, the support forums are generally more congenial and providing support lets me give back a bit for the value I get.

One of the things I really enjoy about providing support is seeing all the ways people are using software to do things they need to do. Things I would never have conceived of. It is a real eye opener to get a handle on someone else’s requirement. And then very gratifying to help someone meet their need.

One of the recent support requests was for a bowling league score sheet kept in Apache OpenOffice. The requestor wished to have the latest match value always be shown in a particular cell. The league has 32 weeks in its season and for each week the bowler’s score is entered for each of three games. The game total and average are calculated and displayed for each week.

The latest week’s match value always needs to show in a particular cell. The method being used was to update the cell manually each time a new week’s scores were entered.

I came up with a solution, posted it. Then, as I often do, rethought the whole idea. What I realized was the way I designed the solution it would always show the value for the 36th week, the last week of the season, rather than the most recently entered week! Not good.

After some deeper inspection I recognized this happened because the formula to show the score for the latest week actually just checked to see if there was anything in the referenced cell. And it counted a formula in the cell as something, even if the formula displayed nothing.

This needed to be fixed! I couldn’t provide a solution that didn’t work.

After some thinking I realized a formula to show the latest value needed to recognize whether the formula on each row displaying the value was showing a numeric value, a blank “”, or a label “DNB”. DNB, Did Not Bowl, was a label indicating the bowler hadn’t bowled that week.

Again, the season is 36 weeks. Scores are entered week by week. This meant the rows showing score and average were always followed by rows showing nothing. Even though cells in the rows contained formulas, the formula result was “” for each week after the latest one entered.

My original formula was detecting a formula in a cell as something. I needed to come up with a formula that could identify the row before the first row with formula result “”. Finally an idea struck. I could use a function that counted the rows where the formula result is “”. A blank. These are always the weeks of the season that are not yet played. The season is 36 weeks. Subtract the number of blank rows from 36 and that’s the last row with a score. Problem solved!

That formula is…

=INDEX($Sheet1.G6:G41,36-COUNTBLANK($G$6:$G$41),1)

With that part of the problem solved I saw that some formulas I hadn’t touched were returning #VALUE! errors. These were formulas that calculated total pins week by week. These errors were happening because of changes I’d made to the formulas to sum the pins for each week and to produce the pin average for each week.

My fixes created the problem so I was determined to resolve it.

I created what I call a “dynamic formula”. A formula that changes based on where it is in the sheet or what it reads from a value elsewhere in the sheet. It didn’t work. It has been some time since I created such a formula in OpenOffice or LibreOffice.

There’s quite a bit of compatible functionality between Microsoft Office, LibreOffice and OpenOffice. For the most part spreadsheets created in one work in the other without modification. For the most part.

As it turned out, I was creating the “dynamic formula” as if writing it in Excel. Dynamic formulas are one of the things that are a bit different between the Microsoft and OpenOffice/LibreOffice spreadsheets. Once I recognized that, I was on the way to developing a solution.

The below dynamic formula, “the solution”, totals values in a column beginning at a specific row and continuing to the row the formula is in.

=IF(ISNUMBER(I6),DSUM($G$5:INDIRECT(CONCATENATE("$G",ROW())),1,$BB$5:$BB$6),"")

The formula needs to calculate a sum from a fixed starting row to whatever row the formula happens to be in. And in the case of the bowling league it needs to do that for thirty-six rows. If the formula couldn’t tell which row it was in and sum from the first row to the formula’s row then thirty-six different formulas would need to be entered. One for each row.

Entering the same formula in thirty-six rows is much easier in my opinion. And easier to maintain and easier to modify.

By tackling this person’s question I:

  • Helped solve a problem
  • Familiarized (again) with the difference between Calc and Excel dynamic formulas
  • Learned about a process, a “functional requirement”, I wasn’t familiar with and provided a way to support it

For me, this was a win all around. What could be better than the warm glow of finding the solution to a previously unknown use case?

Certified Information Systems Security Professional, CISSP

Security. Human factors are always important.

I hold a CISSP certification. Information security is something I’ve found intriguing since I first started my technology career. One of the first user trainings I developed was around the time of the “I love you” malware that struck via a deceptive email attachment. And to this day email continues to be a vector for compromising systems. Or actually I should say, email account holders continue to be a vector for attacking systems.

My office at the time of “I love you” wasn’t struck by it but we would have been except for our mail system. Everyone in the business, about 160 people at the time, had gotten the system security training. And a special alert had gone out after the training warning of “I love you”. By and large the people in the company were well educated professionals with uncommonly high expectations around privacy and confidentiality. Our work was providing counseling and permanency for youth and families involved with various states’ child and family services departments.

What I mean to say is the staff of the organization all understood and practiced privacy and confidentiality and so were an interested and engaged audience for the security training.

With the above as background, this is the story of “I love you” in my office.

One day the Executive Director’s Administrative Assistant called me and said, “Alan, I think I’ve done something I shouldn’t have.” She explained she had gotten an email from the building’s manager with an “I love you” attachment. The man was someone she dealt with often and was on good terms with. She was married and was a bit upset by getting an email with such a bold attachment. She was also intrigued wondering why he would send it to her and what message might be inside.

She didn’t delete the email immediately but kept it and wondered what message it might contain. Finally she opened the email and attempted to open the attachment. Nothing happened.

Our mail system was Lotus Notes client and server. The malware relied on Visual Basic Scripting in Microsoft Outlook and so was unable to propagate in our environment.

This is a case where a knowledgeable person with a commitment to privacy and confidentiality and who had gotten security training as well as read the follow up warnings about “I love you” nearly caused a security incident because of curiosity! The only reason there was no incident was because of a technical feature of our environment.

She realized something was wrong when there was no message to see. And then she relied on her training, called me, and confessed to maybe doing something wrong.

This is a lesson that’s stayed with me. You can have good people and good training but good technical measures are still needed to back them up. People will occasionally do things they suspect might not be in their best interest because of some other overriding impulse, like curiosity.

And this brings me to something else, earning CPE (Continuing Professional Education) credits to keep my CISSP current. I generally enjoy the briefings and learn many interesting things while earning CPEs. However I do struggle sometimes because it is difficult at times to find CPE courses that are not too strongly vendor centric. My preference is for training that is less about the knobs and switches of a particular technology and more about the ideas behind threats and countermeasures.

I was really pleased to get a mailing from (ISC)2 the other day. It introduced courses that are free for members that providing training and CPEs. Much of the training looks to be very relevant to my interests and I’m very excited to get started!

Courses like:

  • Techniques for Malware Analysis
  • Web Appliction Penetration Testing
  • Gaining Support for Your Security Program
  • Introduction to NIST Cybersecurity Framework

…and others are all about topics that I expect to be quite enjoyable.

I also will be producing another post with some examples of phishing attacks I’ve received. Some that were quite good and nearly motivated me to reveal credentials.

Certbot headaches!

Modifying certificates with certbot. It works and it was a long journey to get it done.

If anyone’s reading this you may have noticed the URL is wp.boba.org. Possibly you entered www.wp.boba.org to get here and saw it change to wp.boba.org. Whatever. Until this evening (15 Jan, 2020) the URL’s protocol would have been http://. https:// wouldn’t have even connected. Now, even if http:// is entered it changes to https://. Hooray!!

The SSL certificate for this website is now part of the alanboba.net certificate. But, until tonight, I was unable to expand the domains in the alanboba.net certificate to include wp.boba.org and www.wp.boba.org.

My attempts to expand the alanboba.net certificate began nearly a month ago. Everything I tried failed. In desperation I posted on the LetsEncrypt community forum a little over three weeks ago, Apache certificate modification not successful, hoping someone would quickly recognize the problem and suggest a solution.

That didn’t pan out. Not a lot of respondents. The fix suggested didn’t address the issue the error message presented, the request was “unauthorized”, or suggest if the message might be misleading.

Domain: wp.boba.org
Type: unauthorized
Detail: Invalid response from http://wp.boba.org/.well-known/acme-challenge/QVV-1Skk-Xvrr6QAL-IvvDZuMGnhr2mNOfoAWbkYCnw [67.86.147.116]: "\n\n404 Not Found\n\n

More reading. More checking settings on this server. Some experimental configuration changes to see if the issue resolved and the command certbot –expand… would succeed in adding two additional domains to the existing certificate. None of the changes worked.

Finally came across a different command and decided to try it. As I understood it, it is meant to renew existing certificates not add domains to them. However it does include a “webroot” parameter and some of the documents I’d read suggested the webroot location might not be correctly interpreted by the command I was using.

The documentation I found doesn’t say anything to suggest the command can be used to expand the domain names covered by a certificate. I just had an inspiration and decided that if webroot might be the problem then explicitly specifying the webroot and adding domain names at the same time might turn the trick.

Tonight I tired the command with the webroot parameter and my additional domains appended to the list of domains already on the certificate. Surprise and delight! The domains were added to the certificate AND the protocol is now changed to https:// even if http:// is used in the URL name!

The following command…

sudo certbot run -a webroot -i apache -w /var/www/wp.boba.org/public_html -d alanboba.net,boba.org,sclc.boba.org,train.boba.org,training.boba.org,www.alanboba.net,www.boba.org,wp.boba.org,www.wp.boba.org

Produced the output below. Plus it added my two additional domains to the existing certificate and modified apache’s config for the website so http:// requests are rewritten as https://. Like I said at the beginning, hooray!!

Saving debug log to /var/log/letsencrypt/letsencrypt.log
Plugins selected: Authenticator webroot, Installer apache
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
You have an existing certificate that contains a portion of the domains you requested (ref: /etc/letsencrypt/renewal/alanboba.net.conf)
It contains these names: alanboba.net, boba.org, sclc.boba.org, train.boba.org, training.boba.org, www.alanboba.net, www.boba.org
You requested these names for the new certificate: alanboba.net, boba.org, sclc.boba.org, train.boba.org, training.boba.org, www.alanboba.net, www.boba.org, wp.boba.org, www.wp.boba.org.
Do you want to expand and replace this existing certificate with the new certificate?
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
(E)xpand/(C)ancel: E
Renewing an existing certificate
Performing the following challenges:
http-01 challenge for wp.boba.org
http-01 challenge for www.wp.boba.org
Using the webroot path /var/www/wp.boba.org/public_html for all unmatched domains.
Waiting for verification...
Cleaning up challenges
Deploying Certificate to VirtualHost /etc/apache2/sites-enabled/boba.org-le-ssl.conf
Deploying Certificate to VirtualHost /etc/apache2/sites-enabled/boba.org-le-ssl.conf
Deploying Certificate to VirtualHost /etc/apache2/sites-enabled/sclc.boba.org-le-ssl.conf
Deploying Certificate to VirtualHost /etc/apache2/sites-enabled/train.boba.org-le-ssl.conf
Deploying Certificate to VirtualHost /etc/apache2/sites-enabled/train.boba.org-le-ssl.conf
Deploying Certificate to VirtualHost /etc/apache2/sites-enabled/boba.org-le-ssl.conf
Deploying Certificate to VirtualHost /etc/apache2/sites-enabled/boba.org-le-ssl.conf
Created an SSL vhost at /etc/apache2/sites-available/wp.boba.org-le-ssl.conf
Deploying Certificate to VirtualHost /etc/apache2/sites-available/wp.boba.org-le-ssl.conf
Enabling available site: /etc/apache2/sites-available/wp.boba.org-le-ssl.conf
Deploying Certificate to VirtualHost /etc/apache2/sites-available/wp.boba.org-le-ssl.conf
Please choose whether or not to redirect HTTP traffic to HTTPS, removing HTTP access.
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
1: No redirect - Make no further changes to the webserver configuration. 2: Redirect - Make all requests redirect to secure HTTPS access. Choose this for new sites, or if you're confident your site works on HTTPS.
You can undo this change by editing your web server's configuration.
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
Select the appropriate number [1-2] then [enter] (press 'c' to cancel): 2
Enhancement redirect was already set.
Enhancement redirect was already set.
Enhancement redirect was already set.
Enhancement redirect was already set.
Enhancement redirect was already set.
Enhancement redirect was already set.
Enhancement redirect was already set.
Redirecting vhost in /etc/apache2/sites-enabled/wp.boba.org.conf to ssl vhost in /etc/apache2/sites-available/wp.boba.org-le-ssl.conf
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
Your existing certificate has been successfully renewed, and the new certificate has been installed.
The new certificate covers the following domains: https://alanboba.net,https://boba.org, https://sclc.boba.org, https://train.boba.org,https://training.boba.org, https://www.alanboba.net, https://www.boba.org,https://wp.boba.org, and https://www.wp.boba.org