SQL do's and don'ts with JSS

GaToRAiD
Contributor II

I'm having some trouble with a script taking too long while using curl, want to know if it would be more efficient to use sql insert instead. Here is a breakdown of what the script does.

Queries SQL for computer ID's and puts them into an array. Then for each id it will do another sql query for the user of that computer. Then it checks it against ad for group membership. Once this is done, it will curl an extension attribute depending on the output of the dscl.

Now I've been looking into trying to convert my curl to an sql insert statement, but first I want to know if I should even be doing so. Since messing with the database directly can be dangerous. But my thought is, that when I do a curl, it's just sending the info to the front end of the database which is the web server, then it's being put into the database. So can't I just take out the middle man and use an Insert statement?

Any opinions on doing this would help. Just want to see if anyone else has done this.

6 REPLIES 6

bentoms
Release Candidate Programs Tester

JAMF do not recommend touching the SQL directly. That's theirs to play with, & some think you have working now.. May be broken in an update.

Have you looked at trying the same via re API? That's the approved method.

were_wulff
Valued Contributor II

@GaToRAiD

I'd echo what @bentoms said there, though if you do want to play around with SQL queries and the like, you could certainly spin up a test environment using your production JSS; that way, if something does break, it's just in a test environment and isn't going to affect your production database.

It's still not something we recommend doing in a production database, however, as it has a pretty good potential to mess the database up to the point of needing to restore a backup if data is inserted incorrectly or inserted in a way that may make it unreadable by the JSS itself.
As @bentoms mentioned, there's also the high possibility of something that worked at one point not working after an update, and that can be difficult to unwind and fix as well if it was written directly to the database outside of the JSS or the API.

It would be safer if we stuck to using the API instead of directly trying to write to the database.

There are a lot of users on JAMF Nation that are skilled with using the API for various things, and we also have Expanded Services that can help you out as well as what you're looking for appears to go outside the scope of regular support.
Your Technical Account Manager can get you in touch with our Expanded Services team if that's the route you'd like to go.

Thanks!
Amanda Wulff
JAMF Software Support

GaToRAiD
Contributor II

Well, I'm currently using the API's while doing a curl. Just seems like it would be better to go directly to the source instead of hitting up the middle man. But I do agree messing with the database directly is dangerous, I just wanted to know other people's inputs on the thought.

mike_paul
Contributor III
Contributor III

API provides the safety net that it only accepts content correctly formatted while you can put anything directly into the DB and possibly break functionality.

But for clarification, you are doing a GET of all computers to get their JSS ID, then doing a GET of each individual JSS ID to find the associated user, than you do a local AD lookup to find membership and then you do a PUT with that result to an Extension Attribute, is this correct?

But the issue as you mention is that you are seeing longer query times than desired.

Would doing a single curl with a variation of this work to at least remove one of the lookups: curl -v -u jssuseraccount:password https://jss.company.com:8443/JSSResource/computers/subset/Basic -X GET -o ~/Desktop/computers.xml

This will pull down the ID, computer name, managed status, username, model, building, mac address, udid and serial.

Or would this be something possible with just an extension attribute that each computer ran their own AD lookup based off the current user and submit that via the standard Extension attribute method, to cut out all the queries to the DB in general?

GaToRAiD
Contributor II

Ok, so you can see what I'm doing, here is my script.

#!/bin/bash
#
#Casper Active Directory User Group Membership Query
#
#
#Variables

mysql_client_path='/usr/local/mysql/bin/mysql'
mysql_database='jamfsoftware'
mysql_host='localhost'

xmlUpdateEAAdmin="<?xml version="1.0" encoding="ISO-8859-1" ?>
    <computer>
        <extension_attributes>
            <attribute>
                <name>MacAdmin</name>
                <value>Admin</value>
            </attribute>
        </extension_attributes>
    </computer>"
xmlUpdateEANonAdmin="<?xml version="1.0" encoding="ISO-8859-1" ?>
    <computer>
        <extension_attributes>
            <attribute>
                <name>MacAdmin</name>
                <value>Not Admin</value>
            </attribute>
        </extension_attributes>
    </computer>"


mysql_user='XXXXXXX'
mysql_pass='XXXXXX'

jssUser=XXXXX
jssPass=XXXXX
jssHost=https://localhost:8443/


#Functions

UpdateCasperExtAttribAdmin()
{
theJSSresponse=$( /usr/bin/curl 
--header "Content-Type: text/xml; charset=utf-8" 
--data "${xmlUpdateEAAdmin}" 
--request PUT 
--connect-timeout 5 
--max-time 10 
--user ${jssUser}:${jssPass} 
--insecure 
${jssHost}JSSResource/computers/"id"/$1 2> /dev/null )
}

UpdateCasperExtAttribNonAdmin(){
theJSSresponse=$( /usr/bin/curl 
--header "Content-Type: text/xml; charset=utf-8" 
--data "${xmlUpdateEANonAdmin}" 
--request PUT 
--connect-timeout 5 
--max-time 10 
--user ${jssUser}:${jssPass} 
--insecure 
${jssHost}JSSResource/computers/"id"/$1 2> /dev/null )
}

# Beginning of Procedure

#SQL Query to pull computer ID's
getSQL='select computer_id from computers_denormalized group by computer_id;'
sqlData=$( $mysql_client_path -h$mysql_host -D$mysql_database -u$mysql_user -p$mysql_pass -e "$getSQL" 2< /dev/null)

#Convert Return of SQL Query Into Array
ComputerID=($sqlData)
read -a ComputerID <<<$sqlData

#Process each ID
for ID in "${ComputerID[@]}"; do
    #Remove First Line "computer_id"
    if [ $ID == "computer_id" ]
    then
    continue
    else
    #Pull User based on computer ID
    userSQL='select username from computers_denormalized where computer_id='$ID';'
    username=$( $mysql_client_path -h$mysql_host -D$mysql_database -u$mysql_user -p$mysql_pass -e "$userSQL" 2< /dev/null)
    #Check user membership with AD
    membership=`dscl /Active Directory/BFI/All Domains -read /Users/$username | grep "NonMacAdmins"`
        Echo "Membership: " $membership "User: " $username
        #if [ -z "$membership" ]
        #   then
        #   #Forward to function to update casper
        #   UpdateCasperExtAttribAdmin $ID
        #   else
        #   #Forward to function to update casper
        #   UpdateCasperExtAttribNonAdmin $ID
        #fi
    fi
done

#Check to make sure there was not an error when Querying SQL
if [[ $? -ne 0 ]]; then
    echo "mysql error"
    exit
fi

Bascially, the curl back to jss is what is taking so long. The other processes are relatively quick. The whole process takes about 5 mins(I had a stop watch running). This is why I wanted to be able to use SQL Insert.

freddie_cox
Contributor III

@GaToRAiD][/url][/url - I have seen similar issues with the JSS taking a long time to PUT. I have found that using the HTTP port speeds it up quiet a bit. Obviously you have to judge your risk since you're sending credentials over unencrypted. We're also clustered so I PUT to a single web app as my JSS URL rather than the actual JSS URL and that seemed to take the Load Balancer out of the equation which seemed to speed up the transaction.