SQL do's and don'ts with JSS

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.

@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

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.

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?

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.

@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.