cancel
Showing results for 
Search instead for 
Did you mean: 
cancel
Announcements

AMA-CUCM Troubleshooting: Best Practices for Reading Trace Files

1548
Views
0
Helpful
12
Replies
Highlighted
Beginner

UCCE skill group SQL query

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

1 ACCEPTED SOLUTION

Accepted Solutions
Collaborator

yes, try thisSelect (Select

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'))
12 REPLIES 12
Collaborator

sample Query: Select (Select

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.

 

 

Beginner

That's perfect thank you,

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?

Collaborator

you want add that in results

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

Beginner

Id like to be able to see the

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

Collaborator

yes, try thisSelect (Select

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

That's brilliant! Thank you,

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

Beginner

Re: That's brilliant! Thank you,

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

Beginner

Re: Id like to be able to see the


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

Beginner

Re: Id like to be able to see the

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? 

Enthusiast

Re: Id like to be able to see the

Beginner

Re: Id like to be able to see the

Yes, I saw it, perfect. Thanks again.
Beginner

Re: UCCE skill group SQL query

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

CreatePlease to create content
Content for Community-Ad
August's Community Spotlight Awards