Editing MySQL directly instead of using API.

[jamesreynolds]

I'm guessing that we aren't suppose to edit the MySQL data directly. I tried editing the trigger_manual field in policies and the policy details page (/policies.html?id=*&o=r) shows the new data but the policies list page (/policies.html) doesn't.

I'm trying to change the name of my trigger for 50+ policies and I'm kind of tired of clicking on each policy and then editing the page and saving. Editing the sql was so fast compared but I'm afraid it won't work. If it won't work, is there a similar easy way to do this with the API?

Here's the sql.

update policies set trigger_manual='somename' where trigger_manual='somename2';

[chriscollins]

There are plenty of posts on interacting with the API in whatever language you feel comfortable with so I won't repeat all that but you absolutely can edit policies via the API which is the method you definitely should use.

You'd pull down the policy and replace the xml tag of trigger_other to whatever you need.

[mm2270 · Accepted solution]

I agree doing this via the API is definitely the way to do it. Direct MySQL data manipulation is discouraged in most cases, although there are certainly times when that is necessary.

Just a question, but do you know the policy IDs or exact names of all the policies you need to edit? If not, I wonder if a similar sql query could grab all the policies that use that custom trigger. Maybe something like select policies where trigger_manual='somename' I don't know much about proper mysql queries, so that's just a wild guess. If it does get you the policy IDs, you could use them in a script by dropping all the IDs into an array to loop over. Here's an example that could do this

#!/bin/bash

## Set the API credentials and JSS URL here. API account must have permissions to edit policies
apiUser="apiusername"
apiPass="apipassword"
jssURL="https://your.jss.url"

new_custom_trigger="somename2"

## Edit this array to include the actual policy JSS IDs
POLICYIDS=(
10
20
30
40
)

function update_policy_trigger ()
{

echo "Editing policy ID: $ID"

## Generate a temp xml file with the new custom trigger name
cat << EOD > /tmp/$ID.xml
<policy><general><trigger_other>$new_custom_trigger</trigger_other></general></policy>
EOD

## Upload the new policy data to update the policy
/usr/bin/curl -sfku "${apiUser}:${apiPass}" "${jssURL}/JSSResource/policies/id/$ID" -T /tmp/$ID.xml -X PUT

## Check the exit status of the PUT command
if [ $? == 0 ]; then
    echo -e "
Policy $ID edited successfully"
    rm /tmp/$ID.xml
else
    echo -e "
Policy $ID could not be edited"
    rm /tmp/$ID.xml
fi

}

## Loop over array, running the above function on each ID
while read ID; do
    update_policy_trigger
done < <(printf '%s
' "${POLICYIDS[@]}")

[jamesreynolds]

This might work. I'll try it. Thank you!

[jamesreynolds]

Thank you @mm2270 that worked.