MySQL accounting/Reporting issue

All installation and configuration problems and questions

Moderators: gerski, enjay, williamconley, Op3r, Staydog, gardo, mflorell, MJCoate, mcargile, Kumba, Michael_N

MySQL accounting/Reporting issue

Postby PManley » Tue Jun 23, 2020 1:15 pm

Hello,

We are seeing an interesting problem in the MySQL asterisk DB and with some of the reports. At the end of the day, we use the vicidial_agent_log table to compute the billable hours from the database. We do this by taking the SUM(wait_sec+talk_sec+dispo_sec). However, when we do the same calculation a day later the data appears to have changed. We get a lower number for the same sum over the same period.

We see the same issue if we execute the Agent Performance Detail and Team Performance Detail report for the same time period on different days (e.g. running it 45 minutes after end of shift produces different results if we run it again the next day for the same time period).

Any thoughts on why this would occur.

We're running VERSION: 2.14-708a BUILD: 190414-0924 on a Clustered configuration where we've got a MASTER and SLAVE DB Server, 4 Web Servers and 10 Dialer Servers.

Any insights would be appreciated.

Thanks and regards,

Peter Manley
PManley
 
Posts: 24
Joined: Mon Apr 08, 2019 3:47 pm

Re: MySQL accounting/Reporting issue

Postby williamconley » Tue Jun 23, 2020 1:22 pm

crontab -l

consider: /usr/share/astguiclient/AST_cleanup_agent_log.pl, /usr/share/astguiclient/AST_manager_kill_hung_congested.pl, but there may be others. Including calls that had not yet completed and cleanup scripts which go back after the fact and fill in values.
Vicidial Installation and Repair, plus Hosting and Colocation
Newest Product: Vicidial Agent Only Beep - Beta
http://www.PoundTeam.com # 352-269-0000 # +44(203) 769-2294
williamconley
 
Posts: 20018
Joined: Wed Oct 31, 2007 4:17 pm
Location: Davenport, FL (By Disney!)

Re: MySQL accounting/Reporting issue

Postby PManley » Tue Jun 23, 2020 1:36 pm

Bill,

I see the following in the crontab:

### fix the vicidial_agent_log once every hour and the full day run at night
33 * * * * /usr/share/astguiclient/AST_cleanup_agent_log.pl
50 0 * * * /usr/share/astguiclient/AST_cleanup_agent_log.pl --last-24hours

Can you offer some insight why this needs to be done? What does the --last-24hours flag do?

Thanks

Peter M.
PManley
 
Posts: 24
Joined: Mon Apr 08, 2019 3:47 pm

Re: MySQL accounting/Reporting issue

Postby williamconley » Tue Jun 23, 2020 1:38 pm

Execute the file with "--help" as an added switch for options.

But if you want more ... use a text editor on the file in question. don't take anybody else's word for it. 8-)

There are notes at the top which generally answer most question, and the sql queries and conditions upon which they run are IN the file. Have at it.

Also have a look in /usr/src/astguiclient/trunk/docs ... there are a lot of questions answered in this folder related to their associated scripts.
Vicidial Installation and Repair, plus Hosting and Colocation
Newest Product: Vicidial Agent Only Beep - Beta
http://www.PoundTeam.com # 352-269-0000 # +44(203) 769-2294
williamconley
 
Posts: 20018
Joined: Wed Oct 31, 2007 4:17 pm
Location: Davenport, FL (By Disney!)

Re: MySQL accounting/Reporting issue

Postby PManley » Tue Jun 23, 2020 2:21 pm

Thanks Bill - I'll have a look.

Regards,

Peter M.
PManley
 
Posts: 24
Joined: Mon Apr 08, 2019 3:47 pm

Re: MySQL accounting/Reporting issue

Postby PManley » Tue Jul 07, 2020 2:23 pm

Bill,

As an FYI... we're seeing a difference of about 10% correction in the billable agent time from when the report was generated an hour after end of shift (7:00 PM EST) vs. after the final "/usr/share/astguiclient/AST_cleanup_agent_log.pl --last-24hours" command was run at 0:50 AM EST.

This is significant. Is this documented anywhere in the VICI documentation?

Thanks and regards,

Peter M.
PManley
 
Posts: 24
Joined: Mon Apr 08, 2019 3:47 pm


Return to Support

Who is online

Users browsing this forum: No registered users and 89 guests