I need to get your thoughts on something.. :) I am trying to create a system to process the CDR call logs for department accounting.. I think there are two ways of doing it.. Either I can create an AGI that will run on the "h" extension and will lookup the last entry that matches the account code of the call that just ended in the MySQL CDR and calculate the call cost immediately.. Or I can use a cron job say every 5 mins to move the Master.csv file to a temporary location and then process all the lines in the csv and put the results into the DB and then delete the Master.csv from that temporary location.. Which way will be better in terms of reliability and performance? What will the issues be if the Master.csv is being updated at the exact moment my cron job tries to move it? is there any file locking or a method of delaying Asterisk's write or the cron's move operation till the file is availible? Thanks for you thoughts.. Later
Do it with MySQL CDR and you should avoid all of those issues; just add a field to store the cost to the schema and compute it whenever makes sense. Dave ====================================================================David C. Troy [dave@toad.net] 410-384-2500 Sales ToadNet - Want to go fast? 410-544-1329 FAX 570 Ritchie Highway, Severna Park, MD 21146-2925 www.toad.net On Fri, 14 Nov 2003, WipeOut wrote:> I need to get your thoughts on something.. :) > > I am trying to create a system to process the CDR call logs for > department accounting.. > > I think there are two ways of doing it.. Either I can create an AGI that > will run on the "h" extension and will lookup the last entry that > matches the account code of the call that just ended in the MySQL CDR > and calculate the call cost immediately.. > Or > I can use a cron job say every 5 mins to move the Master.csv file to a > temporary location and then process all the lines in the csv and put the > results into the DB and then delete the Master.csv from that temporary > location.. > > Which way will be better in terms of reliability and performance? > > What will the issues be if the Master.csv is being updated at the exact > moment my cron job tries to move it? is there any file locking or a > method of delaying Asterisk's write or the cron's move operation till > the file is availible? > > Thanks for you thoughts.. > > Later > > _______________________________________________ > Asterisk-Users mailing list > Asterisk-Users@lists.digium.com > http://lists.digium.com/mailman/listinfo/asterisk-users >
One of the major reasons you use a DBMS is to solve this exact kind of problem. Basically what you want is both 1) concurent access by two processes (asterick and your accounting program 2) Each process to see "conssitant" data. this is a reader should never "see" half-writtencall records. I don't see the problem. A call log is produced with just one SQL select querry on the database. Assuming call records are written within a transaction, any process that queries the DBMS will only get data from commited transactions and simply will not see yet to be commited call data. If Asterisk fails to enclose it's incerts to the DBMS in a transaction this is a bug that can be easly fixed --- WipeOut <wipe_out@onetel.com> wrote:> I need to get your thoughts on something.. :) > > I am trying to create a system to process the CDR call logs for > department accounting.. > > I think there are two ways of doing it.. Either I can create an AGI > that > will run on the "h" extension and will lookup the last entry that > matches the account code of the call that just ended in the MySQL CDR > > and calculate the call cost immediately.. > Or > I can use a cron job say every 5 mins to move the Master.csv file to > a > temporary location and then process all the lines in the csv and put > the > results into the DB and then delete the Master.csv from that > temporary > location.. > > Which way will be better in terms of reliability and performance? > > What will the issues be if the Master.csv is being updated at the > exact > moment my cron job tries to move it? is there any file locking or a > method of delaying Asterisk's write or the cron's move operation till > > the file is availible? > > Thanks for you thoughts.. > > Later > > _______________________________________________ > Asterisk-Users mailing list > Asterisk-Users@lists.digium.com > http://lists.digium.com/mailman/listinfo/asterisk-users====Chris Albertson Home: 310-376-1029 chrisalbertson90278@yahoo.com Cell: 310-990-7550 Office: 310-336-5189 Christopher.J.Albertson@aero.org KG6OMK __________________________________ Do you Yahoo!? Protect your identity with Yahoo! Mail AddressGuard http://antispam.yahoo.com/whatsnewfree
All -- I have been making a variety of enhancments to app_queue.c; I just added the ability to start recording a queue call from the time it is answered going forward. This is an alternative to executing the Monitor app before a Queue call, which ends up recording all MOH waiting, plus abandoned calls. My latest patch (1.04) is available here: http://bugs.digium.com/bug_view_page.php?bug_id=0000214 Dave ====================================================================David C. Troy [dave@toad.net] 410-384-2500 Sales ToadNet - Want to go fast? 410-544-1329 FAX 570 Ritchie Highway, Severna Park, MD 21146-2925 www.toad.net
Funny, I am doing the same at the moment... :) We are allowing * to dump call records onto a remote database server. Once there we can do all sort of things with it. My only concern is if this remote server goes down! What happens to the call records which were not written to remote server? The records could be written to * server as well, but that is extra CPU cycles to waste. Ta SJ
Senad Jordanovic wrote:> Funny, I am doing the same at the moment... :) > > We are allowing * to dump call records onto a remote database server. > Once there we can do all sort of things with it. > > My only concern is if this remote server goes down!Use more than one database server... -- JustThe.net Internet & New Media Services 22674 Motnocab Road * Apple Valley, CA 92307-1950 Steve Sobol, Proprietor 888.480.4NET (4638) * 248.724.4NET * sjsobol@JustThe.net
--- Steve Sobol <sjsobol@JustThe.net> wrote:> Senad Jordanovic wrote: > > > Funny, I am doing the same at the moment... :) > > > > We are allowing * to dump call records onto a remote database > server. > > Once there we can do all sort of things with it. > > > > My only concern is if this remote server goes down! > > Use more than one database server... > > -- > JustThe.net Internet & New Media Services > 22674 Motnocab Road * Apple Valley, CA 92307-1950 > Steve Sobol, Proprietor > 888.480.4NET (4638) * 248.724.4NET * sjsobol@JustThe.net > > _______________________________________________ > Asterisk-Users mailing list > Asterisk-Users@lists.digium.com > http://lists.digium.com/mailman/listinfo/asterisk-users====Chris Albertson Home: 310-376-1029 chrisalbertson90278@yahoo.com Cell: 310-990-7550 Office: 310-336-5189 Christopher.J.Albertson@aero.org KG6OMK __________________________________ Do you Yahoo!? Protect your identity with Yahoo! Mail AddressGuard http://antispam.yahoo.com/whatsnewfree
> I think there are two ways of doing it.. Either I can create an AGI that > will run on the "h" extension and will lookup the last entry that > matches the account code of the call that just ended in the MySQL CDR > and calculate the call cost immediately..Use the database. I'd recommend Postgres myself but to each their own.> What will the issues be if the Master.csv is being updated at the exact > moment my cron job tries to move it? is there any file locking or a > method of delaying Asterisk's write or the cron's move operation till > the file is availible?Cheat. do this: mv Master.csv Master.old do sleep 1 fuser Master.old > /dev/null while [ $? -eq 0 ] when you mv a file (within the same filesystem) you don't change its inode; if * is accessing the file it notices nothing. Now you simply wait for * to close the file (man fuser) and it's all yours, since * will create Master.csv if it can't find it. I throw the sleep in there simply to be nice to the system. Seriously though, if you're gonna be throwing this into a database anyway, why not store it there in the first place?? Regards, Andrew