Phantom groups, MySQL queries and Jamf Pro 10.7

On September 13th, Jamf released a new KBase article for Jamf Pro customers who hosted Jamf Pro themselves instead of hosting in Jamf Cloud:

On-Prem Jamf Pro Customers Upgrading to 10.7.0: https://www.jamf.com/jamf-nation/articles/552/on-prem-jamf-pro-customers-upgrading-to-10-7-0

In the KBase article, Jamf provides a couple of MySQL commands to run:

select computer_group_id,criteria,criteria_display from smart_computer_group_criteria where criteria not in (select computer_group_name from computer_groups) and search_field="Computer Group";

select computer_group_id,criteria,criteria_display from smart_computer_group_criteria where binary criteria not in (select binary computer_group_name from computer_groups) and search_field="Computer Group";

If either query returned data, the KBase directs you to contact Jamf Support. This was my output:

	[computername username]# mysql -u root -p
	Password:
	Welcome to the MySQL monitor. Commands end with ; or \g.
	Your MySQL connection id is 2452003
	Server version: 5.7.22-log MySQL Community Server (GPL)
	Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.
	Oracle is a registered trademark of Oracle Corporation and/or its
	affiliates. Other names may be trademarks of their respective
	owners.
	Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
	mysql> use jamfsoftware
	Reading table information for completion of table and column names
	You can turn off this feature to get a quicker startup with -A
	Database changed
	mysql> select computer_group_id,criteria,criteria_display from smart_computer_group_criteria where criteria not in (select computer_group_name from computer_groups) and search_field="Computer Group";
	+——————-+———-+——————+
	| computer_group_id | criteria | criteria_display |
	+——————-+———-+——————+
	| 152 | Testing | Testing |
	| 179 | Testing | Testing |
	| 181 | Testing | Testing |
	| 147 | Testing | Testing |
	| 149 | Testing | Testing |
	| 150 | Testing | Testing |
	| 161 | Testing | Testing |
	| 162 | Testing | Testing |
	| 146 | Testing | Testing |
	| 180 | Testing | Testing |
	| 148 | Testing | Testing |
	| 151 | Testing | Testing |
	| 153 | Testing | Testing |
	| 224 | Testing | Testing |
	| 154 | Testing | Testing |
	| 155 | Testing | Testing |
	| 156 | Testing | Testing |
	| 157 | Testing | Testing |
	| 158 | Testing | Testing |
	| 159 | Testing | Testing |
	| 182 | Testing | Testing |
	| 160 | Testing | Testing |
	+——————-+———-+——————+
	22 rows in set (0.01 sec)
	mysql> select computer_group_id,criteria,criteria_display from smart_computer_group_criteria where binary criteria not in (select binary computer_group_name from computer_groups) and search_field="Computer Group";
	+——————-+———-+——————+
	| computer_group_id | criteria | criteria_display |
	+——————-+———-+——————+
	| 152 | Testing | Testing |
	| 179 | Testing | Testing |
	| 181 | Testing | Testing |
	| 147 | Testing | Testing |
	| 149 | Testing | Testing |
	| 150 | Testing | Testing |
	| 161 | Testing | Testing |
	| 162 | Testing | Testing |
	| 146 | Testing | Testing |
	| 180 | Testing | Testing |
	| 148 | Testing | Testing |
	| 151 | Testing | Testing |
	| 153 | Testing | Testing |
	| 224 | Testing | Testing |
	| 154 | Testing | Testing |
	| 155 | Testing | Testing |
	| 156 | Testing | Testing |
	| 157 | Testing | Testing |
	| 158 | Testing | Testing |
	| 159 | Testing | Testing |
	| 182 | Testing | Testing |
	| 160 | Testing | Testing |
	+——————-+———-+——————+
	22 rows in set (0.02 sec)
	mysql>

view raw

gistfile1.txt

hosted with ❤ by GitHub

What had happened? For more details, please see below the jump.

When I looked at the list, the fact that all of the results returned Testing rang a bell. I’m using JSSImporter, which uses .jss AutoPkg recipes to upload software to my Jamf Pro server. By default, most .jss AutoPkg recipes create smart groups which include the following criteria:

Computer Group: Member of: Testing

However, there is not a static or smart group named Testing on my Jamf Pro server, so that meant the smart groups generated by my .jss AutoPkg recipes contain Computer Group criteria which isn’t valid. This is the issue that Jamf Pro 10.7 has difficulty with and what the MySQL queries were meant to find.

So the fix is to do one of two things:

• Identify the relevant smart groups and either remove or update the criteria.
• Delete the relevant smart groups.

In my case, I’m not actually using the smart groups generated by JSSImporter and my .jss recipes. My own fix for this issue was to do the following:

A. Update the .jss recipes used with my Jamf Pro server to remove the section which creates smart groups.

B. Delete the existing smart groups from my Jamf Pro server.

C. Run a complete AutoPkg run to verify the following:

i. My JSSImporter-created policies now showed no scoping (previously, they were scoped to the smart groups)
ii. The smart groups were not recreated on my Jamf Pro server.

Once I did that and deleted the JSSImporter-created smart groups from my Jamf Pro server, I re-ran the MySQL commands and received the following results back:

	mysql> select computer_group_id,criteria,criteria_display from smart_computer_group_criteria where criteria not in (select computer_group_name from computer_groups) and search_field="Computer Group";
	Empty set (0.00 sec)
	mysql> select computer_group_id,criteria,criteria_display from smart_computer_group_criteria where binary criteria not in (select binary computer_group_name from computer_groups) and search_field="Computer Group";
	Empty set (0.02 sec)
	mysql>

view raw

gistfile1.txt

hosted with ❤ by GitHub

I confirmed with Jamf Support that the output above indicated that the problem was fixed.

Note: I have not edited my publicly-available .jss AutoPkg recipes to remove the section which creates smart groups. If you’re using my .jss recipes and want to remove the section which creates smart groups, please do the following:

1. Make copies of the .jss recipes in question.
2. Assign the copies a new and unique AutoPkg recipe identifier
3. Remove the following section of the .jss recipe: