<?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