SCENARIO:
We are using DMASOFTLAB Radius Manager as our billing system and Playsms along with KANNEL sms gateway is installed on same server. We would like to a function in playsms that if user sends sms with specific keyword like `userinfo USERNAME` , then the scritp should trigger and retreieve user information & sms back to the sender in specified format.
In this example, we are using DMASOFTLAB Radius Manager as our billing system, and KANNEL along-with the playSMS is already configured and in working condition. Kannel+playSMS configuration details have already been described briefly with examples in my previous posts.
We have created an script on the billing system which fetches the user account status and other information from the MYSQL database and print them as per our defined format.
This is just for demonstration purpose. the script have lot of junk data and should be modified before production deployment. I am just sharing some thoughts and ideas only 🙂
REQUIREMENTS
- Radius Manager 4.x Version
- 127.0.0.1 must be added in ALLOWED NASes in all services
- Kannel SMS gateway for IN/OUT sms
- playSMS
SCRIPT !
Script is as follows FYR. Hope it may help someone
cat userinfo.sh
#!/bin/bash #set -x # Thsi script is designed SPECIFICALLY for dmasoftlab radius manager 4.1.x series # This script check user status , Expiry Date, Service Plan, Data Used # mainly it is designed to work with playsms , so that user sends sms , and playsms will execute this script with username # and reply back with the information it fetches # Designed by : Syed Jahanzaib # aacable @ hotmail.com = https://aacable.wordpress.com # Last Modified on 9th June, 2015 DATE=$(date '+%Y-%m-%d_____%H-%M-%S') TMP="/tmp/userinfo_incoming_sms__$DATE.sms" # Strip user name and card number separate oterhwise playsms will treat both variables as one echo $1 > $TMP USR=`cat $TMP | awk {' print $1 '}` # MYSQL USER NAME AND PASSWORD Variables SQLUSER="SQL_ROOT_USER" SQLPASS="SQL_ROOT_PASSWORD" SQLHOST="localhost" SQLPORT="3306" DB="radius" export MYSQL_PWD=$SQLPASS CMD="mysql -u$SQLUSER -p$SQLPASS -h$SQLHOST --port=$SQLPORT --skip-column-names -e" CURRENCY="MVR" RMAUTH="/usr/local/bin/rmauth" ###################### bytes conversion formula bytesToHuman() { b=${1:-0}; d=''; s=0; S=(Bytes {K,M,G,T,P,E,Z,Y}iB) while ((b > 1024)); do d="$(printf ".%02d" $((b % 1024 * 100 / 1024)))" b=$((b / 1024)) let s++ done echo "$b$d ${S[$s]}" } ##################### # Check User Validation, if not found exit with error , else continue FOOTER=" Powered by Syed Jahanzaib" if [ -z "$USR" ]; then echo -e "(Error 0) username missing, Usage: userinfo myusername" exit 1 fi # Check if user in DB or not USRVALID=`$CMD "use $DB; SELECT username FROM rm_users WHERE username = '$USR';"` if [ -z "$USRVALID" ]; then echo -e "(Error 1) USER NOT FOUND !" exit 1 fi # Check if user quota is ended or not IS_USER_QUOTA_AVAILABLE=`$RMAUTH 127.0.0.1 $USR 1 |grep -i -c "Total traffic limit reached!"` if [ "$IS_USER_QUOTA_AVAILABLE" -eq 1 ];then echo "(Error 2) Account Quota limit have finished , please recharge it first!" exit 1 fi # Check if user is expired or not IS_USER_EXPIRED_OR_NOT=`$RMAUTH 127.0.0.1 $USR 1 |grep -i -c "expired!"` if [ "$IS_USER_EXPIRED_OR_NOT" -eq 1 ];then echo "(Error 3)Your account date expired , please recharge to continue our service" exit 1 fi # Check if user is enabled or disabled in radius IS_USER_ENABLED_OR_DISABLED=`$RMAUTH 127.0.0.1 $USR 1 |grep -i -c "Invalid service reference!"` if [ "$IS_USER_ENABLED_OR_DISABLED" -eq 1 ];then echo "(Error 4)Your account is disabled by Admin!" exit 1 fi # VARIABLES TODAY=$(date +"%Y-%m-%d") TODAYDIGIT=`echo $TODAY | sed -e 's/-//g'` MONTH=$(date +"-%m") CMONTH=`echo $MONTH | sed -e 's/-//g'` CURR_MONTHYEAR=$(date +"%Y-%m") MONTHYEAR=$(date +"%B-%Y") ALPHAMONTHYEAR=`echo $MONTHYEAR #| sed -e 's/-//g'` SRVEXPIRYFULL=`$CMD "use $DB; SELECT expiration FROM rm_users WHERE username = '$USR';"` LOGOFFDATE=`$CMD "use $DB; SELECT lastlogoff FROM rm_users WHERE username = '$USR';"` SRVID=`$CMD "use $DB; SELECT srvid from rm_users where username = '$USR';"` SRVEXPIRY_YES_OR_NO=`$CMD "use $DB; SELECT limitexpiration FROM rm_services WHERE srvid = '$SRVID';"` SRV_NEXT_DAILY=`$CMD "use $DB; SELECT dailynextsrvid FROM rm_services where srvid = '$SRVID'";` SRV_NEXT_DISABLED=`$CMD "use $DB; SELECT disnextsrvid FROM rm_services where srvid = '$SRVID'";` SRV_NEXT=`$CMD "use $DB; SELECT nextsrvid FROM rm_services where srvid = '$SRVID'";` SRV_QUOTA_LIMIT_IN_MB=`$CMD "use $DB; SELECT trafficunitcomb FROM rm_services where srvid = '$SRVID'";` SRV_QUOTA_LIMIT_IN_BYTES=`echo "($SRV_QUOTA_LIMIT_IN_MB)*(1024)*(1024)" |bc` SRVPRICE=`$CMD "use $DB; SELECT unitprice FROM rm_services WHERE srvid = $SRVID;"` TOT_DOWN_UP_CURR_MONTH_IN_BYTES=`$CMD "use $DB; SELECT ((SUM(AcctInputOctets)+SUM(AcctOutputOctets))) FROM radacct WHERE username ='$USR' AND acctstarttime LIKE '$CURR_MONTHYEAR-%' LIMIT 0 , 30;"` TOT_USER_DOWNLOAD_SINCE_LAST_REFRESH_IN_BYTES=`$CMD "use $DB; SELECT downlimit from rm_users where username = '$USR';" |sed 's/[\._-]//g'` TOT_USER_UPLOAD_SINCE_LAST_REFRESH_IN_BYTES=`$CMD "use $DB; SELECT uplimit from rm_users where username = '$USR';" |sed 's/[\._-]//g'` TOT_USER_USED_DATA_SINCE_LAST_REFRESH_IN_BYTES=`echo "($TOT_USER_DOWNLOAD_SINCE_LAST_REFRESH_IN_BYTES)+($TOT_USER_UPLOAD_SINCE_LAST_REFRESH_IN_BYTES)" |bc` TOT_USER_DOWNUP_FROM_RADACCT=`$CMD "use radius; SELECT ((SUM(AcctInputOctets)+SUM(AcctOutputOctets))) FROM radacct WHERE username = '$USR';" |sed 's/[\._-]//g'` TOT_USER_USED_DATA_SINCE_LAST_REFRESH_IN_HUMAN_FRIENDLY_VALUE=`bytesToHuman $TOT_USER_USED_DATA_SINCE_LAST_REFRESH_IN_BYTES` USER_QUOTA_LEFT_SINCE_LAST_REFRESH_IN_BYTES=`echo "($SRV_QUOTA_LIMIT_IN_BYTES)-($TOT_USER_USED_DATA_SINCE_LAST_REFRESH_IN_BYTES)" |bc` USER_QUOTA_LEFT_SINCE_LAST_REFRESH_IN_HUMAN_FRIEND_VALUE=`bytesToHuman $USER_QUOTA_LEFT_SINCE_LAST_REFRESH_IN_BYTES` PKGNAME=`$CMD "use $DB; SELECT srvname FROM rm_services WHERE srvid = '$SRVID';"` USER_FIRSTNAME=`$CMD "use $DB; SELECT firstname FROM rm_users WHERE username = '$USR';"` USER_LASTNAME=`$CMD "use $DB; SELECT lastname FROM rm_users WHERE username = '$USR';"` QTL_Y_OR_NO=`$CMD "use $DB; SELECT limitcomb FROM rm_services WHERE srvid = '$SRVID';"` #echo "Account STATUS= OK!" echo -e "Account Registed to = $USER_FIRSTNAME $USER_LASTNAME" echo -e "PACKAGE = $PKGNAME" if [ "$SRVPRICE" == "0.000000" ]; then echo -e "Service Price = n/a" else echo -e "Service Price = $SRVPRICE $CURRENCY" fi if [ "$SRVEXPIRY_YES_OR_NO" -eq 0 ]; then echo -e "Date Expiration = Unlimited" else echo -e "Date Expiration = $SRVEXPIRYFULL" fi # Check QUOTA value if quota is enforced if [ "$QTL_Y_OR_NO" -eq 1 ]; then SRV_QUOTA_LIMIT_IN_HUMAN_FRIENDLY_VALUE=`bytesToHuman $SRV_QUOTA_LIMIT_IN_BYTES` echo "Service Quota Allowed: $SRV_QUOTA_LIMIT_IN_HUMAN_FRIENDLY_VALUE" else echo "Quota Allowed: No Limit" fi # Check If next service is allowed after expiration or over quota if [ "$SRV_NEXT_DAILY" == "-1" ] && [ "$SRV_NEXT_DISABLED" == "-1" ] && [ "$SRV_NEXT" == "-1" ]; then NEXT_SRV_ENABLED_OR_NOT_FRIENDLY_VALUE="Not Configured" else NEXT_SRV_ENABLED_OR_NOT_FRIENDLY_VALUE="Yes" fi #if [ "$TOT_USER_DOWNUP_FROM_RADACCT" == "NULL" ]; then #echo "Total Data Used Since Last Renewal = NULL" #TOT_USER_DOWNUP_FROM_RADACCT="0" #fi # *ZAIB if [ "$QTL_Y_OR_NO" -eq 1 ]; then # Allah shuker - zaib RM_CMD_FOR_USER_ACTUAL_LIVE_QUOTA_VALUE=`$CMD "use $DB; SELECT SQL_CALC_FOUND_ROWS IF (limitdl = 1, downlimit - COALESCE((SELECT SUM(acctoutputoctets) FROM radacct WHERE radacct.username = tmp.username) - (SELECT COALESCE(SUM(dlbytes), 0) FROM rm_radacct WHERE rm_radacct.username = tmp.username), 0), 0), IF (limitul = 1, uplimit - COALESCE((SELECT SUM(acctinputoctets) FROM radacct WHERE radacct.username = tmp.username) - (SELECT COALESCE(SUM(ulbytes), 0) FROM rm_radacct WHERE rm_radacct.username = tmp.username), 0), 0), IF (limitcomb =1, comblimit - COALESCE((SELECT SUM(acctinputoctets + acctoutputoctets) FROM radacct WHERE radacct.username = tmp.username) - (SELECT COALESCE(SUM(ulbytes + dlbytes), 0) FROM rm_radacct WHERE rm_radacct.username = tmp.username), 0), 0), IF (limituptime = 1, uptimelimit - COALESCE((SELECT SUM(acctsessiontime) FROM radacct WHERE radacct.username = tmp.username) - (SELECT COALESCE(SUM(acctsessiontime), 0) FROM rm_radacct WHERE rm_radacct.username = tmp.username), 0), 0) FROM ( SELECT username, firstname, lastname, address, city, zip, country, state, phone, mobile, email, company, taxid, rm_users.srvid, rm_users.downlimit, rm_users.uplimit, rm_users.comblimit, rm_users.expiration, rm_users.uptimelimit, credits, comment, enableuser, staticipcpe, staticipcm, ipmodecpe, ipmodecm, srvname, limitdl, limitul, limitcomb, limitexpiration, limituptime, createdon, verifycode, verified, selfreg, acctype, maccm, mac, groupid, contractid, contractvalid, rm_users.owner, srvtype, lastlogoff FROM rm_users JOIN rm_services USING (srvid) ORDER BY username ASC ) AS tmp WHERE 1 AND username LIKE '$USR%' AND (tmp.acctype = '0' OR tmp.acctype = '1' OR tmp.acctype = '2' OR tmp.acctype = '3' OR tmp.acctype = '4' OR tmp.acctype = '5' ) LIMIT 0, 50;" | awk '{print $3}'` RM_CMD_FOR_USER_ACTUAL_LIVE_QUOTA_VALUE_IN_HUMAN_FRIENDLY_FORMAT=`bytesToHuman $RM_CMD_FOR_USER_ACTUAL_LIVE_QUOTA_VALUE` IS_QUOTA_LEFT_IN_NEGATIVE=`echo $RM_CMD_FOR_USER_ACTUAL_LIVE_QUOTA_VALUE_IN_HUMAN_FRIENDLY_FORMAT | grep -i -c "-"` RM_CMD_FOR_USER_ACTUAL_LIVE_QUOTA_NEGATIVE_VALUE_IN_BYTES_WITHOUT_DASH=`echo $RM_CMD_FOR_USER_ACTUAL_LIVE_QUOTA_VALUE_IN_HUMAN_FRIENDLY_FORMAT |sed 's/[\._-]//g'` RM_CMD_FOR_USER_ACTUAL_LIVE_QUOTA_NEGATIVE_VALUE_IN_HUMAN_FRIENDLY_VALUE=`bytesToHuman $RM_CMD_FOR_USER_ACTUAL_LIVE_QUOTA_NEGATIVE_VALUE_IN_BYTES_WITHOUT_DASH` if [ "$IS_QUOTA_LEFT_IN_NEGATIVE" -eq 1 ]; then echo "Total Quote Left = - $RM_CMD_FOR_USER_ACTUAL_LIVE_QUOTA_NEGATIVE_VALUE_IN_HUMAN_FRIENDLY_VALUE" else echo "Total Quote Left = $RM_CMD_FOR_USER_ACTUAL_LIVE_QUOTA_VALUE_IN_HUMAN_FRIENDLY_FORMAT" fi fi echo "Next Service = $NEXT_SRV_ENABLED_OR_NOT_FRIENDLY_VALUE" # Check for NULL DATA in download section of user if [ "$TOT_DOWN_UP_CURR_MONTH_IN_BYTES" == "NULL" ] then echo "Total Data Used in $ALPHAMONTHYEAR = n/a" else TOT_DOWN_UP_CURR_MONTH_IN_BYTES_HUMAN_FIRNEDLY_VALUE=`bytesToHuman $TOT_DOWN_UP_CURR_MONTH_IN_BYTES` echo -e "Total Data Used in $ALPHAMONTHYEAR = $TOT_DOWN_UP_CURR_MONTH_IN_BYTES_HUMAN_FIRNEDLY_VALUE" fi # check last log off date and print data accordingly if [ "$LOGOFFDATE" == "NULL" ]; then echo -e "LAST LOGOUT = n/a" else echo -e "LAST LOGOUT = $LOGOFFDATE" fi # Check if user is ONLINE or not ONLINE=`$CMD "use $DB; SELECT framedipaddress FROM radacct WHERE acctstoptime IS NULL AND username = '$USR';"` #if [ "$ONLINE" -eq 0 ]; then if [ -z "$ONLINE" ]; then echo -e "STATUS = Offline" else echo -e "STATUS = Online / IP: $ONLINE" fi echo "$FOOTER"
2- playSMS SECTION
Now we have to add COMMAND in playSMS which will actually receive the sms and will act accordingly if found the keyword info
3- TEST PHASE
You can test by executing the script or send sms , as per your choice.
Both methods results are as follows …
by CLI
./userinfo.sh XYZUSER Account Registed to = TEST USER PACKAGE = 10GB with 2Mbps + After quota 1mbps (30 days) Service Price = 500.00000 PKR Date Expiration = 2018-07-10 00:00:00 Service Quota Allowed: 10.00 GiB Total Quote Left = 435.42 MiB Next Service Allowed = Yes Total Data Used in July-2018 = 2.92 GiB LAST LOGOUT = 2018-07-08 06:44:35 STATUS = Online / IP: 10.0.0.10
Regard’s
SYED JAHANZAIB
Hello Sir Syed Jahanzaib Bahi kesay hain ap? sir main Wateen ki GPON Service Use kar raha hoo 100Mb ki With Mikrotik Server sir mujhe ap se 1 help chahiye wateen apne Customer ko login user name and password deta hai
or 1 dafa login karo to 1week login rehta hai or us k baad auto logout ho jaata hai phir se login karna parta hai
yeh problem hai jesay hee logout hota hai net band to Clients tang hote hain kia koe aesa script hai mikrotik ka jis se
auto login ho jaya kare ? please sir Reply me i m Waiting…….
Regards Jalil Abbas
LikeLike
Comment by Jalil Abbas — May 27, 2015 @ 1:49 PM
i can try this bash scrip but i get this error
root@xxx:/var/lib/playsms/sms_command/1# ./userinfo.sh 1234
./userinfo.sh: line 66: unexpected EOF while looking for matching `”
./userinfo.sh: line 84: syntax error: unexpected end of file
LikeLike
Comment by ellaham — June 1, 2015 @ 4:12 AM
try to download it from here
https://drive.google.com/folderview?id=0B8B_P2ljEc2xSndud0hDV29HT2s&usp=sharing&tid=0B8B_P2ljEc2xcEdkd2ttV1ZmNFU
LikeLike
Comment by Syed Jahanzaib / Pinochio~:) — June 2, 2015 @ 9:57 AM
It was a typo mistake. Kindly check and let me know if its working ok now.
LikeLike
Comment by Syed Jahanzaib / Pinochio~:) — June 2, 2015 @ 10:49 AM
Thx.. Script working good now. i got all informations my phone correct and good but when i start ./userinfo.sh with computer get this error;
(standard_in) 1: illegal character: N
(standard_in) 1: illegal character: U
(standard_in) 1: illegal character: L
(standard_in) 1: illegal character: L
(standard_in) 1: syntax error
(standard_in) 1: illegal character: N
(standard_in) 1: illegal character: U
(standard_in) 1: illegal character: L
(standard_in) 1: illegal character: L
(standard_in) 1: illegal character: N
(standard_in) 1: illegal character: U
(standard_in) 1: illegal character: L
(standard_in) 1: illegal character: L
(standard_in) 1: syntax error
(standard_in) 1: illegal character: N
(standard_in) 1: illegal character: U
(standard_in) 1: illegal character: L
(standard_in) 1: illegal character: L
LikeLike
Comment by ellaham — June 3, 2015 @ 5:43 AM
If the user account have not been used , it will give this NULL error,
try to use the account , or just login it. then its accounting will work.
LikeLike
Comment by Syed Jahanzaib / Pinochio~:) — June 3, 2015 @ 11:27 AM
Ok today I have updated it to add a CHECK if user have not used any data or the account is NEW, then it will print NOT USED rather then giving errors of NULL. I have tested it and its working ok.
Kindly check and let me know.
LikeLike
Comment by Syed Jahanzaib / Pinochio~:) — June 3, 2015 @ 11:42 AM
Thx now script working perfect. Can you check mail address i have some idea for this script.
LikeLike
Comment by ellaham — June 4, 2015 @ 4:15 AM
Dear zaib
I want to show only total traffic left please help me
LikeLike
Comment by haroonnimroozy — June 4, 2015 @ 8:19 PM
check i have added another version of the script today ,
LikeLike
Comment by Syed Jahanzaib / Pinochio~:) — June 9, 2015 @ 12:00 PM
I have a radiusmanager on one server and sms on another server. I changed the parameters for the host.
####################################################
# MYSQL USER NAME AND PASSWORD Variables
SQLUSER=”root”
SQLPASS=”radisu123″
SQLHOST=”123.456.789.012″
SQLPORT=”3306″
DB=”radius”
export MYSQL_PWD=$SQLPASS
CMD=”mysql -u$SQLUSER -p$SQLPASS -h$SQLHOST –port=$SQLPORT –skip-column-names -e”
CURRENCY=”MVR”
RMAUTH=”/usr/local/bin/rmauth”
#####################################################
I’m reporting this error!
ERROR 1130 (HY000): Host ‘123.123.123.131’ is not allowed to connect to this MySQL server
(Error 1) USER NOT FOUND !
#####################################################
What I need to do to work?
LikeLike
Comment by Milos Trivic — July 13, 2018 @ 2:32 PM