Low Stakes Automation

Though not as glamorous not vital as using scripts to maintain core infrastructure, every organization has its share of embedded vendor software that require bureaucratic toil that help to keep the lights on in other ways.

The tree of functionality must be refreshed by the sweat of admins. - Me (right now, updating some form)

‘ClickOps’ is a term that gets bandied about a lot when referring to operations tasks which involve unnnecessary toil, especially regarding infrastructure. It’s a bit of a catch-all, but I think that proper use often boils down to IaC, with governance(!). While I would be the first person to agree that automating infrastructure deployments and state management leads to fewer errors and less administrative effort most of the time, I feel like the scope is intentionally narrowed to sell a product, usually some sort of swiss army knife that promises to plug all the disparate LEGO pieces together, preferably after migrating the juicy stuff to a public cloud tenant.

But why not widen the scope to all operations work, not just infrastructure, but all the other foibles that admins have been automating away for decades? This could apply to anyone managing any repetitive task on a computer, especially if they’re starting out and are looking to get into automation and don’t know where to start. Automation should be accessible, and should be oriented around making the life of the automator easier. If the era of artificial intelligence promise is truly coming for the average office worker, then the paranoid BOFHs of the Register and r/sysadmin could provide a useful example of how to keep up.

In the spirit of sharing, I’ve put together some examples of truly tedious operational tasks that I’ve automated over the years below that have nothing to do with managing compute infrastructure, but do involve computers in some way. The scripts for each use case are probably too specific to be of any help, but I think that the thought process behind taking a task that you loathe and making it easier is universal.

2013 - 2015: My first job

My first job in high school was for a book publishing office, who mainly worked with small academic journals. The school guidance councelor ended up getting me the job, and I had dreams about working there for years afterwards.

As a part-time employee, my tasks were as follows:

Keep the Windows workstations updated, including Kaspersky (!!) Antivirus.
Digitize all paper documents in the file cabinets, and upload them to the file server.
Keep an eye on the Debian box backing up that file server, making sure the cron jobs completed and deleting older logs so it doesn’t fill up.

This actually would have been a great time to get into endpoint management, and deploy a common, locked-down image on those workstations. However, I’d be lying if I said I considered that.

What I did do, however, was try and save myself time on putting all those awful scanned documents in folders, which looked something like this (yes, it was in Batch, PowerShell came later):

FileEnforcer.bat

    rem ===  rename_file.bat  <full-path\file>  =========================

    if "%~1"=="" (
        echo Usage: %~nx0 ^<file_to_rename^>
        exit /b 1
    )
    if not exist "%~1" (
        echo File not found: "%~1"
        exit /b 1
    )

    setlocal EnableDelayedExpansion
    set "orig=%~1"
    set "ext=%~x1"

    :ASK_TITLE
    set /p "title=Title: "
    call :SANITIZE title
    if not defined title (
        echo  Title may not be empty.
        goto :ASK_TITLE
    )

    :ASK_AUTHOR
    set /p "author=Author: "
    call :SANITIZE author
    if not defined author (
        echo  Author may not be empty.
        goto :ASK_AUTHOR
    )

    :ASK_DATE
    set /p "date=Year (YYYYMMDD): "
    echo(!date!|findstr /r "^[0-9][0-9][0-9][0-9][0-1][0-9][0-3][0-9]$" >nul || (
        echo  ^> Invalid: date should be form YYYYMMDD
        goto :ASK_DATE
    )

    set "newname=!title!_!author!_!date!!ext!"

    ren "%orig%" "!newname!"
    echo Renamed:
    echo   "%orig%"
    echo   --> "!newname!"
    endlocal
    exit /b 0


    :SANITIZE  <var>
    rem REMOVE SPECIAL CHARACTERS
    set "tmp=!%~1!"
    for %%C in (^< ^> : \" / ^\ ^| ^? ^*) do set "tmp=!tmp:%%C=_!"
    for /f "tokens=* delims= " %%S in ("!tmp!") do set "tmp=%%S"
    :TrimTail
    if "!tmp:~-1!"==" " (set "tmp=!tmp:~0,-1!"& goto TrimTail)
    set "tmp=!tmp: =_!"
    :Collapse
    if "!tmp!"=="!tmp:__=_!" goto EndCollapse
    set "tmp=!tmp:__=_!"
    goto Collapse
    :EndCollapse
    set "%~1=!tmp!"
    goto :eof

The above snippet is not the original file and is totally AI-generated and then picked over, but it’s useful as an example. The original ended up serving me quite well that following summer, when I was working at a summer camp until 2PM and had to play catch up until 5.

2016 - 2019: The college years

To help pay my way through college, I worked did freelance web development work alongside being a desktop technician at the student helpdesk. Because Canadian college tuition is admittedly very affordable, I was blessed to be able to support myself despite not having many clients.

I provided a lot of services for my customers, including:

Graphic Design
Writing Content
Frontend Development
Backend Development
Social media management
Site Hosting

Hosting the websites was by far the most lucrative, and managing the social media was the most time consuming, followed by the design work. There was (and is) a product called Hootsuite that integrated all the different platforms together into one dashboard which helped some, but this only has the effect of reducing some clicks at the expense of others.

I also set up design templates in Illustrator that saved me a lot of time for making wireframes, which saved me time there, with template frameworks as well. Website builders like Squarespace or Wix were becoming increasingly popular, so I found myself managing several accounts with them as well, which wasn’t great for hosting revenue.

All that is to say, there weren’t a lot of opportunities coming out at me for saving time. I did however, have one customer that was an absolutely egregious time waster on content. As an event organizer, they aggregated different concerts around the metro area from several different RSS feeds. This allowed them to direct traffic to their site by showing up higher in the search results for those events, while also providing referral links to ticket sales, or sometimes links to their own ticketing service. Unfortunately, they chose to aggregate all scraped information and promoted events into a signle spreadsheet, which was painstakingly copy and pasted into posts on their Wordpress site. It was very time consuming, and not a lot of fun.

Thinking I might be able to justify some more time on the books with less effort, I automated the import process.

excel‑to‑wp.py

import pandas as pd
import subprocess
from slugify import slugify
from datetime import datetime

DF = pd.read_excel("events.xlsx")

WP = ["wp", "--path=/var/www/site", "post", "create"]

for _, row in DF.iterrows():
    date = pd.to_datetime(row["Date"])
    title = f"{row['Artist']} @ {row['Venue']} – {date.strftime('%b %d %Y')}"
    slug = slugify(title)
    body = (
        f"**When:** {date.strftime('%Y-%m-%d')}\n\n"
        f"**Where:** {row['Venue']}\n\n"
        f"[Tickets]({row['URL']})"
    )
    cmd = WP + [
        "--post_type=event",
        "--post_status=publish",
        f"--post_title={title}",
        f"--post_name={slug}",
        f"--post_content={body}",
        "--post_category=events",
    ]
    subprocess.check_call(cmd)

The script accomplished a few things:

Parsing the Excel file via pandas.
Generating neat URL slugs with python‑slugify.
Building a short blurb for each gig.
Publish the draft through wp post create … using the WordPress CLI, pending approval.

Once I got the spreadsheet every week or so, I was able to pad on a few more hours to my invoice, and they got to focus their interns on more important things.

2020 - 2021: Service Desk Escalations

Though perhaps I’d rather not, some weeks I spend a considerable amount of time creating elections, research surveys and mailing lists. Rather than risk burning out my dominant index finger clicking around the browser, I thought that it would be a good idea to automate some of the processes using Python and the Selenium testing library.

listserv-create.py

    import pyautogui
    import time
    from selenium import webdriver
    from selenium.webdriver.common.by import By
    from selenium.webdriver.common.action_chains import ActionChains
    from selenium.webdriver.chrome.options import Options

    chrome_options = Options()
    chrome_options.add_experimental_option("detach", True)

    #Store variables for mailing list configuration

    ls_name = pyautogui.prompt("What is the name of the list?")
    ls_title = pyautogui.prompt("What is the title of the list?")
    ls_owner = pyautogui.prompt("What is the email address of the list owner?")

    class TestCreationMenuTest():
        def setup_method(self, method):
            self.driver = webdriver.Chrome('.../pythonProject1/chromedriver', options=chrome_options)
            self.vars = {}

        def teardown_method(self, method):
            self.driver.quit()

        def test_creationMenuTest(self):
            self.driver.get("companynewsletter.ca/admin")
            self.driver.find_element(By.ID, "login.cell").click()
            self.driver.find_element(By.ID, "Email Address").click()
            self.driver.find_element(By.ID, "Email Address").send_keys(
                "[email protected]")
            self.driver.find_element(By.ID, "Password").click()
            self.driver.find_element(By.ID, "Password").send_keys("password")
            self.driver.find_element(By.NAME, "e").click()
            self.driver.find_element(By.CSS_SELECTOR, "body").click()
            self.driver.find_element(By.ID, "admnsub.cell").click()
            self.driver.find_element(By.ID, "admn_listsub.cell").click()
            element = self.driver.find_element(By.ID, "admn_list.createsub.cell")
            actions = ActionChains(self.driver)
            actions.move_to_element(element).click_and_hold().perform()
            element = self.driver.find_element(By.CSS_SELECTOR,
                                            "td:nth-child(2) > h2")
            actions = ActionChains(self.driver)
            actions.move_to_element(element).release().perform()
            self.driver.find_element(By.CSS_SELECTOR, "label:nth-child(2)").click()
            self.driver.find_element(By.ID, "List Name").click()
            self.driver.find_element(By.ID, "List Name").send_keys(ls_name)
            self.driver.find_element(By.ID, "List Title").click()
            self.driver.find_element(By.ID, "List Title").send_keys(ls_title)
            self.driver.find_element(By.NAME, "N").click()
            self.driver.find_element(By.ID, "List Owner").click()
            self.driver.find_element(By.ID, "List Owner").send_keys(ls_owner)
            self.driver.find_element(By.ID, "Announcement List")

            #Give 5 minutes until browser closes to manually confirm creation

            time.sleep(300)
            self.driver.close()

This script worked very quickly, and saved a lot of time in configuring the basics of the newsletter service. That being said, there are some issues that could be addressed for the other platforms.

If we were to hypothetically push an interface update from QA, its likely that the CSS classes will change. The login credentials are hard-coded, which is not good OpSec and could leave me in a tricky spot. Therefore, I made several modifications for the survey application script.

limesurvey-create.py

import pyautogui
import time
from selenium import webdriver
from selenium.webdriver.common.by import By
from selenium.webdriver.common.action_chains import ActionChains
from selenium.webdriver.chrome.options import Options
from selenium.webdriver.common.keys import Keys
from selenium.webdriver.common.desired_capabilities import DesiredCapabilities

chrome_options = Options()
chrome_options.add_experimental_option("detach", True)

admin_usr = pyautogui.prompt("Enter admin username:")
admin_pw = pyautogui.prompt("Enter admin password:")

survey_name = pyautogui.prompt("What is the name of the survey owner?")
survey_email = pyautogui.prompt("What is the email of the survey owner?")

class TestLimeSurveyv3siteadduser():
    def setup_method(self, method):
        self.driver = webdriver.Chrome('/Users/oliverherman/PycharmProjects/pythonProject1/chromedriver', options=chrome_options)
        self.vars = {}

    def teardown_method(self, method):
        self.driver.quit()


def test_limeSurveyv3AddInternalUser(self):
    self.driver.get(
        "https://companyresearch/login")
    self.driver.find_element(By.ID, "select2-authMethod-container").click()
    self.driver.find_element(By.CSS_SELECTOR,
                             ".select2-search__field").send_keys("Internal Authentication")
    self.driver.find_element(By.CSS_SELECTOR,
                             ".select2-search__field").send_keys(Keys.ENTER)
    self.driver.find_element(By.ID, "user").click()
    self.driver.find_element(By.ID, "user").send_keys(
        admin_usr)
    self.driver.find_element(By.ID, "password").click()
    self.driver.find_element(By.ID, "password").send_keys(admin_pw)
    self.driver.find_element(By.NAME, "login_submit").click()
    self.driver.find_element(By.LINK_TEXT, "Configuration").click()
    element = self.driver.find_element(By.LINK_TEXT,
                                       "Manage survey administrators")
    actions = ActionChains(self.driver)
    actions.move_to_element(element).perform()
    self.driver.find_element(By.LINK_TEXT,
                             "Manage survey administrators").click()
    element = self.driver.find_element(By.CSS_SELECTOR, "body")
    actions = ActionChains(self.driver)
    actions.move_to_element(element, 0, 0).perform()
    self.driver.find_element(By.ID, "add_user_admin").click()
    self.driver.find_element(By.ID, "new_user").click()
    self.driver.find_element(By.ID, "new_user").send_keys(
        survey_email)
    self.driver.find_element(By.ID, "new_email").click()
    self.driver.find_element(By.ID, "new_email").send_keys(
        survey_email)
    self.driver.find_element(By.ID, "new_full_name").click()
    self.driver.find_element(By.ID, "new_full_name").send_keys(survey_name)
    self.driver.find_element(By.ID, "add_user_btn").click()
    self.driver.find_element(By.CSS_SELECTOR,
                             ".btn-default:nth-child(2)").click()
    self.driver.find_element(By.ID, "btnToggleAdvanced").click()
    self.driver.find_element(By.ID, "all_auth_db").click()
    self.driver.find_element(By.ID, "all_auth_ldap").click()
    self.driver.find_element(By.ID, "perm_surveys_create").click()

    #Give 5 minutes until browser closes to manually confirm creation

    time.sleep(300)
    self.driver.close()

I also ran into an issue where large numbers of client UPNs were not being copied properly by the client into Active Directory, and would need to be corrected manually so as to make sure all users were being added successfully.

Instead of burning out my wrists, I thought that it would be a good idea to automate the process in Powershell.

My first attempt looked something like this:

AddUsersToGroup.ps1

<# PowerShell script to populate the Group $mygroup 
with each UPN listed on the given text file $csv
c:\adduserstogroup\Adduserstogroup.ps1 #>

Function Write-Log {
	Param ([string]$string)
	(  $string) | Out-File -FilePath $LogFileName -Append
}
   
<# Load the AD Routine #>
import-module activedirectory
Set-ExecutionPolicy -ExecutionPolicy Unrestricted -scope currentuser
 
<# Load Variables #>
Write-host "This script will populate specified by the Group variable $mygroup"

$mygroup = "000-Test"
$mydir = "G:\Scripts\new\"
$csvlocation  = $mydir + "elect.csv"
$csv = Get-Content $csvlocation | ConvertFrom-Csv
$LogFileName = $MyDir + "adduserstogroupoutput.CSV" 
New-Item $LogFileName -ItemType file -Force 
 
write-host "Adding Group Members to $mygroup"
Write-Log ("Adding Group Members to " + $mygroup)    
<# Iterate through the CSV #>
$csv | foreach-object {
    $user = Get-ADUser -Filter "UserPrincipalName -eq '$($_.username)'"
    if ($user){
        Add-ADGroupMember -Identity $mygroup -Members $user
        write-host "Added User " $user.UserPrincipalName
        Write-Log ($user.UserPrincipalName + " added")  		
    }
    else{
        Write-host "Unable to add user " $user.UserPrincipalName
        Write-Log ($_.username + " failed")  	
    }
}

This worked OK at adding users to the GPO quickly, but left a lot of room for errors in several places.

The name of the file is hard-coded, so unless you copy and paste new data into the original CSV each time, you may copy the wrong people.
The GPO is also hard-coded, so you if you forget to modify the script, you may copy the right people to the wrong place.
If the user address given by the client is not actually the UPN, but rather an alias, you will only see that it failed. Therefore, I made several modifications.

AddUsersToGroup-pm.ps1

<# PowerShell script to populate the AD Group $ElectionGroup  with everyone from text file $csv
R:\new\AddUsersToGroup-pm.ps1 #>
   
<# .SYNOPSIS #>
Param([Parameter(Mandatory=$True,HelpMessage="Enter the name of the election AD Group")][String]$ElectionGroup)

<#Uncomment this if you want a more verbose prompt
Param([Parameter(HelpMessage="Enter the name of the election AD Group")][String]
$ElectionGroup=$(Read-Host -prompt "Enter the name of the election AD Group"))#>

Function Write-Log {
	Param ([string]$string)
	(  $string) | Out-File -FilePath $LogFileName -Append
}
   
<# Load the AD Routine #>
import-module activedirectory
Set-ExecutionPolicy -ExecutionPolicy Unrestricted -scope currentuser

<# hard coded directory strings
these could be parameterized as well #>
$mydir = "R:\new\"

<# you may wish to change this to 'users.csv' #>
$csvlocation  = $mydir + "elect.csv"
$csv = Get-Content $csvlocation | ConvertFrom-Csv
$LogFileName = $MyDir + "adduserstogroupoutput.CSV" 

<# prime log messages to CSV #>
New-Item $LogFileName -ItemType file -Force

<# store user UPNs here #>

$successes = @()
$aliases = @()
$failures = @()
  
<# Iterate through each UPN listed in the CSV 
note that the column in the source CSV \
must be titled 'username' #>
$csv | foreach-object {

    $user = Get-ADUser -Filter "UserPrincipalName -eq '$($_.username)'"
    $alias = Get-ADUser -Filter "EmailAddress -like '$($_.username)'"

    if ($user){

        if ($ElectionGroup -contains $user) {

            $successes += $_.username
            Write-Log ($_.username + "is already a member of this election group.")
            Write-Host ($_.username + "is already a member of this election group.")

        } else {

            Add-ADGroupMember -Identity $ElectionGroup -Members $user
            $successes += $_.username
            Write-Log ($user.UserPrincipalName + " added")  
            Write-Host ($user.UserPrincipalName + " added")
        }	
            
    } elseif ($alias){

        if ($ElectionGroup -contains $alias) {
            
            Write-Log ($_.username + "is already a member of this election group.")
            Write-Host ($_.username + "is already a member of this election group.")


        } else {
            
            # Comment this out if you would like to confirm the suggestions before adding
            Add-ADGroupMember -Identity $ElectionGroup -Members $alias
            Write-Log ("Alias " + $alias.UserPrincipalName + " added")
            Write-Host ("Alias " + $alias.UserPrincipalName + " added")
            $aliases += $alias

        }
        

    } else {

        Write-Log ($_.username+ " failed to be added")
        Write-Host ($_.username+ " failed to be added")
        $failures += $_.username
    
    }

}

<# get a timestamp value and prime file to be exported #>
$timestamp = Get-Date -format yyyyMMdd
$OutputFile = "$timestamp-electionotput.txt"
$OutputPath = Join-Path -Path $mydir -ChildPath $OutputFile

<# define a hashtable of parameters for Out-File. #>
$outParams = @{

    FilePath = $OutputPath
    Encoding = "ASCII"
    Append = $True
    Width = 120

}

<# save the results to a .txt file report #>
$header = @"
Election Group User Report $((Get-Date).ToShortDateString())

*******************
Data Source = $csvlocation

*******************
Successfully Added
*******************

"@

$header | Out-File @outParams
$successes | Format-Table -AutoSize | Out-file @outParams

$header = @"


*******************
Aliases
*******************

"@

$header | Out-File @outParams
$aliases | Format-Table -AutoSize | Out-file @outParams

$header = @"


*******************
Failures
*******************

"@

$header | Out-File @outParams
$failures  | Format-Table -AutoSize | Out-file @outParams

<# write report file to the pipeline #>
Get-Item -Path $OutputPath

The addition of a formatted .txt report, as well as parameterizing the $ElectionGroup and $CSV variables, made things significantly easier. Additionally, the following block made it a 2-step process to determine which addresses where malformed, given incorrectly, or matched a user alias in on-prem Active Directory:

UserControlFlowSnippet.ps1

<# store user UPNs here #>

$successes = @()
$aliases = @()
$failures = @()
  
<# ... #>
$csv | foreach-object {

    $user = Get-ADUser -Filter "UserPrincipalName -eq '$($_.username)'"
    $alias = Get-ADUser -Filter "EmailAddress -like '$($_.username)'"

    if ($user){

        if ($ElectionGroup -contains $user) {

            $successes += $_.username
            Write-Log ($_.username + "is already a member of this election group.")
            Write-Host ($_.username + "is already a member of this election group.")

        } else {

            Add-ADGroupMember -Identity $ElectionGroup -Members $user
            $successes += $_.username
            Write-Log ($user.UserPrincipalName + " added")  
            Write-Host ($user.UserPrincipalName + " added")
        }	
            
    } elseif ($alias){

        if ($ElectionGroup -contains $alias) {
            
            Write-Log ($_.username + "is already a member of this election group.")
            Write-Host ($_.username + "is already a member of this election group.")


        } else {
            
            # Comment this out if you would like to confirm the suggestions before adding
            Add-ADGroupMember -Identity $ElectionGroup -Members $alias
            Write-Log ("Alias " + $alias.UserPrincipalName + " added")
            Write-Host ("Alias " + $alias.UserPrincipalName + " added")
            $aliases += $alias

        }
        

    } else {

        Write-Log ($_.username+ " failed to be added")
        Write-Host ($_.username+ " failed to be added")
        $failures += $_.username
    
    }

}

2021 - 2024: Sysadmin Stuff

When you’re managing infrastructure, it’s important that you know where things are. Whether you have a single TR or several datacenters, on-prem or colo, it should be catalogued. As you scale out, this quickly becomes a tedious task for several people.

One platform to use for this purpose is (Netbox). It has an Ansible provider, and populating copious amount sof serial numbers, operating systems, and other errata gets a lot easier when you automate it. Netbox themselves actually has a (great write-up) on this with screenshots and copious documentation, so I won’t get it into it too much. However, consider the potential for taking this automation tool and scoping it to your needs. Perhaps you you refresh your network infrastructure every three years, but update your fleet of hypervisors every three months. Perhaps you support an industrial operation with thousands of IoT devices across a dozen worksites. You might need to start parallelizing your update operations, and avoid updating each for every minor change.