Created
June 26, 2012 10:19
-
-
Save zyphlar/2994842 to your computer and use it in GitHub Desktop.
Cheap and Dirty Asterisk Logfile Analysis
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| <?php | |
| /* Cheap and Dirty Asterisk Logfile Analysis | |
| * by Will Bradley, http://willbradley.name | |
| * Released to the public domain, 6-26-2012 | |
| * | |
| * To install, modify the $dbname and other variables, | |
| * create the MySQL database, user, and permissions, | |
| * and double-check the asterisk logfile paths/formats below. | |
| * This is a very cheap script, use at your own risk! | |
| * | |
| * The script analyzes logfiles with regex to get stats | |
| * on % of calls answered versus "lost" versus sent to voicemail. | |
| * I wrote it because the CDR on my FreePBX install doesn't show | |
| * a difference between "Answered" and "Voicemail." | |
| */ | |
| ?><html> | |
| <style type="text/css"> | |
| body { font-family: Calibri, Arial, Helvetica, sans-serif; } | |
| form, input { display: inline; } | |
| </style> | |
| <body> | |
| <form action="#" method="post"><input type="hidden" name="dump" value="1"><input type="submit" value="Dump & Reload" /></form> | |
| <form action="#" method="post"><input type="hidden" name="c" value="1"><input type="submit" value="Load Calls" /></form> | |
| <form action="#" method="post"><input type="hidden" name="a" value="1"><input type="submit" value="Load Answered" /></form> | |
| <form action="#" method="post"><input type="hidden" name="m" value="1"><input type="submit" value="Load Missed" /></form> | |
| <form action="#" method="post"><input type="hidden" name="v" value="1"><input type="submit" value="Load Voicemail" /></form> | |
| <php | |
| $start = 8; | |
| if(isset($_POST['start']) && 0 <= $_POST['start'] && $_POST['start'] < 24) { | |
| $start = $_POST['start']; | |
| } | |
| $end = 19; | |
| if(isset($_POST['end']) && 0 <= $_POST['end'] && $_POST['end'] < 24) { | |
| $end = $_POST['end']; | |
| } | |
| $debug = false; | |
| $dbuser = ""; // your username | |
| $dbpass = ""; // your password | |
| $dbname = ""; // your database | |
| $dbhost = ""; // your db host | |
| $mysqli = new mysqli($dbhost, $dbuser, $dbpass, $dbname); | |
| /* check connection */ | |
| if ($mysqli->connect_errno) { | |
| printf("Connect failed: %s\n", $mysqli->connect_error); | |
| exit(); | |
| } | |
| if($_POST['dump'] == "1") { | |
| if(!$mysqli->real_query("DROP TABLE calls")){ | |
| printf("Errormessage: %s\n", $mysqli->error); | |
| } | |
| $mysqli->real_query("CREATE TABLE IF NOT EXISTS calls ( id VARCHAR(10), extension VARCHAR(10), date DATETIME, answered INT, voicemail INT, recording INT, code VARCHAR(3) )"); | |
| } | |
| if($_POST['c'] == "1") { | |
| $rings = array_merge( | |
| greplogs_calls('grep ringing /var/log/asterisk/full.3',"/-- SIP/","3"), | |
| greplogs_calls('grep ringing /var/log/asterisk/full.2',"/-- SIP/","2"), | |
| greplogs_calls('grep ringing /var/log/asterisk/full.1',"/-- SIP/","1"), | |
| greplogs_calls('grep ringing /var/log/asterisk/full',"/-- SIP/","0") | |
| ); | |
| if($debug)var_dump($rings); | |
| foreach($rings as $ring) { | |
| if(!$mysqli->query("INSERT INTO calls ( id, extension, date) VALUES ('$ring[0]','$ring[1]','$ring[2]')")){ | |
| printf("Errormessage: %s\n", $mysqli->error); | |
| } | |
| } | |
| $qb = query("SELECT id FROM calls group by id having count(*) >= 2",$mysqli); | |
| foreach($qb as $qqq) { | |
| query("DELETE FROM calls WHERE id = $qqq[0]",$mysqli); | |
| } | |
| } | |
| if($_POST['v'] == "1") { | |
| $records = array_merge( | |
| greplogs('grep "Recording the" /var/log/asterisk/full.3',"//","3"), | |
| greplogs('grep "Recording the" /var/log/asterisk/full.2',"//","2"), | |
| greplogs('grep "Recording the" /var/log/asterisk/full.1',"//","1"), | |
| greplogs('grep "Recording the" /var/log/asterisk/full',"//","0") | |
| ); | |
| if($debug)var_dump($records); | |
| $list = ""; | |
| foreach($records as $record) { | |
| $list .= "id='$record' OR "; | |
| } | |
| $query = "UPDATE calls SET recording='1' WHERE ".$list."id=''"; | |
| if(!$mysqli->query($query)){ | |
| printf("Errormessage: %s\n", $mysqli->error); | |
| } | |
| } | |
| if($_POST['m'] == "1") { | |
| $voicemails = array_merge( | |
| greplogs('grep Nobody /var/log/asterisk/full.3',"//","3"), | |
| greplogs('grep Nobody /var/log/asterisk/full.2',"//","2"), | |
| greplogs('grep Nobody /var/log/asterisk/full.1',"//","1"), | |
| greplogs('grep Nobody /var/log/asterisk/full',"//","0") | |
| ); | |
| if($debug)var_dump($voicemails); | |
| $list = ""; | |
| foreach($voicemails as $voicemail) { | |
| $list .= "id='$voicemail' OR "; | |
| } | |
| $query = "UPDATE calls SET voicemail='1' WHERE ".$list."id=''"; | |
| if(!$mysqli->query($query)){ | |
| printf("Errormessage: %s\n", $mysqli->error); | |
| } | |
| } | |
| if($_POST['a'] == "1") { | |
| $answers = array_merge( | |
| greplogs('grep answered /var/log/asterisk/full.3',"/-- SIP/","3"), | |
| greplogs('grep answered /var/log/asterisk/full.2',"/-- SIP/","2"), | |
| greplogs('grep answered /var/log/asterisk/full.1',"/-- SIP/","1"), | |
| greplogs('grep answered /var/log/asterisk/full',"/-- SIP/","0") | |
| ); | |
| if($debug)var_dump($answers); | |
| $list = ""; | |
| foreach($answers as $answer) { | |
| $list .= "id='$answer' OR "; | |
| } | |
| $query = "UPDATE calls SET answered='1' WHERE ".$list."id=''"; | |
| if(!$mysqli->query($query)){ | |
| printf("Errormessage: %s\n", $mysqli->error); | |
| } | |
| } | |
| if($_POST['s'] == "1") { | |
| $stats = array(); | |
| foreach($rings as $call){ | |
| $result = ""; | |
| $answered = "0"; | |
| $voicemail = "0"; | |
| $recording = "0"; | |
| if(in_array($call[0], $answers)){ | |
| $answered = "1"; | |
| $result .= "a"; | |
| } | |
| if(in_array($call[0], $voicemails)){ | |
| $voicemail = "1"; | |
| $result .= "m"; | |
| } | |
| if(in_array($call[0], $records)){ | |
| $recording = "1"; | |
| $result .= "v"; | |
| } | |
| if(!$mysqli->query("UPDATE calls SET answered='$answered', voicemail='$voicemail', recording='$recording', code='$result' WHERE id='$call[0]'")){ | |
| printf("Errormessage: %s\n", $mysqli->error); | |
| } | |
| $stats[] = array($call[0], $result); | |
| } | |
| } | |
| $qa = query("SELECT date_format(date,'%b %e') FROM calls WHERE (HOUR(date) > $start AND HOUR(date) < $end) ORDER BY date ASC LIMIT 1",$mysqli); | |
| $qb = query("SELECT date_format(date,'%b %e') FROM calls WHERE (HOUR(date) > $start AND HOUR(date) < $end) ORDER BY date DESC LIMIT 1",$mysqli); | |
| echo "<h2>Inbound Call Statistics Between ".($start+1).":00-$end:00, ".$qa[0][0]." - ".$qb[0][0]."</h2>"; | |
| $qa = query("SELECT count(id) FROM calls WHERE (HOUR(date) > $start AND HOUR(date) < $end)",$mysqli); | |
| echo "(".$qa[0][0].")"; | |
| $totalcalls = $qa[0][0]; | |
| echo " calls | "; | |
| $qb = query("SELECT count(id) FROM calls WHERE answered = '1' AND (HOUR(date) > $start AND HOUR(date) < $end)",$mysqli); | |
| echo "(".$qb[0][0].")"; | |
| echo " answered | "; | |
| $qb = query("SELECT count(id) FROM calls WHERE (voicemail = '1' OR recording = '1') AND (HOUR(date) > $start AND HOUR(date) < $end)",$mysqli); | |
| echo "(".$qb[0][0].")"; | |
| echo " missed "; | |
| $qb = query("SELECT count(id) FROM calls WHERE voicemail = '1' AND recording IS NULL AND (HOUR(date) > $start AND HOUR(date) < $end)",$mysqli); | |
| echo "[".$qb[0][0]; | |
| echo " lost, "; | |
| $qb = query("SELECT count(id) FROM calls WHERE recording = '1' AND (HOUR(date) > $start AND HOUR(date) < $end)",$mysqli); | |
| echo $qb[0][0]; | |
| echo " voicemails] | "; | |
| $qb = query("SELECT count(id) FROM calls WHERE answered IS NULL AND voicemail IS NULL AND recording IS NULL AND (HOUR(date) > $start AND HOUR(date) < $end)",$mysqli); | |
| echo "(".$qb[0][0].")"; | |
| $totalhangups = $qb[0][0]; | |
| echo " hangups "; | |
| $qa = query("SELECT round(count(answered)/count(date)*100), round((count(voicemail)-count(recording))/count(date)*100), round(count(recording)/count(date)*100) FROM calls WHERE (HOUR(date) > $start AND HOUR(date) < $end)",$mysqli); | |
| echo "<p>Overall ".$qa[0][0]."% Answer Rate, ".$qa[0][1]."% Loss Rate, ".$qa[0][2]."% Voicemail Rate, ".round($totalhangups/$totalcalls*100)."% Hangup Rate.</p>"; | |
| echo "<h3>Percent by Property</h3>"; | |
| $qb = query("SELECT extension, count(answered), count(date), round(count(answered)/count(date)*100), round((count(voicemail)-count(recording))/count(date)*100) FROM calls WHERE (HOUR(date) > $start AND HOUR(date) < $end) GROUP BY extension",$mysqli); | |
| echo "<table border=1>"; | |
| echo "<tr><th>Property</th><th>Answered / # of Calls</th><th>Answer Rate</th><th>Loss Rate</th></tr>"; | |
| foreach($qb as $q) { | |
| echo "<tr><td>".prop($q[0])."</td><td>$q[1] / $q[2]</td><td>$q[3]%</td><td>$q[4]%</td></tr>"; | |
| } | |
| echo "</table>"; | |
| echo "<table border=1>"; | |
| echo "<tr><th>Property</th><th>% of Total Calls</th><th>Loss Rate</th></tr>"; | |
| foreach($qb as $q) { | |
| echo "<tr><td>".prop($q[0])."</td><td>".round($q[2]/$totalcalls*100)."%</td><td>$q[4]%</td></tr>"; | |
| } | |
| echo "</table>"; | |
| echo "<h3>Per Day</h3>"; | |
| $qb = query("SELECT round((COUNT(voicemail)-count(recording))/COUNT(DISTINCT(DAYOFYEAR(date)))), round(COUNT(answered)/COUNT(DISTINCT(DAYOFYEAR(date)))), round(COUNT(date)/COUNT(DISTINCT(DAYOFYEAR(date)))) FROM calls WHERE (HOUR(date) > $start AND HOUR(date) < $end)",$mysqli); | |
| foreach($qb as $q) { | |
| echo "<p>Avg $q[2] calls per day: $q[1] answered, $q[0] lost</p>"; | |
| } | |
| $qqq = query("SELECT * FROM calls WHERE HOUR(date) > $start AND HOUR(date) < $end",$mysqli); | |
| echo "<br/><table>"; | |
| echo "<tr><th>Call #</th><th>Extension</th><th>Date</th><th>Ans</th><th>Mis</th><th>VM</th></tr>"; | |
| foreach($qqq as $stat){ | |
| echo "<tr><td>$stat[0]</td><td>".prop($stat[1])."</td><td>$stat[2]</td><td>".num($stat[3],"A")."</td><td>".num($stat[4],"M")."</td><td>".num($stat[5],"V")."</td></tr>"; | |
| } | |
| echo "</table>"; | |
| if($debug){ | |
| foreach($stats as $stat){ | |
| echo $stat[0].": ".$stat[1]."<br/>"; | |
| } | |
| } | |
| function prop($prop) { | |
| // this was originally for renaming extensions to property (office) names | |
| return "x".$prop | |
| } | |
| function num($num, $output) { | |
| if($num == "1") { | |
| return $output; | |
| } | |
| } | |
| function query($querystring,$mysqli) { | |
| $output = array(); | |
| if ($mysqli->real_query($querystring)) { | |
| do { | |
| /* store first result set */ | |
| if ($result = $mysqli->use_result()) { | |
| while ($row = $result->fetch_row()) { | |
| $output[] = $row; | |
| } | |
| $result->close(); | |
| } | |
| } while ($mysqli->next_result()); | |
| } | |
| else { | |
| printf("Errormessage: %s\n", $mysqli->error); | |
| $j = mysqli_warning_count($db); | |
| if ($j > 0) { | |
| $e = mysqli_get_warnings($db); | |
| for ($i = 0; $i < $j; $i++) { | |
| var_dump($e); | |
| $e->next(); | |
| } | |
| } | |
| } | |
| return $output; | |
| } | |
| function greplogs($grepString, $filterString, $prefix) { | |
| $debug = false; | |
| $recoutput = array(); | |
| exec($grepString, $recoutput); | |
| if(isset($filterString)){ | |
| $recoutput = preg_grep($filterString,$recoutput); | |
| } | |
| $recordings = array(); | |
| if($debug)echo "<h1>Results: ".sizeof($recoutput)."</h1>"; | |
| echo "<dl>"; | |
| foreach($recoutput as $item) { | |
| echo "<dt>"; | |
| if($debug) {echo $item;} | |
| echo "</dt>"; | |
| echo "<dd>"; | |
| preg_match("/VERBOSE\[(.*)\]/",$item,$matches); | |
| if($debug) { var_dump($matches[1]);} | |
| $recordings[] = $prefix.$matches[1]; | |
| echo "</dd>"; | |
| } | |
| echo "</dl>"; | |
| return $recordings; | |
| } | |
| function greplogs_calls($grepString, $filterString, $prefix) { | |
| $debug = false; | |
| $recoutput = array(); | |
| exec($grepString, $recoutput); | |
| if(isset($filterString)){ | |
| $recoutput = preg_grep($filterString,$recoutput); | |
| } | |
| $recordings = array(); | |
| if($debug)echo "<h1>Results: ".sizeof($recoutput)."</h1>"; | |
| echo "<dl>"; | |
| foreach($recoutput as $item) { | |
| echo "<dt>"; | |
| if($debug) {echo $item;} | |
| echo "</dt>"; | |
| echo "<dd>"; | |
| preg_match("/VERBOSE\[(.*)\]/",$item,$matches); | |
| preg_match("/SIP\/(.*)-/",$item,$ext); | |
| preg_match("/^\[(.*)\] VER/",$item,$date); | |
| $parsed_date = date("c",strtotime($date[1])); | |
| if($debug) { var_dump($matches[1]);} | |
| $recordings[] = array($prefix.$matches[1],$ext[1],$parsed_date); | |
| echo "</dd>"; | |
| } | |
| echo "</dl>"; | |
| return $recordings; | |
| } | |
| ?> | |
| </body> | |
| </html> |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
it is not working..table created but nothing happened