Printing all QueueMetrics users and agents in one go

It is sometimes useful to get a tabular output of:

  • all users in a QueueMetrics instance that are currently enabled and not holding the masterkey (as no users should hold the msterkey in production)

  • their current class and extra keys

  • their configured agent aliases, if any

  • their current location, supervisor and agent group

  • the set of queues they are supposed to work on

so that you can see at a glance the current state and whether there are any misconfigurations.

The following query does just that:

SELECT U.login AS LOGIN,
  U.real_name AS NAME,
  C.nome_classe as CLASS,
  C.chiavi as CLASS_KEYS,
  U.chiavi_utente as USER_KEYS,
  U.ultimo_logon as LAST_LOGON,
  AG.descr_agente as AGENT_CODE,
  AG.aliases as FRIENDLY_NAMES,
  LOC.loc_name as LOCATION,
  SUP.login as SUPERVISOR,
  GRO.group_name as AGENT_GROUP,
  ( SELECT group_concat(CP.nome_coda SEPARATOR ', ') AS QUEUES
       FROM code_possibili CP
     WHERE CP.agenti_membri LIKE concat( '%', U.login, '%')
        OR CP.agenti_spilloff_1 LIKE concat( '%', U.login, '%')
        OR CP.agenti_spilloff_2 LIKE concat( '%', U.login, '%')
  ) AS QUEUES

FROM arch_users U
JOIN arch_classes C on C.id_classe = U.classe
LEFT JOIN agenti_noti AG on  AG.nome_agente = U.login
LEFT JOIN locations LOC on LOC.id_location = AG.location
LEFT JOIN arch_users SUP on SUP.user_id = AG.supervised_by
LEFT JOIN agent_groups GRO on GRO.id_group = AG.group_by
WHERE U.abilitato = 1
  AND U.masterkey = 0
ORDER BY U.login ASC

By running it agains the QueueMetrics database from any MySQL shell, you will get a complete report you can export or reprocess as you see fit.