Rename Computer Using GSheet

coachdnadel
Release Candidate Programs Tester

I've been happily utilizing the script from https://www.macblog.org/post/automatically-renaming-computers-from-a-google-sheet-with-jamf-pro/ (bless you @haircut) for naming all of my devices. Since it uses Python, am I going to have to install Python on new devices (running Catalina) and old devices that are wiped? Anyone installing Python on Catalina machines through the JPS? Do I need to worry about it Catalina?

11 REPLIES 11

Tribruin
Valued Contributor II

Python (technically Python 2.7.16) is still installed on Catalina. You should have no problems running the same script.

Be aware that Python 2 is End of Life and no longer updated or supported. Apple is warning that it will be removed in a future version of macOS. At this point, Apple has not given any indication whether they will include Python 3 at that time. It may come to a point in the future that you may have to install a Python run-time.

ega
Contributor III

No doubt @haircut does great work.
We took his idea (full credit here) and a slightly different access to Google sheets and run this in a PreStage pkg installer as post flight. This script is setup so you can download from ASM/ABM and append to the Google Sheet with no formatting change.
I hope this helps.


#!/bin/sh
## postinstall

# the Google Sheet identifier from the published URL 
sheetID="your_sheet_id_here"


# letter designation of the column in the sheet for the device serial numbers
# if following the default format from an Apple School Manager export this will be A
serialCol="A"

# letter designation of the colum in the sheet for the custom device name
nameCol="C"

# get the device serial number
serialNumber=$(ioreg -c IOPlatformExpertDevice -d 2 | awk -F" '/IOPlatformSerialNumber/{print $(NF-1)}')

# uncomment for debug
# echo $serialNumber

#Look up serial number from Google Sheet using the Google visualization api
dname=$(curl --silent "https://docs.google.com/spreadsheets/d/$sheetID/gviz/tq?tqx=out:csv&tq=select%20$nameCol%20WHERE%20$serialCol%3D%27$serialNumber%27")

# Remove the header information and use quotes
dname=$(echo $dname | cut -d '"' -f 2)

# uncomment for debug
# echo $dname

#Test to see if the name return has zero length and use serial number instead
if test -z $dname
then 
dname=$serialNumber 
fi

# uncomment for debug
#echo $dname

# Name the device using best value
scutil --set LocalHostName "$dname"
scutil --set ComputerName "$dname"
scutil --set HostName  "$dname"
dscacheutil -flushcache

exit 0      ## Success

ega
Contributor III

Also note that we fall back to using the serial number if the string returned from the sheet is zero length but you might want more checks.

coachdnadel
Release Candidate Programs Tester

@ega Thank you! This script is extremely helpful! I think we will still name it from our current GSheet but that's just a simple change to the nameCol variable.

jkaigler
Contributor II

@ega testing your script and I am obviously doing something wrong. I input my sheet id with the serial number and the computer name. When the script I can see it gets the serial number but it returns "en" when it should be INFR999. i made no other changes to the script, the serial number is in column A and the computer name is in column C. Any ideas?

ega
Contributor III

@ jkaigler As long as the GSheet is readable by everyone then should return the value. Could be some bad data in the cell due to cut and paste but that is all I can think of.

skinford
Contributor III

@jkaigler, hope all is good,

Wanted to start using this script and I tried it with a good friend of mine Matt and it returned the same "en", that you stated. Matt is a wiz and he found the issue, you may have already found it considering the date of this article, but for the sake of anyone else who wants to use this script, it works like a charm. Thank you to both @ega for this script and @haircut for the original script. You both are excellent in what you do, and thank you also to my friend Matt!

Here is the only fix needed, in @ega's post, in the, curl --silent line go down to the section in the line: :csv&tq=select%20 and add a %20 in front of "select".

Your fixed section would be:
:csv&tq=%20select%20

And, as they say, Bob's Your Uncle! It works like a charm and naming machines couldn't be easier, unless of course, Apple did what they were supposed to do and make it simple to do tasks in labs as they used to be able to do, In What day was that??? Oh Yes, the Netboot Imaging days, but I digress. Thank you again @haircut, @ega, and Matt! You guys rock!!

angryant
New Contributor II

Thanks for this it helped me out.  I had one issue with getting the device serial numbers so I made the following change which worked for me:

serialNumber=$(system_profiler SPHardwareDataType | awk '/Serial/ {print $4}')

 

ega
Contributor III

That will work too!  Note that you might want to use the full path to the binary tools for security sake.

/usr/bin/awk

/usr/sbin/system_profiler

Dhuhi009
New Contributor

Hi ega thanks for the script it works , But the computer name is coming as Version instead of the one which i want, Is there any thing needs to be done on the google sheet part lake any particular format needs to be there. some help on this will be very usefull. Thanks in advance.

ega
Contributor III

@Dhuhi009 I ran into this issue recently.  Remove the header line in your google sheet and that will fix the issue.  Not sure what changed on the Google side but if a header row exists then the query will return 2 lines the header text first and then the value.  I was able to just clear the first row of the sheet that had my column name text in the cells.