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

AMA-CUCM Troubleshooting: Best Practices for Reading Trace Files

288
Views
5
Helpful
12
Replies
Highlighted
Beginner

Query Skill Groups for Agent Team

If I know an Agent Team Name, how can I run an SQL query to pull all the Skill Groups for those agents within that Team? 

2 ACCEPTED SOLUTIONS

Accepted Solutions
Enthusiast

Re: Query Skill Groups for Agent Team

Same query which Bill mentioned, can be altered a bit as below to get what you want.

SELECT
agm.AgentTeamID, ag.*, sg.*
FROM Skill_Group_Member sgm
LEFT OUTER JOIN Agent ag ON sgm.AgentSkillTargetID = ag.SkillTargetID
LEFT OUTER JOIN Skill_Group sg ON sgm.SkillGroupSkillTargetID = sg.SkillTargetID
left outer join Agent_Team_Member agm on ag.SkillTargetID = agm.SkillTargetID
where agm.AgentTeamID = 5002

 

This query can be beautified to include what you want and names instead of IDs.  however if you are going to use as Administrator of the environment, you may not need to change lot of things here.

Enthusiast

Re: Query Skill Groups for Agent Team

Here we go:

 

SELECT DISTINCT P.FirstName, P.LastName, P.LoginName, MAX(AED.LoginDateTime) AS LastLoginTime, Agent_Team_Member.AgentTeamID, Agent_Team.EnterpriseName
FROM Agent A, Agent_Event_Detail AED, Person P, Agent_Team_Member, Agent_Team
WHERE A.SkillTargetID = AED.SkillTargetID
and Agent_Team_Member.AgentTeamID = Agent_Team.AgentTeamID
and A.SkillTargetID = Agent_Team_Member.SkillTargetID
AND A.PersonID = P.PersonID
GROUP BY P.FirstName, P.LastName, P.LoginName, Agent_Team_Member.AgentTeamID, Agent_Team.EnterpriseName
ORDER BY LastLoginTime desc

12 REPLIES 12
Collaborator

Re: Query Skill Groups for Agent Team

If I make time for this I can crank out the query, but here are the different views you need to join.

Agent_Team, Agent_Team_Member, Agent, Skill_Group, and Skill_Group_Member.

 

david

Beginner

Re: Query Skill Groups for Agent Team

Thanks David, let me know if you can find some time to construct the query.

Contributor

Re: Query Skill Groups for Agent Team

Abhilash2001, did you search the previous posts?

 

Isn't this what you want?

https://community.cisco.com/t5/contact-center/export-skill-group-members-list-for-ucce-8-5/td-p/2131524

Beginner

Re: Query Skill Groups for Agent Team

No this is not what I was looking for. I only have Agent Team name, I need a query to pull the skill group using the Agent Team Name. 

Enthusiast

Re: Query Skill Groups for Agent Team

Same query which Bill mentioned, can be altered a bit as below to get what you want.

SELECT
agm.AgentTeamID, ag.*, sg.*
FROM Skill_Group_Member sgm
LEFT OUTER JOIN Agent ag ON sgm.AgentSkillTargetID = ag.SkillTargetID
LEFT OUTER JOIN Skill_Group sg ON sgm.SkillGroupSkillTargetID = sg.SkillTargetID
left outer join Agent_Team_Member agm on ag.SkillTargetID = agm.SkillTargetID
where agm.AgentTeamID = 5002

 

This query can be beautified to include what you want and names instead of IDs.  however if you are going to use as Administrator of the environment, you may not need to change lot of things here.

Beginner

Re: Query Skill Groups for Agent Team

Perfect, thank you very much Piyush. 

Enthusiast

Re: Query Skill Groups for Agent Team

Welcome :-)

 

Please rate useful posts.

Beginner

Re: Query Skill Groups for Agent Team

Hi Piyush, can you help me with one more item? I am trying to pull a query of list of all Agents from all Agent Teams who has logged in recently. I have the two below queries for the two different functions, somehow we need to combine them to pull my query. 

 

select *

FROM Agent_Team, Agent_Team_Member, Skill_Group_Member

INNER JOIN Agent ON Skill_Group_Member.AgentSkillTargetID = Agent.SkillTargetID
INNER JOIN Person ON Agent.PersonID = Person.PersonID

WHERE Agent.SkillTargetID = Agent_Team_Member.SkillTargetID

AND Agent_Team_Member.AgentTeamID = Agent_Team.AgentTeamID

order by Agent_Team.AgentTeamID

----------------------------------------------

 

SELECT DISTINCT P.FirstName, P.LastName, P.LoginName, MAX(AED.LoginDateTime) AS LastLoginTime
FROM Agent A, Agent_Event_Detail AED, Person P
WHERE A.SkillTargetID = AED.SkillTargetID
AND A.PersonID = P.PersonID
GROUP BY P.FirstName, P.LastName, P.LoginName
ORDER BY LastLoginTime ASC

Enthusiast

Re: Query Skill Groups for Agent Team

I think the best way to do this is to store results of one of the query to a temp table and then perform inner join with results of the other query to find out common data between two tables, in this case agents who have recently logged in.

Enthusiast

Re: Query Skill Groups for Agent Team

I looked at both the queries now and i think your second query itself is enough to find out all the agents who have recently logged in by looking at LastLoginTime.

Do you want to add Agent Team name also in this query ?

Beginner

Re: Query Skill Groups for Agent Team

Yes, can we add the Agent Team in that query? 

Enthusiast

Re: Query Skill Groups for Agent Team

Here we go:

 

SELECT DISTINCT P.FirstName, P.LastName, P.LoginName, MAX(AED.LoginDateTime) AS LastLoginTime, Agent_Team_Member.AgentTeamID, Agent_Team.EnterpriseName
FROM Agent A, Agent_Event_Detail AED, Person P, Agent_Team_Member, Agent_Team
WHERE A.SkillTargetID = AED.SkillTargetID
and Agent_Team_Member.AgentTeamID = Agent_Team.AgentTeamID
and A.SkillTargetID = Agent_Team_Member.SkillTargetID
AND A.PersonID = P.PersonID
GROUP BY P.FirstName, P.LastName, P.LoginName, Agent_Team_Member.AgentTeamID, Agent_Team.EnterpriseName
ORDER BY LastLoginTime desc

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