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:ucce:baselinereskiller [2023/06/12 17:05] – [Database] gerardorourkevendors:cisco:uc:ucce:baselinereskiller [2023/07/31 10:05] (current) – [Python Scripts] gerardorourke
Line 177: Line 177:
 ===== Database ===== ===== Database =====
  
 +==== Database Views ====
   *Add UCCE AW SQL Server as a linked server   *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).   *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).
Line 255: Line 256:
 FROM FROM [ucce-hds-a.example.com].[ucce_awdb].[dbo].[t_Agent_Team_Member] t FROM FROM [ucce-hds-a.example.com].[ucce_awdb].[dbo].[t_Agent_Team_Member] t
 </code> </code>
 +
 +==== Database Tables ====
 +<code sql>
 +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
 +</code>
 +
 +<code sql>
 +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
 +</code>
 +
 +<code sql>
 +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
 +</code>
 +
 +<code sql>
 +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
 +</code>
 +
 +
 +
 +
 +
 +
 +
 +
 +
 +===== Python Scripts ====
 +<code python>
 +# 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)
 +</code>
 +
 +===== Troubleshooting =====
 +
 +If the script works OK as but not when running as a sheduled script - run the script under the same account and see if the issue occur.
 +
 +{{:vendors:cisco:uc:ucce:run-as-a-different-user.png?600|}}
 +
 +
  • vendors/cisco/uc/ucce/baselinereskiller.1686585955.txt.gz
  • Last modified: 2023/06/12 17:05
  • by gerardorourke