Thirdlane – Monitor cdr – Reseller report

<?php

// debug mode, make debug = 1
$debug = 0;

$servername = "localhost";
$username = "root";
$password = "passw0rd";
$db_cdr = "pbxlogs";
$db_conf = "pbxconf";
$max_count = 10;

// Create connection
$conn = new mysqli($servername, $username, $password, $db_cdr);
if ($conn->connect_error) {
        die("Connection asterisk failed: " . $conn->connect_error);
}

$sql  = "select userfield, count(*) as total ";
$sql .= "from cdr ";
$sql .= "where dst like '011%' and ";
$sql .= "calldate >= NOW() - INTERVAL 5 MINUTE ";
$sql .= "group by userfield ";
$sql .= "having total > " . $max_count;


print $sql . "\n";

$result = $conn->query($sql);

if ($result->num_rows > 0) {
        while($row = $result->fetch_assoc()) {
                print "tenant: " . $row["userfield"]. "total: " . $row["total"] . "\n";
                send_mail($row["userfield"], $row["total"]);
        }

}

$conn->close();
function send_mail($tenant, $total) {

        $to      = 'support@wuyifan.com';
        $subject = 'Urgent: pbx potential hacked tenant = ' . $tenant;
        $message = 'over ' . $total . " international calls in last 5 mins";
        $headers = 'From: no-reply@prodosec.com' . "\r\n";

        mail($to, $subject, $message, $headers);

}

?>

check number of international calls in last 5 mins, if over max value, send a warning email

Reseller report

#!/bin/bash
user=root
password=hide
database=pbxconf

filename=/home/hide/tenant_report.csv

rm -f "$filename"
mysql --user="$user" --password="$password" --database="$database"<<EOFMYSQL
select current_date;

drop TABLE IF EXISTS tenant_report;

CREATE TABLE tenant_report (
  tenant varchar(40) NOT NULL DEFAULT '',
  reseller varchar(64) DEFAULT NULL,
  extension_count tinyint NOT NULL,
  provision_count smallint(5) unsigned NOT NULL DEFAULT '0',
  register_count smallint(5) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (tenant)
);

insert into tenant_report (tenant, reseller, extension_count, provision_count)
(
select
tenant, tag, exts,
if (count(*)=1, 0, count(*)) as provision_count
from tenants_advanced_view
left join devices
on
(tenants_advanced_view.id = devices.tenantid)
group by
tenant, tag, exts
);

select * into outfile "$filename"
fields terminated by ','
escaped by ''
lines terminated by '\n'
FROM
(
select 'tenant', 'reseller', 'extension_count', 'provision_count'
union all
select tenant, reseller, extension_count, provision_count from tenant_report
) test;

EOFMYSQL