0

The original data from which I've created these 3 files

quota   username        bytes   
20480000        user1@abx.com       896
30720000        user2@abx.com     3002766
20480000        user3@abx.com        20472940
20480000        user4@abx.com      2351

Here I want that if bytes if any user is equal or greater than to quota then the username should be printed or I'll perform another task to block the user.

I have 3 files such as these:

users.txt

Output:
abc@abc.com
xyz@abc.com

consumed_quota.txt

Output:
20
5

allowed_quota.txt

Output:
20
10

I want that if user abc@abc.com from file users.txt consumed_quota is equal to or greater than allowed_quota then print the user.

What would be the bash program?

Kindly help out.

I'm trying below code, but it is printing all users in stead of the matching one.

#!/bin/bash



mysql -e 'select postfix.m.quota, q.* from postfix.mailbox m, postfix.quota2 q where m.username = q.username' > /tmp/all_users_query

username=`cat /tmp/all_users_query | cut -f2 | grep -v username > /tmp/usernf`
quota=`cat /tmp/all_users_query | cut -f1 | grep -v quota > /tmp/quotaf`
consumed=`cat /tmp/all_users_query | cut -f3 | grep -v bytes > /tmp/consumedf`


function show_users()
{
        username=`cat /tmp/usernf`
        for i in $username
        do
        echo $i
        done

}


function actual_quota()
{

        quota=`cat /tmp/quotaf`
        for i in $quota
        do
        akb=`echo $i/1000 | bc`
        amb=`echo $akb/1000 | bc`
        echo $amb
        done

}

function used_quota()
{

        consumed=`cat /tmp/consumedf`
        for i in $consumed
        do
        ukb=`echo "$i/1000" | bc`
        umb=`echo "$ukb/1000" | bc`
        echo "$umb" 
        done

}

declare -a arr_users="$(show_users)"
declare -a arr_act_quota="$(actual_quota)"
declare -a arr_use_quota="$(used_quota)"


for u in ${arr_users[@]}
do

for i in ${arr_use_quota[@]}
do

#       echo $i;

        for j in ${arr_act_quota[@]}
        do

        if [ "$j" == "$i" ]
        then
                echo $u;
                break;
        fi
done
        done
done
  • 2
    Is the first line in these files literally Output:? – jesse_b Jan 23 at 14:13
  • What did you tried ? We can help you fixing issue on your script, but we won't write it for you from scratch. – binarym Jan 23 at 14:41
  • I see you have a database. Why don't you write the query so that you get the desired output from the start? – Kusalananda Jan 24 at 8:51
  • I don't know the query that is why I'm doing it. If you know, please guide me if you know. – User4283 Jan 24 at 9:07
0

I've created the script myself and got the desired results.

#!/bin/bash

rm -rf /tmp/over_quota_users

mysql -e 'select postfix.m.quota, q.* from postfix.mailbox m, postfix.quota2 q where m.username = q.username and q.bytes >= m.quota-bytes;' | cut -f2 | grep -v username > /tmp/over_quota_users

#Function to get users;

function show_users()
{
        username=`cat /tmp/over_quota_users`
        for i in $username
        do
        echo $i
        done

}


#Function to get active users;

function check_active()
{
        for i in $(show_users); do
        mysql -e 'select username, active from postfix.mailbox where username = "'$i'" and active = 1;' | grep -v username
        done
        result=`echo $?`
        echo $result


}

active_results=$(check_active $(show_users))

if [ "$active_results" == 1 ];
then

        exit 0;
else
        for j in $(show_users); do

    #changing over quota users to inactive.

        mysql -e 'update postfix.mailbox set active = '0' where username = "'$j'"';

        done

        rm -rf /tmp/oqa_ul_2_admin.txt

        echo "" > /tmp/oqa_ul_2_admin.txt
        echo "These users were over quota and active "  >> /tmp/oqa_ul_2_admin.txt
        echo "" >> /tmp/oqa_ul_2_admin.txt
        echo "---------------------------------------" >> /tmp/oqa_ul_2_admin.txt
        echo "$(show_users)" >> /tmp/oqa_ul_2_admin.txt
        echo "" >> /tmp/oqa_ul_2_admin.txt
        echo "---------------------------------------" >> /tmp/oqa_ul_2_admin.txt
        echo "" >> /tmp/oqa_ul_2_admin.txt

        /usr/bin/mail -s "Blocked Over quota and active users" emailto@me.com < /tmp/oqa_ul_2_admin.txt



fi
| improve this answer | |
0

For your request, you don't need to split the data into 3 files then have to read files. You can directly read from /tmp/all_users_query

Otherwise, @Kusalananda, answer is a fairly sufficient and fully functional answer.

I have checked the files you've described, and determined they've originated from the query (fairly obvious) with the following order::

allowed_quota users consumed_quota

So I came up with the following::

#!/bin/bash

mysql -e 'select postfix.m.quota, q.* from postfix.mailbox m, postfix.quota2 q where m.username = q.username' > /tmp/all_users_query

gawk '{ if ($3 >= $1) { printf("\n%s ", $2); } }' /tmp/all_users_query

exit 0

Another (better) solution is to have an additional field in the output (that you can discard if you're reading the output file somewhere else). The additional field is called overflow, and it shows how many bytes in overflow the user has consumed; that is, the simple difference between consumed_quota and allowed_quota.

The output is ordered by the overflow field, in descending order so that it shows highest consumption first.

#!/bin/bash

mysql -e 'select postfix.m.quota, q.* from postfix.mailbox m, postfix.quota2 q where m.username = q.username' > /tmp/all_users_query


gawk '{ allowed_amount = $1; consumed_amount = $3; if (consumed_amount >= allowed_amount) { overflow = (consumed_amount - allowed_amount); printf("\n%s\t%d", $2, overflow); } }' all_users_query | sort --key=2gr


exit 0
| improve this answer | |
0

Assuming that the lines saying Output: are not actually part of the data,

$ paste users.txt consumed_quota.txt allowed_quota.txt | awk -F '\t' '$2 >= $3 { print $1 }'
abc@abc.com

The paste command creates a tab-delimited data set where column 1 is the usernames, column 2 is the used quota, and column 3 is the allowed quota.

The awk program reads this tab-delimited data and prints the username if the used quota is larger than or equal to the allowed quota.


Alternatively, query the database directly for the usernames that have exceeded their quota:

mysql -e '
    SELECT q.username
    FROM postfix.quota2 q
    WHERE q.bytes > q.quota'
| improve this answer | |
  • Above is not working – User4283 Jan 24 at 5:49
  • @User4283 In what way is it not working? – Kusalananda Jan 24 at 6:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.