Baseline Reskiller

This is an old revision of the document!

Use a supervisor account (admin account cannot retrieve agent teams in UCCE).
Supervisor accounts use the UCCE username / password and not the AD.

note - an Admin user CAN get the users from a specific Team, by using the Get Agent API and filtering by team - example below

https://ucce-hds-a.example.com/unifiedconfig/config/agent?time=1686302163133&startIndex=0&q=%20teams%3A(PurplePiTeam)&ignoreSearchErrors=true&summary=true&resultsPerPage=64&sort=person.userName%20asc

Get the Agent - to retrieve the changeStampID
Update the Agent with it skills using the same changeStampID
Repeat

An example of retrieving the agent and supervisors for Team ID 5009:

https://ucce-hds-a.example.com/unifiedconfig/config/agentteam/5009

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<agentTeam>
    <refURL>/unifiedconfig/config/agentteam/5009</refURL>
    <changeStamp>121</changeStamp>
    <agentCount>2</agentCount>
    <description>System Test Agent Team</description>
    <name>Team Bananas</name>
    <peripheral>
        <id>5000</id>
        <name>CCM_PG</name>
    </peripheral>
    <peripheralId>5000</peripheralId>
    <supervisorCount>1</supervisorCount>
    <agents>
        <agent>
            <refURL>/unifiedconfig/config/agent/5104</refURL>
            <agentId>555123</agentId>
            <firstName>Gerry</firstName>
            <lastName>O'Rourke</lastName>
            <userName>555123</userName>
        </agent>
        <agent>
            <refURL>/unifiedconfig/config/agent/5667</refURL>
            <agentId>6160001</agentId>
            <firstName>purplepi</firstName>
            <lastName>support1</lastName>
            <userName>ppsupport1</userName>
        </agent>
    </agents>
    <supervisors>
        <supervisor>
            <refURL>/unifiedconfig/config/agent/5104</refURL>
            <agentId>555123</agentId>
            <firstName>Gerry</firstName>
            <lastName>O'Rourke</lastName>
            <userName>555123</userName>
        </supervisor>
    </supervisors>
</agentTeam>

https://ucce-hds-a.example.com/unifiedconfig/config/agent/5667

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<agent>
    <refURL>/unifiedconfig/config/agent/5667</refURL>
    <changeStamp>94</changeStamp>
    <agentId>6160001</agentId>
    <agentServicesEnabled/>
    <agentStateTrace>true</agentStateTrace>
    <agentTeam>
        <refURL>/unifiedconfig/config/agentteam/5009</refURL>
        <name>eirTeam</name>
    </agentTeam>
    <canRemove>true</canRemove>
    <peripheral>
        <id>5000</id>
        <name>CCM_PG</name>
    </peripheral>
    <person>
        <ecePerson>false</ecePerson>
        <firstName>purplepi</firstName>
        <lastName>support1</lastName>
        <loginEnabled>true</loginEnabled>
        <ssoEnabled>false</ssoEnabled>
        <userName>ppsupport1</userName>
    </person>
    <supervisor>false</supervisor>
    <agentDeskSettings>
        <refURL>/unifiedconfig/config/agentdesksetting/5008</refURL>
        <name>purple.DS</name>
    </agentDeskSettings>
    <skillGroups>
        <skillGroup>
            <refURL>/unifiedconfig/config/skillgroup/5666</refURL>
            <name>purpleSK01.SG</name>
        </skillGroup>
        <skillGroup>
            <refURL>/unifiedconfig/config/skillgroup/5928</refURL>
            <name>Chat_Sales.SG</name>
        </skillGroup>
    </skillGroups>
</agent>

https://ucce-hds-a.example.com/unifiedconfig/config/agent/5667

Example Body

<agent>
	<skillGroups>
		<skillGroup>
			<refURL>/unifiedconfig/config/skillgroup/5787</refURL>
		</skillGroup>
		<skillGroup>
			<refURL>/unifiedconfig/config/skillgroup/5928</refURL>
		</skillGroup>
	</skillGroups>
	<changeStamp>94</changeStamp>
</agent>

After the update, if you repeat the HTTP Get of the Agent URL - you will see that the skillgroups assigned have been updated / replaced. And that the ChangeStampID has been updated (usually moves forward 2).

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<agent>
    <refURL>/unifiedconfig/config/agent/5667</refURL>
    <changeStamp>96</changeStamp>
    <agentId>6160001</agentId>
    <agentServicesEnabled/>
    <agentStateTrace>true</agentStateTrace>
    <agentTeam>
        <refURL>/unifiedconfig/config/agentteam/5009</refURL>
        <name>eirTeam</name>
    </agentTeam>
    <canRemove>true</canRemove>
    <peripheral>
        <id>5000</id>
        <name>CCM_PG</name>
    </peripheral>
    <person>
        <ecePerson>false</ecePerson>
        <firstName>purplepi</firstName>
        <lastName>support1</lastName>
        <loginEnabled>true</loginEnabled>
        <ssoEnabled>false</ssoEnabled>
        <userName>ppsupport1</userName>
    </person>
    <supervisor>false</supervisor>
    <agentDeskSettings>
        <refURL>/unifiedconfig/config/agentdesksetting/5008</refURL>
        <name>purple.DS</name>
    </agentDeskSettings>
    <skillGroups>
        <skillGroup>
            <refURL>/unifiedconfig/config/skillgroup/5787</refURL>
            <name>purpleSK02.SG</name>
        </skillGroup>
        <skillGroup>
            <refURL>/unifiedconfig/config/skillgroup/5928</refURL>
            <name>Chat_Sales.SG</name>
        </skillGroup>
    </skillGroups>
</agent>

Add UCCE AW SQL Server as a linked server
When adding - on the security tab, set “Be made using this security context” and enter the sql read only username / password you have already setup on the AW database (with read access only to the ucce_awdb).
Create a view of the Agent Team and the Skillgroups tables - as per below

CREATE VIEW [dbo].[Agent_Team] AS
SELECT t.AgentTeamID AS AgentTeamID,
t.EnterpriseName AS EnterpriseName,
t.PeripheralID AS PeripheralID,
t.DialedNumberID AS DialedNumberID,
t.Description AS Description,
t.PriSupervisorSkillTargetID AS PriSupervisorSkillTargetID,
t.ChangeStamp AS ChangeStamp,
t.DepartmentID AS DepartmentID,
t.DateTimeStamp AS DateTimeStamp
FROM [ucce-hds-a.example.com].[ucce_awdb].[dbo].[t_Agent_Team] t

CREATE VIEW [dbo].[Skill_Group] AS
SELECT t.SkillTargetID AS SkillTargetID,
t.PrecisionQueueID AS PrecisionQueueID,
t.ScheduleID AS ScheduleID,
t.PeripheralID AS PeripheralID,
t.EnterpriseName AS EnterpriseName,
t.PeripheralNumber AS PeripheralNumber,
t.PeripheralName AS PeripheralName,
t.AvailableHoldoffDelay AS AvailableHoldoffDelay,
t.Priority AS Priority,
t.BaseSkillTargetID AS BaseSkillTargetID,
t.Extension AS Extension,
t.SubGroupMaskType AS SubGroupMaskType,
t.SubSkillGroupMask AS SubSkillGroupMask,
t.ConfigParam AS ConfigParam,
t.Description AS Description,
t.Deleted AS Deleted,
t.MRDomainID AS MRDomainID,
t.IPTA AS IPTA,
t.DefaultEntry AS DefaultEntry,
t.UserDeletable AS UserDeletable,
t.ServiceLevelThreshold AS ServiceLevelThreshold,
t.ServiceLevelType AS ServiceLevelType,
t.BucketIntervalID AS BucketIntervalID,
t.ChangeStamp AS ChangeStamp,
t.DepartmentID AS DepartmentID,
t.DateTimeStamp AS DateTimeStamp
FROM [ucce-hds-a.example.com].[ucce_awdb].[dbo].[t_Skill_Group] t

CREATE VIEW [dbo].[Agent] AS
SELECT t.SkillTargetID AS SkillTargetID,
t.PersonID AS PersonID,
t.AgentDeskSettingsID AS AgentDeskSettingsID,
t.ScheduleID AS ScheduleID,
t.PeripheralID AS PeripheralID,
t.EnterpriseName AS EnterpriseName,
t.PeripheralNumber AS PeripheralNumber,
t.ConfigParam AS ConfigParam,
t.Description AS Description,
t.Deleted AS Deleted,
t.PeripheralName AS PeripheralName,
t.TemporaryAgent AS TemporaryAgent,
t.AgentStateTrace AS AgentStateTrace,
t.SupervisorAgent AS SupervisorAgent,
t.ChangeStamp AS ChangeStamp,
t.UserDeletable AS UserDeletable,
t.DefaultSkillGroup AS DefaultSkillGroup,
t.DepartmentID AS DepartmentID,
t.DateTimeStamp AS DateTimeStamp
FROM [ucce-hds-a.example.com].[ucce_awdb].[dbo].[t_Agent] t

CREATE VIEW [dbo].[Agent_Team_Member] AS
SELECT t.AgentTeamID AS AgentTeamID,
t.SkillTargetID AS SkillTargetID
FROM FROM [ucce-hds-a.example.com].[ucce_awdb].[dbo].[t_Agent_Team_Member] t

USE [reskiller_db]
GO
 
/****** Object:  Table [dbo].[Department]    Script Date: 23/06/2023 09:31:02 ******/
SET ANSI_NULLS ON
GO
 
SET QUOTED_IDENTIFIER ON
GO
 
CREATE TABLE [dbo].[Department](
	[DepartmentID] [INT] NOT NULL,
	[DepartmentName] [VARCHAR](50) NOT NULL,
	[Supervisor] [VARCHAR](50) NOT NULL,
	[Password] [VARCHAR](50) NOT NULL,
	[ScheduleID] [INT] NULL,
	[Active] [CHAR](1) NOT NULL
) ON [PRIMARY]
GO
 
ALTER TABLE [dbo].[Department] ADD  CONSTRAINT [DF_Department_Active]  DEFAULT ('Y') FOR [Active]
GO

USE [reskiller_db]
GO
 
/****** Object:  Table [dbo].[Agent_Team_Department]    Script Date: 23/06/2023 09:32:08 ******/
SET ANSI_NULLS ON
GO
 
SET QUOTED_IDENTIFIER ON
GO
 
CREATE TABLE [dbo].[Agent_Team_Department](
	[AgentTeamID] [INT] NOT NULL,
	[DepartmentID] [INT] NOT NULL,
	[Active] [CHAR](1) NOT NULL,
 CONSTRAINT [PK_AgentTeam_Department] PRIMARY KEY CLUSTERED 
(
	[AgentTeamID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
 
ALTER TABLE [dbo].[Agent_Team_Department] ADD  CONSTRAINT [DF_Agent_Team_Department_Active]  DEFAULT ('Y') FOR [Active]
GO

USE [reskiller_db]
GO
 
/****** Object:  Table [dbo].[Agent_Team_Skill_Group]    Script Date: 23/06/2023 09:36:02 ******/
SET ANSI_NULLS ON
GO
 
SET QUOTED_IDENTIFIER ON
GO
 
CREATE TABLE [dbo].[Agent_Team_Skill_Group](
	[AgentTeamID] [INT] NOT NULL,
	[SkillTargetID] [INT] NOT NULL
) ON [PRIMARY]
GO

USE [reskiller_db]
GO
 
/****** Object:  Table [dbo].[Log]    Script Date: 23/06/2023 09:36:19 ******/
SET ANSI_NULLS ON
GO
 
SET QUOTED_IDENTIFIER ON
GO
 
CREATE TABLE [dbo].[Log](
	[ID] [INT] IDENTITY(1,1) NOT NULL,
	[DateTime] [datetime] NOT NULL,
	[SevLevel] [INT] NOT NULL,
	[DATA] [nvarchar](MAX) NOT NULL,
 CONSTRAINT [PK_Log] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
 
ALTER TABLE [dbo].[Log] ADD  CONSTRAINT [DF_Log_DateTime]  DEFAULT (getdate()) FOR [DateTime]
GO

# Author: Gerry O'Rourke
# June 2023
 
from datetime import datetime
import sys
import pyodbc
import requests
from requests.auth import HTTPBasicAuth
import xml.etree.ElementTree as ET
import urllib3
urllib3.disable_warnings()
 
version = 1.0
cce_api_hostname = "ucce-hds-a.example.com"
debug_level = 1  # 0 error and high level info, 1 = more info , 2 = include responses from update agents
 
# Reference: https://docs.python.org/3/library/xml.etree.elementtree.html
 
# exit codes
# 0  : Success
# 1  : Argument not set
# 10 : Error retrieving Team Details
# 11 : update to retrieve agent details failed (need to get changeStamp) or unable to retrieve agent timestamp
# 12 : Update to Agent Failed
# 13 : Error retrieving Team Skills
# 14 : Error retrieving Team Agents
 
# SEV Level (taken from Syslog) only 3-7 should be used
# VALUE	SEVERITY	    KEYWORD	    DESCRIPTION	                                                    EXAMPLES
# 0	    Emergency	    emerg	    System is unusable	                                            This level should not be used by applications.
# 1	    Alert	        alert	    Should be corrected immediately	                                Loss of the primary ISP connection.
# 2	    Critical	    crit	    Critical conditions	                                            A failure in the system's primary application.
# 3	    Error	        err	        Error conditions	                                            An  application has exceeded its file storage limit and attempts to write are failing.
# 4	    Warning	        warning	    May indicate that an error will occur if action is not taken.   A non-root file system has only 2GB remaining.
# 5	    Notice	        notice	    Events that are unusual, but not error conditions.
# 6	    Informational	info	    Normal operational messages that require no action.	            An application has started, paused or ended successfully.
# 7	    Debug	        debug	    Information useful to developers for debugging the application.
 
def getTeamDetails(departmentid):
    logToDatabase(6, f'Updating Teams in Department ID {departmentid}')
 
    rows = cursor.execute(
        'SELECT ATD. [AgentTeamID],[Supervisor],[Password],ATD.[DepartmentID] FROM [reskiller_db].[dbo].['
        'Agent_Team_Department] ATD LEFT JOIN Department D ON ATD.DepartmentID = D.DepartmentID WHERE ATD.Active = '
        '\'Y\' AND ATD.DepartmentID = (?) ORDER BY AgentTeamID',
        (departmentid))
 
    global teamId
    global apiPassword
 
    for row in list(rows):
        try:
            teamId = row[0]
            global apiUserName
            apiUserName = row[1]
            global apiPassword
            apiPassword = row[2]
            print("DepartmentId:", departmentid)
            print("TeamId:", teamId)
            print("Supervisor:", apiUserName)
            logToDatabase(6, f'Updating TeamId: {teamId}')
            getTeamSkills(teamId)
            getTeamAgents(teamId)
 
        except Exception as e:
            errorstr = e
            print(errorstr)
            logToDatabase(3, errorstr)
            exit(10)
 
 
def getTeamSkills(teamid):
    rows = cursor.execute(
        'SELECT [SkillTargetID] FROM [reskiller_db].[dbo].[Agent_Team_Skill_Group] WHERE AgentTeamID = (?)', (teamid))
 
    global skills
 
    n = 0
    for row in list(rows):
        try:
            skillid = row[0]
            if (n == 0):
                n = n + 1
                skills = [skillid]
            else:
                n = n + 1
                skills.append(skillid)
 
        except Exception as e:
            errorstr = e
            print(errorstr)
            logToDatabase(3, errorstr)
            exit(13)
 
    print("skills:", skills)
 
 
def getTeamAgents(teamid):
    rows = cursor.execute(
        'SELECT A.SkillTargetID FROM Agent A LEFT JOIN Agent_Team_Member ATM ON A.SkillTargetID = ATM.SkillTargetID '
        'LEFT JOIN Agent_Team AT ON ATM.AgentTeamID = AT.AgentTeamID WHERE A.Deleted = \'N\' AND AT.AgentTeamID = (?) '
        'ORDER BY SkillTargetID',
        (teamid))
 
    global agents
 
    n = 0
    for row in list(rows):
        try:
            agentid = row[0]
            changeStamp = getAgentChangeStamp(apiUserName, apiPassword, agentid)
            # print ('Agent:',agentid,', changeStamp:',changeStamp)
            agentUpdateStatus = updateAgentSkills(apiUserName, apiPassword, agentid, changeStamp)
            if agentUpdateStatus != 200:
                exit(12)
            if (n == 0):
                n = n + 1
                agents = [agentid]
            else:
                n = n + 1
                agents.append(agentid)
 
        except Exception as e:
            errorstr = e
            print(errorstr)
            logToDatabase(3, errorstr)
            exit(14)
 
    print("agents:", agents)
 
    # Check if Web Service API is in service
 
 
def getApiStatus():
    api_url = f'https://{cce_api_hostname}/unifiedconfig/config/deployment'
    response = requests.get(api_url, verify=False)
    status_code = response.status_code
    return (status_code)
 
 
def getAgentChangeStamp(username, password, agent):
    basic = HTTPBasicAuth(username, password)
    api_url = f'https://{cce_api_hostname}/unifiedconfig/config/agent/{agent}'
    response = requests.get(api_url, verify=False, auth=basic)
    status_code = response.status_code
    if status_code == 200:
        response_xml_as_string = response.text
        responseXml = ET.fromstring(response_xml_as_string)
        # print (responseXml.tag)
        for child in responseXml:
            # print(child.tag,":", child.text)
            if child.tag == 'changeStamp': return (child.text)
        errorstr = f'teamID: {teamId}, FailedAgent: {agent} - Unable to retrieve changeStamp'
        print(errorstr)
        logToDatabase(3, errorstr)
        exit(11)
    else:
        errorstr = f'teamID: {teamId}, Failed to retrieve Agent: {agent} details, Status Code: {status_code}'
        print(errorstr)
        logToDatabase(3, errorstr)
        exit(11)
 
 
def updateAgentSkills(username, password, agent, changestamp):
    basic = HTTPBasicAuth(username, password)
    api_url = f'https://{cce_api_hostname}/unifiedconfig/config/agent/{agent}'
    headers = {'Content-Type': 'application/xml'}
    skillRefURL = ""
    for skill in (skills):
        skillRefURL += f'<skillGroup><refURL>/unifiedconfig/config/skillgroup/{skill}</refURL></skillGroup>'
    # print(skillRefURL)
    agentSkillsXml = f'<agent><skillGroups>{skillRefURL}</skillGroups><changeStamp>{changestamp}</changeStamp></agent>'
    response = requests.put(api_url, verify=False, auth=basic, data=agentSkillsXml, headers=headers)
    status_code = response.status_code
 
    if debug_level > 1:
        debugstr = f'status_code: {status_code}, teamID: {teamId}, agentId: {agent}, changeStamp {changestamp}, skillRefURL: {skillRefURL}'
        print(debugstr)
        logToDatabase(7, debugstr)
 
    if status_code != 200:
        errorstr = f'teamID: {teamId}, agentId: {agent}, errorResponse:', response.text
        print(errorstr)
        logToDatabase(3, errorstr)
    print(f'Agent: {agent}, Updated Successfully, ChangeStamp: {changestamp}.')
    return (status_code)
 
 
def logToDatabase(sev, data):
    sql = 'INSERT INTO Log (SevLevel, Data) VALUES (?, ?)'
    val = (int(sev), str(data))
    cursor.execute(sql, val)
    conn.commit()
 
 
#################################################################################################
#                                  This is where we start                                       #
#################################################################################################
 
# Check for argument '-s' or '-d' set to an integer or to the value 'all'
 
if len(sys.argv) == 3:
    if sys.argv[1] == "-s" and (sys.argv[2].isdigit() or (sys.argv[2] == 'all')):
        print("Scheduler ID:", (sys.argv[2]))
        scheduleId = (sys.argv[2])
    elif (sys.argv[1] == "-d") and (sys.argv[2].isdigit()):
        print("Department ID:", (sys.argv[2]))
        departmentId = (sys.argv[2])
    else:
        print(
            "Invalid Arguments. Please pass in the schedule argument 's' with a valid schedule ID or the department "
            "argument 'd' with a valid department ID")
        print("Example: reskiller.py -s 1 OR reskiller.py -s all OR reskiller.py -d 1")
        exit(1)
else:
    print(
        "Invalid Arguments. Please pass in the schedule argument 's' with a valid schedule ID or the department "
        "argument 'd' with a valid department ID")
    print("Example: reskiller.py -s 1 OR reskiller.py -s all OR reskiller.py -d 1")
    exit(1)
 
# Get the Current Time - rounded down to minute.
now = datetime.now()
current_datetime = now.strftime('%Y-%m-%d %H:%M:%S')
print("Start TimeStamp:", current_datetime)
 
conn = pyodbc.connect('Driver={SQL Server};'
                      'Server=localhost;'
                      'Database=reskiller_db;'
                      'Trusted_Connection=yes;')
cursor = conn.cursor()
 
logToDatabase(6, f'Baseline Reskiller {version} Started. Arguments passed are {sys.argv[1]}  {sys.argv[2]}')
 
# Checking CCE API is in service before starting...
apiStatus = getApiStatus()
if apiStatus != 200:
    errorstr = f'WebService API is not in service! Status Code returned: {apiStatus}. Exiting...'
    logToDatabase(3, errorstr)
    exit(10)
 
if (sys.argv[1] == "-d"):
    getTeamDetails(departmentId)
 
if (sys.argv[1] == "-s"):
    logToDatabase(6, f'Updating Departments with Schedule ID {scheduleId}')
    if sys.argv[2].isdigit():
        rows = cursor.execute("SELECT [DepartmentID] FROM [Department] WHERE  Active = 'Y' AND ScheduleID = (?) ORDER BY DepartmentID",scheduleId)
    else:
        rows = cursor.execute("SELECT [DepartmentID] FROM [Department] WHERE Active = 'Y' ORDER BY DepartmentID")
    for row in list(rows):
        try:
            departmentId = row[0]
            getTeamDetails(departmentId)
        except Exception as e:
            errorstr = e
            print(errorstr)
            logToDatabase(3, errorstr)
            exit(13)
 
logToDatabase(6, 'Baseline Reskiller Script Completed.')
 
print("Completed.")
now = datetime.now()
current_datetime = now.strftime('%Y-%m-%d %H:%M:%S')
print("End TimeStamp:", current_datetime)

Baseline Reskiller

Agent Team Retrieval

HTTP GET URL

HTTP RESPONSE

Example of Retrieving the ChangeStampID for a specific agent - needed before the update

HTTP GET URL

HTTP Response

Example of updating an Agent and assigning it two specific skill group

HTTP PUT Example URL

Example Body

After the update

Database

Database Views

Database Tables

Python Scripts