mirror of https://github.com/sipwise/kamailio.git
You can not select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
132 lines
3.5 KiB
132 lines
3.5 KiB
#!/bin/sh
|
|
#
|
|
# examples for gaining some interesting values from SIP DB
|
|
#
|
|
|
|
SERUN=ser
|
|
SERDB=ser
|
|
DBHOST=localhost
|
|
|
|
# ------------
|
|
|
|
|
|
|
|
usage() {
|
|
COMMAND=`basename $0`
|
|
cat <<EOF
|
|
usage: $COMMAND logged_cnt # number of logged-in users
|
|
$COMMAND natted_cnt # number of natted users
|
|
$COMMAND contact_cnt # number of registered contacts
|
|
$COMMAND calls_lh # number of calls in last hour
|
|
$COMMAND calls_cnt # number of calls
|
|
$COMMAND subs_1d # new subscribers in last day
|
|
$COMMAND subs_cnt # number of subscriber
|
|
$COMMAND minutes_cnt # number of minutes
|
|
$COMMAND minutes_1d # number of minutes in last days
|
|
$COMMAND top_calls [dst] [ago] # longest calls
|
|
$COMMAND top_callers [dst] [ago] # most active callers
|
|
|
|
EOF
|
|
} #usage
|
|
|
|
query() {
|
|
mysql -h $DBHOST -u$SERUN -p -e "$1" $SERDB
|
|
}
|
|
|
|
|
|
case $1 in
|
|
|
|
logged_cnt)
|
|
query "select count(distinct username,domain) from location;"
|
|
;;
|
|
natted_cnt)
|
|
query "select count(distinct username,domain) from location
|
|
where flags>0;"
|
|
;;
|
|
contact_cnt)
|
|
query "select count(*) from location;"
|
|
;;
|
|
calls_lh)
|
|
query "select count(*) from acc where sip_method='INVITE' and
|
|
sip_status='200' and
|
|
(DATE_SUB(CURDATE(), INTERVAL 1 hour) <= timestamp);"
|
|
;;
|
|
calls_cnt)
|
|
query "select count(*) from acc where sip_method='INVITE'
|
|
and sip_status='200';"
|
|
;;
|
|
subs_1d)
|
|
query "select count(*) from subscriber where
|
|
(DATE_SUB(CURDATE(), INTERVAL 1 day) <= datetime_created);"
|
|
;;
|
|
subs_cnt)
|
|
query "select count(*) from subscriber;"
|
|
;;
|
|
minutes_cnt)
|
|
query "select sum(unix_timestamp(t2.time)-unix_timestamp(t1.time))/60
|
|
as length from acc t1, acc t2
|
|
where t1.sip_method='INVITE' and t1.sip_status='200'
|
|
and t2.sip_method='BYE' and t1.sip_callid=t2.sip_callid
|
|
and ((t1.totag=t2.totag and t1.fromtag=t2.fromtag)
|
|
or (t1.totag=t2.fromtag and t1.fromtag=t2.totag));"
|
|
;;
|
|
minutes_1d)
|
|
query "select
|
|
sum(unix_timestamp(t2.time)-unix_timestamp(t1.time))/60 as length
|
|
from acc t1, acc t2
|
|
where t1.sip_method='INVITE' and t1.sip_status='200'
|
|
and (DATE_SUB(CURDATE(), INTERVAL 1 hour) <= t1.timestamp)
|
|
and t2.sip_method='BYE' and t1.sip_callid=t2.sip_callid
|
|
and ((t1.totag=t2.totag and t1.fromtag=t2.fromtag)
|
|
or (t1.totag=t2.fromtag and t1.fromtag=t2.totag));"
|
|
;;
|
|
top_calls)
|
|
if [ -n "$2" ] ; then
|
|
LIKE="and t1.i_uri like '%$2%'"
|
|
fi
|
|
if [ -n "$3" ] ; then
|
|
AGO="and (DATE_SUB(CURDATE(), INTERVAL $3 day) <= t1.timestamp)"
|
|
fi
|
|
query "select t1.time,
|
|
((unix_timestamp(t2.time)-unix_timestamp(t1.time))/60) as min,
|
|
t1.username, t1.domain, t1.i_uri
|
|
from acc t1, acc t2
|
|
where t1.sip_method='INVITE' and t1.sip_status='200'
|
|
and t2.sip_method='BYE' and t1.sip_callid=t2.sip_callid
|
|
and ((t1.totag=t2.totag and t1.fromtag=t2.fromtag)
|
|
or (t1.totag=t2.fromtag and t1.fromtag=t2.totag))
|
|
$LIKE
|
|
$AGO
|
|
ORDER by min desc limit 20;"
|
|
;;
|
|
top_callers)
|
|
if [ -n "$2" ] ; then
|
|
LIKE="and t1.i_uri like '%$2%'"
|
|
fi
|
|
if [ -n "$3" ] ; then
|
|
AGO="and (DATE_SUB(CURDATE(), INTERVAL $3 day) <= t1.timestamp)"
|
|
fi
|
|
query "select
|
|
sum((unix_timestamp(t2.time)-unix_timestamp(t1.time))/60) as min,
|
|
t1.username, t1.domain
|
|
from acc t1, acc t2
|
|
where t1.sip_method='INVITE' and t1.sip_status='200'
|
|
and t2.sip_method='BYE' and t1.sip_callid=t2.sip_callid
|
|
and ((t1.totag=t2.totag and t1.fromtag=t2.fromtag)
|
|
or (t1.totag=t2.fromtag and t1.fromtag=t2.totag))
|
|
$LIKE
|
|
$AGO
|
|
GROUP by t1.username,t1.domain
|
|
ORDER by min desc limit 20;"
|
|
|
|
;;
|
|
*)
|
|
usage
|
|
exit 1
|
|
;;
|
|
|
|
esac
|
|
|
|
|
|
|