Building My Own NAS (with Plex, Crashplan, NFS file sharing, bitTorrent, etc.)

For about the last seven years (since 2012), I’ve been using a Synology NAS (Network Attached Storage) device in my house as a central repository for files, photos, music, and movies. It has generally worked well. However, there have been a number of serious problems with the Synology NAS I bought (DS413J). First, the amount of RAM is limited and cannot be upgraded. Second, the CPU (MARVELL Kirkwood, Arm processor) is in the same situation. While the box is small and draws very little power, the inability to upgrade the hardware (other than hard drives), means that I’m basically stuck with what was considered cutting edge back in 2012 when I bought it. In practical terms, these limitations have meant that I have not been able to run Crashplan on my Synology box since the first year I bought it because I have more than a terabyte of files I am backing up and the 512 mbof memory cannot handle that (I created a workaround where I run Crashplan on a different computer, but am backing up the files on the Synology box over the network). It also means that I haven’t been able to run the latest version of Plex for the last 4 years because Plex stopped supporting the CPU in my Synology box. This eventually came to a head about a month ago when the latest Plex client on my Roku stopped working with the very outdated version of Plex server on my Synology NAS device. As a result, I was no longer able to serve videos to my Roku, which was one of the primary reasons why I even have a NAS in the house.

The convenience of having a pre-built NAS with a web interface has been nice. There is a lot to like about Synology products. However, you are locked into their hardware and their software and are restricted by their timelines for upgrading to the latest software. Additionally, my Synology NAS, which is a 4-bay device, has a problem with one of the bays that actually ended up destroying 2 hard drives, so I only have 3 usable hard drive bays. And, Synology devices are crazy expensive. Given my use scenario, paying as much as they now charge for a high-end NAS that might temporarily meet my needs doesn’t make a lot of sense.

So, I finally decided that it’s time to go back to building my own NAS (I had one for a short while before). As I started researching what I wanted for my Do It Yourself (DIY) NAS, I basically went down a rabbit hole of options: Which Operating System (OS)? What hard drives? What file system for the hard drives? Do I use a RAID? What software do I need to install? What CPU and motherboard? How much memory? In this rather lengthy post, I will detail what I ultimately decided to do and why.

DIY NAS: OS (Operating System) Options

As a long-time Linux user, I was never going to consider anything but a Unix-based system. That means I never even considered Windows as an option. Those who use Windows could certainly consider it, but I have no interest in using Windows for my NAS. That, however, didn’t narrow my options that much. With Unix-based systems, all of the following are real contenders: Unraid, FreeNAS, Amahi, Open Media Vault, Rockstor, Openfiler, NAS4Free, or just the Linux distribution of my choice (currently, Kubuntu 18.10). I spent quite a bit of time considering these OSes, all but the last being designed specifically as OSes for NAS boxes. The more I thought about theses pre-packaged options, the more I realized that they fall in between the proprietary OS of my Synology box and the OS I use on all my other computers (Kubuntu), and they are all crippled (to varying degrees) by the same problem I had with Synology – I am beholden to the companies/people who maintain this software to release new packages for the software I need to run: Crashplan and Plex. This is a particular concern with Amahi, Rockstor, Openfiler, and NAS4Free, since they all require the installation of software through their “packages.” That would not be the case if I just went with a standard Linux distribution that gets regular updates (e.g., Kubuntu). I can install pretty much whatever I want on such an OS, which means the NAS will be whatever I want it to be.

Unraid (which isn’t free) will really allow you to install an OS on top of their OS, so that might not be a problem. But I’m also not convinced that I need Unraid for drive management (as I’ll detail below when I discuss how I organized my hard drives). FREENAS is probably the most appealing OS for this as it really is just an OS, and you can install what you want on top of it. My biggest concern here is that FreeNAS is BSD based, which really shouldn’t be a concern, but I have limited experience with BSD (tons with Linux), and I wasn’t certain what FreeNAS would give me over a standard distribution.

There very well may be advantages to one of these specific OSes for NAS devices that I am missing. But, after having suffered under the proprietary lock-in and inability to upgrade my software under Synology, I realized that I was very wary of getting locked into a pre-packaged OS that would mean I couldn’t install what I want to install. Ultimately, I decided to just install Kubuntu 18.04 on what would become my new, DIY NAS box. Some might note that I should have just gone with Ubuntu server, as it reduces the amount of CPU power and memory since I wouldn’t have a graphical front end (KDE). I considered it. But that would also mean that I would have to manage the entire device through the command line or try to find some other software (e.g., webmin, which won’t do everything I need) that would allow me to monitor my device via a web browser. Since I’m most comfortable with a graphical desktop environment, why not just go with what I know and what works for me?

Final choice on OS: Kubuntu 18.04, which is a long-term support release (important for future upgrades to the OS).

DIY NAS: Hardware Options (CPU, motherboard, RAM, etc.)

When Plex stopped working with our Roku, my wife quickly realized it. We all use Plex on a regular basis to watch our movie and TV collection. When I told her what the problem was and said that I thought I was going to need to replace our NAS, she asked me how much it was going to be. Being honest, I told her it could be fairly expensive, depending on what I decided to build. Luckily, we are in a position financially where I could spend upwards of a $1,000 on a new NAS if I needed to and she said that would be fine.

I spent quite a bit of time consider hardware. The biggest question was really whether I wanted to go with dual Xeon processors to build out a real server or go with what I am most familiar, AMD CPUs like Ryzen (I build all my own desktop computers). The dual Xeon approach made a lot of sense as they can manage a lot of concurrent threads. The latest processors from AMD (and Intel, but I’m an AMD guy), Threadripper, also manage a lot of simultaneous processors. However, these are all pretty expensive processors, even somewhat older Xeon’s can be a little pricey if I’m trying to get something that was made in the last few years. Additionally, the motherboards that go with these can be almost as expensive. I debated these options for quite a while.

Then, I had an idea. I had an older computer lying around from my latest upgrade (I usually upgrade my desktop, give my immediate past desktop to my wife; during the last one, I ended up upgrading the entire device, case included, which left a third desktop computer sitting unused). I wanted to test out Plex and Crashplan on that computer just to make sure it worked. It’s an older AMD Athlon II X4 620 that has 24 gb of RAM and room for 8 SATA devices on the motherboard. Once I got everything up and running on this test system, I realized that, given my use scenario, I actually didn’t need the latest Threadripper AMD CPU or even dual Xeon processors. I don’t need concurrent ripping of 4k or 8k video files. I don’t even have any 4k video files (my main TV has 4k capabilities, but I rarely use them). Most of my video is 1080p, which looks great. I tested the system out streaming a video to my TV and backing up files and it worked great. So, I decided to re-purpose this older computer and make it my new NAS.

Once I got everything set up (see below for details), I wanted to see just how much my NAS could handle, especially since the old Synology NAS struggled with streaming just a single HD stream. To run my real world test, I started streaming audio from three Amazon Echo devices in three different rooms in the house via Plex, started streaming a 1080p video file to my phone on the home network, and started streaming a 1080p video file to my main TV. This screenshot shows the Plex server with 5 simultaneous streams:

Simultaneously streaming to five devices on the home network.

The big question was whether my 4 cores could handle this. Here’s how things looked:

All four CPUs were never maxed out simultaneously.

Conclusion: My older AMD Athlon X4 620 was more than up to the task. With two simultaneous 1080p video streams and three mp3 streams, the server was working, but it was far from maxing out. Since we have just one TV in the house, the odds of us ever needing to simultaneously stream more than two videos are almost zero, and that’s true even considering that I am allowing some of my siblings access to my Plex server.

What does this mean for the average person building a DIY NAS? Unless you have, let’s say, 10 4k TVs in your house and you want to simultaneously stream 4k videos to all of them, you probably don’t need the latest and greatest CPU in your NAS. In all likelihood, I’ll let the system I have run for a year or two (unless there is a problem), and then upgrade my box, my power supply, my mother board, and my CPU (which will also require upgrading my RAM). By that time, Ryzen Threadrippers will have dropped in price enough that it won’t cost me $2,000 to build a beast of a NAS that can serve multiple 4K streams at the same time. I’m not sure I’ll ever need that much bandwidth or power given my use scenario, but I can imagine needing a bit more power in the future.

Where you probably do not want to skimp is on RAM. As I have been transferring my files and media to my new NAS from the old one, I have seen my RAM usage go up to as high as about 16 gb at different times. That has been the result of large bandwidth file transfers, Plex indexing the media files (video and audio), and simultaneous streaming of files. In short, you can go with an older, not crazy expensive, multi-core CPU for your NAS and be fine, but make sure you’ve got at least 16 gb of RAM, maybe more.

DIY NAS: Hard Drive Options

Where I got the most bogged down in my research was in deciding on how many hard drives to use, what file system to use, and whether or not I should use a RAID for the drives. In my Synology box, I had just two 4 terabyte hard drives in a RAID 1 (or mirror) arrangement. While I am getting close to filling up the 4 terabytes (I have about 3 terabytes of photos, movies, files, music), I was more concerned with not losing data. With a RAID 1, all of my data was backed up between the two hard drives. Thus, if I lost a drive, I would still have a copy of all of the data.

Additionally, I pay for unlimited storage through Crashplan. Everything on my NAS is backed up off-site. This way, I have a local copy of all of my files (the mirrored drive) and an off-site copy of all my files (in case there is a fire or catastrophic failure). Since I do back up everything off-site, I could theoretically just go for speed with, for instance, a RAID 0 that stripes all my data between drives. But a failure would mean I would lose all my local data and I would have to restore from the off-site backup (which would take quite some time given the amount of data I have).

As I considered the options for hard drives, my initial thought was to use the same system but increase the size of the hard drives. There are now hard drives with capacities in excess of 10 tb. They are expensive, but two 10 tb hard drives in a RAID 1 would basically replicate what I was doing but give me plenty of room to grow my photo, video, and file collection. I was just about to pull the trigger on this plan until I realized that another option might make more sense.

RAID 6 requires 4 hard drives, but offers a number of advantages over RAID 1 (e.g., similar speed but better redundancy, as well as the possibility of hot swapping hard drives). And, if I went with RAID 6, I could actually buy cheaper, 4 or 6 tb hard drives instead of 10 tb hard drives and end up with just as much or more space as I would get with two 10 tb hard drives for less money. From the many articles I read on this, it seems that lots of corporations use a RAID 6 given the redundancy and speed advantages that result. I ultimately decided to go with a RAID 6 with four 4 tb hard drives. Effectively, that meant I would be doubling my storage (8 tb) while improving my redundancy.

I still needed to figure out what file system to use. Having worked with Linux for over a decade, I typically use EXT4 on all of my computers. It doesn’t have any file size limitations, obviously, and does everything I need. However, I had been hearing about ZFS for a while (as well as BTRFS) and what I had heard made me think that ZFS was really what I should be running on my NAS. While it may slow down my NAS a little bit, the benefits to preventing bit rot and the redundancy it includes meant the impact on performance would likely be worth it. However, ZFS doesn’t come as a standard file system option in Linux distributions. I have used various disk partitioning programs enough to know what the standard file systems are that ship with Linux distributions and ZFS is not one of those. I was a little worried about what might be entailed in installing ZFS and setting up ZFS in a RAID6 (in ZFS terms, it’s called RAIDZ2). Once I found this handy guide, I realized that it wasn’t that difficult and was something that I could easily do. Before I headed down this path, I tested the guide with a spare drive I had lying around and it really was simple to set up ZFS as the file system on a drive. That test convinced me that ZFS was going to be the route to go for my file system on my drives. Thus, my four 4 tb RAID6 is actually a RAIDZ2 with a functional capacity of 8 tb of storage, but is actually expandable should I want to do so. (Also useful for ZFS is this post on scheduling regular scrubs on the drives.)

Update: 2019-08-18 – I restarted my file server after installing system updates and my ZFS pool was missing. That was terrifying. I finally found a solution that was a little nerve-wracking but worked. Somehow, the mount point where my ZFS pool was supposed to mount either got corrupted or had something in it (for me, it was /ZFSNAS). I renamed that folder:

mv /ZFSNAS /ZFSNAS-temp

I was then able to import my ZFS pool with the following command:

sudo zpool import ZFSNAS

Apparently, this is the result of an upgrade to the ZFS software. This happened again right after I rebooted. There is a new option in ZFS systems that has to be enabled in order for these to mount after reboots:

systemctl enable zfs-import.target

Now, when I reboot, my ZFS pool comes right back up.

DIY NAS: What software to install?

I have been running Plex for about 6 years to manage my media collection. With my Synology box, the limited RAM and not very good CPU meant that it actually had a pretty hard time managing my media collection. It would transmit videos across my network to my ROKU device, but only if they were in a specific format (mp4, which was probably a limitation of the Synology box as that is not a problem with Plex or ROKU). Setting up a video slide show was basically impossible on my ROKU from my Synology box as the CPU and RAM just couldn’t cut it. While I could play my music across the network, the Synology box would also not play nice with my music in Plex. As a result, I used Plex just to watch videos but not for anything else, even though it is a great way to manage all sorts of media – video, photos, and music. Thus, one of the requirements of my new DIY NAS would be that it has to take advantage of all the features that Plex has to offer (I have a lifetime Plex Pass). So, Plex was the core requirement for my NAS.

But I also wanted my NAS to run Crashplan. As noted above, my Synology box didn’t have enough RAM to run Crashplan, which meant I had to run it on my desktop to backup the files on my NAS to Crashplan. It was a hack to get around the limitations of the Synology NAS (FYI, you need about a gigabyte of RAM for every terabyte of files you want to back up to Crashplan). Plex and Crashplan were minimum software requirements. My DIY NAS had to be able to run both of those and run them well.

I do occasionally download stuff via bitTorrent (mostly Linux distributions). So, having a bitTorrent client installed would be nice. Kubuntu comes with one, kTorrent, which was fine.

The last piece of software I really needed was a way to control my NAS remotely. The goal was to basically have it run headless, stick it in a corner in my office, and just let it do it’s thing. I can control the Plex server through the Plex website, but to do everything else I would need VNC software. I was actually surprised at how difficult it was to find software that would let me control my NAS. I tried Remmina first and had no luck. The interface was clunky, not intuitive, and I only was able to successfully connect about 1 time out of 10. I finally went with nomachine. (I initially went with TeamViewer, but they claimed I was using their software for commercial purposes and cut me off. Fuck them.) Also, in order to make this work reasonably well, you need to install a dummy driver for the X11 driver and create an X configuration file for the dummy driver, as detailed here. Otherwise, the VNC software will be slow as molasses.

I also installed SSH so I could access the NAS remotely in case the GUI VNC programs were having issues. I followed this guide.

DIY NAS: NFS Fileserver

This guide made it very easy.

DIY NAS: Specifications

Here’s the final rundown of what I put together:
OS: Kubuntu 18.04 (long term support)
Hard Drives: ZFS filesystem; four 4 terabyte drives in a RAIDZ2 for 8 tb of storage
CPU: AMD Athlon X4 620
Motherboard:
RAM: 24 GB
Software: Plex, Crashplan, NFS, TeamViewer, kTorrent

UPDATE – 2019-04-24

It’s been almost 4 months running this NAS box. Generally, it’s worked really well. I have, however, run into two problems.

The first one was pretty recent (about 2 weeks ago). I’m not sure what happened, but over the last month, I’m assuming some update to Kubuntu 18.04 led to the screen going black when I would VNC into the box. The server was still working, but I couldn’t get it to display anything via VNC until I restarted the box manually (SSH on the NAS box would have given me another option for that). I’m still not 100% sure what the problem was, but it was somehow related to KDE. I ended up installing Unity/Gnome as the desktop environment and now the problem is gone. That was about a week ago that I solved the problem. The box has been running without a hitch since then.

The only other issue I will note is that there have been two times when I have been streaming shows on my NAS box through ROKU where my box had to actually transcode a 1080p file. With the hardware I have inside it, the 4-core processor was not up to the task. It ended up stopping playback several times and buffering a lot. It can easily stream a 1080p file in almost any format (both Plex and Roku can handle almost every format) and can even manage to stream two of them simultaneously (tested on my TV and phone simultaneously), but the two movies it had issues with were in a weird MKV format that my Roku couldn’t handle. I ended up ripping them into a different MKV format and, voila, problem solved. The point being – my NAS box is a little underpowered. But, a year or two from now, the price of an AMD Threadripper will be half of what it is now. I’ll swap that in and will have all the power I need.

So, 4 months into this, it’s working extremely well. I’d give it a 99 out of 100.

Linux – OCR PDF

One of the few tasks I have not been able to do on Linux since I switched over from Windows more than a decade ago is optical character recognition (OCR) of PDF documents. I work with a lot of PDFs. Most of them were digital documents to begin with and the text is readily selectable. However, the occasional need arises when I either have to scan something myself or I receive a document that does not have selectable text and is just an image. Up until now, I have kept a software package on a Windows virtual machine (in Virtualbox) specifically to OCR PDFs on the rare occasion when I need to do that. But, I think I can safely move past that thanks to recent advances in OCR on Linux.

I scanned a chapter I wrote in a book recently. The scan looked good (especially after I used Scan Tailor’s Dewarping feature to flatten the pages).

I then converted the TIF files from Scan Tailor into PDF files, put them in the correct order, and was ready to OCR them in the software I used in Windows. However, my virtual machine was giving me some issues and required me to install some updates that were going to take a while (’cause, Windows!). I got the updates started, then realized that I hadn’t checked to see if any progress had been made on OCR on Linux for quite a while (probably a couple of years). A quick Google search landed me on Stack Exchange (where I seem to spend a lot of time these days). There, I found two new options for OCR on Linux. 

The first option was a command line program called “ocrmypdf.” That sounds like a dream! I quickly installed it on my Kubuntu machine:

$ sudo apt install ocrmypdf

A number of additional packages were installed as well. Once it was installed, I gave it a whirl. The command is pretty easy. Navigate to the directory where you have your PDF you want to have recognized then type in the following:

$ ocrmypdf input.pdf output.pdf

My initial PDF (on the left below) was 14 mb in size and looked fine, but I couldn’t select the text – it was just an image. With a quick command, I ran it through the “ocrmypdf” program and got out a nearly identical PDF that was smaller (just 9 mb) and allowed me to select the text (image on the right below).

Original PDF on the left; OCR PDF on the right.

In the next image, you can see that I can select the text in the OCRd image:

I can select the text in the OCRd image.

Finally, the real question is, how accurate is the OCR? The image below shows the OCR document next to the text:

PDF on the left; selected text copied and pasted on the right.

As you can see, this isn’t award-winning software. It’s probably 80 to 90% accurate. But that is more than sufficient for what I need most of the time. And, as FOSS, I’ll take it. My rating: 8 out of 10. But given the speed with which I can do this, I’ll absolutely be using this over the old software I was using on a virtual machine before.

On the same page where I found “ocrmypdf,” there was mention of another software package: “ocrfeeder.” Since I had been dreaming of this kind of software for ages, I figured I’d give it a spin as well. I downloaded “ocrfeeder” quickly:

$ sudo apt install ocrfeeder

I then tried to pull up the GUI… And… Nothing. Perhaps I needed to reboot. Not sure. But “ocrfeeder” didn’t seem to be working on my install (Kubuntu 18.10) when I tried to run it.

LibreOffice – Impress Templates and Object Styles

I just upgraded one of my computers to Kubuntu 18.10. With that upgrade came the latest version of LibreOffice (6.1.2.1).


With this new version, when I try to create a new Impress document, I’m now forced to pick a template. The options aren’t terrible, but I am of the opinion that presentations should focus on the content, not the pretty colors, shapes, or designs around the content.

I wanted a plain as can be, black and white template. There wasn’t one to be found. So, I created one.

I’m not going to go into the process of how to create a template in this post, but I do want to note one thing that I learned while creating my template. There is a quick way to change the characteristics of drawing objects in LibreOffice as well. I have always hated the default color when I create a box or circle in Impress (usually it’s some ugly blue color). It is possible to change the defaults for those, but doing so also seems to affect the defaults for outline boxes (not sure why). But it’s also possible to simply create a new style for objects and then apply that style once you’ve created your object, just like can be done with text. Here’s how you do that.

First, make sure you have your right side panel open in Impress and click on the Styles tab.

I initially tried changing the Default option in the list of styles, but that ended up changing the characteristics of the outline boxes, which I didn’t want to do. So, instead, what you need to do is create a new style (or modify one of the existing ones). You can create a new style by right-clicking on one of the existing styles and selecting “New” (my mouse was on “Modify” in the screenshot below). 

In the window that pops up, you can change the fill color under the “Area” tab and the line color under the “Line” tab. 

Since I like super plain, I changed mine to a grey fill with a black outline. Now, when I create a new drawing object, I can simply select it and double-click the style and it is how I like it:

I also saved this as part of my template. So, now, when I create a new Impress presentation, I load my template, and all of the settings I like are already in place.

(NOTE: How to save this as a template. Click on “File -> Templates -> Save as Template.” You’ll get a new window. Name your template and pick your folder. It will then be saved as a template for you that you can use when you create a new Impress file.)

LibreOffice – animating elements or series in a chart/graph in Impress

I really like LibreOffice. It’s not just my default office suite because it’s free. I like it because of the control it gives over so many elements of what I do in my day-to-day work. However, this post is one of those where I lament the fact that it is missing a feature that other similar software has had for over a decade: the ability to animate a series or element in a chart or graph. There is a way to do this, but it is extremely clunky and should really be a feature that is added to the software.

Assuming you want to use LibreOffice to animate a series in a graph or chart, here’s the process.

First, create your graph. I created mine in LibreOffice Calc.

Now, copy and paste that into a slide in Impress. 

Check to be sure the chart looks exactly how you want it to look, because this next step is going to make it so any future changes to your chart will be really time consuming. What you’re going to do is right-click on the chart and select “Break”.

What the “Break” command does is separate every element of your chart into individual pieces. You can test this once it’s done by clicking on any one piece of the chart and you’ll see that it doesn’t select the chart anymore. It select just the single piece of the chart, as shown in this next image.

So, after “breaking” your chart, you have all the same elements, but they are all broken into individual pieces. What that means is you can now animate every single piece of your chart.

Of course, if your goal is like mine and you want to show change over time by animating a series from left to right, there are a couple more steps. 

To animate an entire series, you have to select the whole series. You really need to do this by dragging a selection square around the relevant elements, since there are is an outline of each marker in an element, the fill component, and then the line. Selecting each component individually is basically impossible. So, drag a square around the elements you want to link together like this:

Then right-click on them and select “Group”. Once you’ve got the elements grouped together, you can then “Animate” the group using a Wipe animation from left to right.

Here was my result once you play the slideshow:

LibreOffice programmers, if you’re listening out there. I love the software. But this is a feature that should be included and not require this length of work. Please add it.

Linux – tinyMediaManager on Kubuntu 18.04

I run a network attached storage (NAS) device at home to manage all my media (e.g., music, videos, photos, etc.). I have used various programs over the years to manage the naming and organizing of my music files but just recently discovered tinyMediaManager for managing video files. Since it’s written in Java, it works on any OS, including Linux.

Given my large collection of movies, I have been looking for software that would properly name and organize all of them. tinyMediaManager seemed like the perfect solution, but I immediately hit a snag once I tried to get it running on Kubuntu 18.04 (my current distribution of choice). I couldn’t get the GUI to launch. It took some doing, but I eventually figured out how to make this work on Kubuntu 18.04.

First, download the tar.gz file here. (Note: I couldn’t download it using Chrome, as the tinyMediaManager site only lets you download it using a browser that allows Java and, as of Chrome 45, Chrome doesn’t. I used Firefox, which worked fine.).

Untar that file and move the resulting folder wherever you want it to reside (obviously, somewhere you have access to it, but, otherwise, it doesn’t matter).

Now, the tricky part. According to the tinyMediaManager website, all you need to do to launch the program is use a terminal to navigate to the folder you just untarred and use the command:

cd /home/ryan/tinyMediaManager
./tinyMediaManager.sh

When I tried this, it didn’t work. It seemed like it was trying to do something, but then the GUI wouldn’t open and… nothing. Disappointed, I started looking for answers. I eventually found the “launcher.log” file in the tinyMediaManager folder and that gave me the clue I needed to solve the problem. As it was trying to launch, it was running into a problem with a specific thread and library in the version of Java I had installed by default. Here was the error:

Exception in thread "Getdown" java.awt.AWTError: Assistive Technology not found:

It turns out, tinyMediaManager has not been updated to work with newer versions of Java. So, here’s what you can do.

First, install the Open Java Development Kit version 8 which is the latest version it works with:

sudo apt-get install openjdk-8-jdk

It turns out, you can have multiple openjdk’s installed at the same time. I was running as my default openjdk 11. Now, in order to switch to the openjdk 8 environment, type in the following command at the terminal:

sudo update-alternatives --config java

You’ll then be given the chance to choose which openjdk you want to use, like in this screenshot:

Choose openjdk 8 as your default. Then try running tinyMediaManager again. If the software gods are smiling upon you, the GUI will launch:

LibreOffice Calc – Comparing and Aligning Two Lists Using VLOOKUP

One of the tasks I have to do regularly as part of my job is to compare two lists to see which items are missing on one list but not the other. I have been doing this by hand but figured there had to be a way to do this in Excel. I finally figured it out but don’t want to forget how to do it. So, I’m documenting it here so I can draw on this whenever I need to do it again.

Here’s the scenario. I have a list of “items” but every month or so I receive an updated list of “items” from someone else. During that month, some of the items on my list have been taken off and some have been added to it. Likewise, the same has happened to the other list that this other person sends me (I’m being really vague here because I work at a university and there are laws that govern academic records).

My list and the other list have to be kept synchronized but we have two separate databases that are used for this because… (argh, yeah). Anyway, what that means is that I have to compare the two lists and quickly find the items on one list but not the other and vice versa. Here’s how to do it quickly in LibreOffice Calc.

Here are two lists:

(NOTE: You can actually do this in two separate Calc sheets or within the same one.)

LIST 1 is in Column A and List 2 is in Column C.

In Column B I am going to create a function that allows me to search all of Column C to see which of the items in LIST 1 show up in LIST 2. Click in Row 2 of Column B then click on the Function Wizard (or start typing):

In the Function Wizard dialogue box, you’ll see this:

Search for VLOOKUP then double-click it and you’ll see this:

You now need to build the function. This is where I got confused, so I’m going to try to explain this carefully.

The first box is the “Search criterion.” Basically, this is what you want to find. In our example, let’s say that we want to see which items in List 1 are in List 2 (Hint: It’s apples and grapes.). So, we are going to put in the Search criterion that we want the software to search the items in column A or LIST 1. We do this by simply selecting cell A2 (you can do this by typing it in or by selecting it by clicking the button to the right of the box and then selecting A2 like I did below):

Next is the “Array” box. This is the content through which you want the software to search to find the items in LIST 1. Again, you can type this in or select it using the button to the right. However, there is an important change that you need to make here. If you select the array with your mouse and leave it as is, the values will change as you drag this formula down in Column B as it will assume you want to adjust the array as well. Since we don’t want to adjust the array but rather want to search through the same items in LIST 2, we need to put dollar signs before the letters and numbers in the Array which will lock the boundaries of the array into place so they don’t change when we copy the formula to other cells, as shown below:

The next part is the part that threw me off for a long time in figuring this out. The “Index” is the column in the Array you want to compare to the Search Criterion. In this case, all you need to do is specify “1” since there is only one column. But, presumably, you could have an array made up of multiple columns and want to choose just the 4th column (so you would enter 4 in the Index). Here’s how this looks:

Finally, the last box that is part of our function is the “Sort order” box. This tells the software whether the lists are ordered alphabetically or not. If they are not, enter zero (“0”). If they are, enter “1.” If you leave this blank, LibreOffice Calc thinks the lists are ordered. So, make sure you fill this out.

Once you’ve got that all entered, your formula should look like what I have above. Hit “OK” and it will search the Array C2:C7 for “bananas.” If it finds it, it will return “bananas.” If it doesn’t, it will return “#N/A” as shown below:

You can then, of course, drag this formula down. When it finds the item from LIST 1 in LIST 2 it returns that item. When it doesn’t, you get #N/A.

And there you have it. A LibreOffice Calc function for searching a list for a target and returning an indicator.

(NOTE: What I typically do after I have run this function is sort by the column where the function is located so I know which items are missing from LIST 1 and which are missing from LIST 2.)

Linux – Adding and Organizing Music with Clementine

In the almost 15 years I’ve been using Linux I have gone through a number of music playing apps. From Amarok to Banshee to Rhythmbox and at least a few more. My favorite at the moment is Clementine. I’ve grown to really like the basic interface of Clementine and it does a good job with pretty much everything I need.

The one issue I had with it over the years is that I didn’t think I could use it to add music to my music library and organize it at the same time. Turns out, I was wrong. Clementine does have the ability to add songs to your music library and organize them (into folders and renaming the files). It’s just not the most intuitive process. So, I figured I’d outline the process below.

First, you obviously need to install Clementine.

You’ll then need to set your music directory in Clementine which is where you want to store your music files. Go to Tools -> Preferences.

In the window that opens, click on “Music Library” and then add the folder where you want to store your music:

Next, to add music to your folder, go to the left side of Clementine and look for where it says “Files”:

Click on that and you’ll be able to browse to any folder on your computer.

I usually put the music I want to add to my collection on my desktop, so I navigate there, find the folder, and then right-click on it. In the context menu that comes up, click on “Copy to library.”

In the window that comes up next, you’ll have the option to change how the files are organized, both at the folder level and with the renaming of files.

You also have the option of changing the destination (which doesn’t make much sense since you want the music in your primary music folder). You also have a bunch of other options, like deleting the files after they are copied, changing the naming conventions, etc. Once you’re ready, hit “OK” and your music files will be copied into your music library named exactly how you want them to be named and organized how you want them to be organized.

Linux – Fixing the Epson (V33 Perfection) Scanner Issue in Kubuntu 18.04

As of Kubuntu 17.10, my Epson V33 Perfection scanner stopped working with Linux. I bought this scanner specifically because it did work with Linux. That it stopped working was a serious disappointment as I have been using the same scanner for years and regularly need it for a variety of things.

I tried installing older versions of the software (I’ve kept the downloads from Epson for years) but that didn’t work. I tried it on a different computer with the same OS and that didn’t work. I tried a lot of troubleshooting and came up empty. After hours of trying various solutions, I gave up (that was a few months ago).

However, when my CPU died on my desktop computer forcing me to reformat it again about a month after I had previously tried, I figured it was time to try to solve this again. This time, however, I quickly came across the answer online (see here). It turns out, this is a bug in the latest version of the scanning software that ships with Linux, which confirmed that it wasn’t a problem with my scanner or with Epson’s software. Here’s what I did to fix the problem.

First, go ahead and install the latest version of Epson’s software from their website (start here – or just here – lots of clicks to get to where you need to go).

Untar the file you download and read the directions for how to install the software. (Basically, navigate to the directory you just untarred in a terminal then run ./install.sh).

Before this bug, that was all you needed to do and the scanner would work. Now, there are two more steps.

The second step, also at the terminal, is to type in:

sudo ln -sfr /usr/lib/sane/libsane-epkowa* /usr/lib/x86_64-linux-gnu/sane

I’m not exactly sure what this command does but the “ln” command creates symbolic links, so I’m guessing it’s linking two files or commands that help correct the bug in the latest iteration of the software.

Then, open a text editing program and create a new, blank file. In that file, you’re going to add the following content:

# chmod device EPSON group
ATTRS{manufacturer}=="EPSON", DRIVERS=="usb", SUBSYSTEMS=="usb", ATTRS{idVendor}=="04b8", ATTRS{idProduct}=="*", MODE="0777"

The first line of this file is just a comment. The second adds information about Epson scanners.

Save the file as “79-udev-epson.rules”.

Now, move that file from wherever you created it to the following directory:

“/etc/udev/rules.d/”

Like this:

Now, restart your computer. This worked for me. Hopefully, it will work for you too.

 

Linux: How to Install Zotero Standalone (in KDE or Kubuntu)

I’ve used Zotero to manage my collection of books, articles, and other citations since Zotero was released. The software has become much more robust over time. As of the latest major milestone release (5.0), Zotero has moved to standalone software exclusively (they no longer piggyback on Firefox). With Mac and Windows, you can download executable files to install Zotero. With Linux, it’s a little trickier. Here’s my method for installing Zotero so I can launch it from the KDE application menu.

First, download the tar.gz file from Zotero.

Second, untar (i.e., unzip) the folder inside the tar.gz compressed file.

Move the resulting folder (Zotero_linux-x86_64) to a directory where you’ll want to run Zotero from. I use my /home/[user]/ folder but it can be any folder with read/write/execute access.

The Zotero website says that you should then run the bundled “set_launcher_icon.” I usually do that, but I still need to add Zotero to my application launcher. To do this, right-click on the KDE application launcher menu like this:

Select “Edit Applications.”

In the window that pops up, decide where you want to put Zotero (I went with Education) then select “New Item.” You’ll get this window:

Fill it in with the following information:

Name: Zotero
Description: (Whatever you want.)
Comment: (Again, whatever you want.)
Command: /home/[user]/Zotero_linux-x86_64/zotero

You can also add an icon for Zotero (I always do), like this one.

Obviously, replace [user] with your username. The line that really matters is the Command line as that is where the OS will know to look for the Zotero launcher. Make sure it is pointing to the file names “zotero” in the application folder (not “zotero.desktop” as that won’t launch the application).

Once you’ve done that, you should be able to launch Zotero for the quick launcher or from the application launcher:

 

 

Virtual Private Hosting – How to Remove a Domain with Certbot SSL Certificate (on Ubuntu 16.04 with apache)

It used to be pretty easy to remove a domain from my Virtual Private Server – dissable the site in apache, delete the files, delete the underlying database, and remove the domain from my DNS manager. Done.

With SSL certificates now a standard part of hosting websites (see here), this is complicated by the removal of those certificates. I am extremely grateful to the EFF for providing a free way to get SSL certificates through certbot and letsencrypt. Adding domains with certbot is pretty easy, but changing the domains with certificates is not so easy. In fact, it’s pretty complicated and there aren’t great directions out there (which is why I put this guide together).

In this guide, I’ll show you how to remove a domain from a virtual private server (I use linode.com) along with removing the SSL certificate. I was hosting a domain and website for a friend, but that person decided they no longer needed the domain. Here’s how I removed it.

First, it’s always a good idea to back up the files associated with the domain, just in case. I use phpMyAdmin to manage my SQL databases. Log in to your phpMyAdmin site, find the database associated with your site, and select Export.

You shouldn’t need to change any options, just select “Go” and phpMyAdmin will export the entire database.

To download all the files associated with your site, you can zip them from an SSH terminal or download them via FTP. I use Filezilla. Find the folder that contains all the files for your website and download the whole thing.

Now that you have a backup of everything, it’s time to start unmounting.

First, you should dismount your site in apache.

sudo a2dissite [domain]

You also need to dismount the site with encryption, which is the same command, but with the following addition:

sudo a2dissite [domain]-le-ssl.conf

Then reload your apache2 configuration:

service apache2 reload.

If you’ve done everything correctly, when apache reloads, there won’t be any errors.

Next, delete the certificate associated with your domain in certbot. To delete the certificate associated with a specific domain, the command is:

certbot delete –cert-name [enter domain here]

That will delete the certificate associated with that domain.

You may also need to update your certificate profile by using the command:

certbot –apache

This will list all of the domains you have certificates for. You can then select all of the other domains on your server but drop the one you want to delete. That will create a new certificate with all of the domains minus the one you have deleted.

If you are wondering whether certbot has actually removed your domain, you can check by going to /etc/letsencrypt. You want to make sure that your domain is no longer showing up in either the /live,  /renewal, or /archive folders. If you still see it in there, it should be safe at this point to delete any folders with the name of the domain in it.

Next, you can delete the corresponding database. In phpMyAdmin, click on “Server: localhost” then click on “Databases.” Select the box next to the database you want to delete, then, at the bottom, click “Drop”. You’ll get a warning about destroying a database. Select OK and the database is gone.

We’re almost done. Now, delete the files for your site. You can do this with the rm command from the terminal or using your FTP client.

Now, you should delete the apache site files that are located in /etc/apache2/sites-available. These are the two that were dismounted earlier: [DOMAIN].conf and [DOMAIN]-le-ssl.conf.

You can restart the apache service one more time to make sure everything is working, but you should be good.

Finally, you can delete the domain from your DNS manager.

That should do it. The domain should now be gone, entirely, from your server.