Differences

This shows you the differences between two versions of the page.

Link to this comparison view

Both sides previous revision Previous revision
Next revision		 Previous revision
vendors:cisco:uc:icm:sql [2023/06/28 09:51] – [Number of Concurrent Agent Logged from Agent Half Hour Table] gerardorourkevendors:cisco:uc:icm:sql [2024/12/06 15:03] (current) – [UCCE SQL] gerardorourke
Line 6: Line 6:
   *[[vendors:cisco:uc:icm:sql:inventory|UCCE Inventory SQL]]   *[[vendors:cisco:uc:icm:sql:inventory|UCCE Inventory SQL]]
   *[[vendors:cisco:uc:icm:sql:outbound|UCCE Outbound SQL]]   *[[vendors:cisco:uc:icm:sql:outbound|UCCE Outbound SQL]]
 +  *[[vendors:cisco:uc:icm:sql:parsing|Parsing PV Variable Example]]
   *[[vendors:cisco:uc:icm:sql:script|UCCE Script SQL]]   *[[vendors:cisco:uc:icm:sql:script|UCCE Script SQL]]
   *[[vendors:cisco:uc:icm:sql:scriptdata|UCCE Script Data SQL]]   *[[vendors:cisco:uc:icm:sql:scriptdata|UCCE Script Data SQL]]
Line 376: Line 377:
 ===== Total Calls Handled in a Half Hour===== ===== Total Calls Handled in a Half Hour=====
 <code sql> <code sql>
-SELECT SUM(CallsOfferedToHalf)As Calls ,DateTime  +SELECT SUM(CallsOffered)As Calls ,DateTime  
-FROM Peripheral_Half_Hour+FROM Peripheral_Interval
 WHERE DateTime > GetDate()-1 WHERE DateTime > GetDate()-1
 Group BY DateTime Group BY DateTime
 Order By Calls Desc Order By Calls Desc
 +</code>
  
- +<code> 
-select SUM(CallsHandledHalf) AS TotalHalfCalls,DateTime   +SELECT SUM(CallsHandled) AS TotalHandledCalls,DateTime   
-From t_Call_Type_Half_Hour +FROM Call_Type_Interval 
-Where DateTime > GetDate()-1 +WHERE DateTime > GetDate()-1 
-Group by DateTime +GROUP BY DateTime 
-Order By TotalHalfCalls Desc+ORDER BY TotalHandledCalls DESC
 </code> </code>
  
Line 435: Line 437:
 </code>   </code>  
  
 +===== Details (Names, Team) of the Named Agents over the last X Days =====
 +<code sql>
 +SELECT 
 +DISTINCT 
 + SkillTargetID = AL.SkillTargetID
 +,AgentEnterpriseName = A.EnterpriseName
 +,FirstName = P.FirstName
 +,LastName = P.LastName 
 +,LoginName = P.LoginName
 +,AgentTeam = AT.EnterpriseName 
 +
 +FROM [ucce_awdb].[dbo].[Agent_Logout] AL
 +LEFT JOIN Agent A ON A.SkillTargetID = AL.SkillTargetID
 +LEFT JOIN Agent_Team_Member ATM ON A.SkillTargetID = ATM.SkillTargetID
 +LEFT JOIN Agent_Team AT ON ATM.AgentTeamID = AT.AgentTeamID
 +LEFT JOIN Person P ON A.PersonID = P.PersonID
 +WHERE AL.LogoutDateTime > GetDate()-45
 +ORDER BY AT.EnterpriseName
 +</code>
 ====== Configured Agents ===== ====== Configured Agents =====
 <code sql> <code sql>
Line 1180: Line 1201:
 </code> </code>
  
 +==== Active Teams =====
 +Look at the distinct agents who have logged in over the last 24 hours - and sort by teams.
 +In Excel - you can gran Teams and remove duplicates to get a clean list of the exact teams therefore used over 24 hours.
  
 +<code sql>
 +SELECT     
 +DISTINCT AL.SkillTargetID,
 +A.PeripheralID,
 +A.PeripheralNumber,
 +A.PersonID, 
 +P.FirstName, 
 +P.LastName, 
 +P.LoginName,
 +T.EnterpriseName AS AgentTeamName
 +
 +FROM Agent_Logout AL
 +Left JOIN Agent A ON A.SkillTargetID = AL.SkillTargetID
 +LEFT JOIN Agent_Team_Member ATM ON A.SkillTargetID = ATM.SkillTargetID
 +LEFT JOIN Agent_Team T ON ATM.AgentTeamID = T.AgentTeamID
 +LEFT JOIN Person P ON A.PersonID = P.PersonID
 +
 +WHERE AL.DbDateTime > GetDate()-1
 +ORDER BY T.EnterpriseName,FirstName,LastName
 +</code>
 ==== Troubleshooting ==== ==== Troubleshooting ====
 On Logger Only by default! On Logger Only by default!
  • vendors/cisco/uc/icm/sql.1687942294.txt.gz
  • Last modified: 2023/06/28 09:51
  • by gerardorourke