This is an old revision of the document!

UCCE Agent SQL

Agent <> Teams
SELECT     
AgentSkillTargetID = Agent.SkillTargetID, 
TeamID = isNull(Agent_Team_Member.AgentTeamID,'0'), 
PersonalID = Agent.PersonID, 
AgentPeripheralNumber = Agent.PeripheralNumber,
FirstName = Person.FirstName, 
LastName = Person.LastName, 
LoginName = Person.LoginName,
TeamName = isNull(Agent_Team.EnterpriseName,'No Team Assigned')
 
FROM Agent
LEFT JOIN Agent_Team_Member ON Agent.SkillTargetID = Agent_Team_Member.SkillTargetID
LEFT JOIN Agent_Team ON Agent_Team_Member.AgentTeamID = Agent_Team.AgentTeamID
LEFT JOIN Person ON Agent.PersonID = Person.PersonID
 
-- WHERE Agent.PeripheralNumber = '55510001'
 
ORDER BY Person.FirstName

Agent Logged in and Other Agent Stats

SET ARITHABORT OFF SET ANSI_WARNINGS OFF SET NOCOUNT ON 
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
 
SELECT 
	  --AI.[DateTime]
      --,AI.[SkillTargetID]
	  P.[FirstName]
	  ,P.[LastName]
      ,LoggedOnTime = CONVERT(VARCHAR, DATEADD(SECOND,SUM(AI.[LoggedOnTime]),0),108)
	  ,LoggedOnTimeSec = SUM(AI.[LoggedOnTime])
	  ,LoggedOnTimeDD = CONCAT((SUM(AI.[LoggedOnTime])/3600/24),':',(SUM(AI.[LoggedOnTime])/3600%24),':',(SUM(AI.[LoggedOnTime])/60%60))
      ,AvailTime = CONVERT(VARCHAR, DATEADD(SECOND,SUM(AI.[AvailTime]),0),108)
      ,NotReadyTime = CONVERT(VARCHAR, DATEADD(SECOND,SUM(AI.[NotReadyTime]),0),108)
      ,TalkOtherTime = CONVERT(VARCHAR, DATEADD(SECOND,SUM(AI.[TalkOtherTime]),0),108)
      ,AI.[Extension]
	  ,TeamName = isNull(AT.EnterpriseName,'No Team Assigned')
  FROM 
  [Agent_Interval] AI
  JOIN [Agent] A ON AI.SkillTargetID = A.SkillTargetID  
  JOIN [Person] P ON A.PersonID = P.PersonID 
  LEFT JOIN Agent_Team_Member ATM ON A.SkillTargetID = ATM.SkillTargetID
  LEFT JOIN Agent_Team AT ON ATM.AgentTeamID = AT.AgentTeamID
 
  WHERE
  (AI.DateTime >= '2019-07-11 00:00:00' AND AI.DateTime < '2019-07-15 00:00:00')
  --(AI.DateTime > '2019-07-11 00:00:00' AND AI.DateTime < '2019-07-12 00:00:00')
 
  GROUP BY 
  P.[FirstName]
 ,P.[LastName]
 ,AI.[Extension]
 ,AT.EnterpriseName
 ORDER BY SUM(AI.[LoggedOnTime]) DESC

Bull Update Agents with Description so it matches Team Names

Note: I am unable to match the Bulk Edit order, which is alphabetical, but case sensitive, with lowercase first for each letter.

Agent with Team names in alpbetical Order (not the same as bulk edit order)

SELECT     
Agent.SkillTargetID, 
--Agent_Team_Member.AgentTeamID, 
--Agent.PersonID, 
Person.FirstName, 
Person.LastName, 
Person.LoginName,
Agent.EnterpriseName AS AgentEnterpriseName,
Agent_Team.EnterpriseName AS AgentTeamName

FROM Agent
LEFT JOIN Agent_Team_Member ON Agent.SkillTargetID = Agent_Team_Member.SkillTargetID
LEFT JOIN Agent_Team ON Agent_Team_Member.AgentTeamID = Agent_Team.AgentTeamID
LEFT JOIN Person ON Agent.PersonID = Person.PersonID

WHERE Agent.Deleted ='N'

ORDER BY Agent.EnterpriseName COLLATE Latin1_General_CS_AS

Steps

  • Using BulkEdit Agent Tool do a complete Export (all Headers) - keep this copy for Backup - do not edit.
  • Run SQL Agent<> Team Export above against the AW database
  • Sort the SQL Agent Team Export by Agent ID
  • Sort the Agent Bulk Export by Agent ID

WARNING - If you copy directly to Excel, it changes Peripheral Number values to Numbers, removing leading zeros. Import to Excel and convert all fields to Text.

Both should contain same agents in SAME order

  • Copy and pasts Team Column from SQL Export into Bulk Edit Export into The Description Field
  • Remove DateTimeStamp Field from the Bulk Edit export
  • Save as txt file as same format as original bulk edit
  • Import into bulk edit tool, review and save.
  • Backout - reimport original bulk export

Agent Event Details (Login, Ready, Not Ready Logout) Report

SET ARITHABORT OFF SET ANSI_WARNINGS OFF SET NOCOUNT ON 
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
 
SELECT
DateTime =  AED.DateTime,
MRDomainID = AED.MRDomainID,
MRDomainName = MRD.EnterpriseName,
AgentSkillTargetID = A.SkillTargetID,
AgentPeripheralNumber = A.PeripheralNumber,
AgentName = CONCAT(P.LastName,', ',P.FirstName),
FirstName = P.FirstName,
LastName = P.LastName,
LoginName = P.LoginName,
Event = AED.Event,
EventString = CASE [Event]
 WHEN '1' THEN 'Login'
 WHEN '2' THEN 'Logout'
 WHEN '3' THEN 'Not Ready'
 ELSE CAST(Event AS VARCHAR(200))
 END,
Duration = AED.Duration,
ReasonCode = AED.ReasonCode,
ResoncodeString = CASE (AED.ReasonCode)
WHEN '-1'    THEN 'System - Agent reinitialized due to peripheral restart'
WHEN '-2'    THEN 'PG reset the agent - PG Failure'
WHEN '-3'    THEN 'Administrator modified the agents extension while the agent was logged in'
WHEN '999' THEN 'Finesse Supervisor - Force Agent State Change'
WHEN '50002' THEN 'System - CTI Failure - Agent Logged Out Incorrectly?'
WHEN '50003' THEN 'System - The agent was logged out because CUCM reported the agents device as out of service'
WHEN '50004' THEN 'System - The agent was logged out due to agent inactivity as configured in agent desk settings'
WHEN '50005' THEN 'System - The agent''s state was changed to Not Ready because the agent either answered or made a non-ACD call.'
WHEN '50010' THEN 'System - The agent did not receive multiple consecutive calls routed to him/her. The system makes the agent Not Ready automatically so that additional calls are not routed to the agent. By default, the number of consecutive calls missed before the agent is made Not Ready is 2.'
WHEN '50020' THEN 'System - For reskilling operations on active agents, the agent was logged out of the skill group due to a reskilling operation that removed the skill group assignment to that agent. This reason code is used in the Agent_Event_Detail record and the Agent_Skill_Group_Logout record to identify the skill group the agent was removed from (due to the reskilling operation).'
WHEN '50040' THEN 'System - The mobile agent was logged out because the call failed.'
WHEN '50042' THEN 'System - The mobile agent was logged out because the phone line disconnected when using nailed connection mode.'
WHEN '51004' THEN 'System - This reason codes applies if an agent logs onto an extension which already has a call or if the agent is on a call when the PG restarts.'
WHEN '50041' THEN 'System - The agent''s state was changed to Not Ready because the call fails when the agent''s phone line rings busy.'
WHEN '32767' THEN 'System - The agent''s state was changed to Not Ready because the agent did not answer a call and the call was redirected to a different agent or skill group.'
WHEN '20001' THEN 'System - The agent''s state was changed to Not Ready and the agent was forcibly logged out.'
WHEN '20002' THEN 'System - This is the normal logout reason code condition from Not Ready.'
WHEN '20003' THEN 'System - If the agent is not in Not Ready state, a request is made to place the agent in Not Ready state and then a logout request is made to log the agent out.'
 
ELSE RC.ReasonText
END,
EnterpriseName = A.EnterpriseName,
ReasonText = RC.ReasonText
 
 
FROM Agent_Event_Detail AED
INNER JOIN Media_Routing_Domain MRD ON (MRD.MRDomainID=AED.MRDomainID)
INNER JOIN Agent A ON (A.SkillTargetID=AED.SkillTargetID)
INNER JOIN Person P ON (A.PersonID=P.PersonID)
LEFT JOIN Reason_Code RC ON (RC.ReasonCode=AED.ReasonCode)
 
 
--WHERE DateTime > '2018-08-09 00:00:00'
--AND DateTime < '2018-08-09 23:00:00'
 
WHERE DateTime > GetDate()-1

CUIC Agent System Logout Report (any logout code that is not zero)

agent_system_logout.zip 

SET ARITHABORT OFF SET ANSI_WARNINGS OFF SET NOCOUNT ON 
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

SELECT
DateTime =  AED.DateTime,
AgentSkillTargetID = A.SkillTargetID,
AgentName = CONCAT(P.LastName,', ',P.FirstName),
FirstName = P.FirstName,
LastName = P.LastName,
LoginName = P.LoginName,
Event = AED.Event,
EventString = CASE [Event]
 WHEN '1' THEN 'Login'
 WHEN '2' THEN 'Logout'
 WHEN '3' THEN 'Not Ready'
 ELSE CAST(Event AS Varchar(200))
 END,
Duration = AED.Duration,
ReasonCode = AED.ReasonCode,
ResoncodeString = CASE (AED.ReasonCode)
WHEN '-1'    THEN 'System - Agent reinitialized due to peripheral restart'
WHEN '-2'    THEN 'PG reset the agent - PG Failure'
WHEN '-3'    THEN 'Administrator modified the agents extension while the agent was logged in'
WHEN '999' THEN 'Finesse Supervisor - Force Agent State Change'
WHEN '50002' THEN 'System - CTI Failure - Agent Logged Out Incorrectly?'
WHEN '50003' THEN 'System - The agent was logged out because CUCM reported the agents device as out of service'
WHEN '50004' THEN 'System - The agent was logged out due to agent inactivity as configured in agent desk settings'
WHEN '50005' THEN 'System - The agent''s state was changed to Not Ready because the agent either answered or made a non-ACD call.'
WHEN '50010' THEN 'System - The agent did not receive multiple consecutive calls routed to him/her. The system makes the agent Not Ready automatically so that additional calls are not routed to the agent. By default, the number of consecutive calls missed before the agent is made Not Ready is 2.'
WHEN '50020' THEN 'System - For reskilling operations on active agents, the agent was logged out of the skill group due to a reskilling operation that removed the skill group assignment to that agent. This reason code is used in the Agent_Event_Detail record and the Agent_Skill_Group_Logout record to identify the skill group the agent was removed from (due to the reskilling operation).'
WHEN '50040' THEN 'System - The mobile agent was logged out because the call failed.'
WHEN '50042' THEN 'System - The mobile agent was logged out because the phone line disconnected when using nailed connection mode.'
WHEN '51004' THEN 'System - This reason codes applies if an agent logs onto an extension which already has a call or if the agent is on a call when the PG restarts.'
WHEN '50041' THEN 'System - The agent''s state was changed to Not Ready because the call fails when the agent''s phone line rings busy.'
WHEN '32767' THEN 'System - The agent''s state was changed to Not Ready because the agent did not answer a call and the call was redirected to a different agent or skill group.'
WHEN '20001' THEN 'System - The agent''s state was changed to Not Ready and the agent was forcibly logged out.'
WHEN '20002' THEN 'System - This is the normal logout reason code condition from Not Ready.'
WHEN '20003' THEN 'System - If the agent is not in Not Ready state, a request is made to place the agent in Not Ready state and then a logout request is made to log the agent out.'

ELSE RC.ReasonText
END,
EnterpriseName = A.EnterpriseName,
ReasonText = RC.ReasonText


FROM Agent_Event_Detail AED
INNER JOIN Agent A ON (A.SkillTargetID=AED.SkillTargetID)
INNER JOIN Person P ON (A.PersonID=P.PersonID)
LEFT JOIN Reason_Code RC ON (RC.ReasonCode=AED.ReasonCode)

WHERE Event='2'

All Agents Real Time State with Team Details

SELECT     
Agent_Real_Time.SkillTargetID,
Media_Routing_Domain.MRDomainID,
Media_Routing_Domain.EnterpriseName,
Agent_Real_Time.DateTimeLogin AS LoginTime, 
Agent_Real_Time.DateTimeLastStateChange AS LastStateChangeTime, 
 
Agent_Real_Time.AgentState AS AgentState, 
 
CASE Agent_Real_Time.AgentState 
WHEN '0' THEN 'Logged Out'
WHEN '1' THEN 'Logged On'
WHEN '2' THEN 'Not Ready'
WHEN '3' THEN 'Ready'
WHEN '4' THEN 'Talking'
WHEN '5' THEN 'Work Not Ready'
WHEN '6' THEN 'Work Ready'
WHEN '7' THEN 'Busy Other'
WHEN '8' THEN 'Reserved'
WHEN '9' THEN 'Unknown-9'
WHEN '10' THEN 'Calls On Hold'
WHEN '11' THEN 'Active'
WHEN '12' THEN 'Paused'
WHEN '13' THEN 'Interrupted'
WHEN '14' THEN 'Not Active'
 
ELSE 'unknown'
END AS AgentStateName,
 
Person.FirstName AS FirstName, 
Person.LastName AS LastName,
Person.LoginName AS LoginName,
Agent.EnterpriseName AS EnterpriseName,
Agent_Real_Time.Extension AS Extension,
AgentTeamName = IsNull(Agent_Team.EnterpriseName,'No Team Assigned')
 
FROM            
Agent_Team_Member 
INNER JOIN
Agent_Team ON Agent_Team_Member.AgentTeamID = Agent_Team.AgentTeamID RIGHT OUTER JOIN
Agent INNER JOIN
Agent_Real_Time ON Agent.SkillTargetID = Agent_Real_Time.SkillTargetID INNER JOIN
Person ON Agent.PersonID = Person.PersonID ON Agent_Team_Member.SkillTargetID = Agent_Real_Time.SkillTargetID INNER JOIN 
Media_Routing_Domain ON Agent_Real_Time.MRDomainID = Media_Routing_Domain.MRDomainID
CUIC Report

agent_real_time_-_all_agents.zip

Agent Real Time Enterprise SkillGroup

SET ARITHABORT OFF SET ANSI_WARNINGS OFF SET NOCOUNT ON 
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED


SELECT        
ART.AgentState, 
Case ART.AgentState 
WHEN '0' THEN 'Logged Out'
WHEN '1' THEN 'Logged On'
WHEN '2' THEN 'Not Ready'
WHEN '3' THEN 'Ready'
WHEN '4' THEN 'Talking'
WHEN '5' THEN 'Work Not Ready'
WHEN '6' THEN 'Work Ready'
WHEN '7' THEN 'Busy Other'
WHEN '8' THEN 'Reserved'
WHEN '9' THEN 'Unknown-9'
WHEN '10' THEN 'Calls On Hold'
WHEN '11' THEN 'Active'
WHEN '12' THEN 'Paused'
WHEN '13' THEN 'Interrupted'
WHEN '14' THEN 'Not Active'
ELSE 'unknown'
END AS AgentStateName,

Count(DISTINCT ART.SkillTargetID) As Count,
ESG.EnterpriseName

INTO #tempESGAgentState

FROM            
Agent_Real_Time ART INNER JOIN
Skill_Group_Member ON ART.SkillTargetID = Skill_Group_Member.AgentSkillTargetID INNER JOIN
Skill_Group ON Skill_Group_Member.SkillGroupSkillTargetID = Skill_Group.SkillTargetID INNER JOIN
Enterprise_Skill_Group_Member ON Skill_Group.SkillTargetID = Enterprise_Skill_Group_Member.SkillTargetID INNER JOIN
Enterprise_Skill_Group ESG ON Enterprise_Skill_Group_Member.EnterpriseSkillGroupID = ESG.EnterpriseSkillGroupID

GROUP BY ART.AgentState,ESG.EnterpriseName


SELECT * FROM #tempESGAgentState WHERE AgentState = '3'
DROP TABLE #tempESGAgentState
		
--ORDER BY EnterpriseName DESC
SET ARITHABORT OFF SET ANSI_WARNINGS OFF SET NOCOUNT ON 
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

SELECT        
 EnterpriseSGName =  ESG.EnterpriseName
,CallsQueuedNow = SUM(ISNULL(SGRT.RouterCallsQNow, 0))
--,SGRT.RouterLongestCallInQ
,LongestCallQ = ISNULL(DATEDIFF(ss,max(SGRT.RouterLongestCallInQ), (Select NowTime From Controller_Time)),0)

FROM            
Skill_Group_Real_Time SGRT INNER JOIN
Enterprise_Skill_Group_Member ON SGRT.SkillTargetID = Enterprise_Skill_Group_Member.SkillTargetID INNER JOIN
Enterprise_Skill_Group ESG ON Enterprise_Skill_Group_Member.EnterpriseSkillGroupID = ESG.EnterpriseSkillGroupID

GROUP BY EnterpriseName
SET ARITHABORT OFF SET ANSI_WARNINGS OFF SET NOCOUNT ON 
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

SELECT
 EnterpriseSkillGroupID = ESG.EnterpriseSkillGroupID       
,ESGName =  ESG.EnterpriseName
,CallsQueuedNow = SUM(ISNULL(SGRT.RouterCallsQNow, 0))
--,SGRT.RouterLongestCallInQ
,LongestCallQ = ISNULL(DATEDIFF(ss,max(SGRT.RouterLongestCallInQ), (Select NowTime From Controller_Time)),0)

INTO #tempESG_Stats

FROM            
Skill_Group_Real_Time SGRT INNER JOIN
Enterprise_Skill_Group_Member ON SGRT.SkillTargetID = Enterprise_Skill_Group_Member.SkillTargetID INNER JOIN
Enterprise_Skill_Group ESG ON Enterprise_Skill_Group_Member.EnterpriseSkillGroupID = ESG.EnterpriseSkillGroupID

GROUP BY ESG.EnterpriseSkillGroupID,ESG.EnterpriseName

SET ARITHABORT OFF SET ANSI_WARNINGS OFF SET NOCOUNT ON 
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

SELECT * from #tempESG_Stats ORDER BY ESGName

SELECT        
 EnterpriseSkillGroupID = ESG.EnterpriseSkillGroupID
,ESGName = ESG.EnterpriseName
,ART.AgentState, 
Case ART.AgentState 
WHEN '0' THEN 'Logged Out'
WHEN '1' THEN 'Logged On'
WHEN '2' THEN 'Not Ready'
WHEN '3' THEN 'Ready'
WHEN '4' THEN 'Talking'
WHEN '5' THEN 'Work Not Ready'
WHEN '6' THEN 'Work Ready'
WHEN '7' THEN 'Busy Other'
WHEN '8' THEN 'Reserved'
WHEN '9' THEN 'Unknown-9'
WHEN '10' THEN 'Calls On Hold'
WHEN '11' THEN 'Active'
WHEN '12' THEN 'Paused'
WHEN '13' THEN 'Interrupted'
WHEN '14' THEN 'Not Active'
ELSE 'unknown'
END AS AgentStateName,

 Count(DISTINCT ART.SkillTargetID) As AgentStateCount

INTO #tempESG_AgentState

FROM            
Agent_Real_Time ART INNER JOIN
Skill_Group_Member ON ART.SkillTargetID = Skill_Group_Member.AgentSkillTargetID INNER JOIN
Skill_Group ON Skill_Group_Member.SkillGroupSkillTargetID = Skill_Group.SkillTargetID INNER JOIN
Enterprise_Skill_Group_Member ON Skill_Group.SkillTargetID = Enterprise_Skill_Group_Member.SkillTargetID INNER JOIN
Enterprise_Skill_Group ESG ON Enterprise_Skill_Group_Member.EnterpriseSkillGroupID = ESG.EnterpriseSkillGroupID

WHERE AgentState = '3'
GROUP BY ESG.EnterpriseSkillGroupID,ESG.EnterpriseName,ART.AgentState

SELECT * FROM #tempESG_AgentState ORDER BY ESGName

SELECT 
 EnterpriseSkillGroupID = #tempESG_Stats.EnterpriseSkillGroupID
,ESGName = #tempESG_Stats.ESGName
,CallsQueuedNow = #tempESG_Stats.CallsQueuedNow
,LongestCallQ = #tempESG_Stats.LongestCallQ
,AgentState = isNULL(#tempESG_AgentState.AgentState,0)
,AgentStateName = isNULL(#tempESG_AgentState.AgentStateName,0)
,AgentStateCount = isNULL(#tempESG_AgentState.AgentStateCount,0)


FROM #tempESG_Stats
LEFT JOIN #tempESG_AgentState ON #tempESG_Stats.EnterpriseSkillGroupID=#tempESG_AgentState.EnterpriseSkillGroupID

ORDER BY #tempESG_Stats.ESGName

DROP TABLE #tempESG_Stats
DROP TABLE #tempESG_AgentState
Final Version - minimum
SET ARITHABORT OFF SET ANSI_WARNINGS OFF SET NOCOUNT ON 
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

SELECT	EnterpriseSkillGroupID = ESG.EnterpriseSkillGroupID,       
		SkillGroup =  ESG.EnterpriseName,
		CallsQueuedNow = SUM(ISNULL(SGRT.RouterCallsQNow, 0)),
		LongestCallQ = ISNULL(DATEDIFF(ss,max(SGRT.RouterLongestCallInQ), (Select NowTime From Controller_Time)),0)

INTO #tempESG_Stats

FROM            
Skill_Group_Real_Time SGRT INNER JOIN
Enterprise_Skill_Group_Member ON SGRT.SkillTargetID = Enterprise_Skill_Group_Member.SkillTargetID INNER JOIN
Enterprise_Skill_Group ESG ON Enterprise_Skill_Group_Member.EnterpriseSkillGroupID = ESG.EnterpriseSkillGroupID

GROUP BY ESG.EnterpriseSkillGroupID,ESG.EnterpriseName

SELECT		EnterpriseSkillGroupID = ESG.EnterpriseSkillGroupID,
			AgentsAvailable = Count(DISTINCT ART.SkillTargetID)

INTO #tempESG_AgentState

FROM            
Agent_Real_Time ART INNER JOIN
Skill_Group_Member ON ART.SkillTargetID = Skill_Group_Member.AgentSkillTargetID INNER JOIN
Skill_Group ON Skill_Group_Member.SkillGroupSkillTargetID = Skill_Group.SkillTargetID INNER JOIN
Enterprise_Skill_Group_Member ON Skill_Group.SkillTargetID = Enterprise_Skill_Group_Member.SkillTargetID INNER JOIN
Enterprise_Skill_Group ESG ON Enterprise_Skill_Group_Member.EnterpriseSkillGroupID = ESG.EnterpriseSkillGroupID

WHERE AgentState = '3'
GROUP BY ESG.EnterpriseSkillGroupID,ART.AgentState

SELECT	EnterpriseSkillGroupID = #tempESG_Stats.EnterpriseSkillGroupID,
		SkillGroup = #tempESG_Stats.SkillGroup,
		AgentsAvailable = isNULL(#tempESG_AgentState.AgentsAvailable,0),
		CallsQueuedNow = #tempESG_Stats.CallsQueuedNow,
		LongestCallQ = #tempESG_Stats.LongestCallQ
FROM #tempESG_Stats
LEFT JOIN #tempESG_AgentState ON #tempESG_Stats.EnterpriseSkillGroupID=#tempESG_AgentState.EnterpriseSkillGroupID

DROP TABLE #tempESG_Stats
DROP TABLE #tempESG_AgentState

Assigned Attributes

SELECT        
AttributeEntName = Attribute.EnterpriseName, 
AttributeDefaultValue = Attribute.DefaultValue, 
AttributeDescription = Attribute.Description, 
AgentAttributeValue = Agent_Attribute.AttributeValue,
AgentSkillTargetID = Agent_Attribute.SkillTargetID, 
FirstName = Person.FirstName, 
LastName = Person.LastName, 
LoginName = Person.LoginName, 
AgentEntName = Agent.EnterpriseName
 
FROM            
Attribute INNER JOIN
Agent_Attribute ON Attribute.AttributeID = Agent_Attribute.AttributeID INNER JOIN
Agent ON Agent_Attribute.SkillTargetID = Agent.SkillTargetID INNER JOIN
Person ON Agent.PersonID = Person.PersonID
 
ORDER BY Attribute.EnterpriseName, Person.FirstName, Person.LastName

Agents No Records in HDS

SELECT     
Person.FirstName, 
Person.LastName, 
Person.LoginName,
Person.LoginEnabled,
Person.Deleted,
Agent.SkillTargetID, 
Agent.PeripheralID,
Agent.PeripheralNumber,
Agent.PersonID, 
Agent_Team.EnterpriseName AS AgentTeamName,
AgentLogoutSKID = AL.SkillTargetID
 
 
 
FROM Agent
 
LEFT JOIN Agent_Team_Member ON Agent.SkillTargetID = Agent_Team_Member.SkillTargetID
LEFT JOIN Agent_Team ON Agent_Team_Member.AgentTeamID = Agent_Team.AgentTeamID
LEFT JOIN Person ON Agent.PersonID = Person.PersonID
LEFT JOIN Agent_Logout AL ON AL.SkillTargetID = Agent.SkillTargetID
 
WHERE AL.SkillTargetID IS NULL	
ORDER BY FirstName, LastName, LoginName

Agent Count Logged into a Skillgroup for a certain time interval over a number of days

SET ARITHABORT OFF SET ANSI_WARNINGS OFF SET NOCOUNT ON 
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
 
SELECT 
 ASGI.DateTime
,ASGI.SkillGroupSkillTargetID
--,ASGI.SkillTargetID
,SGName =  SG.EnterpriseName
,AgentCount = COUNT(*)
 
 
  FROM Agent_Skill_Group_Interval ASGI
  JOIN Skill_Group SG ON SG.SkillTargetID = ASGI.SkillGroupSkillTargetID
 
  WHERE 
  --ASGI.DateTime > GetDate()-1
  (ASGI.DateTime >= '2019-07-01 12:00:00' AND ASGI.DateTime < '2019-07-29 23:59:59')
  --(ASGI.DateTime >= '2018-07-23 12:00:00' AND ASGI.DateTime < '2018-07-23 12:30:00')
  --(ASGI.DateTime >= GetDate()-12)
  AND (DATEPART(HOUR,ASGI.DateTime) = '12' AND DATEPART(MINUTE,ASGI.DateTime) = '00')
  AND SG.EnterpriseName LIKE '%SkillGroupName%'
 
  GROUP BY 
ASGI.DateTime,
ASGI.SkillGroupSkillTargetID,
SG.EnterpriseName
ORDER BY DateTime

Agent Bulk export - to help with Agent bulk Import in PCCE

SELECT 
	   username = [LoginName]
      ,firstName = [FirstName]
      ,lastName = [LastName]
      ,ecePerson = CASE([ECEPerson])
	  WHEN 'N' THEN 'FALSE'
	  WHEN 'Y' THEN 'TRUE'
	  END
      ,[ScreenName]
  FROM [ucce_awdb].[dbo].[Person]
  WHERE 
  ECEPerson = 'N' AND 
  Deleted = 'N'
  ORDER BY LoginName COLLATE Latin1_General_CI_AS
  • vendors/cisco/uc/icm/sql/agent.1580389142.txt.gz
  • Last modified: 2020/01/30 12:59
  • by gerardorourke