More API help needed

blackholemac
Valued Contributor III

I want to apologize in advance...I am going to be taking the CJA course very soon and am looking forward to learning more about the back-end of JAMF databases and the API. I'm a bit of a noob with the API.

Would someone know how to take an Excel CSV formatted with the following: Device Name (column1), Serial Number (column 2), Building, (column 3), Room Number (column 4).

I want to take this spreadsheet and use the API to add the info contained in columns 3 and 4 to the JSS. There may be existing data there in the JSS and I would like it to be overwritten.

I know this isn't McD's where I put in an order and receive it, but I hope someone here will take pity on me and teach me how to do this as it will help us with would could have been a bad data entry task. I know you can do Building with a mass action, but I can't do that with room number necessarily and this is why I want to go the API route.

Thank you to any kind souls in advance,
blackholemac

1 ACCEPTED SOLUTION

stevewood
Honored Contributor II
Honored Contributor II

@blackholemac the below should work for you. It was the script I used to update lease data here, I just adjusted it to work with mobile devices to update Building and Room. I tested here and it seemed to work fine.

This script will not handle spaces in the names of the building or room, so you'll either need to scrub the data or work around that.

Since you are not using the device name in the script, you do not have to have that in the CSV file. This is written to the specs you stated above, so it will take the device name, but nothing is done with it.

Make sure the CSV file is saved as a UNIX file with LF and not CRs. You will also need to edit the script to add your JSS server address.

This is designed to run from the Terminal by passing the api user name and password along with the CSV file name. Make sure the CSV is in the same folder as the script.

./updateBuildingRoomNum.sh apiuser apipass file.csv

You can also find this script on my GitHub repo: Github

#!/bin/sh

# Name: updateBuildingRoomNum.sh
# Date: 07 January 2016
# Author: Steve Wood (swood@integer.com) 
# Purpose: used to read in lease data from a CSV file and update the record in the JSS
# The CSV file needs to be saved as a UNIX file with LF, not CR
# Version: 1.0
#
#
# A good portion of this script is re-purposed from the script posted in the following JAMF Nation article:
#
#  https://jamfnation.jamfsoftware.com/discussion.html?id=13118#respond
#
# USAGE: updateBuildingRoomNum.sh apiusername apipass csvfile.csv

args=("$@")
jssAPIUsername="${args[0]}"
jssAPIPassword="${args[1]}"
jssAddress="<jss address>"
file="${args[2]}"

#Verify we can read the file
data=`cat $file`
if [[ "$data" == "" ]]; then
    echo "Unable to read the file path specified"
    echo "Ensure there are no spaces and that the path is correct"
    exit 1
fi

#Find how many computers to import
computerqty=`awk -F, 'END {printf "%s
", NR}' $file`
echo "Computerqty= " $computerqty
#Set a counter for the loop
counter="0"

duplicates=[]

#Loop through the CSV and submit data to the API
while [ $counter -lt $computerqty ]
do
    counter=$[$counter+1]
    line=`echo "$data" | head -n $counter | tail -n 1`
        deviceName=`echo "$line" | awk -F , '{print $1}'`
    serialNumber=`echo "$line" | awk -F , '{print $2}'`
    building=`echo "$line" | awk -F , '{print $3}'`
    room=`echo "$line" | awk -F , '{print $4}'`

    echo "Attempting to update data for $serialNumber"

    apiData="<mobile_device><location><building>$building</building><room>$room</room></location></mobile_device>"
    output=`curl -sS -k -i -u ${jssAPIUsername}:${jssAPIPassword} -X PUT -H "Content-Type: text/xml" -d "<?xml version="1.0" encoding="ISO-8859-1"?>$apiData" ${jssAddress}/JSSResource/mobiledevices/serialnumber/$serialNumber`

    error=""
    error=`echo $output | grep "Conflict"`
    if [[ $error != "" ]]; then
        duplicates+=($serialnumber)
    fi

done

echo "The following computers could not be created:"
printf -- '%s
' "${duplicates[@]}"

exit 0

As always, test, test, test, and finally TEST! :-)

Hope this helps.

View solution in original post

8 REPLIES 8

mm2270
Legendary Contributor III

@blackholemac What is the timeframe requirement for getting this done? I'd like to help, but I don't really have the time at this moment to write up a script to get you started. I do think this would be possible, but I'd need to play with it a bit.

blackholemac
Valued Contributor III

Some background: iPads were distributed to multiple Configurator carts in a school building. Not true 1 to 1s...they are shared so LDAP isn't in play. ( I wish it were.)

As for timeframe...sooner rather than later...for sure is better. That being said...the building techs are receiving a pre-populated spreadsheet of all iPads in a given building with serial and asset tag. The building tech is going to record what room the device is in. We are doing this at 6 schools with about 300 iPads per school. It will take them about a week to collect and me a day or so to "scrub". Once I have scrubbed data from them in the form that I described in the original post, I hope to import. In other words...about 1-2 weeks.

Again thank you very much...I wish I had more knowledge in this area and have made it my "new skill goal" for this year. I also appreciate you @mm2270 even if you don't have the time right at the moment...thank you

stevewood
Honored Contributor II
Honored Contributor II

@blackholemac the below should work for you. It was the script I used to update lease data here, I just adjusted it to work with mobile devices to update Building and Room. I tested here and it seemed to work fine.

This script will not handle spaces in the names of the building or room, so you'll either need to scrub the data or work around that.

Since you are not using the device name in the script, you do not have to have that in the CSV file. This is written to the specs you stated above, so it will take the device name, but nothing is done with it.

Make sure the CSV file is saved as a UNIX file with LF and not CRs. You will also need to edit the script to add your JSS server address.

This is designed to run from the Terminal by passing the api user name and password along with the CSV file name. Make sure the CSV is in the same folder as the script.

./updateBuildingRoomNum.sh apiuser apipass file.csv

You can also find this script on my GitHub repo: Github

#!/bin/sh

# Name: updateBuildingRoomNum.sh
# Date: 07 January 2016
# Author: Steve Wood (swood@integer.com) 
# Purpose: used to read in lease data from a CSV file and update the record in the JSS
# The CSV file needs to be saved as a UNIX file with LF, not CR
# Version: 1.0
#
#
# A good portion of this script is re-purposed from the script posted in the following JAMF Nation article:
#
#  https://jamfnation.jamfsoftware.com/discussion.html?id=13118#respond
#
# USAGE: updateBuildingRoomNum.sh apiusername apipass csvfile.csv

args=("$@")
jssAPIUsername="${args[0]}"
jssAPIPassword="${args[1]}"
jssAddress="<jss address>"
file="${args[2]}"

#Verify we can read the file
data=`cat $file`
if [[ "$data" == "" ]]; then
    echo "Unable to read the file path specified"
    echo "Ensure there are no spaces and that the path is correct"
    exit 1
fi

#Find how many computers to import
computerqty=`awk -F, 'END {printf "%s
", NR}' $file`
echo "Computerqty= " $computerqty
#Set a counter for the loop
counter="0"

duplicates=[]

#Loop through the CSV and submit data to the API
while [ $counter -lt $computerqty ]
do
    counter=$[$counter+1]
    line=`echo "$data" | head -n $counter | tail -n 1`
        deviceName=`echo "$line" | awk -F , '{print $1}'`
    serialNumber=`echo "$line" | awk -F , '{print $2}'`
    building=`echo "$line" | awk -F , '{print $3}'`
    room=`echo "$line" | awk -F , '{print $4}'`

    echo "Attempting to update data for $serialNumber"

    apiData="<mobile_device><location><building>$building</building><room>$room</room></location></mobile_device>"
    output=`curl -sS -k -i -u ${jssAPIUsername}:${jssAPIPassword} -X PUT -H "Content-Type: text/xml" -d "<?xml version="1.0" encoding="ISO-8859-1"?>$apiData" ${jssAddress}/JSSResource/mobiledevices/serialnumber/$serialNumber`

    error=""
    error=`echo $output | grep "Conflict"`
    if [[ $error != "" ]]; then
        duplicates+=($serialnumber)
    fi

done

echo "The following computers could not be created:"
printf -- '%s
' "${duplicates[@]}"

exit 0

As always, test, test, test, and finally TEST! :-)

Hope this helps.

blackholemac
Valued Contributor III

@stevewood Have tested and noted that it works for me with a sampling of two devices. Once I get an one of my inventory sheets back, I will test on a whole building. The line breaks issue was very important...I made it work after checking that explicitly.

I want to thank you and extend a thank you from the Lafayette School Corporation. You made our data entry process much better. Like I said, I'm signed up for the CJA in March so I hope to learn more backend stuff, but more importantly, I am looking at some of your scripts and am learning from that too.

Thank you,
blackholemac

stevewood
Honored Contributor II
Honored Contributor II

@blackholemac No problem, that's the beauty of this community, we all want to help each other out.

If you're looking for more scripts to learn from, there are plenty. @mm2270 has some great work here and comes up with some great solutions to problems.

I wish you luck!

PeterClarke
Contributor II

Hi, "blackholemac"

Just wanted to say i attended a CJA course in London UK recently.

Though I didn't get the certification - The exam was far too fast for me..
In fact the whole course was very fast..

We did nothing at all on the API

The CJA course is all about "How to install and Configure" Casper Server

I did learn a lot on the course, but would need to practice a lot in order to develop the speed required to pass this course.

I think there is distinction between:
Wanting to attend a course in order to lean about the topic area
as compared with wanting to get a qualification.

For comparison, the whole 4-Day CJA course that I was on, went "at the same speed" as the Exam-Part did on the CCT course. So it's very fast paced.

Since you have already done the CCE course - you have already covered all that the JAMF courses do on the API..
I would suspect that it's only just 'introduced'..
Certainly extensive use of the API is a very advanced topic, as far as Casper goes..
I think some things, are simply outside of ALL of these courses.
But I would say that all of the JAMF course are good, and well worth attending.

I find myself working at multiple different levels,
some things I do can be very advanced, while other things are quite simple.

I am shortly going to a CCA course..
so hope to get some useful things out of that, especially since we have only recently moved to Casper Vn 9..
( now on 9.81 at this instant )

-- Regards
-- Peter Clarke

mm2270
Legendary Contributor III

@blackholemac @PeterClarke is actually correct. Its been some years now since I was in a CJA course, but its not about learning how to use the API, or at least that's not the focus of it. They may touch on it lightly these days, but he's correct that its really about learning how to configure a JSS in an advanced way, for example, setting up multiple "instances" of a JSS all hosted on the same box, setting up an externally accessible JSS (Limited Access JSS) and other stuff like that. Its an advanced course with a lot of useful material, but you're not going to come away with in-depth knowledge of using the API, I don't think.

Its possible one of JAMF's other courses may address some scripting and API stuff, not really sure. If that's what you're after, you may want to check in with your JAMF account manager and talk to them to see what makes the most sense to attend.

blackholemac
Valued Contributor III

I know they won't cover the API extensively. I'm guessing we may have a minor exercise but that's it. I am taking the course to learn more about the backend of the JSS...how to scale up, how to work with the database, etc. learning the anatomy of the database is good too.

As for scripting...I am fairly good at reuse of scripts and have written a few but am going to study that better on my own this year.