cancel
Showing results for 
Search instead for 
Did you mean: 
cancel
576
Views
7
Helpful
10
Replies

need help to check how many agents registered to each PG

kavle
Beginner
Beginner

Hello All,

I am trying to find how many agents registered on PG1 and PG2 and PG3. 

could anyone help me with SQL query that gives us result like how many agents currently registred to PGs

regards,

Kavle   

3 Accepted Solutions

Accepted Solutions

Omar Deen
Spotlight
Spotlight

You can try this...Feel free to add/remove what you can in the SELECT statement. In the CASE statement, you'll want to replace the PG Peripheral IDs with whatever is in your environment. You may also want to update the THEN clause to match whatever works for you. In my case, simply saying PG1, PG2, PG3, and PG4 was sufficient enough.

SELECT P.PersonID,
P.FirstName,
P.LastName,
P.LoginName,
A.EnterpriseName,
A.PeripheralNumber,
A.Description,
A.SupervisorAgent,
A.AgentDeskSettingsID,
AT.EnterpriseName AS AgentTeam,
A.PeripheralID,
CASE A.PeripheralID
WHEN '5002' THEN 'PG1'
WHEN '5004' THEN 'PG2'
WHEN '5007' THEN 'PG3'
WHEN '5010' THEN 'PG4'
ELSE 'UNKNOWN'
END AS PG
FROM Agent A,
Person P,
Agent_Team_Member ATM,
Agent_Team AT
WHERE A.PersonID = P.PersonID
AND ( A.Deleted = 'N' )
AND A.SkillTargetID = ATM.SkillTargetID
AND ATM.AgentTeamID = AT.AgentTeamID
ORDER BY P.LastName

View solution in original post

Omar Deen
Spotlight
Spotlight

One more...

If you're simply looking for a count with no details at all, you can use this. Obviously, update your own PeripheralIDs and what you want it returned AS.

SELECT
COUNT(CASE WHEN PeripheralID = '5002' THEN 1 END) AS PG1,
COUNT(CASE WHEN PeripheralID = '5004' THEN 1 END) AS PG2,
COUNT(CASE WHEN PeripheralID = '5007' THEN 1 END) AS PG3,
COUNT(CASE WHEN PeripheralID = '5010' THEN 1 END) AS PG4
FROM Agent;

View solution in original post

With SQL, the output columns display in the order they appear in the query. So have you tried just reordering them?
You'll have to fix a couple of commas though... See below.

SELECT

A.PeripheralID,
CASE A.PeripheralID
WHEN '5002' THEN 'PG1'
WHEN '5004' THEN 'PG2'
WHEN '5007' THEN 'PG3'
WHEN '5010' THEN 'PG4'
ELSE 'UNKNOWN'
END AS PG, <--- add this comma



P.PersonID,
P.FirstName,
P.LastName,
P.LoginName,
A.EnterpriseName,
A.PeripheralNumber,
A.Description,
A.SupervisorAgent,
A.AgentDeskSettingsID,
AT.EnterpriseName AS AgentTeam, <-- delete this trailing comma

FROM Agent A,
Person P,
Agent_Team_Member ATM,
Agent_Team AT
WHERE A.PersonID = P.PersonID
AND ( A.Deleted = 'N' )
AND A.SkillTargetID = ATM.SkillTargetID
AND ATM.AgentTeamID = AT.AgentTeamID
ORDER BY P.LastName

View solution in original post

10 Replies 10

It's been years since this has come up. The way I do it, which I consider to be the best, is to use Wnidows Performance Counters. Check this link out https://www.cisco.com/c/en/us/td/docs/voice_ip_comm/cust_contact/contact_center/icm_enterprise/icm_enterprise_12_0_1/Configuration/Guide/ucce_b_12-serviceability-guide/ucce_b_1171-serviceability-guide_chapter_0111.html. Specifically the OPC section.

davidmacias_0-1701351657943.png

david

Omar Deen
Spotlight
Spotlight

You can try this...Feel free to add/remove what you can in the SELECT statement. In the CASE statement, you'll want to replace the PG Peripheral IDs with whatever is in your environment. You may also want to update the THEN clause to match whatever works for you. In my case, simply saying PG1, PG2, PG3, and PG4 was sufficient enough.

SELECT P.PersonID,
P.FirstName,
P.LastName,
P.LoginName,
A.EnterpriseName,
A.PeripheralNumber,
A.Description,
A.SupervisorAgent,
A.AgentDeskSettingsID,
AT.EnterpriseName AS AgentTeam,
A.PeripheralID,
CASE A.PeripheralID
WHEN '5002' THEN 'PG1'
WHEN '5004' THEN 'PG2'
WHEN '5007' THEN 'PG3'
WHEN '5010' THEN 'PG4'
ELSE 'UNKNOWN'
END AS PG
FROM Agent A,
Person P,
Agent_Team_Member ATM,
Agent_Team AT
WHERE A.PersonID = P.PersonID
AND ( A.Deleted = 'N' )
AND A.SkillTargetID = ATM.SkillTargetID
AND ATM.AgentTeamID = AT.AgentTeamID
ORDER BY P.LastName

Hello @Omar Deen is there a way to make the "PG" column appear at first in the output after running the above query.

With SQL, the output columns display in the order they appear in the query. So have you tried just reordering them?
You'll have to fix a couple of commas though... See below.

SELECT

A.PeripheralID,
CASE A.PeripheralID
WHEN '5002' THEN 'PG1'
WHEN '5004' THEN 'PG2'
WHEN '5007' THEN 'PG3'
WHEN '5010' THEN 'PG4'
ELSE 'UNKNOWN'
END AS PG, <--- add this comma



P.PersonID,
P.FirstName,
P.LastName,
P.LoginName,
A.EnterpriseName,
A.PeripheralNumber,
A.Description,
A.SupervisorAgent,
A.AgentDeskSettingsID,
AT.EnterpriseName AS AgentTeam, <-- delete this trailing comma

FROM Agent A,
Person P,
Agent_Team_Member ATM,
Agent_Team AT
WHERE A.PersonID = P.PersonID
AND ( A.Deleted = 'N' )
AND A.SkillTargetID = ATM.SkillTargetID
AND ATM.AgentTeamID = AT.AgentTeamID
ORDER BY P.LastName

excellent Ma'am . Thank you !! its working.

Omar Deen
Spotlight
Spotlight

One more...

If you're simply looking for a count with no details at all, you can use this. Obviously, update your own PeripheralIDs and what you want it returned AS.

SELECT
COUNT(CASE WHEN PeripheralID = '5002' THEN 1 END) AS PG1,
COUNT(CASE WHEN PeripheralID = '5004' THEN 1 END) AS PG2,
COUNT(CASE WHEN PeripheralID = '5007' THEN 1 END) AS PG3,
COUNT(CASE WHEN PeripheralID = '5010' THEN 1 END) AS PG4
FROM Agent;

Thnak you very much Omar , yes this is what I was looking for . Can we get the Agent logged in Count  with respective PG and calls in progress on that PG . 

Hi Bill , I was not able to do that it prompted for Admin logins and I will check for the admin logins internally.

kavle
Beginner
Beginner

@bill.king1 @Omar Deen Thank you for always helping me .

on the agent logged on count .. I figured out  ,it was already there on the Peripheral_Realtime table , so I just tweaked a SQL query a little bit and got the desired result .
I am good now sir .

Getting Started

Find answers to your questions by entering keywords or phrases in the Search bar above. New here? Use these resources to familiarize yourself with the community: