Rename computer using Google Sheet

[kwoodard]

I have been using a Python script that uses a Google Sheet to locate serial numbers and match them up with names. When trying to use this with the M1 Mac's, it seems that the script isn't working as it should. Upon investigation, the script I was using was only updating the computer name, but not the two hostnames. On the Intel Mac's, this didn't seem to be an issue, but with the M1 Mac's, renaming via the script has become unreliable. More often than not, I have to manually rename the computers and manually add them to AD.

Anyone have a better script for this? Here is mine that I cobbled together from other threads on Jamf Nation.

#!/usr/bin/python
'''
Rename computer from remote CSV using Jamf binary
Pass in the URL to your remote CSV file using script parameter 4
The remote CSV could live on a web server you control, OR be a Google Sheet
specified in the following format:
https://docs.google.com/spreadsheets/u/0/d/1H9gsdRtmzb0v9TY8r47j8p8uUs8vhSZS3v446czCOjU/export?format=csv&id=<document ID>&gid=0
'''


import os
import sys
import urllib2
import subprocess


CSV_PATH = '/var/tmp/computernames.csv'


def download_csv(url):
    '''Downloads a remote CSV file to CSV_PATH'''
    try:
        # open the url
        csv = urllib2.urlopen(url)
        # ensure the local path exists
        directory = os.path.dirname(CSV_PATH)
        if not os.path.exists(directory):
            os.makedirs(directory)
        # write the csv data to the local file
        with open(CSV_PATH, 'w+') as local_file:
            local_file.write(csv.read())
        # return path to local csv file to pass along
        return CSV_PATH
    except (urllib2.HTTPError, urllib2.URLError):
        print 'ERROR: Unable to open URL', url
        return False
    except (IOError, OSError):
        print 'ERROR: Unable to write file at', CSV_PATH
        return False


def rename_computer(path):
    '''Renames a computer using the Jamf binary and local CSV at <path>'''
    cmd = ['/usr/local/bin/jamf', 'setComputerName', '-fromFile', path]
    proc = subprocess.Popen(cmd, stdout=subprocess.PIPE, stderr=subprocess.PIPE)
    out, _ = proc.communicate()
    if proc.returncode == 0:
        # on success the jamf binary reports 'Set Computer Name to XXX'
        # so we split the phrase and return the last element
        return out.split(' ')[-1]
    else:
        return False


def main():
    '''Main'''
    try:
        csv_url = sys.argv[4]
    except ValueError:
        print 'ERROR: You must provide the URL of a remote CSV file.'
        sys.exit(1)
    computernames = download_csv(csv_url)
    if computernames:
        rename = rename_computer(computernames)
        if rename:
            print 'SUCCESS: Set computer name to', rename
        else:
            print ('ERROR: Unable to set computer name. Is this device in the '
                   'remote CSV file?')
            sys.exit(1)
    else:
        print 'ERROR: Unable to set computer name without local CSV file.'
        sys.exit(1)


if __name__ == '__main__':
    main()

[jkaigler]

https://www.jamf.com/jamf-nation/discussions/36187/rename-computer-using-gsheet

[kbremner]

This is the script that I'm using to rename our new M1 computers as well and it works great except that it only changes the computer name. I have no experience with python but in looking at the code, wondering if I can add a couple of lines to rename the HostName and LocalHostName assuming I want them to be the same as the computer name? Would the following addition break the python code?

def rename_computer(path):
    '''Renames a computer using the Jamf binary and local CSV at <path>'''
    cmd = ['/usr/local/bin/jamf', 'setComputerName', '-fromFile', path]
    cmd = ['/usr/local/bin/jamf', 'setHostName', '-fromFile', path]
    cmd = ['/usr/local/bin/jamf', 'setLocalHostName', '-fromFile', path]
    proc = subprocess.Popen(cmd, stdout=subprocess.PIPE, stderr=subprocess.PIPE)
    out, _ = proc.communicate()
    if proc.returncode == 0:
        # on success the jamf binary reports 'Set Computer Name to XXX'
        # so we split the phrase and return the last element
        return out.split(' ')[-1]
    else:
        return False

[kwoodard]

@kbremner I had not thought to try that… I will do so and report back.

[kbremner]

As I looked over the code again I think part of the issue with why it isn't working for me is that setHostName and setLocalHostName are not verbs available with the Jamf Binary so those would have to be rewritten using the native macOS commands?

[kwoodard]

@kbremner I think I have found a solution to setting the other two names... I found a Jamf document on setting names and it suggested this script to go along with the Jamf binary command that renames the computers.

#!/bin/bash
# get Computer Name
computerName=$( /usr/sbin/scutil --get ComputerName )
echo "Computer Name: $computerName"

# create network name using only alphanumeric characters and hyphens for spaces
networkName=$( /usr/bin/sed -e 's/ /-/g' -e 's/[^[:alnum:]-]//g' <<< "$computerName" )
echo "Network Name: $networkName"

# set hostname and local hostname
/usr/sbin/scutil --set HostName "$networkName"
/usr/sbin/scutil --set LocalHostName "$networkName"

exit 0

[Darkman24]

So Kwoodard,
What's your update on scripts you are using. Did you find a fix for the M1's? I am also using a Google Sheet and would love for it to work with my new macs. And looking at the above script, I would run that in the same policy to make sure both computer name and hostname are changed?

Thanks for any advice.

[kwoodard]

Here is what we came up with. Written in bash script, so far has been working on everything...

#!/bin/bash

CSV_PATH='/var/tmp/computernames.csv'

# Downloads a remote CSV file to CSV_PATH
function download_csv() {
# -s flag to turn off curl's output
# -f flag to have catchable exit status
# -L for url link
# -o for output to filename
# double quotes around CSV_URL becuase curl prefers the url in quotes
curl -s -f -L -o "$CSV_PATH" ""$CSV_URL""
CURL_STATUS=$?

echo "waiting 10 seconds"
sleep 10
echo "finished waiting"

if [[ $CURL_STATUS -eq 0 ]]; then
echo "Successfully downloaded $CSV_URL to $CSV_PATH"
elif [[ $CURL_STATUS -eq 3 ]]; then
# File I/O error
echo "ERROR: Unable to write file at $CSV_PATH"
exit 1
else
# Download error
echo "ERROR: Unable to open URL at $CSV_URL"
exit 1
fi
}

# Renames a computer using the Jamf binary and local CSV at CSV_PATH
function rename_computer() {
/usr/local/bin/jamf help
JAMF_MESSAGE=$(sudo /usr/local/bin/jamf setComputerName -fromFile "$CSV_PATH")
JAMF_STATUS=$?
echo $JAMF_STATUS
echo $JAMF_MESSAGE
if [[ JAMF_STATUS -eq 0 ]]; then
RENAME=$(echo $JAMF_MESSAGE | awk 'END{print $NF}')
echo $RENAME
if [[ -n RENAME ]]; then
# on success the jamf binary reports 'Set Computer Name to XXX'
# so we split the phrase and return the last element
echo "SUCCESS: Set computer name to $RENAME"
else
echo "ERROR: Unable to set computer name. Is this device in the remote CSV file?"
exit 1
fi
else
echo "ERROR: Unable to set computer name without local CSV file."
exit 1
fi

}

if [[ -z $4 ]]; then
echo 'ERROR: You must provide the URL of a remote CSV file.'
exit 1
fi

CSV_URL=$4

#call download_csv
download_csv

#call rename_computer
rename_computer

[Darkman24]

Thank you so much. I will be testing this out soon!

[kwoodard]

I have a shorter one that doesn't display some of the echo commands and such, but I still use this one daily. Lets me see what's going on in the logs if something fails. 

And you more than likely already know this, but $4 is where the script calls the URL for your Google sheet, the option when you are creating the Policy... (My brain is mush today, I hope this makes sense)

[thomasC]

@kwoodard Thanks for this! 

[meaderp]

This script worked great for me.  Even ran faster than the old Python script. 4-6-2022

[tcaldana]

Thanks kwoodard for bash script. Works great! I was using the python version but now that python is not included with macOS 12.3 our erase-n-place workflows were broken... bash to rescue! 

[kwoodard]

Glad it's working out for you! I'm hoping to get it updated for zsh, but thats a longer term project.

[FMCSD_TimK]

I'm new to scripting, so I'm having problems figuring out specifically where to insert the URL of my Google Sheet. Do I substitute the $4 with the URL in both locations, or in a totally different spot? Sorry if this is a silly question.

[kwoodard]

$4 is Parameter 4 when you are creating the profile in Jamf Pro...

[FMCSD_TimK]

Forgot about that part of scripting from the Jamf 200 course. I'm on the right page now. Thanks very much for your help.

[kwoodard]

No worries, took me a bit to figure that out too.

[Luke_cater]

My organization wasn't too keen on putting the details in a google sheet so I ended up writing a script to create a CSV. Then do the remaining steps. Here is an example, hope this helps anyone in a similar situation.

cat << 'EOF' >/tmp/Mac_Computer_Names.csv
SERIAL, NAME, DEVICE_TYPE, Asset, 

C02XXXXXXXXX, Computer-Name-01, iMac, XXXXXX, 
D25XXXXXXXXX, Computer-Name-02, iMac, XXXXXX, 
D25XXXXXXXXX, Computer-Name-03, iMac, XXXXXX, 
EOF

/usr/local/bin/jamf setComputerName -fromFile '/private/tmp/Mac_Computer_Names.csv'

rm -Rf /private/tmp/Mac_Computer_Names.csv

[kwoodard]

That's essentially what the Google sheet is doing, creating a csv that the Jamf policy downloads to the computer.

[averion_j]

Hi @kwoodard Can you please share your googlesheet? I just need to know details needed. Thank you so much

[meaderp]

You will need the serial in column a and the name in b. The sheet must be openly shared. The format of the link must call the .CSV format.

Sent from my iPad

[kwoodard]

Here is where I started.

https://www.macblog.org/posts/automatically-renaming-computers-from-a-google-sheet-with-jamf-pro/ 

[averion_j]

Thank you so much @kwoodard  @meaderp 

[Heavy_D]

@kwoodard Thank you for this it saved me the headaches. I been using the original rename-computer.py piece since 2017 and this script now takes its place in 2022 I was wondering if there was a way to stop the echoing of the JAMF options binary while running it echoed the the options despite it being successful and exiting with 0.

[kwoodard]

You can. I did the echo's so I could follow which step things were on and it helped with troubleshooting.

[Heavy_D]

I am assuming its one of these correct?

echo $JAMF_STATUS
echo $JAMF_MESSAGE

[elund]

Thank you for this script!

[dwaterbury]

Has anyone else had a problem when exporting the Google Spreadsheet to a CSV?  I went to /var/tmp/ and when I open the csv file, it seems to error out on cell B9 and then stops adding the rest of the records.  The first 6 computers appear, but the computer name appears to always break at cell B9.  I've tried a brand new spreadsheet and moving that record to the bottom of the list, but it error still occurs on B9.

[jkaigler]

It may be a format error. I had an issue when copying and pasting the info but everything worked when I typed it in.

[dwaterbury]

OK. I figured it out thanks to iTecNote. In Parameter 4, I had to remove the edit#gid=0 from the end of the URL and add export?exportFormat=csv

Now it's successfully downloading the csv file.  I also am using a completely new Google Spreadsheet.  For some reason, when I went to change the Document ID back to the old one, it would fail again.  I was able to copy and paste all the data into the new one and confirmed the csv gets the correct number of records.

[JKingsnorth]

Has anyone had trouble getting this to work recently? I have been using this rename-computer.py for several years now without changing any of the script or policy and all of a sudden it is returning errors. It's acting as if the sheet isn't downloading but the link I'm using downloads the csv file just fine so I'm not sure where the error would be. 

[JKingsnorth]

I tried @dwaterbury 's fix above and am still having the same issue. New sheet, new format, same error.

[kwoodard]

Try using the script I shared above that’s written in bash, instead of python. 

[JKingsnorth]

Switching to Bash worked. No idea why Python stopped working all of a sudden today, I use this nearly daily.

[dwaterbury]

Sorry that I wasn't fully clear in my earlier post.  I had switched over to the Bash script because starting around Mac OSX 12.3, it appears Apple has removed Python 2.

[JKingsnorth]

Here we are again... Another update another issue (thanks Apple). Still using this script in Bash and it works on Intel, M1 and M2 Macs prior to 2023 models. The script is failing on new M2 2023 MBP 14's.

This is the error its returning but only on these new devices:

"There was an error. You must specify a computer name. Use the -name flag ERROR: Unable to set computer name without local CSV file."

I've used this method for years. I have the spreadsheet filled out and the script has been working until today.

[kwoodard]

I wonder if the script needs to be written in zsh? How big is the document getting that you have your name file? I know mine is about 1mb, so it downloads pretty quickly. Perhaps yours is too large and the script is running before the csv file is downloaded?

[JKingsnorth]

It cant be too big, it's only 160 lines. I currently have a 10 second wait time for download on the script but the error it returns starts off saying that the file downloaded successfully to a /var location.

[kwoodard]

Have you tried opening the folder where the CSV is downloading to and watching it to see if the file actually is downloaded? And if it is downloaded, is there any data in it?