Smart Groups Queries

SQL queries for Smart Groups

List all the smart groups present in that particular environment:

select * from smartgroup.smartGroup

Current version of the smart group being used can be found in the table generated by the query below:

select * from smartgroup.SmartGroupVersion where sgdm.SmartGroupId = ##

Details of devices associated with a smart group:

select * from SmartGroup.SmartGroupDeviceMap sgdm Inner Join smartgroup.SmartGroupVersion sgv on sgdm.smartGroupId = sgv.smartGroupID
and sgdm.SmartGroupVersion = sgv.SmartGroupVersion where sgdm.SmartGroupId = ##

Relationship between smart group and entity can be found from the table generated by the query mentioned below:

select * from smartGroup.AWEntitySmartGroupAssignmentMap where SmartGroupId = ##

The device ID of device to which the entity is assigned can be obtained from the tables generated by the queries mentioned below:

  • Profile
  • InternalApp
  • ExternalApp
select * from deviceProfile.ProfileAssignment where ProfileID = ##
select * from [deviceApplication].[InternalAppAssignment] where InternalAppID = ##
select * from [deviceApplication].[ExternalAppAssignment] where ExternalAppID = ##

Devices associated with a smart group can be seen in the table generated by the query mentioned below:

select batchEvaluateLogic from smartGroup.smartGroupRuleLogic where smartGroupOverrideID = ##
Note

Apps and books are together considered as an app in SQL.

Troubleshooting Smart Groups

Warning

Do not name a Smart Group with the same name as an existing User Group - this leads to Smart Group being “locked” from deletion

Problem 1:

  • After assigning a smart group to the application and selecting Save & Publish , the app assignment in application grid shows 0/0/0 even though the View Device assignment shows all devices in smart group.

Go to Deployment tab and check the deployment time and if possible change it to 12.00 AM same day. If the time is already set at 12.00 AM, check the admin user’s time zone and compare it with OG time zone. Also check if app wrapping is enabled and whether it is still in progress or not completed.

Problem 2:

  • When a smart group is attempted to be deleted, the following error appears: " Deletion is unsuccessful. This smart group is currently being used in assignment. Please remove the smart group from the assignment, and try again."

Check if the smart group is currently assigned to any app, profiles, compliance etc. Check if there is any user group which is having the same name as the smart group which the smart group uses.

Problem 3:

  • Certain apps are not being shown in App Catalog for some users.

Create a new smart group which contains these users. After that edit the app and add this smart group in exclusion list.