Quota report using LDAP and Dict

This script will export all LDAP information including current quota usage. You should use quota dict for quota storage

http://wiki1.dovecot.org/Quota/Dict

LDAP attributes

1. givenName
2. sn
3. uid
4. mail
5. quota

This is the way how script will work :

• Script will collect all information from LDAP database. Using NET::LDAP. http://search.cpan.org/~gbarr/perl-ldap/lib/Net/LDAP.pod
• Script collect data from quota dict for using perl DBI
• Script stores all information in a file 
• Script sent mail to administrator attaching the same report using MIME:Lite. http://search.cpan.org/~rjbs/MIME-Lite-3.027/lib/MIME/Lite.pm

#!/usr/bin/perl

# This script will export LDAP information including current quota usage.
# Install NET::LDAP, DBI, MIME::Lite for running this script.
# You have full freedom to modify this script. This will help me to improve the usage.
# Please feel free to reach me on praveen.velu84@yahoo.com

use Net::LDAP;

use DBI;

use MIME::Lite;

# Create a file for storing data

open (MYFILE, '> /tmp/quota_report.csv');

# Modify connection settings of dovecotdict database

my $dbh = DBI->connect("DBI:Pg:dbname=dovecot;host=localhost", "dovecot", "paassword", {'RaiseError' => 1});

my $sth = $dbh->prepare('SELECT * from quota where username = ?') or die "Couldn't prepare statement: " . $dbh->errstr;

$ldap = Net::LDAP->new("localhost");

# Modify your LDAP connectivity.

$ldap->bind("cn=admin,dc=example,dc=com", password=>"secret");

$mesg = $ldap->search(filter=>"(&(mail=*)(objectclass=posixAccount))", base=>"dc=example,dc=com");

print MYFILE "User_Name;First_Name;Last_Name;Email;LDAP_quota(MB);Quota_Usage(MB);No_of_messages\n";

@entries = $mesg->entries;

foreach $entry (@entries){

                $name = $entry->get_value("uid");

                $fname = $entry->get_value("givenName");

                $lname = $entry->get_value("sn");

                $mail = $entry->get_value("mail");

                $quota = $entry->get_value("quota");

$sth->execute($mail);

@data = $sth->fetchrow_array();

$usedquotamb = ($data[1]/1048576);

$quotausage = int $usedquotamb;

$messages = $data[2];

                print MYFILE $name . ";" . $fname . ";" . $lname . ";" .$mail . ";" . $quota . ";" . $quotausage . ";" . $messages ."\n"

}

close MYFILE;

# Senting mail to administrator. Replace postmaster email id with your email id

my $msg = MIME::Lite->new(

    From    => 'postmaster@example.com',

    To      => 'admin@example.com',

    Subject => 'LDAP quota report',

    Type    => 'multipart/mixed',

);

$msg->attach(

    Type     => 'TEXT',

    Data     => "Dear Sir, \n\nThis is an automated message. Please find attached User Quota Report.\n\nEmail Administrator ",

);

$msg->attach(

    Type     => 'text/csv',

    Path     => '/tmp/to_report.csv',

    Filename => 'quota_report.csv',

);

$msg->send;

$dbh->disconnect;