05-15-2012 01:34 PM - edited 03-14-2019 09:52 AM
I am looking for a way to view concurrent license and/or user count on a daily basis. UCCE 8.5(2)
05-15-2012 02:39 PM
You can get the max as follows (this is quite accurate)
declare @start datetime
declare @end datetime
set @start = '2012/05/15 0:00'
set @end = DATEADD(dd, 1, @start)
SELECT AHHAdjustedCount = MAX(AHHCount.AdjustedLoggedOn)
FROM (SELECT DateTime,
AgentsLoggedOn = Count(*),
AdjustedLoggedOn = Ceiling(SUM(LoggedOnTimeToHalf)/1800.0)
FROM Agent_Half_Hour AS AHH
WHERE DateTime >= @start
AND DateTime < @end
AND AHH.MRDomainID = 1
GROUP BY DateTime) AHHCount
Perhaps you can find a way to parameterize the day and schedule in SQL Server to run each night and send you an email with the answer.
Regards,
Geoff
05-16-2012 06:35 AM
And how do I do this? I use UIC a GUI application. Is this something that I would put into Report Definitions?
05-16-2012 06:43 AM
Mmm. That's a query on the AW/HDS - I just run it on the AW every now and again in SQL Query Analyzer.
But you could certainly build a custom report in CUIS.
Regards,
Geoff
05-16-2012 06:49 AM
Er... Agent_Half_Hour in ICM 8.5.x? Might not be populated. Use Agent_Interval instead.
G.
05-16-2012 07:59 AM
Thank you - excellent point. It's a very old query.
Regards,
Geoff
05-22-2012 02:41 PM
Here is the solution:
In UIC create a report definition:
SELECT agents_logged_on = AgentTeamRealTime.agents_logged_on,
total_agents= AgentTeam.total_agents
From
(SELECT agent_team_name= Agent_Team.EnterpriseName,
agentteamid=Agent_Team_Member.AgentTeamID,
Supervisor=(SELECT Person.LastName + ', ' + Person.FirstName FROM Agent (nolock), Person (nolock) WHERE Agent.SkillTargetID = Agent_Team.PriSupervisorSkillTargetID AND Agent.PersonID = Person.PersonID),
agents_logged_on = sum(CASE WHEN Agent_Real_Time.AgentState <> 0 Then 1 else 0 END),
media = Media_Routing_Domain.EnterpriseName,
DOY = MAX(Datepart(dy, Agent_Real_Time.DateTime)),
active_in=sum(CASE WHEN ((Agent_Real_Time.AgentState = 11 or Agent_Real_Time.AgentState = 4) and Agent_Real_Time.Direction=1) Then 1 else 0 END),
active_out=sum(CASE WHEN ((Agent_Real_Time.AgentState = 11 or Agent_Real_Time.AgentState = 4) and Agent_Real_Time.Direction=2) Then 1 else 0 END),
active_other=sum(CASE WHEN ((Agent_Real_Time.AgentState = 11 or Agent_Real_Time.AgentState = 4) and Agent_Real_Time.Direction=3) Then 1 else 0 END),
hold_state=sum(CASE WHEN (Agent_Real_Time.AgentState = 10 or Agent_Real_Time.AgentState = 12) Then 1 else 0 END),
not_active= sum(CASE WHEN (Agent_Real_Time.AgentState = 14 or Agent_Real_Time.AgentState = 3) Then 1 else 0 END),
wrap_up = sum(CASE WHEN (Agent_Real_Time.AgentState = 5 or Agent_Real_Time.AgentState = 6) Then 1 else 0 END),
not_ready= sum(CASE WHEN Agent_Real_Time.AgentState =2 Then 1 else 0 END),
reserved =sum(CASE WHEN Agent_Real_Time.AgentState = 8 Then 1 else 0 END),
eligible_for_task=sum( CASE when Agent_Real_Time.AvailableInMRD <> 0 THEN 1 else 0 END)
From Agent (nolock),Agent_Real_Time (nolock),Agent_Team_Member (nolock),
Person (nolock), Media_Routing_Domain (nolock), Agent_Team (nolock)
WHERE Agent.PersonID = Person.PersonID
AND Agent_Real_Time.MRDomainID = Media_Routing_Domain.MRDomainID
AND Agent.SkillTargetID = Agent_Real_Time.SkillTargetID
AND Agent.SkillTargetID = Agent_Team_Member.SkillTargetID
AND Agent_Team_Member.AgentTeamID = Agent_Team.AgentTeamID
GROUP BY Agent_Team_Member.AgentTeamID,Agent_Team.EnterpriseName,
Agent_Team.PriSupervisorSkillTargetID,
Media_Routing_Domain.EnterpriseName) AgentTeamRealTime,
(Select total_agents= count(SkillTargetID), agentteamid=Agent_Team_Member.AgentTeamID from Agent_Team_Member (nolock)
GROUP BY Agent_Team_Member.AgentTeamID ) AgentTeam
where AgentTeamRealTime.agentteamid = AgentTeam.agentteamid
ORDER BY AgentTeamRealTime.media, AgentTeamRealTime.agent_team_name
Leave the Fields Tab as created
Properties Tab:
Description: Whatever you want to call it
Key Criteria Field: agents_logged_on (agents_logged_on)
Adjust permissions to My Group and All Users
Then create report
works great to add a scheduler in giving hourly updates of concurrent logged in agents.
Larry Gyrion
Cisco Unified Communications Manager Administrator - Consultant
05-22-2012 03:38 PM
Thanks for posting your CUIC query.
That's a real time report though - an slightly easier solution in some respects, since the tables are there. It does seem quite complicated - won't AgentTeamRealTime just have the answer?
However, you would not know if between the two hour marks another 50 agents logged in and then logged out after 30 mins. If you are wondering how close you are to your licence limit, that may slip by you.
Not what you were asking, of course; and no criticism intended. I hope readers find the thread useful.
I'm going to see what the query does.
Regards,
Geoff
05-22-2012 05:56 PM
Larry, thanks for the query.
I played with it a bit so I could understand it and reorganized a few things and eliminated the state counts, which are not being used. I print out the name of the team and the name of the primary supervisor.
SELECT TeamName=teamname, PrimarySupervisor=prisuper, LoggedOn=AgentTeamRealTime.numloggedon, TeamTotal=AgentTeam.totalagents
FROM
(
SELECT teamname=Agent_Team.EnterpriseName, agentteamid=Agent_Team_Member.AgentTeamID,
prisuper=(
SELECT Person.FirstName + ' ' + Person.LastName
FROM Agent (nolock), Person (nolock)
WHERE Agent.SkillTargetID=Agent_Team.PriSupervisorSkillTargetID AND Agent.PersonID=Person.PersonID
),
numloggedon=sum(CASE WHEN Agent_Real_Time.AgentState <> 0 THEN 1 ELSE 0 END),
media=Media_Routing_Domain.EnterpriseName
FROM Agent (nolock), Agent_Real_Time (nolock), Agent_Team_Member (nolock), Person (nolock), Media_Routing_Domain (nolock), Agent_Team (nolock)
WHERE Agent.PersonID = Person.PersonID
AND Agent_Real_Time.MRDomainID = Media_Routing_Domain.MRDomainID
AND Agent.SkillTargetID = Agent_Real_Time.SkillTargetID
AND Agent.SkillTargetID = Agent_Team_Member.SkillTargetID
AND Agent_Team_Member.AgentTeamID = Agent_Team.AgentTeamID
GROUP BY Agent_Team_Member.AgentTeamID, Agent_Team.EnterpriseName,
Agent_Team.PriSupervisorSkillTargetID,
Media_Routing_Domain.EnterpriseName
) AgentTeamRealTime,
(
SELECT totalagents=COUNT(SkillTargetID), agentteamid=Agent_Team_Member.AgentTeamID
FROM Agent_Team_Member (nolock)
GROUP BY Agent_Team_Member.AgentTeamID
) AgentTeam
WHERE AgentTeamRealTime.agentteamid=AgentTeam.agentteamid
ORDER BY AgentTeamRealTime.media, AgentTeamRealTime.teamname
This gives results that look like this in SQL Management Studio:
I don't have a CUIC I can play with, unfortunately.
Regards,
Geoff
07-18-2012 10:36 AM
Hi Geoff,
I tested this query CUC it's working perfectly.I need to get the data & time in report .
It will help us to get the conncurrent usage report for cad license.
Thanks +5
07-29-2013 11:07 AM
Has anyone created a historical report that would show this information? I'd be interested in having the ability to run a historical report that shows peak agent logins per team.
07-29-2013 11:52 AM
Considering this is happening in the real time data, you will have to create some sort of procedure to move this to a table outside of UCCE for historical purposes. Get your DBA involved, I'm sure s/he will be able to help you out, but given the way this is setup, it's not going to work out of the box using this query.
david
Find answers to your questions by entering keywords or phrases in the Search bar above. New here? Use these resources to familiarize yourself with the community: