Category Archives: Information Technology

Rename Computer using Scripting and Google Sheets

Google has made some changes that made this script not work, but it was an easy fix, once I bothered to look at why it wasn’t working.

The page redirects to another page, and thus the cURL doesn’t work as usual. Instead you need to add the redirect flag to the cURL command so, instead of:

curl 'https://docs.google.com/spreadsheets/d/YOURGOOGLESHEETIDHERE/export?exportFormat=csv' -o $OUTPUT

You want:

curl -L 'https://docs.google.com/spreadsheets/d/YOURGOOGLESHEETIDHERE/export?exportFormat=csv' -o $OUTPUT

So the full script would be:

#!/bin/sh

# Get the current device's serial number
SERIAL="$(ioreg -l | grep IOPlatformSerialNumber | sed -e 's/.*\"\(.*\)\"/\1/')"

# Where the file will be saved using today's date. On date of writing would be /tmp/serials20190802.csv
OUTPUT=/tmp/serials$(date +%Y%m%d).csv

# Download the CSV from Google Drive, file must be set to Shared With Anyone with Link (or Shared with Anyone)
curl -L 'https://docs.google.com/spreadsheets/d/YOURGOOGLESHEETIDHERE/export?exportFormat=csv' -o $OUTPUT

# With much thanks to @Gerk and the rest of the crew on the MacAdmins #toronto channel, this now grabs the entire line from the CSV file 
LINE=$(grep $SERIAL $OUTPUT)

# This will grab all the text before the ,
ASSETTAG="$( cut -d ',' -f 1 <<< "$LINE" )"

# Set the ComputerName, HostName and LocalHostName
scutil --set ComputerName $ASSETTAG
scutil --set HostName $ASSETTAG
scutil --set LocalHostName $ASSETTAG

Missing Profiles Button in System Preferences

See my previous post for a full history on this.

In it, I had migrated from WorkspaceONE to Mosyle. One some devices I needed to clear the profiles database to be able to remove WS1 and enroll with Mosyle. When you do that the Profiles button in System Preferences goes away, forever.

Well, I did that one machine running macOS 10.14 and upgraded it to 10.15. Once upgraded, the profiles button returned! HOORAY!

macOS 11 Compatibily Check

Yesterday, Apple announced macOS 11. I need to know what members of my computer fleet are compatible.

William Smith, aka talkingmoose, was kind enough to post the regex for what models are compatible with macOS 11. You can find that here. I tested Smith’s regex in BBEdit and it worked as expected.

The question was, how do I get an easy list of my fleet in our MDM, Mosyle?

I sent a Custom Command to my fleet, it’s here on GitHub. I told it to run that custom command on all computers and to store the results as “Big Sur.”

model=$(sysctl hw.model)
if echo $model | grep -Ei "(MacBookAir[6-9]|MacBookPro1[0-6]|MacPro[6-7]|MacBook(10|9|8)|Macmini[7-8]|MacPro[6-7]|iMacPro1),\d|iMac1(4,4|[5-9],\d)" ; then
  echo "macOS 11 Compatible"
else
  echo "macOS 11 Incompatible"
fi

So now I needed to use that data to create a list. I created a new Device Group and said that the Custom Command Big Sur was like incompatible. It immediately showed me my Library machines, which are iMac12,1 and running their max OS, macOS 10.14.

And here’s the result!

FileMaker Export to Tab Delimited

I want to export a table to a tab delimited text file1.

FileMaker supports that without any question. What it doesn’t do is create a header row.

I’m using this file to import into PickATime, a software designed to book parent-teacher conferences. I want to automate this as much as possible. So how do I get the header rows that are required?

In FileMaker my field might be called OpenApplyID, whereas PickATime requires the field to be called StudentID. So I can’t just take those field titles.

There’s new script steps in FileMaker 18 that will let me do this.

Script

Go to Layout [ “StudentContacts” (StudentContacts) ] 
Show All Records
Set Variable [ $path; Value:"filemac:/Macintosh HD/Users/USERFOLDER/Downloads/" & Year ( Get ( CurrentDate ) ) & Month ( Get (CurrentDate ) ) & Day ( Get (CurrentDate ) ) &" - " & "PAT_Students.txt" ] 
Export Records [ File Name: “$path”; Create folders:No; Character Set: “Unicode (UTF-8)”; Field Order: StudentContacts:: OpenApply_id 
 StudentContacts::first_name StudentContacts::last_name StudentContacts::birthday ] [ No dialog ] 
Set Variable [ $additionalText; Value:"StudentID<tab>First<tab>Last<tab>SecurityValue"]
Open Data File [ “$path” ; Target: $dataFile ]
Write to Data File [ File ID: $dataFile ; Data source: $additionalText ; Write as: UTF-8 ; Append line feed: On ] 
Close Data File [ File ID: $dataFile ] 

English

First go to to the student contacts and show all of the kids. I tell it where I want to save the file.

Then it exports the records.

I create a variable called $additionalText in which I want to store the contents of the first row2.

Then we open the file

Write $additionalText to the file.

Then Close the file.

Problems

Usually I’d do filemac:../Downloads and not have the absolute path, so it will work on other computers. For some odd reason when I do the relative path, it fucks up when I try to write to data file.

It puts a spare blank line in the file, but that works.

  1. That’s a text file which is like a spreadsheet where you have a tab separating columns and line separating rows, you can open them in Excel. []
  2. WordPress doesn’t like the tabs, so I had to replace them with <tab> []

Rename Computer using Scripting and Google Sheets

UPDATE: You need to allow redirects in your cURL by adding -L. See this post.

With Apple’s Device Enrollment program, when a organization-owned device first turns on, it checks in with Device Enrollment and gets the information to know what MDM is managing it and how to contact it. That MDM system can then install what’s known as a bootstrapping package.

In our old bootstrapping package, which was developed by an employee no longer here, was a giant if statement. If SerialNumber = x then set ComputerName to Bob, elseif SerialNumber = y then set ComputerName to Frank, elseif SerialNumber = z then set ComputerName to Jane. Pretty simple and straight forward, but a long list that is static and cannot be updated, without getting the package, rewriting the script to include new computers, repackage it and redeploy it. UGH!

I can’t find the original package. It’s in AirWatch, but sadly, I can’t find a “Download your package” button anywhere in there.

So I was going to rewrite it. Then I came across a page that talked about doing it from a spreadsheet. So I thought, well I can host a CSV file on a server somewhere and the computer can use the curl command to download it. Then I discovered that you can curl command to download a Google Sheet from the internet. At that point then I never have to change the package again1, I just have to update the Google Sheet.

I got into some trouble, and as usual, the killer community in the #toronto channel of the MacAdmins Slack are amazing and came to the rescue.

Specifically @Gerk, I had this weird awk command that wasn’t working. I also didn’t quite understand the command I had found in my Googling. He told me how he would do it, I quickly changed the line and tested it and it worked. I think googled how to parse $LINE variable to only have the value prior to the ,.

I think this works well. If anyone wants to improve on it, let me know. This is why we share knowledge in the MacAdmins community.

#!/bin/sh

# Get the current device's serial number
SERIAL="$(ioreg -l | grep IOPlatformSerialNumber | sed -e 's/.*\"\(.*\)\"/\1/')"

# Where the file will be saved using today's date. On date of writing would be /tmp/serials20190802.csv
OUTPUT=/tmp/serials$(date +%Y%m%d).csv

# Download the CSV from Google Drive, file must be set to Shared With Anyone with Link (or Shared with Anyone)
curl 'https://docs.google.com/spreadsheets/d/YOURGOOGLESHEETIDHERE/export?exportFormat=csv' -o $OUTPUT

# With much thanks to @Gerk and the rest of the crew on the MacAdmins #toronto channel, this now grabs the entire line from the CSV file 
LINE=$(grep $SERIAL $OUTPUT)

# This will grab all the text before the ,
ASSETTAG="$( cut -d ',' -f 1 <<< "$LINE" )"

# Set the ComputerName, HostName and LocalHostName
scutil --set ComputerName $ASSETTAG
scutil --set HostName $ASSETTAG
scutil --set LocalHostName $ASSETTAG
  1. Well, never say never []

Let’s Encrypt with CPanel

A lot of web hosts have Let’s Encrypt built into the CPanel. Not mine. I was kinda annoyed. I tweeted at them to ask if they’d support it. They wouldn’t. So I decided I would make it work myself. To do this is pertty simple. I’m just going to start by copying and pasting text from a previous article.

Open up your Terminal.app (Go to the Go menu, choose Utilities, double-click on Terminal). This isn’t even a step, you should know this.

Step One – Install Homebrew

/usr/bin/ruby -e "$(curl -fsSL https://raw.githubusercontent.com/Homebrew/install/master/install)"

At this point, you will be prompted to press RETURN to continue. Press the return key.

It will then start downloading and installing Homebrew.

Install XCode Select

Turns out you need XCode Select installed, too. So I ran this code.

xcode-select --install

That popped up a dialogue box, I said Install.

This install took a few minutes, and then once it was done, I was ready to install certbot.

Install certbot

brew install certbot

That easy? Aye!

Begin the Process

sudo certbot -d anklewicz.com -d neverhadtofight.com -d www.neverhadtofight.com -d www.anklewicz.com --manual --preferred-challenges dns certonly

The program will ask you a few questions, if you’re okay with your IP being logged.

Then it will, for each included domain, ask you to set up a TXT record.

Setup DNS Records

Go to your CPanel, yourdomain.com/cpanel is usually the address. Click on the DNS Zone Editor.

Choose the domain you want and click “Manage.”

Click the down arrow beside “Add Record” and choose to add a TXT record.

In the name field put what it told you in Terminal, aka _acme-challenge.yourdomain.com and under Record paste in the gibberish string that certbot told you.

Back to Terminal, press enter to proceed.

Repeat these steps for all domains.

Wait for it to validate your domains.

Copying certificate to desktop

Using the cp command you can copy the two files over to your desktop.

sudo cp /etc/letsencrypt/live/yourdomain.com/privkey.pem ~/Desktop/privkey.pem
sudo cp /etc/letsencrypt/live/yourdomain.com/fullchain.pem ~/Desktop/fullchain.crt

Installing in CPanel

Go back to your front page of CPanel and look for SSL/TSL, and click on that link.

Click on “Generate, view, upload, or delete SSL certificates.”

Scroll down to find “Choose a certificate file (*.crt).”

Upload the CRT file.

Your list of certificates at the top will update and beside the new one, click on Install.

Open the privkey.pem file in a text editor, like BBEdit, and copy it’s contents.

Paste that into the key area and save.

You’re done.

OMG! APPLE IS GUTTING SERVER.APP!!!! Part 3 DNS to BIND

In July, I will be presenting at MacAdmins at PSU. My talk will be called “OMG! APPLE IS GUTTING SERVER.APP!!!!” I will be using my blog to document all the processes taken to get all the data.

The goal of this is to find easy ways to move away from Server.app while utilizing the existing Apple hardware in your server closet and macOS. Sure you can move to a new system, but you might not have the money or time.

You can find the slide deck here.

Server.app -> BIND

So before I start, I should say that this is fully documented in Apple’s macOS Server Service Migration documentation, which is almost perfect. I’m only making two small changes.

  1. First step is to test the server. After booting this VM, I set my DNS to the testserver. It loaded no problem.
  2. Turn off DNS services in Server.app
  3. Install Xcode.
  4. Here’s the first change, and important one… LAUNCH XCODE and agree to the terms and conditions, otherwise it won’t work.
  5. Go to https://www.isc.org/downloads/
    1. Click on “BIND” to expand that section
    2. Click on Download beside “Current Stable”
    3. The top row will be Windows installers, the second row will have the *nix version, choose that. The link currently says bind-9.12.1-P2.tar.gz – tar.gz
    4. Apple says to grab at least one signature. I did it, I don’t know if that was necessary.
  6. Open Terminal and navigate to the directory you downloaded to, in my case it was cd ~/Downloads
  7. Uncompress the files. tar xzf bind-9.12.1-P2.tar.gz
  8. Navigate the uncompressed directory cd ./bind-9.12.1-P2
  9. Run this command ./configure --infodir="/usr/share/info" --sysconfdir="/etc" --localstatedir="/var" --enable-atomic="no" --with-openssl=no --withgssapi=yes --enable-symtable=none --with-libxml2=no
  10. Make it by typing make
  11. Test the build by running the following commands
    1. sudo ./bin/tests/system/ifconfig.sh up
    2. make test … this seemed to go on FOREVER, so control-c‘ed out of there. I don’t know if I was supposed to.
    3. sudo ./bin/tests/system/ifconfig.sh down
  12. Let’s install it. sudo make install
  13. Apple suggests you verify that it’s installed by pulling up the manual for the DNS service called “named.” You do that by typing man named
  14. Create the launchdaemon by typing sudo nano  /Library/LaunchDaemons/org.isc.named.plist
  15. Go here, go to page 7, copy the contents from step two of “Create a launchd .plist file for the BIND9 service”
  16. Save and exit control-x, y, enter
  17. Here’s the next place I disagree with Apple, they say, “Set file ownership to root:wheel.” However, they use chmod, and it’s chown.
    sudo chown root:wheel /Library/LaunchDaemons/org.isc.named.plist
  18. Load the job sudo launchctl load -w /Library/LaunchDaemons/org.isc.named.plist
  19. Test the job launchctl print system/org.isc.named

Wow. Apple has made this super easy. Hooray for Apple. All your existing settings will be there already. Really. It just works.

OMG! APPLE IS GUTTING SERVER.APP!!!! Part 2 Web to Apache

In July, I will be presenting at MacAdmins at PSU. My talk will be called “OMG! APPLE IS GUTTING SERVER.APP!!!!” I will be using my blog to document all the processes taken to get all the data.

The goal of this is to find easy ways to move away from Server.app while utilizing the existing Apple hardware in your server closet and macOS. Sure you can move to a new system, but you might not have the money or time.

You can find the slide deck here.

Server.app -> Apache

  1. First step is to test the server. After booting this VM, I visited my testserver. Mine was at testserver.leobaeck.ca. It loaded no problem
  2. Then I turned off Websites in Server.app
  3. At that point I duplicated /etc/apache2/httpd.conf, renamed the duplicate httpd.backup and now I have a backup in case I screw anything else up.
  4. Edit /etc/apache2/httpd.conf. Uncomment LoadModule php7_module libexec/apache2/libphp7.so by removing #
  5. Restart apache with sudo apachectl restart
  6. Visit your test server and make sure you see “It works!”
  7. Create a test PHP file to see if it works
    1. sudo touch /Library/WebServer/Documents/phpinfo.php
    2. Using your favourite terminal-based text editor, or mine, edit that file. sudo nano /Library/WebServer/Documents/phpinfo.php
    3. Paste this into that document <?php
      phpinfo();
      ?>
    4. Save, control-o and exit control-x in nano
  8. test by visiting your sever server.domain.com/phpinfo.php
  9. Transfer contents from Server.app’s location to Apache’s
    sudo rsync -av /Library/Server/Web/Data/Sites/Default/ /Library/WebServer/Documents/
  10. Set proper permissions for the documents
    sudo chgrp -R _www /Library/WebServer/Documents/
    sudo chmod -R 775 /Library/WebServer/Documents/
  11. Since I was using Munkireport as my test, I needed to edit httpd.conf to point to /Library/WebServer/Documents/public.
  12. Restart apache, sudo apachectl restart
  13. Test

Migrate existing SSL Certs from Let’s Encrypt to apache

This makes the assumption that you already have an SSL certificate. Much of this is universal, but it’s told from the point of view of using a free cert you got from Let’s Encrypt.

My starting point was this document.

  1. You need to start by editing the /etc/apache2/httpd.conf file, again. This time we’re enabling modules to support SSL
    LoadModule socache_shmcb_module libexec/apache2/mod_socache_shmcb.so
    LoadModule ssl_module libexec/apache2/mod_ssl.so
  2. Uncomment by removing # the line Include /private/etc/apache2/extra/httpd-ssl.conf
  3. I don’t know if this set actually matters, but I did it. You need to edit the Virtual Host file /etc/apache2/extra/httpd-vhosts.conf and paste into the end of chunk of text. Go up to the link and grab the text.

At this point we diverge from the above link, I had tested and it didn’t work.

  1. Find your old downloads from Let’s Encrypt, the two PEM files. Rename fullchain.pem to server.crt and key.pem to server.key. I actually renamed them to the FQDN.*, so testserver.leobaeck.ca.key
  2. Move them into /private/etc/apache2
  3. Edit /private/etc/apache2/extra/httpd-ssl.conf and find ## SSL Virtual Host Context
  4. Make sure DocumentRoot is correct
  5. Put in ServerName
  6. Scroll down a bit more and put in SSLCertificateFile and SSLCertificateFile
  7. Save and exit
  8. Restart Apache sudo apachectl restart

OMG! APPLE IS GUTTING SERVER.APP!!!! Part 1 Web to MAMP

In July, I will be presenting at MacAdmins at PSU. My talk will be called “OMG! APPLE IS GUTTING SERVER.APP!!!!” I will be using my blog to document all the processes taken to get all the data.

The goal of this is to find easy ways to move away from Server.app while utilizing the existing Apple hardware in your server closet and macOS. Sure you can move to a new system, but you might not have the money or time.

You can find the slide deck here.

Server.app -> MAMP

I started with a simple MunkiReport instance running in Server.app. I figured this would be using enough resources to move. I also got a certificate with Let’s Encrypt and had all traffic going through SSL. While the blog post is specifically for servers running 10.12, my steps were the same in 10.13.

  1. Let’s start by downloading MAMP.
  2. Turn off Server.app’s web function
  3. Run the installer you had downloaded.
  4. From /Applications/MAMP launch MAMP
  5. Click start Servers, and check that yourdomain:8888 is working.
  6. Go to Preferences and click on Web-Server. Set the location to be the old root folder.
  7. Stop/Start the server and test.
  8. Go to Preferences and click on Ports. Click that “Set Web & MySQL ports to 80 & 3306 button.
  9. Stop/Start the server and check to make sure it’s working on port 80.

Your web server is up and running. Mostly. Next we need to get SSL setup. MAMP Pro has an easy GUI to do this, but that costs money and this is easy enough for our needs.

I used this this gist to help me through this process.

We already know that MAMP is working on port 80, so you can ignore the first bits of that file.

  1. Duplicate your /Applications/MAMP/conf/ folder, rename the copy something like conf.backup.
  2. Obviously you were using Server.app already and were using it with a Let’s Encrypt certificate using my procedure outlined on this post. So you should have on your Desktop a couple .pem files. Rename them fqdn.crt and fqdn.key and move them to /Applications/MAMP/conf/apache
  3. Open /Applications/MAMP/conf/apache/httpd.conf in BBEdit or your favourite text editor and uncomment by removing the #, Include /Applications/MAMP/conf/apache/extra/httpd-ssl.conf.
  4. Edit /Applications/MAMP/conf/apache/extra/httpd-ssl.conf and search for General setup for the virtual host. From there, you’ll want to enter the path to the web files.
  5. In the same file, you’ll see information about server.crt and server.key. Rename the server part to match the names of the files in step two.
  6. Stop/Start the service.

Find a Mac’s serial number in Recovery Partition or the macOS installer

I couldn’t find this anywhere, so I thought I’d blog about this. Thanks to the #general channel on the MacAdmins Slack for the help.

To find a serial number for a Mac when booted into the Recovery Partition or the macOS installer, to go Utilities and choose Terminal, type in ioreg -rd1 -c IOPlatformExpertDevice | awk -F'"' '/IOPlatformSerialNumber/{print $4}'

That’s it, it will output the serial number for the computer.