3

Is there a way to find the number of users in each queue in Salesforce Org?

Context: We have 5000 queues in our system. We believe most of them are empty or assigned with very less number of users. So, if we have a way to come up with the number of users assigned to each, will be able to take an informed decision.

Got an answer that involves exporting group and group member data and then using VLOOKUP. However didn't end up with desired results. Any other answers or detiled approach is appreciated!

3

Try this:

SELECT COUNT(UserOrGroupId),
       Group.Name 
FROM GroupMember 
WHERE GroupId IN 
             (SELECT QueueId FROM QueueSobject) 
GROUP BY GroupId,Group.Name

Verified this in my org and it seems to work.

  • AggregateResult doesn't support pagination, so there's a hard limit of 2,000 rows (groups). You can still work around this, but pagination will be manual, such as querying all 5,000 queues and then breaking up the Id values into groups of 2,000. – sfdcfox Feb 21 '17 at 21:10
  • @sfdcfox I did not think about that. You mean queryMore right when you say pagination? – javanoob Feb 21 '17 at 22:26
  • 1
    Yeah, AggregateResult doesn't support queryMore/QueryLocator/database cursors. It doesn't change your answer (much), it's more of a caveat to be aware of. – sfdcfox Feb 21 '17 at 22:27
1

The following will bring you the count of users only for each queue:

SELECT GroupId, Group.Name, COUNT(UserOrGroupId)
FROM GroupMember
WHERE UserOrGroupId NOT IN (SELECT Id FROM Group WHERE Type = 'Queue')
 AND GroupId IN (SELECT Id FROM Group WHERE Type = 'Queue')
GROUP BY Group.Name, GroupId

*It might be a better way to exclude queues, members of queues than what I have WHERE UserOrGroupId NOT IN (SELECT Id FROM Group WHERE Type = 'Queue') - anyone?

UPDATE: To be fair, you would also have to run another query to get the list of queues, having other queues as a members:

SELECT GroupId, Group.Name, UserOrGroupId
    FROM GroupMember
    WHERE UserOrGroupId IN (SELECT Id FROM Group WHERE Type = 'Queue')
     AND GroupId IN (SELECT Id FROM Group WHERE Type = 'Queue')

and then add the number of users from each queue-member (e.g. by calculations using VLOOKUP and SUMIF in Excel). Note: Pay attention to the members of queue-members, as it could have in its turn another queue(s) as a member(s). Hint: You can calculate number of queue-members for each queue by taking out 'NOT' from the first criteria in WHERE clause, and you could also add (optional) HAVING COUNT(UserOrGroupId) > 0 to filter out those queues that don't have any queue-members.

UPDATE2: Considering your 5000 queues count, and @sfdcfox's comment for another answer where he mentioned a hard limit of 2,000 rows (groups) on AggregateResult, you can just add another criteria into subquery to extract next 2000 records by using the last GroupId from each chank: ...WHERE Type = 'Queue' AND Id > 'lastReturnedId'

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.