MySQL accounting/Reporting issue

[PManley]

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

[williamconley]

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.

[PManley]

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.

[williamconley]

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.

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.

[PManley]

Thanks Bill - I'll have a look.

Regards,

Peter M.

[PManley]

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.