05-27-2015 01:45 AM - edited 03-14-2019 02:49 PM
Hi guys,
I'm try to see all agents available in a particular skillgroup, real time. It would be handy to be able to see there status also. Does anyone know an SQL query to run that will show me this?
Thanks
Solved! Go to Solution.
05-28-2015 03:22 AM
yes, try this
05-27-2015 03:22 AM
sample Query:
Select (Select EnterpriseName from Agent a where a.SkillTargetID = asg.SkillTargetID) as AgentName,
(case when asg.AgentState = '0' then 'LoggedOff'
when asg.AgentState = '1' then 'LoggedOn'
when asg.AgentState = '3' then 'NotReady'
when asg.AgentState = '3' then 'Ready'
else 'unknownState'
end) as AgentState
from Agent_Skill_Group_Real_Time asg
where SkillGroupSkillTargetID in (Select SkillTargetID from Skill_Group where EnterpriseName in ('put Skill Group Name' here ))
please refer to db schema guide for the table Agent_Skill_Group_RealTime and define the agentstate accordingly.
you should run the query on realtime db(awdb) and you will get the results.
05-27-2015 06:18 AM
That's perfect thank you, works just as I need it too. If I wanted to add in the agent name and agent ID, is that possible?
05-27-2015 07:21 AM
you want add that in results or you want give it as input?
05-28-2015 01:53 AM
Id like to be able to see the agent names in the same output? Is that possible?
05-28-2015 03:22 AM
yes, try this
05-28-2015 06:59 AM
That's brilliant! Thank you, this will help me very much!
04-23-2019 06:43 AM
Select TOP 100
(Select EnterpriseName from Agent a where a.SkillTargetID = asg.SkillTargetID) as AgentName,
(Select FirstName from Person p where p.PersonID = (Select PersonID from Agent a where a.SkillTargetID = asg.SkillTargetID)) as Firstname,
(Select LastName from Person p where p.PersonID = (Select PersonID from Agent a where a.SkillTargetID = asg.SkillTargetID)) as Lastname,
(Select p.LoginName from Person p where p.PersonID = (Select PersonID from Agent a where a.SkillTargetID = asg.SkillTargetID)) as LoginName,
(case when asg.AgentState = '0' then 'LoggedOff'
when asg.AgentState = '1' then 'LoggedOn'
when asg.AgentState = '2' then 'NotReady'
when asg.AgentState = '3' then 'Ready'
when asg.AgentState = '4' then 'Talking'
when asg.AgentState = '5' then 'Work Not Ready'
when asg.AgentState = '6' then 'Work Ready'
when asg.AgentState = '7' then 'Busy Other'
when asg.AgentState = '8' then 'Reserved'
when asg.AgentState = '9' then 'Unknown'
when asg.AgentState = '10' then 'Calls On Hold'
when asg.AgentState = '11' then 'Active'
when asg.AgentState = '12' then 'Paused'
when asg.AgentState = '13' then 'Interrupted'
else 'Not Specified'
end) as AgentState
from Agent_Skill_Group_Real_Time asg
where SkillGroupSkillTargetID in (Select SkillTargetID from Skill_Group where EnterpriseName in ('Your skill group name here'))
04-23-2019 07:08 AM
Select TOP 100
(Select EnterpriseName from Agent a where a.SkillTargetID = asg.SkillTargetID) as AgentName,
(Select FirstName from Person p where p.PersonID = (Select PersonID from Agent a where a.SkillTargetID = asg.SkillTargetID)) as Firstname,
(Select LastName from Person p where p.PersonID = (Select PersonID from Agent a where a.SkillTargetID = asg.SkillTargetID)) as Lastname,
(Select p.LoginName from Person p where p.PersonID = (Select PersonID from Agent a where a.SkillTargetID = asg.SkillTargetID)) as LoginName,
(case when asg.AgentState = '0' then 'LoggedOff'
when asg.AgentState = '1' then 'LoggedOn'
when asg.AgentState = '2' then 'NotReady'
when asg.AgentState = '3' then 'Ready'
when asg.AgentState = '4' then 'Talking'
when asg.AgentState = '5' then 'Work Not Ready'
when asg.AgentState = '6' then 'Work Ready'
when asg.AgentState = '7' then 'Busy Other'
when asg.AgentState = '8' then 'Reserved'
when asg.AgentState = '9' then 'Unknown'
when asg.AgentState = '10' then 'Calls On Hold'
when asg.AgentState = '11' then 'Active'
when asg.AgentState = '12' then 'Paused'
when asg.AgentState = '13' then 'Interrupted'
else 'Not Specified'
end) as AgentState, Agent.SkillTargetID, Agent.PeripheralNumber, Agent.EnterpriseName, Agent.PeripheralName
from Agent_Skill_Group_Real_Time asg
inner join Agent on Agent.SkillTargetID = asg.SkillTargetID
where SkillGroupSkillTargetID in (Select SkillTargetID from Skill_Group where EnterpriseName in ('Your skill group name here'))
09-11-2019 03:24 PM
if I have the Agent Team Name, how do I pull the Skill Groups they are on and also what script they are configured? Can you help with the SQL?
09-16-2019 12:07 AM
Have a look at my reply in your original post:
https://community.cisco.com/t5/contact-center/query-skill-groups-for-agent-team/m-p/3922903
09-16-2019 05:18 AM
04-23-2019 06:47 AM
Select TOP 100
(Select EnterpriseName from Agent a where a.SkillTargetID = asg.SkillTargetID) as AgentName,
(Select FirstName from Person p where p.PersonID = (Select PersonID from Agent a where a.SkillTargetID = asg.SkillTargetID)) as Firstname,
(Select LastName from Person p where p.PersonID = (Select PersonID from Agent a where a.SkillTargetID = asg.SkillTargetID)) as Lastname,
(Select p.LoginName from Person p where p.PersonID = (Select PersonID from Agent a where a.SkillTargetID = asg.SkillTargetID)) as LoginName,
(case when asg.AgentState = '0' then 'LoggedOff'
when asg.AgentState = '1' then 'LoggedOn'
when asg.AgentState = '2' then 'NotReady'
when asg.AgentState = '3' then 'Ready'
when asg.AgentState = '4' then 'Talking'
when asg.AgentState = '5' then 'Work Not Ready'
when asg.AgentState = '6' then 'Work Ready'
when asg.AgentState = '7' then 'Busy Other'
when asg.AgentState = '8' then 'Reserved'
when asg.AgentState = '9' then 'Unknown'
when asg.AgentState = '10' then 'Calls On Hold'
when asg.AgentState = '11' then 'Active'
when asg.AgentState = '12' then 'Paused'
when asg.AgentState = '13' then 'Interrupted'
else 'Not Specified'
end) as AgentState
from Agent_Skill_Group_Real_Time asg
where SkillGroupSkillTargetID in (Select SkillTargetID from Skill_Group where EnterpriseName in ('Your skill group name here'))
Discover and save your favorite ideas. Come back to expert answers, step-by-step guides, recent topics, and more.
New here? Get started with these tips. How to use Community New member guide