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. 

[MCerano]

Hello,

I'm having some trouble getting this to work on my end. I keep getting the error shown in the image below, I've also noticed that no files are downloaded to the temp folder. I'm using @kwoodard's bash script from above, I think I may have added in the google docs link incorrectly, I'm not sure.

[JKingsnorth]

Check to make sure the user running that command is an admin and the admin account has access to the /var folder.

[kwoodard]

Your picture of the log shows that the script is looking for the file in the /Library/ folder, whereas the file is put into the right folder...

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

Perhaps that is your issue? Also, on the Google Sheet, make sure your permissions are set correctly.

[MCerano]

Thanks for the response, I figured out what was wrong and got it working properly.

[kwoodard]

What was the issue, may help someone else in the future.

[MCerano]

Has anyone managed to get this working off of a file on an internal network?

[kwoodard]

I would imagine that you would need the full server path, maybe with the FQDN?

[Heavy_D]

Hello All hate to bring back this thread again but I have not been able to get this to work what I am essentially trying to do is rename the computer that comes in as macbook pro to its serial number then from the .csv find the correct name, change it to that name and then exit by changing the hostname and local host name to the same computer name. So far have managed to have the computer change the name to the Serial Number but the .csv portion does not seem to be working. Can someone with much better script skills take a look?

#!/bin/bash
# Get Serial of computer
Serial=$(ioreg -rd1 -c IOPlatformExpertDevice | awk -F'"' '/IOPlatformSerialNumber/{print $4}')
# Generate new computer name
computerName=${Serial}
# Set the computer name in Jamf to reflect what is set locally on the computer
/usr/local/bin/jamf setComputerName -name $computerName
/usr/local/bin/jamf recon
echo "Computer name has been changed to $computerName"
exit 0

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

# get Computer Name
computerName2=$( /usr/sbin/scutil --get ComputerName )
echo "Computer Name: $computerName2"

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

exit 0

#call download_csv
download_csv

#call rename_computer
rename_computer

It does the first part but doesn't acknowledge the .csv nor the last renames at all.

Executing Policy Computer Renaming from CSV
Running script 01 - Rename Computer via CSV...
Script exit code: 0
Script result: Set Computer Name to C02V50
Retrieving inventory preferences from https:mycloudinstance...
Finding extension attributes...
Locating hard drive information...
Locating accounts...
Locating package receipts...
Locating software updates...
Gathering application usage information from the JamfDaemon...
Locating applications...
Locating printers...
Searching path: /System/Applications
Searching path: /Applications
Locating hardware information (macOS 13.7.0)...
Submitting data to https:mycloudeinstance...
<computer_id>265222</computer_id>
Computer name has been changed to C02V50...
Running Recon...
Retrieving inventory preferences from https: mytcloudinstance...
Locating accounts...
Locating software updates...
Locating package receipts...
Locating printers...
Searching path: /System/Applications
Gathering application usage information from the JamfDaemon...
Searching path: /Applications
Locating hardware information (macOS 13.7.0)...

But i see what I might of done wring will test and get back to all.

[meaderp]

Sounds like you may not have the Google sheet set to share to anyone with
the link. Plus you have to make the change to that url to tell it to
export that as a csv when your script calls for it

[kwoodard]

I have the Google Sheet permissions set to "anyone with the link can view the list." Then I have a policy that calls the script below and in $4, I put the link to the Google Sheet. The script downloads the sheet as a CSV and reads through it to find the serial number and match it to the new name.

#!/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

[Heavy_D]

UPDATE: I got this to all work properly w/o odd Echos. And doing everything I need ty.

[kwoodard]

I kept the echo's in mine so I can see if anything failed. Glad you got it working. What did you end up having to do?

[Heavy_D]

I divided the policy in three parts scripts.

1. Renames machine to Serial Number after Enrollment so no more MacBook Pro entries.

#!/bin/bash
# Get Serial of computer
Serial=$(ioreg -rd1 -c IOPlatformExpertDevice | awk -F'"' '/IOPlatformSerialNumber/{print $4}')
# Generate new computer name
computerName=${Serial}
# Set the computer name in Jamf to reflect what is set locally on the computer
/usr/local/bin/jamf setComputerName -name $computerName
echo "Computer name has been changed to $computerName"
exit 0

2. Grabs the Serial Number and Changes the name of the machine from the .csv

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() {
JAMF_MESSAGE=$(sudo /usr/local/bin/jamf setComputerName -fromFile "$CSV_PATH")
JAMF_STATUS=$?
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

3. Grabs the current and already changed name and sets the HostName, and LocalHostName to the same and removes the .csv from the local machine.

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

# set hostname and local hostname
/usr/sbin/scutil --set HostName "$computerName"
/usr/sbin/scutil --set LocalHostName "$computerName"
/usr/local/bin/jamf recon
rm -rf /private/var/tmp/computernames.csv
exit 0