JAMF API - Export Serial Number and Computer Name to CSV file.

dwaterbury
New Contributor III

Hi Everyone,
I'm new to bash scripting and the JAMF API and need some help. I'm trying to export a csv file with the Serial Number (Column A) and Computer Name (Column B) so I can then import it nightly into our inventory system. I use the computer name to assign each computer and iOS device to that user so this will help confirm who has the device in the inventory.

I have figured out how to export the file, but it all seems to be in 1 cell. I'm looking for help on what I should do to break it apart. Thank you in advance.

!/bin/bash

server information

jamfUser="apiread"
jamfPass="apireadpassword"
jamfURL="https://jss.url:8443/JSSResource"

search computers and GET SerialNumbers and ComputerNames exported to csv file

apiData=$(/usr/bin/curl
--user "$jamfUser":"$jamfPass"
--header "Accept: text/xml"
--request GET
$jamfURL/computergroups/id/1 |
/usr/bin/xpath "/computer_group/computers" |
/usr/bin/xmllint --format - |
/usr/bin/grep -E "<serial_number>|<name>" |
/usr/bin/awk -F "<serial_number>|</serial_number>|<name>|</name>" '{ print $4,$2 }' )

echo $apiData --output
echo $apiData > ~/Desktop/computersList.csv

10 REPLIES 10

ryan_ball
Valued Contributor

@dwaterbury Do you want to have serial numbers and computer names for all Macs in the Jamf Pro Server exported into the CSV, or only those in a specific group?

ryan_ball
Valued Contributor

Here is something I've used for something else, but adapted it to get all Macs in the JSS for you. Seems like that is what you might want. You can modify that to only get serial and name, but if it is your inventory system you are trying to populate, you might consider exporting as much information as possible.

#!/bin/bash

jssUser="apiread"
jssPass='apireadpassword'
jssURL="https://jss.url:8443"
outputFile="$HOME/Desktop/computersList.csv"

echo "Downloading list of computer IDs..."
ids+=($(curl -X GET -s -k -u "$jssUser:$jssPass" "$jssURL/JSSResource/computers" | xmllint --format - | awk -F'>|<' '/<id>/{print $3}' | sort -n))

for id in "${ids[@]}"; do
    echo "Obtaining name of computer with ID: $id"
    name=$(curl -X GET -s -k -u "$jssUser:$jssPass" "$jssURL/JSSResource/computers/id/$id" | xmllint --xpath xmllint --xpath '/computer/general/name/text()' - )
    asset=$(curl -X GET -s -k -u "$jssUser:$jssPass" "$jssURL/JSSResource/computers/id/$id" | xmllint --xpath xmllint --xpath '/computer/general/asset_tag/text()' - )
    serial=$(curl -X GET -s -k -u "$jssUser:$jssPass" "$jssURL/JSSResource/computers/id/$id" | xmllint --xpath xmllint --xpath '/computer/general/serial_number/text()' - )
    udid=$(curl -X GET -s -k -u "$jssUser:$jssPass" "$jssURL/JSSResource/computers/id/$id" | xmllint --xpath xmllint --xpath '/computer/general/udid/text()' - )
    if [[ -n "$name" ]]; then
        echo "Success; found computer with ID: $id"
    else
        echo "Error; can't GET name of computer with ID: $id"
        sleep 1
    fi
    echo "$name,$id,$asset,$serial,$udid" >> "$outputFile"
done

exit 0

dwaterbury
New Contributor III

Thanks Ryan for sharing!

Yes, I am trying to get all the computers. Computer Group ID 1 is "All Managed" so it was just a starting point that I was able to get to work.

Thank you for clarifying the array and how to get it to output several data pieces. I was having difficulty understanding how to do that. I had initially been following a JAMF API recording from the Mac Admins Conference 2018 and was having trouble understanding how arrays would be used in this case.

I'll take a deeper look and give it a shot! Thanks again.

Dave W.

DanielMa
New Contributor III

@dwaterbury

I had written this a while back, its since bene archived since the feature is being moved into the product.

The premise is you create a saved search based on criteria and fields you want and it saves it to your desktop

Hope that helps

#!/bin/bash

# # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # 
#
# Copyright (c) 2016, JAMF Software, LLC.  All rights reserved.
#
#       Redistribution and use in source and binary forms, with or without
#       modification, are permitted provided that the following conditions are met:
#               * Redistributions of source code must retain the above copyright
#                 notice, this list of conditions and the following disclaimer.
#               * Redistributions in binary form must reproduce the above copyright
#                 notice, this list of conditions and the following disclaimer in the
#                 documentation and/or other materials provided with the distribution.
#               * Neither the name of the JAMF Software, LLC nor the
#                 names of its contributors may be used to endorse or promote products
#                 derived from this software without specific prior written permission.
#
#       THIS SOFTWARE IS PROVIDED BY JAMF SOFTWARE, LLC "AS IS" AND ANY
#       EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED
#       WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE
#       DISCLAIMED. IN NO EVENT SHALL JAMF SOFTWARE, LLC BE LIABLE FOR ANY
#       DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES
#       (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES;
#       LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND
#       ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
#       (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS
#       SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
#
# # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # 

# # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # 
# 
# This script is designed to be run that will query the JSS and download a csv of an 
# Advanced Computer  search, it is designed to be able to be run from the JSS with script
# variables or have the variables hardcoded, and save a CSV onto that users Desktop 
#
#
# Written by: Daniel MacLaughlin | Professional Services Engineer | Jamf
# with thanks to Russell Kenny, and the Jamf Professional Services team
#
# Created On: April 14th 2018
# 
# version 2.0
#
# Version 1.1 by Daniel MacLaughlin 21st July 2016
# 
#
# # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # 

# # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # 
# VARIABLES
# # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # 


JSSURL="https://jamf.mycompany.com" #Please be sure to include the port if used
JSSUSER="api"
JSSPASS="api"
SEARCHNAME="Encrypted" #write the Advanced search name as it appears in the jamf server ie "VPN Report"


# # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # 
# START APPLICATION
# # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # 



#Lets get the current User so we can store the report on their Desktop
CURRENTUSER=$(ls -l /dev/console | awk '/ / { print $3 }')

# Since we like Data we can also do a timestamp this can be modified as dd.mm.yyyy or dd-mm-yyyy
TIME=$(date +"%d.%m.%Y")

#Checks if there are spaces in the report name and if so change to %20 for the URL
JSSSEARCH=$(echo "$SEARCHNAME" | sed -e 's/ /%20/g')

#Do an API call to see if the Variables are correct
check=$(curl -sfk -H "Accept: application/xml" -u "${JSSUSER}":"${JSSPASS}" "${JSSURL}/JSSResource/advancedcomputersearches/name/${JSSSEARCH}")


    if [[ $check == "" ]]; then

        echo "Uh Oh something went wrong please check your URL, username, password and Search Name"
        exit 1
    fi

#######################################
# Create an XSLT file for the headers for the second XSLT File
#######################################
cat << EOF > /tmp/stylesheet.xslt
<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:output method="text"/>
<xsl:template match="/"> 
    <xsl:for-each select="//display_fields/display_field"> 
        <xsl:value-of select="name"/>
        <xsl:text>&#xa;</xsl:text> 
    </xsl:for-each> 
</xsl:template> 
</xsl:stylesheet>
EOF



#get display fields and remove the space and add a _ to be used for building the stylesheet
curl -sfk -H "Accept: application/xml" -u "${JSSUSER}":"${JSSPASS}" "${JSSURL}/JSSResource/advancedcomputersearches/name/${JSSSEARCH}" | xsltproc /tmp/stylesheet.xslt - | sed -e 's/ /_/g' | sed -e 's/-/_/g' | sort > /tmp/header.txt

#This runs the same command however it has an extra sed command to add a , to the end of each record for csv
curl -sfk -H "Accept: application/xml" -u "${JSSUSER}":"${JSSPASS}" "${JSSURL}/JSSResource/advancedcomputersearches/name/${JSSSEARCH}" | xsltproc /tmp/stylesheet.xslt - | sed -e 's/ /_/g' | sed -e 's/-/_/g' | sed "s/$/,/g" | sort > /tmp/header1.txt

#command to cycle through the csv list and covert to table headers
xargs -n 120 < /tmp/header1.txt > "/Users/$CURRENTUSER/Desktop/${SEARCHNAME}.${TIME}.csv"

#Build the stylesheet with while loop to cycle through the headers.txt and embede the variables in rather than hardcoding
cat << EOF > /tmp/stylesheet.xslt
<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:output method="text"/>
<xsl:template match="/"> 
    <xsl:for-each select="//computers/computer"> 
EOF
while read line;do
    echo "<xsl:value-of select=""$line""/>" >> /tmp/stylesheet.xslt
    echo "<xsl:text>,</xsl:text>" >> /tmp/stylesheet.xslt
done < /tmp/header.txt
cat << EOL >> /tmp/stylesheet.xslt
<xsl:text>&#xa;</xsl:text>
</xsl:for-each> 
</xsl:template> 
</xsl:stylesheet>
EOL

#this grabs the machines and parses through the new xsltproc and appends to the report on the desktop
curl -sfk -H "Accept: application/xml" -u "${JSSUSER}":"${JSSPASS}" "${JSSURL}/JSSResource/advancedcomputersearches/name/${JSSSEARCH}" | sed -e 's/,/./g'| xsltproc /tmp/stylesheet.xslt - >> "/Users/$CURRENTUSER/Desktop/${SEARCHNAME}.${TIME}.csv"

#lets clean up the files
rm "/tmp/header.txt"
rm "/tmp/header1.txt"
rm "/tmp/stylesheet.xslt"

bvondeylen
Contributor II

If you already own FileMaker, this could be done easily in Filemaker. I have a database I can send you which uses the APIs and GETs data from JAMF and places it into a FileMaker Database. This can be scripted to run nightly as well, and can be scripted to export your csv file (or even using the APIs to PUT the data directly into your inventory system so you wouldn't need to do anything).

Let me know if you would like to pursue that direction and I can send you my database template. It does require FileMaker Pro 16 or newer.

mm2270
Legendary Contributor II

Some years back I created a bash script that could take a saved advanced computer search and convert it into a csv file on your local machine using the API. I even created a small "app" (a script wrapped in a Platypus GUI) that could be used for the purpose of pulling down CSVs of saved searches in your Jamf server.
Unfortunately the JSS Resource used for this was eventually labeled as deprecated by Jamf. I'm not even 100% sure it still exists in the latest versions of the product. It was replaced by another resource that doesn't work as well with my script because of the formatting differences in the XML output. I never put in a lot of time to figure out how to modify it to make it work.

That being said, I have used @daniel.maclaughlin's script, and it works well, and just as importantly, works with the current API resource, so my recommendation is to go with that one. Once this gets baked into the product, a la this Feature Request, then it may not be as necessary to use these workarounds and special scripts.

dwaterbury
New Contributor III

@bvondeylen Thank you for offering to share your database template. I'm upgrading us from FM15 to FM16 in early January so I would be interested to see what you have. It might help me get a better understanding overall and work as a stop-gap.

Ultimately, though I would like to use AWS Lambda to perform this task (yet something else to learn) to automatically pull the csv and copy it to our new cloud-hosted inventory system's FTP so they can upload it into their system nightly. I would have preferred another inventory system that has JAMF integrated all-ready, but this is what I have to work with.

Thank you.

luke_michelson
New Contributor III

@daniel.maclaughlin Thanks for share your script. It is exactly what I was looking for.

rstasel
Contributor III

@daniel.maclaughlin Thanks for this! It's nearly perfect! My one question trying to figure it out is how to enquote each of the column values so I can remove the sed that replaces comma's with periods (this only applies to computer model identifier). I can get it partially working by adjusting the xslt "<xsl:text>,</xsl:text>" to "<xsl:text>","</xsl:text>", but that leaves the header unquoted, and I am not seeing a simple way to do that.

The super hacky way would be to swap any commas for some other character, then replace all commas with ",", then swap back commas. But I will admit, I'm not super familiar with xslt's.

Thanks!

rstasel
Contributor III

Managed to get this working in powershell...

$JssUrl = "https://server.example.com:8443"
$Username = "apiaccount"
$PasswordUnencrypted = "supersecret"
$SecureStringParams = @{
    String = $PasswordUnencrypted;
    AsPlainText = $True;
    Force = $True;
}
$Password = ConvertTo-SecureString @SecureStringParams  # Enforcing SecureString type for password

$UserCredential = [PSCredential]::New($Username, $Password)

########### End JSS Init Section ###########

# Returns report from Jamf.
$result = @{}
$requestResponse = Invoke-WebRequest -Uri "$JssUrl/JSSResource/advancedcomputersearches/name/reportname" -Credential $UserCredential -UseBasicParsing

try {
    $root = [Xml]$requestResponse.Content
    $result = $root.advanced_computer_search.computers.computer
}
catch {
    # There were no headers
    $result = $null
}

$result.GetEnumerator() | Export-Csv -Path ".
eport.csv" -Delimiter ',' -NoTypeInformation