Vicidial Agent Log structure & table synchronization iss

[rey_philip]

Hi to all or developers of vicidial,

I need your help on Asterisk database (vicidial). By the way
Im Rey Im an intermediate PHP programmer, mostly I do reports
and statistics based from asterisk database. I also do manage the database in our company.

The first problem is that vicidial_agent_log and vicidial_log were not synchronized when it comes to status, example one agent has 25 sales
on vicidial_agent_log then if I look at vicidial_log his sales is just 24. The good part is that vicidial_agent_log is accurate than vicidial_log, so we have atleast an accurate table to get the reports.

But here's the second problem, yes vicidial_agent_log is accurate but when you query on that table like this
`select user, status from vicidial_agent_log where user = 'user1' and
status = 'sale' and event_time between '2007.....' and '2007....'`
it takes atleast 2 seconds to output the results.

What I did is that, I look at the structure of the vicidial_agent_log table
and saw that there are no index, just a primary key which is agent_log_id which is useless when I execute my query. So what I did is that I back up the database and created my own database in my computer. I did some experimentation on vicidial_agent_log table, I index the user, status and event_time and when I execute the query, it just takes .002 secs, which is very effective, but I haven't implement it in our production yet cause I want to make sure that If I edit the vicidial_agent_log table nothing will happen on the vicidial system.

So guys (developers of vicidial or matt), just want to ask. Is it okey
to add an index user, status and event_time to vicidial_agent_log
and nothing bad will happen to vicidial?

Also, I want to ask, what are the factors why the vicidial_log and vicidial_agent_log are not synchronized? And how can we fix that?

I really need your advice and suggestions.
Thanks,

Rey

[mflorell]

Thanks for the in-depth explanation.

As for indexing, I cannot see any issues with that, and I would like to add the recommended indexes if your tests work well in production.

As for vicidial_log and vicidial_agent_log, the log has a record for every outbound call, while the agent_log only has records of calls that went to agents.

In the example that you gave, what is the vicidial_log status for the one call that is not marked as a SALE?

[rey_philip]

By the way matt thanks for the reply.

In the example I gave to you, the vicidial_agent_log has the accurate data in terms of agent logs.

For example has 60 sales appeared on vicidial_agent_log at the end of the shift. When I look at the vicidial_log it is always less than the output of vicidial_agent_log.

What I did is that I compared all the leads of both tables (vicidial_log and vicidial_agent_log). Then I look for their difference. The result of that, will be queried in the vicidial_log whether it exists on the table. And the problem is that, the lead is inserted in the vicidial_agent_log table and in the other hand the lead is not existing in the vicidial_log table.

Visual Representation:
ex:

vicidial_log
lead_id | status | user
3242 | SALE | 2009
2445 | SALE | 2009
3422 | SALE | 2009
6545 | SALE | 2009
6577 | SALE | 2009
3434 | SALE | 2009

vicidial_agent_log
lead_id | status | user
3242 | SALE | 2009
6778 | SALE | 2009
2445 | SALE | 2009
3422 | SALE | 2009
6545 | SALE | 2009
6577 | SALE | 2009
3434 | SALE | 2009

The italic row in the vicidial_agent_log is tagged as SALE. But when
I look that lead in the vicidial_log table, it does'nt exists.

My query:
`select * from vicidial_log where lead_id = 6778` result 0

Based on my observation from the past few days, some agents have accurate data on both tables and others are not.

What can you suggest to fix this kind problem?

Thanks.

Rey

[mflorell]

What is the status of that lead in vicidial_list?

[rey_philip]

Actually the status of the lead in the vicidial_list is same in the vicidial_agent_log.

[mflorell]

Do you do any inbound calling by chance?

[rey_philip]

nope, we only do outbound calls. I will just investigate more on this.

Thanks.

[janokary]

I have the same problem with logging and I think there is some problem in inserting rows in vicidial_log

What I did is a compare between call_log and vicidial_list and I found that for every entry in call_log there is one in vicidial_list. The key hear was the phone number.

When I did the same for vicidial_list and vicidial_log i found that there is a 8% lines loss. That is there are entries in vicidial_list and in call_log that do not exist in vicidial_log. The key for the comparison is again the number.

I must say here that I only call the same numbers once a month.

I made some more investigation and I found out that the most of calls that have this problem are those that drop in the first 3 seconds.

I use manual dialing

here is the output of missing lines for 2 days

[Length in sec] Mising entries in vicidial_log
0 114
1 311
2 25
3 18
4 15
5 4
6 4
7 1
8 4
9 5
10 4
12 2
sum 507


Maybe this has something to do with your problem also.

[mflorell]

Thanks for posting this, did you notice any consistency of the numbers that were not logged in vicidial_log?

[janokary]

In every case I get a DEAD status in vicidial_manager



In addition to that many agents complain that after a succesfull call they have to dial 3-4 times in order to get a call. The first 3-4 allways hangup in the first second.

When they don't click "Dial next Number" too quickly the can get a good call at the fist time.

[mflorell]

OK, so this is with Manual Dial only? or both manual and auto dialing?

Have you tried using HotKeys with manual dial?

Could you explain a bit more how you use VICIDIAL?

[janokary]

sorry I thing me and angelos are crossposting
I'll continue the conversation here:

http://www.eflo.net/VICIDIALforum/viewt ... highlight=

sorry for the inconvenience