Hi, It seems that OM doesn't delete streams form the filesystem. Thus I needed a way to track diskspace usage of streams so I developped a "not so quick and yet dirty script" to do the following tasks: * delete streams marked as deleted in OpenMeetings DB from the filesystem * report diskspace used by not deleted streams with associated information (owner, room, ...) * report diskspace per user, and send an overquota email if needed * report streams older than and Expiration duration threshold and send an expriation email if needed
It requires: * linux system * openmeetings with mysql DB (tested on openmeetings_1_9_1_r4707) * mysql client, a mysqk user able to do selects to the OM DB * usual linux tools like cut, awk, sed and bash 4.x It comes with no warranty as I had to reverse engineer the DB to understand how streams ar recorded and marked as deleted and I'm quite sure there are things I missed so It may be considered as a "template". I just post it here, in case it can prove to be usefull to someone. Now I would need help on how uploaded files are recorded to filesystem and DB to do the same for these ones. If anyone can help me... ;-) Regards, Thibault
#!/bin/bash - #title :openmeetings_streams.sh #description :Delete unused openmeetings streams and report disk usage or expired streams #requires :openmeetings with mysql database, mysql client with a user to read the DB, bash 4.x #author :Thibault Le Meur #date :20120405 #version :0.9 #usage :./openmeetings_streams.sh #notes : #bash_version :4.1.5(1)-release #============================================================================ #set -x # Setup ## The directory where your OM streams are STREAMDIR="/opt/red5/webapps/openmeetings/streams" ## The MYSQL command line client MYSQL="/usr/bin/mysql" ## The MYSQL User (must have the SELECT right on all openmeetings tables) MYSQLUSR="meetingsinfo" ## The MYSQL Password of user MYSQLUSR MYSQLPWD="VerySecretPassword" ## The MYSQL database for openmeetings MYSQLDB="openmeetings" ## PERUSERQUOTA=20971520 // 20MB PERUSERQUOTA=20971520 ## MAIL command, set to empty string if you don't want user notification MAIL="/usr/bin/mail" ## APPNAME: application name APPNAME="OpenMeetings" ## APPURL: URL of the openmeetings instance APPURL="http://myopenmeetings.mydom.org:5080/openmeetings"; ## ADMINEMAIL: email address of the administrator ADMINEMAIL="[email protected]" ## TIMELIMIT: number of seconds to keep a recording (604800 = 7 days) TIMELIMIT=604800 ## VERBOSE: if set to "Yes", then the scripts outputs information about the process VERBOSE="Yes" localdir=`dirname $0` if [ -r $localdir/local_openmeetings_streams.cfg ] then . $localdir/local_openmeetings_streams.cfg fi # BEGIN: Helper functions section ## bytesToHuman: returns the Human readable string that corresponds to the number of bytes given in arg1 ## - arg1: <nb of bytes> ## - stdout: string representing the number of bytes in human readable format ## - return: n/A bytesToHuman () { if [ -z $1 -o $1 -eq 0 ] then echo 0 else echo $1 | awk 'BEGIN{hum[1024^3]="Gb";hum[1024^2]="Mb";hum[1024]="Kb";} {for (x=1024^3; x>=1024; x/=1024){ if ($1>=x) { printf "%.2f %s\n",$1/x,hum[x];break };}}'; fi } ## isNotSet: returns 1 if the key "k" of associative array "A" is not set ## - arg1: A[k] ## - stdout: n/a ## - return: 1 if not set isNotSet() { if [[ ! ${!1} && ${!1-_} ]] then return 1 fi } ## logMe: outputs the log message only if VERBOSE mode is enabled logMe() { if [ $VERBOSE = "Yes" ] then echo "$1" fi } # END: Helper functions section # BEGIN: purge of unused streams ## this deletes unsused streams (which are flagged as deleted in OM DB) logMe "BEGIN purging unsused streams flagged as deleted in OM DB" dbquery=$($MYSQL --batch -u $MYSQLUSR -p$MYSQLPWD $MYSQLDB -e "select concat_ws(';',a.flvrecording_id,u.login,e.email,a.alternate_download,a.filehash,a.preview_image,m.stream_name,room_id,DATE_FORMAT(a.record_end,'%Y-%m-%d--%T'),REPLACE(r.name,' ','_')) as HEADERTODELETE from flvrecording as a, flvrecording_metadata as m, users as u, adresses as e,rooms as r where a.flvrecording_id=m.flvrecording_id and a.owner_id=u.user_id and u.adresses_id=e.adresses_id and a.room_id=r.rooms_id and a.deleted='true'" | grep -v HEADERTODELETE) totalSavedBytes=0 for stream in $dbquery do flvid=`echo $stream | cut -d';' -f1` userlogin=`echo $stream | cut -d';' -f2` useremail=`echo $stream | cut -d';' -f3` filebase1=`echo $stream | cut -d';' -f4` filebase2=`echo $stream | cut -d';' -f5` filebase3=`echo $stream | cut -d';' -f6` filebase4=`echo $stream | cut -d';' -f7` roomid=`echo $stream | cut -d';' -f8` enddate=`echo $stream | cut -d';' -f9` roomname=`echo $stream | cut -d';' -f10` savebytes1=`find $STREAMDIR -type f -name "$filebase1*" -printf '%s\n' -exec rm {} \; | awk 'BEGIN {cnt=0} {cnt=$1} END{ print cnt;}'` savebytes2=`find $STREAMDIR -type f -name "$filebase2*" -printf '%s\n' -exec rm {} \; | awk 'BEGIN {cnt=0} {cnt=$1} END{ print cnt;}'` savebytes3=`find $STREAMDIR -type f -name "$filebase3*" -printf '%s\n' -exec rm {} \; | awk 'BEGIN {cnt=0} {cnt=$1} END{ print cnt;}'` savebytes4=`find $STREAMDIR -type f -name "$filebase4*" -printf '%s\n' -exec rm {} \; | awk 'BEGIN {cnt=0} {cnt=$1} END{ print cnt;}'` streamSavedBytes=`expr $savebytes1 + $savebytes2 + $savebytes3 + $savebytes4` streamSavedBytesHr=`bytesToHuman $streamSavedBytes` totalSavedBytes=`expr $totalSavedBytes + $streamSavedBytes` if [ $streamSavedBytes -gt 0 ] then logMe " * Deleting $streamSavedBytesHr from stream n°$flvid for user '$userlogin ($useremail)' [in room $roomname date: $enddate]" fi done # Now let's report used streams size (which are flagged as not deleted in OM DB) logMe "BEGIN reporting used streams" dbquery=$($MYSQL --batch -u $MYSQLUSR -p$MYSQLPWD $MYSQLDB -e "select concat_ws(';',a.flvrecording_id,u.login,e.email,a.alternate_download,a.filehash,a.preview_image,m.stream_name,room_id,DATE_FORMAT(a.record_end,'%Y-%m-%d--%T'),REPLACE(r.name,' ','_')) as HEADERTODELETE from flvrecording as a, flvrecording_metadata as m, users as u, adresses as e,rooms as r where a.flvrecording_id=m.flvrecording_id and a.owner_id=u.user_id and u.adresses_id=e.adresses_id and a.room_id=r.rooms_id and a.deleted='false'" | grep -v HEADERTODELETE) totalUsedBytes=0 declare -A perUser declare -A perUserExpired nowTimestamp=`date +%s` deleteBefore=`expr $nowTimestamp - $TIMELIMIT` for stream in $dbquery do flvid=`echo $stream | cut -d';' -f1` userlogin=`echo $stream | cut -d';' -f2` useremail=`echo $stream | cut -d';' -f3` filebase1=`echo $stream | cut -d';' -f4` filebase2=`echo $stream | cut -d';' -f5` filebase3=`echo $stream | cut -d';' -f6` filebase4=`echo $stream | cut -d';' -f7` roomid=`echo $stream | cut -d';' -f8` enddate=`echo $stream | cut -d';' -f9 | sed 's/--/ /'` enddatets=`date -d "$enddate" +%s` roomname=`echo $stream | cut -d';' -f10` usedbytes1=`find $STREAMDIR -type f -name "$filebase1*" -printf '%s\n' | awk 'BEGIN {cnt=0} {cnt=$1} END{ print cnt;}'` usedbytes2=`find $STREAMDIR -type f -name "$filebase2*" -printf '%s\n' | awk 'BEGIN {cnt=0} {cnt=$1} END{ print cnt;}'` usedbytes3=`find $STREAMDIR -type f -name "$filebase3*" -printf '%s\n' | awk 'BEGIN {cnt=0} {cnt=$1} END{ print cnt;}'` usedbytes4=`find $STREAMDIR -type f -name "$filebase4*" -printf '%s\n' | awk 'BEGIN {cnt=0} {cnt=$1} END{ print cnt;}'` streamUsedBytes=`expr $usedbytes1 + $usedbytes2 + $usedbytes3 + $usedbytes4` streamUsedBytesHr=`bytesToHuman $streamUsedBytes` totalUsedBytes=`expr $totalUsedBytes + $streamUsedBytes` if [ $streamUsedBytes -gt 0 ] then logMe " * Used $streamUsedBytesHr for stream n°$flvid by user '$userlogin ($useremail)' [in room $roomname date: $enddate]" ### check for expired streams if [ $enddatets -lt $deleteBefore ] then perUserExpired["${useremail}"]="${perUserExpired["$useremail"]} * Expired stream n$flvid ($streamUsedBytesHr) since $enddate in room '$roomname' \n" fi isNotSet perUser["$useremail"] if [ $? -ne 0 ] then # Not set yet perUser["${useremail}"]=$streamUsedBytes else perUser["${useremail}"]=`expr ${perUser["$useremail"]} + $streamUsedBytes` fi fi done logMe "BEGIN sending email to users for expried streams notifications" for user in "${!perUserExpired[@]}" do msg=${perUserExpired[$user]} logMe " * Expired stream sending email notification to '$user'" if [ -n "$MAIL" ] then $MAIL -s "Expired recording in $APPNAME" $user <<EOM Some of your recordings on $APPNAME have expired. Please review the list below: `echo -e "$msg"` 1) Please log in to $APPURL and go to the "Recordings" tab. 2) You can download your recordings to your local computer as AVI or FLV files by selecting a recording and right-clicking the appropriate icon at the bottom of the screen. 3) Right click the recording entry, and select "Delete file" option Regards, $ADMINEMAIL EOM fi done logMe "BEGIN reviewing users streams diskspace usage" for user in "${!perUser[@]}" do readableUsage=`bytesToHuman ${perUser[$user]}` readableQuota=`bytesToHuman $PERUSERQUOTA` logMe " * Diskspace streams usage for '$user' is $readableUsage" if [ ${perUser[$user]} -gt $PERUSERQUOTA ] then logMe " ** Authorized quota of $readableQuota exceeded, sending email notification" if [ -n "$MAIL" ] then $MAIL -s "Quota of recordings exceeded for $APPNAME" $user <<EOM You're using $readableUsage for your recordings on $APPNAME, this is more than the $readableQuota authorized quota. 1) Please log in to $APPURL and go to the "Recordings" tab. 2) You can download your recordings to your local computer as AVI or FLV files by selecting a recording and right-clicking the appropriate icon at the bottom of the screen. 3) Right click the recording entry, and select "Delete file" option Regards, $ADMINEMAIL EOM fi fi done
