UCCE 8.5(2) Concurrent User license count

[Larry Gyrion]

I am looking for a way to view concurrent license and/or user count on a daily basis.  UCCE 8.5(2)                  

[geoff]

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

[Larry Gyrion]

And how do I do this?  I use UIC a GUI application.  Is this something that I would put into Report Definitions? 

[geoff]

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

[Gergely Szabo]

Er... Agent_Half_Hour in ICM 8.5.x? Might not be populated. Use Agent_Interval instead.

G.

[geoff]

Thank you - excellent point. It's a very old query.

Regards,

Geoff

[Larry Gyrion]

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

[geoff]

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

[geoff]

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

[gpsriramdc]

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

[chris.warren]

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.

[david.macias]

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