API call to get a json equivalent of View Sent Summary

Neutrall

Active Member
PartnerNOC
Jul 22, 2014
26
3
3
cPanel Access Level
DataCenter Provider
Hi,

We are looking into the development of an small application to help us quickly eliminate spammer from our servers.

is there a way we send an API request to access information similar to the "View Sent Summary" in the WHM interface?

With the information given from this report, we could improve (hopefully) our response time to an infected spamming account!

Thank you!
 

Neutrall

Active Member
PartnerNOC
Jul 22, 2014
26
3
3
cPanel Access Level
DataCenter Provider
Thank you Michael,

I think the API can help me a bit, but my research are now sending my into the sqlite eximstats db (eximstats_db.sqlite3).

I'm not trying to connect to the database to run a few queries but I kept hitting a wall! Ideally, I'll use a PHP script, but for the testing purpose, I'm using simple CLI.

I can manage to connect to the DB (sqlite3 /var/cpanel/eximstats_db.sqlite3) but then one every command, I get the response "Error: file is encrypted or is not a database".

I've done a little (lot) of research and so far, I still can connect into the DB. Some of the puzzle piece I've gathered so far are :

1- The location of the exim db password (/var/cpanel/eximstatspass)
2- From this post, I see Nick using a Perl script to call a eximStats librairy from cPanel. (
/usr/local/cpanel/3rdparty/bin/perl -MCpanel::EximStats::DB::Sqlite -e 'Cpanel::EximStats::DB::Sqlite->dbconnect()->do("VACUUM;");')


Any tips will be appreciated!
 

Neutrall

Active Member
PartnerNOC
Jul 22, 2014
26
3
3
cPanel Access Level
DataCenter Provider
Thank you for you reply :D,

This was also one thing I've already tried before going into shell. Since the sqlite DB seams to be encrypted, I can only return empty record set, not mater which query I try.

Trying with a different approch (sqlite3 librairy) I get the following message in the error_log file :

"PHP Warning: SQLite3::query(): Unable to prepare statement: 26, file is encrypted or is not a database"


They must be a way to send a query directly to the DB from shell. Either with sqlite CLI or cPanel Perl Script.

would running something like this be plausible?

Code:
/usr/local/cpanel/3rdparty/bin/perl -MCpanel::EximStats::DB::Sqlite -e 'Cpanel::EximStats::DB::Sqlite->dbconnect()->do("SELECT * FROM defers;");'
 
Last edited:

cPanelMichael

Technical Support Community Manager
Staff member
Apr 11, 2011
47,911
2,233
363
cPanel Access Level
DataCenter Provider
Twitter
Trying with a different approch (sqlite3 librairy) I get the following message in the error_log file :

"PHP Warning: SQLite3::query(): Unable to prepare statement: 26, file is encrypted or is not a database"
Hello,

Could you verify the specific command you are using when receiving that error message? Did you review the StackOverflow thread linked below?

Cant access eximstats sqlite3 db after WHM64 upgrade

Thank you.
 

Neutrall

Active Member
PartnerNOC
Jul 22, 2014
26
3
3
cPanel Access Level
DataCenter Provider
Hi,

Even if I copy the sqlite DB and/or change the permission, I still get the same error in PHP.

I did however manage to do a small Perl Script that could send back a json result when passing and "SQL" Query to the script.

I've also notice that the script only function if I use the cPanel Perl version (not the Os version) in the header since to open the sqlite DB we need to use the cPanel librairy (DBD:SQLite).

Code:
#!/usr/local/cpanel/3rdparty/bin/perl
use strict;
use JSON;
use CGI;
use DBD::SQLite ();

my $dbh = DBI->connect('dbi:SQLite:/var/cpanel/eximstats_db.sqlite3', undef, undef,
{
sqlite_open_flags => DBD::SQLite::OPEN_READONLY(),
sqlite_use_immediate_transaction => 0,
RaiseError => 1,
PrintWarn => 0,
}
);

if ( not $dbh or $DBI::errstr ) {
my $err = $DBI::errstr // q{something went wrong};
print $err."\n";
die qq{$err\n};
}

my ($query) = @ARGV;
if (not defined $name) {
my $query = "SELECT name FROM sqlite_master WHERE type='table'";
}

my $sth = $dbh->prepare( $query );
my $rv = $sth->execute() or die $DBI::errstr;

if($rv < 0) {
print $DBI::errstr;
}

my @output;
while ( my $row = $sth->fetchrow_hashref ){
push @output, $row;
}

my $cgi = CGI->new;
print $cgi->header( 'application/json' );
print to_json( { myData => \@output } );


$dbh->disconnect();

The script could be use like so :

Code:
./sqlite.pl "SELECT name FROM sqlite_master WHERE type='table'"

I still whish we could find a strickly PHP way of doing this. Maybe by analysing the DBD::SQLite package!


Note : This script it just a proof of concept. there no validation of the argument in the script itself and I'm not a Perl programmer!