David Gessel
Frequency of occurrence analysis in LibreOffice
One fairly common analytic technique is finding out, for example, the rate at which something appears in a time referenced file, for example a log file.
Lets say you’re looking for the rate of some reported failure to determine, say, whether a modification or update had made it better or worse. There are log analysis tools to do this (like Splunk), but one way to do it is with a spreadsheet.
Assuming you have a table with time in a column (say A) and some event text in another (say B) like:
11-19 16:51:03 a bad error happened 11-19 16:51:01 something minor happened 11-19 12:51:01 cat ran by
you might convert that event text to a numerical value (into, say, column C) for example by:
=IF(ISERROR(FIND("error",B1)),"",1)
FIND returns true if the text (“error”) is found, but returns an error if not. ISERROR inverts that and returns logical values for both. The IF ISERROR construction allows one to specify values if the text is found or not – a bit complex but the result in this case will be “” (blank) if “error” isn’t found in B1 and 1 if “error” is found.
Great, fill down and you have a new column C with blank or 1 depending if “error” was found in column B. Summing column C yields the total number of lines in which the substring “error” occurred.
But now we might want to make a histogram with a sum count of the occurrences within a specific time period, say “errors/hour”.
Create a new column, say column D, and fill the first two rows with date/time values separated by the sampling period (say an hour), for example:
11/19/2019 17:00:00 11/19/2019 16:00:00
And fill down; there’s a quirk where LibreOffice occasionally loses one second, which looks bad. It probably won’t meaningfully change the results, but just edit the first error, then continue filling down.
To sum within the sample period use COUNTIFS in (say) column E to count the occurrences in entire columns that meet a string of criterion: in this case three criterion have to be met: the value of C is 1 (not “”), The value of A (time) is before the start of the sampling period (D1) and after the end (D2). That is:
=COUNTIFS(C1:C500,"1",$A1:$A500,">="&$D2,$A1:$A500,"<"&$D1)
Filling this formula down populates the sampling periods with the count per sampling period, effectively the occurrence rate per period, in our example errors/hour.
Lets encrypt with security/dehydrated (acme-client is dead)
Well…. security/acme-client is dead. That’s sad.
Long live dehydrated, which uses the same basic authentication method and is pretty much a drop in replacement (unlike scripts which use DNS authentication, say).
In figuring out the transition, I relied on the following guides:
- https://ogris.de/howtos/freebsd-dehydrated.html
- https://wiki.freebsd.org/BernardSpil/LetsEncrypt.sh
- https://erdgeist.org/posts/2017/just-add-water.html
If you’re migrating from acme-client, you can delete it (if you haven’t already)
portmaster -e acme-client
And on to installation. This guide is for libressl/apache24/bash/dehydrated. It assumes you’ve been using acme-client and set it up more or less like this.
Installation of what’s needed
if you don’t have bash installed, you will. You can also build with ZSH but set the config before installing.
cd /usr/ports/security/dehydrated && make install clean && rehash
or
portmaster security/dehydrated
This guide also uses sudo, if it isn’t installed:
cd /usr/ports/security/sudo && make install clean && rehash
or
portmaster /security/sudo
Set up directories and accounts
mkdir -p /var/dehydrated pw groupadd -n _letsencrypt -g 443 pw useradd -n _letsencrypt -u 443 -g 443 -d /var/dehydrated -w no -s /nonexistent chown -R _letsencrypt /var/dehydrated
If migrating from acme-client this should be done but:
mkdir -p -m 775 /usr/local/www/.well-known/acme-challenge chgrp _letsencrypt /usr/local/www/.well-known/acme-challenge
# If migrating from acme-client
chmod 775 /usr/local/www/.well-known/acme-challenge chown -R _letsencrypt /usr/local/www/.well-known
Configure Dehydrated
ee /usr/local/etc/dehydrated/config
add/adjust
014 DEHYDRATED_USER=_letsencrypt 017 DEHYDRATED_GROUP=_letsencrypt 044 BASEDIR=/var/dehydrated 056 WELLKNOWN="/usr/local/www/.well-known/acme-challenge" 065 OPENSSL="/usr/local/bin/openssl" 098 CONTACT_EMAIL=gessel@blackrosetech.com
save and it should run:
su -m _letsencrypt -c 'dehydrated -v'
You should get roughly the following output:
# INFO: Using main config file /usr/local/etc/dehydrated/config Dehydrated by Lukas Schauer https://dehydrated.io Dehydrated version: 0.6.2 GIT-Revision: unknown OS: FreeBSD 11.2-RELEASE-p6 Used software: bash: 5.0.7(0)-release curl: curl 7.65.1 awk, sed, mktemp: FreeBSD base system versions grep: grep (GNU grep) 2.5.1-FreeBSD diff: diff (GNU diffutils) 2.8.7 openssl: LibreSSL 2.9.2
File adjustments and scripts
by default it will read /var/dehydrated/domains.txt for the list of domains to renew
Migrating from acme-client? Reuse your domains.txt, the format is the same.
mv /usr/local/etc/acme/domains.txt /var/dehydrated/domains.txt
Create the deploy script:
ee /usr/local/etc/dehydrated/deploy.sh
The following seems to be sufficient
#!/bin/sh /usr/local/sbin/apachectl graceful
and make executable
chmod +x /usr/local/etc/dehydrated/deploy.sh
Give the script a try:
/usr/local/etc/dehydrated/deploy.sh
This will test your apache config and that the script is properly set up.
There’s a bit of a pain in the butt in as much as the directory structure for the certs changed. My previous guide would put certs at /usr/local/etc/ssl/acme/domain.com/cert.pem, this puts them at /var/dehydrated/certs/domain.com
Check the format of your certificate references and use/adjust as needed. This worked for me – note you can set your key locations to be the same in the config file, but the private key directory structure does change between acme-client and dehydrated.
sed -i '' "s|/usr/local/etc/ssl/acme/|/var/dehydrated/certs/|" /usr/local/etc/apache24/extra/httpd-vhosts.conf
Or if using httpd-ssl.conf
sed -i '' "s|/usr/local/etc/ssl/acme/|/var/dehydrated/certs/|" /usr/local/etc/apache24/extra/httpd-ssl.conf
And privkey moves from /usr/local/etc/ssl/acme/private/domain.com/privkey.pem to /var/dehydrated/certs/domain.com/privkey.pem so….
sed -i '' "s|/var/dehydrated/certs/private/|/var/dehydrated/certs/|" /usr/local/etc/apache24/extra/httpd-vhosts.conf
# or
sed -i '' "s|/var/dehydrated/certs/private/|/var/dehydrated/certs/|" /usr/local/etc/apache24/extra/httpd-ssl.conf
Git sum certs
su -m _letsencrypt -c 'dehydrated --register --accept-terms'
Then get some certs
su -m _letsencrypt -c 'dehydrated -c'
-c is “chron” mode which is how it will be called by periodic.
and “deploy”
/usr/local/etc/dehydrated/deploy.sh
If you get any errors here, track them down.
Verify your new certs are working
cd /var/dehydrated/certs/domain.com/ openssl x509 -noout -in fullchain.pem -fingerprint -sha256
Load the page in the browser of your choice and view the certificate, which should show the SHA 256 fingerprint matching what you got above. YAY.
Automate Updates
ee /etc/periodic.conf
insert the following
weekly_dehydrated_enable="YES" weekly_dehydrated_user="_letsencrypt" weekly_dehydrated_deployscript="/usr/local/etc/dehydrated/deploy.sh" weekly_dehydrated_flags="-g"
note the flag is –keep-going (-g) Keep going after encountering an error while creating/renewing multiple certificates in cron mode
Update Waterfox with the new PPA on Mint 19.1
The Waterfox PPA changed recently. The following let me update from 56.2.8 to 56.2.10 (between which the old PPA was removed).
First remove the old hawkeye PPA from your sources list, then:
echo "deb http://download.opensuse.org/repositories/home:/hawkeye116477:/waterfox/xUbuntu_18.04/ /" | sudo tee -a /etc/apt/sources.list wget -nv https://download.opensuse.org/repositories/home:/hawkeye116477:/waterfox/xUbuntu_18.04/Release.key -O Release.key sudo apt-key add - < Release.key sudo apt update sudo apt upgrade
Note Ubuntu 18.04 = Mint 19/19.1 the 18.10 deb fails.
1976 GMC Suburban
When I was a young child, my dad bought a brand new 1976 GMC Suburban. Yellow. No extras at all – no head liner, plastic seats, manual everything, 305 V8.
It became my car in high school, survived that. Came out to California with me; ended up in the service of SRL, survived that too.
Eventually, it escaped.
Ruby config options fail
Ruby is a horrible nightmare language, like almost all modern languages. They try to be so clever and modular, but end up making a maintenance hassle as various modules come and go, dependencies break, and the developer community moves on to the next shiny thing that claims to be the best thing to happen to programming since C.
Oh well.
If you get a bunch of "invalid option: --no-rdoc" errors, it is because sometime in the last few years --no-rdoc and --no-ri were depreciated in favor of --no-document. And, apparently, just recently builds started barfing on the deprecated errors. Building universally with these options is a pretty standard thing as it vastly improves build time and the rdoc system is a whole big kettle of annoying weirdness you just don’t need to wade through.
Now Ruby, being oh-so-clever and friendly, has all sorts of places where these might be set universally or semi-universally. The references will tell you about /.gemrc and /etc/gemrc, but only by doing a grep -FrHIis 'no-rdoc' * at / did I find these sneaky little bastards:
basejail/usr/ports/sysutils/vagrant/Makefile:RUBYGEM_ARGS= --no-ri --no-rdoc -l --no-update-sources \
basejail/usr/ports/Mk/Uses/gem.mk:RUBYGEM_ARGS+= --no-rdoc --no-ri
basejail/usr/ports/devel/ruby-gems/Makefile:DOCS_VARS_OFF= RUBY_SETUP_OPTIONS+="--no-ri --no-rdoc"
basejail/usr/ports/devel/rubygem-io-like/Makefile:DOCS_VARS_OFF= RUBYGEM_ARGS+=--no-rdoc
After converting those to the “new” “better” “shiny” version of the config option string did my gems build.
Getting the postfixadmin 3.2 update to work with FreeBSD
Postfixadmin is a very nice tool for managing a mail server via a nice web interface that just went through an update to add some security and compatibility features, but at the current revision there are a few bugs (the maintainer says these will all be cleared up in the next release). A few work-arounds:
If you get:
pkg-static: Unable to access file /var/ports/usr/ports/mail/postfixadmin/work/stage/usr/local/share/postfixadmin/README.md:No such file or directory
Then run
# make config
and enable DOCS.
If you get
PHP Fatal error: Uncaught exception 'PharException' with message 'phar "/usr/local/www/postfixadmin/lib/random_compat.phar" openssl signature could not be verified: openssl not loaded' in /usr/local/www/postfixadmin/lib/random_compat.phar:8\nStack trace:\n#0 /usr/local/www/postfixadmin/lib/random_compat.phar(8): Phar::webPhar(NULL, 'index.php')\n#1 /usr/local/www/postfixadmin/common.php(72): require_once('/usr/local/www/...')\n#2 /usr/local/www/postfixadmin/public/common.php(2): require_once('/usr/local/www/...')\n#3 /usr/local/www/postfixadmin/public/setup.php(27): require_once('/usr/local/www/...')\n#4 {main}\n thrown in /usr/local/www/postfixadmin/lib/random_compat.phar on line 8
There’s a dependency that’s not built into the makefile yet.
Run
# portmaster security/php56-openssl
(adjust the PHP version to match, or the install command to suit your environment). Remember to run # apachectl restart.
Also note that the directory the code is served from has been moved to the subdirectory /public for security. This may require updating URLs, DocumentRoot, or modrewrite as appropriate to the webserver environment to get to the login page.
After updating, navigate to public/upgrade.php to update the database automatically.
And because this is open source and not some horrible closed source product, it took a whole 2 hours for a fix.
https://bugs.freebsd.org/bugzilla/show_bug.cgi?id=231424
Thanks ports.maintainer@evilphi.com!
How to stick with a decent version of Firefox (pre-Quantum)
Firefox (52) remains my browser of choice – entirely because of plug-ins. When Firefox completely destroyed the UI/UX with “Australis,” a horrific UI change that basically made Firefox into a crappy clone of Chrome, the only thing that made Firefox usable was “Classic Theme Restorer.” Apparently, unsatisfied with the damage Mozilla had managed to wreak on their user-base with idiotic UI decisions, over the past year or so, a new version called “Quantum” (57) was rolled out that broke the functionality of almost every important plug-in.
This utterly disastrous and truly unforgivable transgression against the user-base was only slightly mitigated by sustaining 52-ESR, at least until the Sept of this year. After that, everyone who cares about having a decent alternative to Chrome will have to migrate to Waterfox.
In the mean time, one really important thing you have to remember to do if you stuck reinstalling your system on Linux (e.g. Linux Mint) is to immediately uninstall Firefox before using it even once. Then change your install version to ESR and install. If you let Quantum run even once, it will mark all your good plugins as disabled and you need to reinstall them one at a time to get them working again.
sudo add-apt-repository ppa:jonathonf/firefox-esr sudo apt-get update sudo apt-get install firefox-esr
Well, that’s the end of Firefox…. Sad to see it go after all these years, but the new plugin concept has made Firefox a subordinate version of Chrome rather than a powerful, customizable tool.
I followed these fine directions and now have waterfox running.
echo 'deb https://web.archive.org/web/20190207094152/https://dl.bintray.com/hawkeye116477/waterfox-deb/ release main' | sudo tee /etc/apt/sources.list.d/waterfox.list curl https://web.archive.org/web/20200209091904/https://bintray.com/user/downloadSubjectPublicKey?username=hawkeye116477 | sudo apt-key add - sudo apt-get update sudo apt upgrade sudo apt install waterfox
Just import your firefox preferences on starup and walla, you instant happiness with all the plugins that Quantum broke restored, including such absolute essentials as “Classic Theme Restorer” (which undoes the absolutely horrible UI changes that Mozilla adopted) and downthemall, privacy plugins, etc. If you’ve updated some plugins to be Quantum compatible, you’ll have to back those up (for me that’s things like FoxClocks and Noscript, which managed to hack together semi-viable Quantum compatible plugins after slogging through Mozilla’s buggy WebExtensions API and HTML5 quirks).
This whole translation is quite unfortunate. Waterfox is dependent on the Firefox code base, so this solution may have a finite lifespan, but for now it works and undoes the horror of Firefox 57+/Quantum.
Rubygem passenger flavors in FreeBSD
The latest bit of code to adopt the trendy new “flavors” model is passenger. As “flavors” aren’t supported in Portmaster, this means the transition results in errors like:
/bin/rmdir /var/ports/usr/ports/www/rubygem-passenger/work-apache/stage/usr/local/lib/ruby/gems/2.4/extensions 2> /dev/null || true
( cd /var/ports/usr/ports/www/rubygem-passenger/work-apache/passenger-5.1.12 && /bin/sh -c '(/usr/bin/find -Ed $1 $3 | /usr/bin/cpio -dumpl $2 >/dev/null 2>&1) && /usr/bin/find -Ed $1 $3 \( -type d -exec /bin/sh -c '\''cd '\''$2'\'' && chmod 755 "$@"'\'' . {} + -o -type f -exec /bin/sh -c '\''cd '\''$2'\'' && chmod 0644 "$@"'\'' . {} + \)' COPYTREE_SHARE buildout /var/ports/usr/ports/www/rubygem-passenger/work-apache/stage/usr/local/lib/ruby/gems/2.4/gems/passenger-5.1.12 )
find: buildout: No such file or directory
find: buildout: No such file or directory
You can make with flavors manually or (assuming your current version is 5.1.12, adjust as needed):
# portmaster -o www/rubygem-passenger rubygem-passenger-5.1.12
Meltdown and Spectre
I find the Meltdown/Spectre drama a bit amusing. Years ago, I was miffed that Intel (allegedly?) made a backroom deal with server manufacturers to exclude AMD and made an effort (which wasn’t trivial) to find an IBM x3655 7943 (I couldn’t, but I got an Intel-based x3650 and a x3655 mobo on ebay, then some QC AMD Opterons, heat sink compound, and off I went.)
Kind of a frankencomputer, but less so than my previous Netfinity 5500 M20 quad socket home server with some homebuilt mods and custom EIDE cables to support the internal (full height! 5.25″!) backup drives. Ohh… 500GB drives were big back then.
Anyhow, for no better reason than because I was offended by Intel’s tactics a decade ago, my server isn’t vulnerable to “Meltdown” or, according to AMD’s recently posted status, “Spectre 2.” As it doesn’t have a GUI, nobody is browsing compromised Java-bearing websites, Spectre 1 is unlikely to cause any problems, though I’m keeping an eye on the LLVM updates.
I have, however, updated my phones and laptop and you should too. Stay on top of this one for a while as the updates are still coming. I’ve seen Meltdown-mitigating updates on my Samsung, but not Huawei, on Linux but not (yet) on Microsoft. Apple says their December updates not only crippled your older iPhones so you’d make the pilgrimage to the Apple store for your cool aide, but also patched them (if you accepted the slowdown penalty). Update. Everything. Spectre is going to be harder: OS and Application and possibly microcode updates may be required, depending on platform. Be extra cautious about opening unknown attachments or visiting unfamiliar websites. Run noscript or another code blocker. Remember, ads that sites run can also carry malicious code, and blockers not only make the web run faster, but safer too. Of course they break the advertising funded web model you rely on.
However, if you’re dumb enough to have any important data (as an individual or a company) on anyone else’s hardware (e.g. AWS, Google, Azure, etc), good luck. All anyone has to do to get your datas is run their own VM instance on the same server you’re on. And while they’re all saying they “only” cost you 5-30% of your performance to isolate kernel memory to protect against meltdown attacks, there does not seem to be a really viable patch for Spectre yet, and perhaps not without new hardware. If you were running your own server without unknown guests, you wouldn’t need to worry about this. I will never fail to be astonished at how utterly incompetent most companies are that they can’t run a mail server. If you can’t afford an IT department, I get it, but if you have IT staff and they can’t run a mail server, are they really IT staff?
The Daily, from the NYT, weirdly slow
From my distant location overseas, listening to the news via podcasts is a great way to keep up: something I’m quite grateful to have access to on demand and via the internets. Until the end of Net Neutrality means that only “Verizon Insights” and “Life at AT&T” are still accessible, I enjoy a range of news sources on a daily basis using a podcatcher called Beyond Pod. One of the essential features it has is the ability to speed up the tempo of podcasts, some of which are a bit slow as recorded. But one…. one is like dripping molasses on a winter day: The Daily from the NYT by Michael Barbaro. I’m pretty sure silences are inserted in editing to draw out the drama to infuriating lengths and the tempo of the audio is selectively slowed to about half normal speed. Nobody can actually talk that slowly. I mean listen and try – like actually try to draw out a word that might take 1 second to pronounce to two full seconds. It is a pretty good news summary and has some useful information, but there’s no way I’d suffer through it without setting the tempo to 2x.
Every time I accidentally stream the podcast, rather than downloading and playing, the tempo control is disabled and while I scramble to skip to the next podcast before my I start questioning reality I often wonder for a moment just how bad the pauses are. Here’s my analysis:
I consider the BBC Global News to be a very professional, truly “broadcast quality” podcast. The announcers are clear, comprehensible, and speak at a pace that is appropriate for a news broadcast. I still speed it up because daily life is like that now, but if I listen at normal pace, it isn’t even slightly annoying.
The Economist Radio is fairly typical of a print publication’s efforts at presenting print journalists as audio stars. it doesn’t always work out perfectly and the pacing varies a lot by who is speaking and the rather eclectic line-up. In general it is annoyingly slow, but not interminably so. It comes across as a bit amateur by broadcast standards, but well done and very informative.
But then there’s The Daily from the NYT. This podcast was the reason I took the time to figure out how to speed up playback. There was no other choice: either unsubscribe or speed it up to something not aneurysm inducing. Looking at the waveforms, I suspect they might actually be inserting silences of around 500msec between words, perhaps for dramatic effect (there’s way too much dramatic effect in a lot of the stories, which speeding up only hastens rather than fully alleviating—never have you heard so many interviewees break into uncomfortable tears as they’re overwhelmed by whatever the day’s tragedy is, an artifice only slightly less annoying than broadcasting the sound of someone eating. OMG, that’s real. Rule 34.)
