UCCE skill group SQL query

[Stuart Cross]

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

[Chintan Gajjar]

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.

[Stuart Cross]

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?

[Chintan Gajjar]

you want add that in results or you want give it as input?

[Stuart Cross]

Id like to be able to see the agent names in the same output? Is that possible?

[Chintan Gajjar · Accepted solution]

yes, try this

Select 

(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,

(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 your skill here'))

[Stuart Cross]

That's brilliant! Thank you, this will help me very much!

[pchaudhary2000]

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'))

[pchaudhary2000]

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'))

[abhilash2001]

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? 

[piyush aghera]

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

[abhilash2001]

Yes, I saw it, perfect. Thanks again.

[pchaudhary2000]

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'))