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()

[JKingsnorth]

Got this working. Wasn't actually an Apple update that killed it but rather a Google update that affected the security/sharing settings on the Google Sheet we used.

[kwoodard]

That's good to know.

[meaderp]

Thanks for the heads up.  I bumped into this issue earlier in the year and made the adjustment without thinking to update the JN thread. :)

[Francoislb]

Hi @kwoodard 

The bash script was working until 10 days ago. Also have been using it for a lonng time.

Please tell me you have a fix...           that would make my day!

Thanks

[kwoodard]

I'm not sure. Mine is still working.

[Francoislb]

You mentioned a Google update changed something in Google security, but cannot see what changed. I do have the exact error when the policy runs:  "You must specify a computer name. Use -name flag.

It was working a week ago.

[maestromarv]

I have the same issue with M2 mac minis.  The same error insisting on -name where this script has -fromFile.

[Francoislb]

Check the share permissions. I "fixed it" by switching to the following: 

Someone, something rolled it back to Restricted. 

[maestromarv]

That's still the same on mine.  No joy.  It's still asking for -name

[meaderp]

We are only running Airs, but we have both M1 and M2 and they are working just fine.  Be sure you are not using the Python script as that version of Python is no longer functional in Mac OS

#!/bin/bash

CSV_PATH='/var/tmp/Tech Inventory based Laptop Naming Sheet - Sheet1.csv'
CSV_URL=$4

# 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

[maestromarv]

Script result: waiting 10 seconds
finished waiting Successfully downloaded https://drive.google.com/file/d/109bL3ee--------------ijkr-dnu/view?usp=drive_link to /var/tmp/computernames.csv 1 There was an error. You must specify a computer name. Use the -name flag ERROR: Unable to set computer name without local CSV file.

[meaderp]

Your fields on the Spreadsheet need to be as follows... Column A MUST BE SERIAL NUMBER and Column B MUST BE THE NAME YOU WANT ON THE DEVICE.  Anything else wont work.  Also, you do not want Column B to be an equation.  You need a text version of the name there.   The sheet must be openly shared with EVERYONE.

IN ours, the first three columns are as follows.

Serial

Display Name

Asset Tag

This is the script I use below.  It is in BASH rather than the old depreciated Python.  

#!/bin/bash

CSV_PATH='/var/tmp/Tech Inventory based Laptop Naming Sheet - Sheet1.csv'
CSV_URL=$4

# 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

[maestromarv]

I managed to get completed instead of failed by going into the Google Sheet and setting both columns as text.

That's the good news.

However, the name it pulls is now completely wrong.  I'm not sure what part of the spreadsheet is supplying the info.

sharing to /var/tmp/BC_mac_list.csv 0 Set Computer Name to "timeZoneConstants":{"GMT":{"names_ext":{"STD_GENERIC_LOCATION":"GMT" "timeZoneConstants":{"GMT":{"names_ext":{"STD_GENERIC_LOCATION":"GMT" SUCCESS: Set computer name to "timeZoneConstants":{"GMT":{"names_ext":{"STD_GENERIC_LOCATION":"GMT"

I know the serial number column is being read because only those on the list of 20 get renamed.  I have several others in the Policy deployment for testing which aren't on the csv and they still fail as expected.

I tried saving the csv to MS Notepad, saving as ANSI or UTF to test, to get rid of any chance of file structure problems, but that just results in the same old -name error from previous posts.

It would have been quicker for me to name them all manually at this rate but I would love to have working automation the same as you guys going forward.

[meaderp]

Have you tried creating the CSV text with TextEdit in macOS? MS stuff
always seems to leave a bunch of hidden garbage in my experience

[Luke_cater]

Hey @maestromarv 

This is what I use. Had no issues so far. 

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 

[maestromarv]

Turns out I hadn't put the export as csv bit on the end of the link in parameter 4.

However, I don't plan to use Google long term.  I need to host it on our SMB so I'll need to solve these -name issues.  

@Luke_cater  are you just pushing the csv to the machines before running the script?

[Luke_cater]

The script I posted is an example of what I use. 
The script writes the CSV so I don't have to rely on fetching a CSV from another source (SMB or Google.) All done in Jamf policy running this one script.