My dilemma:
We are noticing that crafty sales agents will abuse the timeclock system by remaining on pause for excessive periods, or worse, will actually clock-in people that aren't present, forcing us to pay people despite their actual presence. Our ultimate goal is to save on labor costs by using a programmatic/technology centric approach instead of a human approach (which would require our managers to monitor the sales agents more closely). Priority one is to completely eliminate that ability for a sales agent to "help his buddy" by clocking someone in who is not even in the building. We feel we can accomplish that with a script that grabs some values out of Vicidial's database tables and verifies that people who are time-clocked in ("LOGIN" status in the vicidial_timeclock_status table) are actually doing work (making calls) (check relevant fields in the vicidial_live_agents table for existence). The second priority is to keep people from remaining on pause excessively by also using a script ran at a regular interval (Thinking maybe "1/* cronjob??) that can calculate a user's pause time and react by logging out and clocking out a user that has a pause time greater than 12 minutes (thinking of comparing the pause_epoch to the current epoch). Our brainstorming sessions have lead us to think that by running a "1/*" cronjob could do what we need. Is this even possible? Does this sound as if it can become a viable solution? If it is possible does anyone see any obvious problems with this approach? I kindly ask for any input related to this matter.
Summary of Goals:
1.) Clock out and log out anyone that exceeds 12 minutes of pause time.
2.) Prevent people from remaining "on the clock" for more than five minutes without being logged into a Vicidial campaign.
3.) Achieve steps one and two without introducing any issues that could lead to inaccurate agent timeclock or reporting data.
Logic flow/Psuedo-code:
Step 1: First, an array is created by getting a list of every agent that is currently clocked into the vicidial timeclock. I attempt to create this list by selecting all users from the "vicidial_timeclock_status" where user status is equal to 'LOGIN'.
Step 2: Next I try to create a list of all active Vicidial agents by selecting all users in the "vicidial_live_agents" table
Step 3: In this step I loop over the live agent list created in step 2 and place every user with status = 'PAUSED' into another "paused" list to be acted upon later when/if other criteria is met.
Step 4: Here is where I iterate over a combined list that is contains all users that are clocked in AND are live agents and simply try to make another list of what I am calling exempt users. Exempt users are users with a user_level > 7. (We have various personnel that utilize Vicidial's timeclock but do not place calls are part of their job duties) The users in this list are "immune" to any auto-clockout/logout action.
Step 5: The goal of the logic in this step is to actually find out who has been paused for > than what we have determined to be an unacceptable pause time. (12 minutes) While iterating over each user in the "paused" list the pause time is calculated by subtracting the current epoch from the value of "pause_epoch" in the "vicidial_agent_log" for each user. If the difference between the current epoch and the "pause_epoch is greater than 720 seconds than that user is added to what we are calling a "kill list", more on that later...
Step 6: Here is where the actual loging out and clocking out is executed. The kill list that was created in step 5 is iterated over and each user in the list gets logged out and clocked out by programatically injecting values into the; "vicidial_timeclock_log", "vicidial_timeclock_status", "vicidial_timeclock_audit_log" and finally deleting the user from the "vicidial_live_agents" table. This step is the step that is causing me to do the most thinking since I don't want to update the tables with erroneous data and mess up the reporting/time accounting.
Included is the script I have so far if anyone is interested... (whipped up in Python)
- Code: Select all
#!/usr/bin/python
import MySQLdb, time, datetime, getopt, sys
print "**************************************************************************************************************************"
######################################
## Get and set some time parameters
######################################
now = datetime.datetime.now()
todayDate = now.strftime("%Y-%m-%d")
datedate = now.strftime("%Y-%m-%d %H:%M:%S")
currentEpoch = int(time.mktime(time.localtime()))
# Print out time at heading (mostly for log purposes)
print "[ "+str(datedate), str(currentEpoch)+" ]"
exemptList = []
killList = []
pausedLiveAgents = []
maxPauseTime = 720
###################################################################################################
## Subroutine for help printout
###################################################################################################
def help():
print """
NAME
auto_clockout.py: a program to logout/clockout Vicidial User Agents based on pause time and other
metrics.
SYNOPSIS
auto_clockout.py [OPTIONS]
OPTIONS:
-d : debug, test mode. Will not perform an action on a user.
-a : action mode. Users can be clocked/logged out in this mode.
-h : this help screen.
AUTHOR:
"""
exit()
####################################################################################################
## Parse CLI options
####################################################################################################
options, args = getopt.getopt(sys.argv[1:], 'dah',['debug', 'action', 'help'])
write = False
read = False
for o, a in options:
if o in ('-d', '--debug'):
write = False
read = True
if o in ('-a', '--action'):
write = True
read = True
if o in ('-h', '--help'):
help()
####################################################################################################
## Connect to database
####################################################################################################
db = MySQLdb.connect('vdialDB_IP_address', 'cron', 'supersecurepassword', 'asterisk')
cursor = db.cursor()
####################################################################################################
## Get list of every user that is currently clocked into the Timeclock [timeClockActive]
## VICIDIAL_TIMECLOCK_STATUS
####################################################################################################
cursor.execute("SELECT user from vicidial_timeclock_status where status = 'LOGIN' ORDER BY user")
result = cursor.fetchall()
timeClockActive = result
####################################################################################################
## Get a list of all active Vicidial user agents [liveAgents]
## VICIDIAL_LIVE_AGENTS
####################################################################################################
cursor.execute("SELECT user from vicidial_live_agents ORDER BY user")
result = cursor.fetchall()
liveAgents = result
####################################################################################################
## Get a list of users Clocked in ONLY (and not live agents) [onlyTimeclock]
####################################################################################################
onlyTimeclock = set(timeClockActive).difference(set(liveAgents))
####################################################################################################
## Get a list of users that are Live Agents ONLY (and not clocked in) [onlyAgent]
####################################################################################################
onlyAgent = set(liveAgents).difference(set(timeClockActive))
####################################################################################################
## Get a list of 'PAUSED' [pausedLiveAgents]
## VICIDIAL_LIVE_AGENTS
####################################################################################################
for user in liveAgents:
cursor.execute("SELECT status from vicidial_live_agents WHERE user = '%s'" % user)
result = cursor.fetchone()
try:
print ', '.join(user)+" = "+str(result[0])
if str(result[0]) == 'PAUSED':
pausedLiveAgents.append(user)
except:
break
####################################################################################################
## Create an exemption list for users with levels > 7 for BOTH logged in and clocked in Users
## VICIDIAL_USERS [exemptUsers]
####################################################################################################
for user in set(timeClockActive).union(set(liveAgents)):
cursor.execute("SELECT user_level from vicidial_users WHERE user = '%s'" % user)
result = cursor.fetchone()
try:
if int(result[0]) > 7:
exemptList.append(user)
except:
break
####################################################################################################
## Get a list of users that are clocked in AND logged in [logAndClock]
####################################################################################################
logAndClock = set(liveAgents).intersection(set(timeClockActive))
####################################################################################################
## Iterate over list of [onlyTimeclock] and get clock in time and then make sure the user has not-
## been clocked in but not logged into Vicidial for more than five minutes. NOT IN [exemptList]!
## Add users that fit above to [killList]
## VICIDIAL_TIMECLOCK_STAUTS
####################################################################################################
for user in onlyTimeclock:
if user not in exemptList:
cursor.execute("SELECT event_epoch from vicidial_timeclock_status WHERE user = '%s'" % (user)) #Get login epoch to calc. seconds since clock in
result = cursor.fetchone()
try:
timeclockSecs = currentEpoch - int(result[0])
if timeclockSecs >= 300:
killList.append(user)
print "User : "+', '.join(user)+" has been clocked in for : "+str(timeclockSecs)+" secs. but not logged into VICIDIAL."
except:
break
####################################################################################################
## Iterate over list of [pausedliveAgents] and get epoch time when they went paused status. If pausetime-
## -is greater than max pause time add user to kill list.
## VICIDIAL_AGENT_LOG
####################################################################################################
for user in pausedLiveAgents:
if user not in exemptList:
cursor.execute("SELECT pause_epoch from vicidial_agent_log WHERE user = '%s' ORDER BY agent_log_id DESC LIMIT 1" % (user)) #Get pause epoch to calc. seconds paused
result = cursor.fetchone()
try:
pauseSecs = currentEpoch - int(result[0])
if pauseSecs > maxPauseTime:
killList.append(user)
print "User : "+', '.join(user)+" has been PAUSED for : "+str(pauseSecs)+" secs."
except:
break
####################################################################################################
## Print quick Summary
####################################################################################################
print "-------------------------------------------------------------------\n"
now = datetime.datetime.now()
datedate = now.strftime("%Y-%m-%d %H:%M:%S")
currentEpoch = int(time.mktime(time.localtime()))
print "[ "+str(datedate), str(currentEpoch)+" ]"
print "Total, Users Clocked in : "+ str(len(timeClockActive)) #!!!
print "Total, Users Logged into Vicidial : " + str(len(liveAgents)) #!!!
print "Total, Users Logged in and Clocked in : " +str(len(logAndClock)) #!!!
print "Total, number of users only clocked in : " +str(len(onlyTimeclock))
print "Total, number of users only logged into Vicidial : " +str(len(onlyAgent))
print "Total, number of agents on PAUSE : "+str(len(pausedLiveAgents))
print "Total, number of exempt users : "+str(len(exemptList))
print "So far the KILL LIST contains : " +str(len(killList))+ " total user(s)"
print "-------------------------------------------------------------------\n"
####################################################################################################
## Print [killList]
####################################################################################################
print "Kill List : "
for user in killList:
print ', '.join(user)
print "-------------------------------------------------------------------\n"
####################################################################################################
## Print [exemptList]
####################################################################################################
print "Exempt List : "
for user in exemptList:
print ', '.join(user)
####################################################################################################
## Loop through [killList] and log out and clock out each user
## VICIDIAL_TIMECLOCK_STATUS, VICIDIAL_TIMECLOCK_LOG, VICIDIAL_TIMECLOCK_AUDIT_LOG,-
## - VICIDIAL_LIVE_AGENTS
####################################################################################################
print "-------------------------------------------------------------------\n"
if write:
for user in killList:
now = datetime.datetime.now()
datedate = now.strftime("%Y-%m-%d %H:%M:%S")
currentEpoch = int(time.mktime(time.localtime()))
cursor.execute("SELECT event_epoch FROM vicidial_timeclock_status WHERE user = '%s'" % user)
result = cursor.fetchone()
last_event = int(result[0])
loginSecs = currentEpoch - last_event
### Add an event to the timeclock log
sql = "INSERT INTO vicidial_timeclock_log SET event='LOGOUT', user='%s', user_group='TEST', event_epoch='%d', ip_address='10.10.2.20', login_sec='%d', event_date='%s'"
cursor.execute(sql % (', '.join(user), currentEpoch, loginSecs, datedate))
timeclock_id = cursor.lastrowid
print "Timeclock ID : "+str(timeclock_id),
### Update last login record in the timeclock log
sql="UPDATE vicidial_timeclock_log set login_sec='%d',tcid_link='%s' where event='LOGIN' and user='%s' order by timeclock_id desc limit 1;";
cursor.execute(sql % (int(currentEpoch - last_event), int(timeclock_id), ', '.join(user)))
### Update users timeclock_status record
sql="UPDATE vicidial_timeclock_status set status='LOGOUT', event_epoch='%s', ip_address='10.10.2.20' where user='%s'"
cursor.execute(sql % (int(currentEpoch), ', '.join(user) ))
### Add a record to the timeclock audit log
sql="INSERT INTO vicidial_timeclock_audit_log set timeclock_id='%d', event='LOGOUT', user='%s', user_group='TST', event_epoch='%d', ip_address='10.10.2.10', login_sec='%d', event_date='%s'"
cursor.execute(sql % (timeclock_id, ', '.join(user), int(currentEpoch), int(currentEpoch - last_event), str(datedate)))
### Update last login record in the timeclock audit log
sql="UPDATE vicidial_timeclock_audit_log set login_sec='%d', tcid_link='%d' where event='LOGIN' and user='%s' order by timeclock_id desc limit 1"
cursor.execute(sql % (int(currentEpoch - last_event), timeclock_id, ', '.join(user) ))
# Log user out of vicidial
sql="DELETE from vicidial_live_agents where user='%s'"
print "Logging user: "+', '.join(user)+" out of Vicidial!"
cursor.execute(sql % (', '.join(user)))
print "**************************************************************************************************************************"
ViciBox Redux v.3.1.8
SVN installation option
VERSION: 2.4-307 BUILD: 110112-1254
[/code]