API call to get a json equivalent of View Sent Summary

[Neutrall]

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!

 

[cPanelMichael]

Hello,

There's no direct equivalent, however the following WHM API 1 functions are available for viewing and searching email data:

WHM API 1 Functions - emailtrack_search - Software Development Kit - cPanel Documentation
WHM API 1 Functions - emailtrack_stats - Software Development Kit - cPanel Documentation

Thank you.

 

[Neutrall]

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!

 

[cPanelMichael]

Hello,

You can take a look at the following file for an example of how we collect eximstats data:

/usr/local/cpanel/scripts/eximstats_spam_check

There's also a thread here you may find helpful:

Fetch data from sqlite database

Thank you.

 

[Neutrall]

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?

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

 

[cPanelMichael]

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]

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).

#!/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 :

./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!

 

[Neutrall]

Update :

For anyone interested, I've place a working example on our GitHub Repo : GitHub - AstralInternet/query_cpanel_eximstats_sqlite: Script to query the EximStats Database used by cPanel.

Fell free to send us improvement!