Please whitelist cPanel in your adblocker so that you’re able to see our version release promotions, thanks!

The Community Forums

Interact with an entire community of cPanel & WHM users!

API call to get a json equivalent of View Sent Summary

Discussion in 'cPanel Developers' started by Neutrall, Nov 2, 2017.

Tags:
  1. Neutrall

    Neutrall Member
    PartnerNOC

    Joined:
    Jul 22, 2014
    Messages:
    24
    Likes Received:
    2
    Trophy Points:
    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!
     
  2. cPanelMichael

    cPanelMichael Technical Support Community Manager
    Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    44,803
    Likes Received:
    1,898
    Trophy Points:
    363
    cPanel Access Level:
    Root Administrator
    Twitter:
    Stop hovering to collapse... Click to collapse... Hover to expand... Click to expand...
  3. Neutrall

    Neutrall Member
    PartnerNOC

    Joined:
    Jul 22, 2014
    Messages:
    24
    Likes Received:
    2
    Trophy Points:
    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!
     
  4. cPanelMichael

    cPanelMichael Technical Support Community Manager
    Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    44,803
    Likes Received:
    1,898
    Trophy Points:
    363
    cPanel Access Level:
    Root Administrator
    Twitter:
    Hello,

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

    Code:
    /usr/local/cpanel/scripts/eximstats_spam_check
    There's also a thread here you may find helpful:

    Fetch data from sqlite database

    Thank you.
     
    Stop hovering to collapse... Click to collapse... Hover to expand... Click to expand...
  5. Neutrall

    Neutrall Member
    PartnerNOC

    Joined:
    Jul 22, 2014
    Messages:
    24
    Likes Received:
    2
    Trophy Points:
    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;");'
     
    #5 Neutrall, Nov 29, 2017
    Last edited: Nov 29, 2017
  6. cPanelMichael

    cPanelMichael Technical Support Community Manager
    Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    44,803
    Likes Received:
    1,898
    Trophy Points:
    363
    cPanel Access Level:
    Root Administrator
    Twitter:
    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.
     
    Stop hovering to collapse... Click to collapse... Hover to expand... Click to expand...
  7. Neutrall

    Neutrall Member
    PartnerNOC

    Joined:
    Jul 22, 2014
    Messages:
    24
    Likes Received:
    2
    Trophy Points:
    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!
     
  8. Neutrall

    Neutrall Member
    PartnerNOC

    Joined:
    Jul 22, 2014
    Messages:
    24
    Likes Received:
    2
    Trophy Points:
    3
    cPanel Access Level:
    DataCenter Provider
    cPanelMichael likes this.
Loading...

Share This Page

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice