Check if internet connection is available in bash script

This bash function uses netcat to test for internet connection (by pinging google.com) so it should work on most Linux computers. We use it on our Raspberry Pies because some times if the internet is disconnected, it won’t establish a new connection once the network is back up. To bypass the problem we trigger a reboot which re-initialises network connection.

#!/bin/bash

# Description:  Reboot machine if internet is down

function check_internet_connection() {
    echo -e "GET http://google.com HTTP/1.0\n\n" | nc google.com 80 > /dev/null 2>&1
    if [ $? -eq 0 ]; then
        echo "Online"
    else
        echo "Offline"
        sudo reboot
    fi
}

check_internet_connection

Getting reboot history with correct time on Raspberry Pi

If you run the last reboot command to list reboot history, you might’ve noticed that the dates are wrong. That’s because the Raspberry Pi doesn’t have a RTC (Real Time Clock). But, the last command actually saves two dates (login and logout). The login date will be wrong because at this point of the boot-sequence the Raspberry hasn’t synchronized it’s clock with NTP (Network Time Protocol) yet, but the logout date will actually be stored correctly.

So, instead of doing last reboot, you have to run last reboot -F to view both dates in full format.

Example output:

last reboot
last reboot -F

CRON script to auto-renew SSL certificate and restart apache webserver using Certbot

Setting up a SSL certificate on a brand new server is so easy thanks to Certbot. But remember that every LetsEncrypt certificate expires after 3 months, so you better remember to renew it. Also, just renewing the certificate isn’t enough. Your apache webserver doesn’t know that it has been renewed, so you have to restart the webserver as well. Thankfully, certbot has a hook if the certificate has been renewed where we can add extra commands, such as restarting apache.

I like to put this in my crontab so I never have to think about it:

* 04 * * * sudo certbot renew --renew-hook "service apache2 restart" >> /home/user/logs/certbot.txt

Replace service apache2 restart with systemctl restart apache2 if you prefer to use systemctl syntax.

This will check the certificate every night at 0400 and renew it if necessary. If the certificate has been renewed, it will also restart apache.

Find unused MySQL tables

MyISAM

If using MyISAM you can use the following query to list all tables which has not been modified in the last 10 days (or not modified at all):

SELECT table_schema, table_name, create_time, update_time 
FROM information_schema.tables 
WHERE table_schema NOT IN ('information_schema', 'mysql', 'sys', 'performance_schema') 
AND engine IS NOT NULL 
AND ((update_time < (now() - INTERVAL 10 DAY)) OR update_time IS NULL);

InnoDB

This is not supported in InnoDB so we have to check the actual database files on the server when they were last modified. The DB files are located in /var/lib/mysql/<dbname> , but you will have to enter root prompt in order to access this directory.

Run this command to see when the DB tables were last modified:

ls -l --time-style="+%Y-%m-%d" | awk '{print $6,$7}' | grep ibd

Let’s break down the command a bit. We simply use the regular ls to list all files in the directory, then we change the date format to YYYY-MM-DD, then we print only filename+date using awk, then finally we grep (filter) by ibd (because all tables have two files, one .ibd file and one .frm file. – However, only the .ibd file gives the correct modified date).

Example output:

Custom sorting algorithm for array using PHP spaceship operator

The new spaceship operator introduced in PHP 7 can be used to compare two expressions. Here’s a little example showing a custom sorting function using usort which first orders the given array by name, then by category, alphabetically.

usort($mylist, function($a, $b) {
    if($a->category == $b->category) {
        return $a->name <=> $b->name;
    }
    return $a->category <=> $b->category;
});

How to see livestream video for your unbranded chinese CCTV/IP camera

Many unbranded cameras from AliExpress etc comes with bad or close to no instructions so you’re sometimes left to yourself to figure it out. As I just went through this painful process myself, I’ve written down some notes till next time. Scroll down if you wish to see the easy solution, or continue reading for a little more in-depth process.

Livestream CCTV/IP camera with VLC using RTSP

Luckily most of the unbranded IP cameras use standard protocols so we can stream the picture directly in VLC by using rtsp (Real Time Streaming Protocol).

First step would be to get the cameras IP address. Login to your router and find the device in your DHCP list. If it’s configured to use a static address, check the instructions or use an IP scanner utility to find it.

Once you have the IP address, verify and try to login the usual way at http://xxx.xxx.xxx.xxx (i.e. http://192.168.1.88). The camera will typically have a web server running giving you some administration/settings. Sometimes you must also enable media streaming / RTSP.

Anyway, I’m gonna pretend everything works out of the box. You have the IP address. Now you can open it in VLC (keyboard shortcut CTRL+N to open a Network media stream).

Enter the URL and click Play. If necessary, a password prompt will ask for admin/password combination. Default login tends to be admin/admin or admin/123456 etc.

If you’re not sure what the URL should be, try one of the following. One of them will hopefully work:

rtsp://<ipaddress>:554
rtsp://<ipaddress>:554/mpeg4
rtsp://<ipaddress>:554/1/h264major
rtsp://<ipaddress>:554/user=<username>&password=<password>&channel=1&stream=0.sdp?real_stream--rtp-caching=100
http://<ipaddress>/monitor.cgi?Channel=1&Audio=0000&Live=1

Obviously replace <ipaddress> with the cameras IP address, and <username>/<password> with… yes, the username/password. You could also try switching the channel=1 to 2.

Hopefully one of the URLs will work, and voila! You can stream the camera!!

Let’s continue on with the easy solution:

Find IP address and stream directly using ONVIF Device Manager

ONVIF Device Manager is a great open source project which offers plenty of features for managing your IP cameras/CCTVs including network discovery, live stream and PTZ controls. It’s pretty much plug n play, just install the software and let it find your cameras automatically.

Left column shows all available cameras found on the network. Middle column shows various settings. Right column shows preview/live stream.

Zoom stuck at 70% in MS Word 365

So this little ridiculous issue has been haunting me for two days until I finally got a moment to troubleshoot it.

My Word page zoom has been stuck at 70%-something the last few days, even though I had plenty of white space (or gray space) on both sides of the document.

Turns out the page view layout was setup to use “Page-to-page” instead of “Vertical”.

No matter how hard I try to zoom out, nothing happens.

Change layout to “Vertical” and the issue is resolved!

Finally… I can read the text again.

New PC stuck in Windows 10 S mode? Here’s how to unlock it

So a colleague just bought a new PC and it came pre-installed with “Windows 10 in S mode”. This is a locked down version of Windows where you can only install apps from the app store. Regular exe files etc are blocked.

Luckily you can disable the locked down mode rather easily. However, as of today, there’s no way to reactivate it again, so keep that in mind. Your 85 y/o grandma might be better off having it enabled as it’s a good way to block potential viruses.

The annoying thing is that in order to disable S mode, you actually have to “download” or run an app from the app store, and this requires a Microsoft account. So, for those of us who likes to use a local user account instead of logging on with a Microsoft account, we still have to login in order to run the app from the Microsoft Store.

Steps for disabling Windows 10 S mode:

1: Open Settings

2: Go to Updates & Security

3: Open the Activation tab.

You will see that your current Windows edition is set to “Windows 10 X in S mode” (where X is probably Home or Pro).

4. Click on the Go to the Store button. This will open the necessary “app” you need to run in order to unlock the regular Windows mode.

5. Run the app. Login with your Microsoft account when prompted (or create a throwaway account). Your PC is now unlocked.

6. Alternative step: Install Ubuntu

Creating an email censoring filter in Vuejs

This is a neat little function you can use as a filter in Vuejs to partially censor emails so for instance myemail@example.com will show as m****l@e*********m. In other words, show the first and last letter before and after the @ delimiter.

filters: {
    censorEmail(val) {
        try {
            let arr = val.split("@");
            let wrd = arr[0][0] + "*".repeat(arr[0].length-2) + arr[0].slice(-1);
            let wrd2 = arr[1][0] + "*".repeat(arr[1].length-2) + arr[1].slice(-1);
            return wrd + "@" + wrd2;   
        } catch (error) {
            return "Not a valid email";
        }
    }
}

There’s not really any validation for a proper email address happening, we just check if the input value contains a @. If not, it simply returns “Not a valid email”.