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:21] – [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 256: Line 257:
 </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> <code python>
 # Author: Gerry O'Rourke # Author: Gerry O'Rourke
-# June 2021 +# June 2023 
-# Version 1.1 +
-import array+
 from datetime import datetime from datetime import datetime
 +import sys
 import pyodbc import pyodbc
-# +import requests 
-#import sys +from requests.auth import HTTPBasicAuth 
-  +import xml.etree.ElementTree as ET 
-database_write=0 #disable database writes+import urllib3 
 +urllib3.disable_warnings()
  
-#Get the Current Time - rounded down to minute. +version = 1.0 
-now = datetime.now() +cce_api_hostname = "ucce-hds-a.example.com" 
-current_datetime = now.strftime('%Y-%m-%d %H:%M') +debug_level = 1  # 0 error and high level info, 1 = more info , 2 = include responses from update agents
-print("Current DateTime =", current_datetime)+
  
-conn = pyodbc.connect('Driver={SQL Server};' +# Reference: https://docs.python.org/3/library/xml.etree.elementtree.html
-                      'Server=localhost;' +
-                      'Database=reskiller_db;' +
-                      'Trusted_Connection=yes;') +
-  +
-cursor = conn.cursor()+
  
-# Note this function is not used - and can be deleted +# exit codes 
-def getApiUserDetails(departmentid): +# 0  : Success 
-    rows = cursor.execute('SELECT [Supervisor],[Password] FROM [reskiller_db].[dbo].[Department] Where DepartmentID = (?)',(departmentid)) +# 1  : Argument not set 
-         +# 10 : Error retrieving Team Details 
-    for row in list(rows): +# 11 : update to retrieve agent details failed (need to get changeStamp) or unable to retrieve agent timestamp 
-        try: +# 12 : Update to Agent Failed 
-            global apiUserName +# 13 : Error retrieving Team Skills 
-            apiUserName = row[0] +# 14 : Error retrieving Team Agents
-            global apiPassword +
-            apiPassword= row[1] +
- +
-        except Exception as e: +
-            print("Error:", e)+
  
 +# 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): def getTeamDetails(departmentid):
 +    logToDatabase(6, f'Updating Teams in Department ID {departmentid}')
 +
     rows = cursor.execute(     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',+        '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))         (departmentid))
  
Line 308: Line 419:
             global apiUserName             global apiUserName
             apiUserName = row[1]             apiUserName = row[1]
 +            global apiPassword
             apiPassword = row[2]             apiPassword = row[2]
 +            print("DepartmentId:", departmentid)
             print("TeamId:", teamId)             print("TeamId:", teamId)
-            print("Username:", apiUserName)+            print("Supervisor:", apiUserName) 
 +            logToDatabase(6, f'Updating TeamId: {teamId}')
             getTeamSkills(teamId)             getTeamSkills(teamId)
             getTeamAgents(teamId)             getTeamAgents(teamId)
  
         except Exception as e:         except Exception as e:
-            print("Error:", e)+            errorstr = e 
 +            print(errorstr) 
 +            logToDatabase(3, errorstr) 
 +            exit(10)
  
  
 def getTeamSkills(teamid): def getTeamSkills(teamid):
     rows = cursor.execute(     rows = cursor.execute(
-        'SELECT [SkillTargetID] FROM [reskiller_db].[dbo].[Agent_Team_Skill_Group] WHERE AgentTeamID = (?)',(teamid))+        'SELECT [SkillTargetID] FROM [reskiller_db].[dbo].[Agent_Team_Skill_Group] WHERE AgentTeamID = (?)', (teamid))
  
     global skills     global skills
  
-    n=0+    n = 0
     for row in list(rows):     for row in list(rows):
         try:         try:
             skillid = row[0]             skillid = row[0]
-            if (n==0):+            if (n == 0):
                 n = n + 1                 n = n + 1
                 skills = [skillid]                 skills = [skillid]
Line 336: Line 453:
  
         except Exception as e:         except Exception as e:
-            print("Error:", e)+            errorstr = e 
 +            print(errorstr) 
 +            logToDatabase(3, errorstr) 
 +            exit(13) 
 + 
 +    print("skills:", skills)
  
-    print ("skills:",skills) 
  
 def getTeamAgents(teamid): def getTeamAgents(teamid):
     rows = cursor.execute(     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))+        '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     global agents
  
-    n=0+    n = 0
     for row in list(rows):     for row in list(rows):
         try:         try:
             agentid = row[0]             agentid = row[0]
-            if (n==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                 n = n + 1
                 agents = [agentid]                 agents = [agentid]
Line 358: Line 487:
  
         except Exception as e:         except Exception as e:
-            print("Error:", e)+            errorstr = e 
 +            print(errorstr) 
 +            logToDatabase(3, errorstr) 
 +            exit(14)
  
-    print ("agents:",agents)+    print("agents:", agents)
  
 +    # Check if Web Service API is in service
  
-#getApiUserDetails(1) 
-departmentId = 1 
-getTeamDetails(departmentId) 
  
-#print("TeamId:", teamId)+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)
  
-#input("Press Enter to continue...")+ 
 +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> </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.1686586885.txt.gz
  • Last modified: 2023/06/12 17:21
  • by gerardorourke