Internet connection utility for Raspberry Pi (Or my first Python script)

I have a Raspberry Pi server, only a Pi 3, but it copes very well with what it has to do.

  • It’s my main webserver however, for various reasons, it handles all internet traffic via a 2.4Ghz Wi-Fi connection so very suprising that it copes.
  • It’s located in the loft space as it runs several flight tracking applications and needs an external aerial as high as possible.

Recently, either down to a router firmware update or an OS update, it’s become unstable and doesn’t always reconnect whent the Wi-Fi goes down – I’ve still not pinned this down exactly but Wi-Fi connection is the probable cause as another Raspberry Pi connected via ethernet handles this situation perfectly. Anyway, digressing, so I needed to create a job that would check the internet connection is present and if not reboot the Pi and hope that solves the issue, so a really simplistic fix as this doesn’t take into account issues with the router/network/ISP and just assumes it’s the local Wi-Fi connection that’s gone. This post is how I went about creating the job and then tweaking it, and using GPT to verify.

So the first draft of the code is below.

import os
import time
import subprocess

def check_wifi_connection(host="8.8.8.8"):
    try:
        response = subprocess.check_output(["ping", "-c", "1", host])
        return True
    except subprocess.CalledProcessError:
        return False

def reboot_raspberry_pi():
    print("Wi-Fi connection lost. Rebooting Raspberry Pi...")
    time.sleep(5)  # Delay before rebooting to allow any running processes to finish
    os.system("sudo reboot")

def main():
    print("Wi-Fi monitoring script started.")
    while True:
    if not check_wifi_connection():
        reboot_raspberry_pi()
        time.sleep(10)  # Adjust the interval as per your requirements

if __name__ == "__main__":
    main()

While generating the second draft I found out that Python doesn’t like the mixing of tabs and spaces, it generates runtime errors. Takes me back to the 80’s! Had to make changes to Notepad++ and the Tab Settings for python so that the Use default is unchecked, Tab size is set to 4 and Replace by space is ticked (checked).

So the working implementations now follow. First one is the most basic just check the connection and reboot the Pi if there isn’t one.

import os
import time
import subprocess

def check_wifi_connection(host="8.8.8.8"):
    try:
        response = subprocess.check_output(["ping", "-c", "1", host])
        return True
    except subprocess.CalledProcessError:
        return False

def reboot_raspberry_pi():
    print("Wi-Fi connection lost. Rebooting Raspberry Pi...")
    os.system("sudo reboot")

def main():
    print("Wi-Fi monitoring script started.")
    if not check_wifi_connection():
        reboot_raspberry_pi()
    print("Wi-Fi monitoring script finished.")

if __name__ == "__main__":
    main()

The above Python script, wifi_monitor.py, is run by a cron job every three hours:

33 */3 * * * python3 /home/pi/wifi_monitor.py

Now we’re going to add an email notification to the script, this will take a few iterations to get right. The first script is the example Python template to send an email, generated using Chat GPT (Interesting that it uses gmail):

import smtplib
from email.mime.text import MIMEText
from email.mime.multipart import MIMEMultipart

# Replace these placeholders with your own information
sender_email = 'your_email@gmail.com'
receiver_email = 'receiver_email@example.com'
smtp_server = 'smtp.example.com'
smtp_port = 587  # Default SMTP port for TLS
smtp_username = 'your_email@gmail.com'
smtp_password = 'your_email_password'
subject = 'Sample Email Subject'
body = 'This is the body of the email.'

# Create a MIMEText object for the email body
email_body = MIMEText(body, 'plain')

# Create a MIMEMultipart object and attach the body
msg = MIMEMultipart()
msg.attach(email_body)
msg['From'] = sender_email
msg['To'] = receiver_email
msg['Subject'] = subject

try:
    # Connect to the SMTP server
    server = smtplib.SMTP(smtp_server, smtp_port)

    # Start TLS encryption (optional, but recommended)
    server.starttls()

    # Login to your email account
    server.login(smtp_username, smtp_password)

    # Send the email
    server.sendmail(sender_email, receiver_email, msg.as_string())

    # Close the SMTP server connection
    server.quit()

    print('Email sent successfully!')
except Exception as e:
    print(f'Error: {str(e)}')

But before I can implement it there is already a problem! How do you send an email to say the Internet connection has been lost when you have no Internet?! My stop gap solution is to create a file just before the reboot of the Raspberry Pi, when the loss of Internet is detected. Then on system startup, or after three hours as per the cron settings above, the Python script runs, checks for the presence of the file and deletes it, then sends an email saying a reboot has occurred because the Internet connection was lost, or it reboots the Pi again…

So my current monitoring script now looks like this:

import os
import os
import time
import subprocess
import smtplib
from email.mime.text import MIMEText
from email.mime.multipart import MIMEMultipart

#Internet down marker
no_internet_file = "/tmp/no_internet.txt"

def is_internet_connected(host="8.8.8.8"):
    try:
        response = subprocess.check_output(["ping", "-c", "4", host])
        return True
    except subprocess.CalledProcessError:
    # Create a file to indicate no internet connection
        with open(no_internet_file, 'w') as file:
            file.write('No Internet connection detected.')
        return False

def reboot_raspberry_pi():
    print("Internet connection lost. Rebooting Raspberry Pi...")
    os.system("sudo reboot")

# Function to send an email
def send_email(subject, body):
    sender_email = 'RaspberryPi@example.com'  # Replace with your email
    receiver_email = 'RaspberryPi@example.com'  # Replace with the recipient's email
    smtp_server = 'smtp.example.com'  # Replace with your SMTP server
    smtp_port = 587  # Default SMTP port for TLS
    smtp_username = 'your_email@gmail.com'  # Replace with your email
    smtp_password = 'your_email_password'  # Replace with your email password

    # Create a MIMEText object for the email body
    email_body = MIMEText(body, 'plain')

    # Create a MIMEMultipart object and attach the body
    msg = MIMEMultipart()
    msg.attach(email_body)
    msg['From'] = sender_email
    msg['To'] = receiver_email
    msg['Subject'] = subject

    try:
        # Connect to the SMTP server
        server = smtplib.SMTP(smtp_server, smtp_port)

        # Start TLS encryption (optional, but recommended)
        server.starttls()

        # Login to your email account
        server.login(smtp_username, smtp_password)

        # Send the email
        server.sendmail(sender_email, receiver_email, msg.as_string())

        # Close the SMTP server connection
        server.quit()

        print('Email "', subject, '" sent successfully!', sep='')
    except Exception as e:
        print(f'Error: {str(e)}')


def main():
    print("Internet monitoring script started.")

# On startup, check if the file exists
    if os.path.exists(no_internet_file):
        # Send an email notification
        subject = 'Loft Raspberry Pi rebooted due to No Internet Connection'
        body = 'The Raspberry Pi, in the loft, has been rebooted due to a lack of Internet connection.'
        send_email(subject, body)

        # Delete the file
        try:
            os.remove(no_internet_file)
        except FileNotFoundError:
            pass
    
    if not is_internet_connected():
        reboot_raspberry_pi()

    print("Internet monitoring script finished.")

if __name__ == "__main__":
    main()

So the final leg now. As mentioned this Raspberry Pi also runs several flight tracking programs. One of them, Dump1090, has been failing recently but not on this Pi, on another Pi. My suspicion is that the USB antenna has a dodgy connection as restarting the Dump1090 process doesn’t resolve the issue but a full reboot of the Pi does. So now I want to expand the Internet checking process to also cover checking Dump1090. Simples. Off we go…

import os
import time
import subprocess
import smtplib
from email.mime.text import MIMEText
from email.mime.multipart import MIMEMultipart

# Define the RaspberryPi location
raspberry_bush = "Window"

# Internet down marker
no_internet_file = "/tmp/no_internet.txt"

# Define the command to check the Dump1090 status
status_command = "sudo systemctl status dump1090-fa"

def is_internet_connected(host="8.8.8.8"):
    try:
        response = subprocess.check_output(["ping", "-c", "4", host])
        return True
    except subprocess.CalledProcessError:
    # Create a file to indicate no internet connection
        with open(no_internet_file, 'w') as file:
            file.write('No Internet connection detected.')
        return False

# Define a function to check the Dump1090 status
def check_dump_status():
    try:
        # Run the status command and capture its output
        status_output = subprocess.check_output(status_command, shell=True, text=True)
        
        # Check if the output contains "FAILURE" to determine if data is NOT being sent
        if "FAILURE" in status_output:
            return False
        else:
            return True
    except subprocess.CalledProcessError:
        return False

def reboot_raspberry_pi():
    print("Rebooting Raspberry Pi...")
    os.system("sudo reboot")

# Function to send an email
def send_email(subject, body):
    sender_email = 'RaspberryPi@example.com'  # Replace with your email
    receiver_email = 'RaspberryPi@example.com'  # Replace with the recipient's email
    smtp_server = 'smtp.example.com'  # Replace with your SMTP server
    smtp_port = 587  # Default SMTP port for TLS
    smtp_username = 'your_email@gmail.com'  # Replace with your email
    smtp_password = 'your_email_password'  # Replace with your email password

    # Create a MIMEText object for the email body
    email_body = MIMEText(body, 'plain')

    # Create a MIMEMultipart object and attach the body
    msg = MIMEMultipart()
    msg.attach(email_body)
    msg['From'] = sender_email
    msg['To'] = receiver_email
    msg['Subject'] = subject

    try:
        # Connect to the SMTP server
        server = smtplib.SMTP(smtp_server, smtp_port)

        # Start TLS encryption (optional, but recommended)
        server.starttls()

        # Login to your email account
        server.login(smtp_username, smtp_password)

        # Send the email
        server.sendmail(sender_email, receiver_email, msg.as_string())

        # Close the SMTP server connection
        server.quit()

        print('Email "', subject, '" sent successfully!', sep='')
    except Exception as e:
        print(f'Error: {str(e)}')


def main():
    print("Dump1090 and Internet monitoring script started.")

# On startup, check if the no Internet file exists
    if os.path.exists(no_internet_file):
        # Send an email notification
        print("Internet down file found")
        subject = raspberry_bush + ' Raspberry Pi rebooted due to No Internet Connection'
        body = 'The Raspberry Pi, on the " + raspberry_bush + ", has been rebooted due to a lack of Internet connectivity.'
        send_email(subject, body)

        # Delete the file
        try:
            os.remove(no_internet_file)
        except FileNotFoundError:
            print("File deletion failed")
            pass
    
    if not is_internet_connected():
        reboot_raspberry_pi()

    if not check_dump_status():
        subject = raspberry_bush + ' Raspberry Pi rebooted as Dump1090 not connected.'
        dump_status = subprocess.check_output(status_command, shell=True, text=True)
        body = str(dump_status)
        send_email(subject, body)
        reboot_raspberry_pi()

    print("Dump1090 and Internet monitoring script finished.")

if __name__ == "__main__":
    main()

I highlighted the latest changes for the Dump1090 checks. Hopefully it’s all clear, any questions please ask!

4-Jan-2024

Remember to add the file and command to the right cron entry though. For example sudo crontab -e doesn’t edit the same file as crontab -e, the latter edits settings for the current user the former, I believe, for root; then you can also edit /etc/crontab, which is for the system.

Remote Desktop: Map a local drive on the remote host

If you repeatedly use a Remote Desktop session and that session needs access to files on the machine you’re connecting from then you’d normally set up a network drive, possibly mapped as follows:

net use z: \\yourmachine\c$ /persistent:Yes

But, depending on how Draconian your network security is then mapping folders on user machines may actually be blocked. Strange one as this ability is mostly essential when working on a remote machine, especially if a developer. Fortunately you can map using the built in sharing of RDP. Same format as earlier just the machine name changes and effectively becomes a constant, so the mapping is now:

net use z: \\tsclient\c /persistent:Yes

And job done, until the next group policy is introduced blocking that…

 

Search an MS-SQL database for a specific named column

Need to find a column in a database? The following SQL will return the tables, and columns, that contain a wildcard value

SELECT c.name AS ColumnName,
       t.name AS TableName
FROM sys.columns c
JOIN sys.tables t
 ON c.object_id = t.object_id
WHERE c.name LIKE '%column_name%'
ORDER BY TableName,
         ColumnName;

Transact SQL – (Global) script variables

Problem:

When running a SQL script using the command sqlcmd tool if you switch context to another database, by using the “use database” command, then any variables defined before this are lost. A similar thing happens if you use the “go” command to group, or batch, commands in the script.

Solution 1:

Create a temporary table, e.g

CREATE TABLE #GlobalVariables (variableOne varchar(30), variableTwo int)
INSERT INTO #GlobalVariables (variableOne, variableTwo) VALUES ('ABCdef', 222222)

Solution 2:

Use script variables

:setvar variableOne "ABCdef"
:setvar variableTwo 222222

To access these in your SQL code you would do the following:

UPDATE #GlobalVariables SET variableOne = '$(variableOne)', variableTwo = $(variableTwo)

The current identity (IIS APPPOOl\xxxxxxx) does not have write access to…

If you encounter the following error then this may be the fix you’re looking for.


Server Error in ‘/’ Application.


The current identity (IIS APPPOOL\xxxxx) does not have write access to ‘C:\Windows\Microsoft.NET\Framework\v4.0.30319\Temporary ASP.NET Files’.


Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Web.HttpException: The current identity (IIS APPPOOL\xxxxx) does not have write access to ‘C:\Windows\Microsoft.NET\Framework\v4.0.30319\Temporary ASP.NET Files’.

Source Error:

An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.

Stack Trace:

[HttpException (0x80004005): The current identity (IIS APPPOOL\xxxxx) does not have write access to ‘C:\Windows\Microsoft.NET\Framework\v4.0.30319\Temporary ASP.NET Files’.]
System.Web.HttpRuntime.SetUpCodegenDirectory(CompilationSection compilationSection) +10003412
System.Web.HttpRuntime.HostingInit(HostingEnvironmentFlags hostingFlags, PolicyLevel policyLevel, Exception appDomainCreationException) +204

[HttpException (0x80004005): The current identity (IIS APPPOOL\xxxxx) does not have write access to ‘C:\Windows\Microsoft.NET\Framework\v4.0.30319\Temporary ASP.NET Files’.]
System.Web.HttpRuntime.FirstRequestInit(HttpContext context) +9947380
System.Web.HttpRuntime.EnsureFirstRequestInit(HttpContext context) +101
System.Web.HttpRuntime.ProcessRequestInternal(HttpWorkerRequest wr) +456

 


Version Information: Microsoft .NET Framework Version:4.0.30319; ASP.NET Version:4.6.1055.0


Simply run this code, from the Developer Command prompt: aspnet_regiis -i

Update 3-Jul-17:
On Windows Server 2012 you may need to run this command:

dism /online /enable-feature /featurename:IIS-ASPNET45 /all

 

Embedding a file in an assembly

Why? I have some unit tests that need to run off of some pre-defined static XML data and while the tests run fine so long as the file and assembly are in the same location as soon as the assembly is moved elsewhere they start to fail, “file not found” exceptions.
So if the file is embedded in the assembly I don’t need to worry about where it is located anymore.

The assumption, in the following, is that the file is XML that has been serialised from an existing List and is being reloaded back into the same, and that the file is in the current assembly. “_states” is a global variable.

So here are the basic steps for this:

In your Solution Explorer view right-click on the file, in this case “state.xml”, and view it’s Properties, make sure that the “Build Action” is set to “Embedded Resource”, optionally set the “Copy to Output Directory” to “Do not copy”.

Once this has done then the file can be loaded and copied into the required object using:

GetFileFromAssembly

An important thing to remember is that the “Namespace” prefix to the file is the “Default namespace” as entered in the Application properties tab and not the namespace used in the referencing file.

You can reference the file, if it’s in a sub-folder by adding the folder just after the namespace, e.g.

GetFileFromAssemblyNamespace