How to Connect to Webtrends using PHP, through ODBC.

How to Connect to Webtrends using PHP, through ODBC.

By sublet on Sep 2nd, 2010 at 11:18 AM

[youtube-popup-display 1]

So I spent a few days, on and off, pounding my head against the desk trying to get a ODBC Connection to a WebTrends OnDemand Account using PHP.  Now 99% of you will probably never run into this, but I learned some things, and since it isn’t documented at all, and I don’t have anything else to write about now…let me share.

Now if you spend some time running through the Developers Network you might think I am an idiot because it is fairly well documented on how to do so. The Developers API is a very thorough, and the forums answer a lot of other questions.  But most of it is geared towards version 9.0+, which is the kicker, because I was being forced to come up with a solution to make it work with 7.5.

Another big thing…This won’t work on Mac OS or any Linux Box, which leaves you with the dreaded Windows 2003 or 2008 Server.  Reason???  You have to download and install the Webtrends ODBC Driver.  (This can be found in your Webtrends control panel under Administration -> Install Components.)  So yippie…Windows.

Also, something that is good to checkout is the “Marketing Warehouse Programmer’s Reference.”   It’s a 90 page document that has a pile of stuff you can reference and should know when querying the “database” on the Webtrends server.

Anyway, once you see it there isn’t a lot to it.  It’s a simple ODBC connection, but to access the data you first have to pull the Profile ID that you want (Line 15-18,) and then use this Profile ID to get the Database ID (Line 20-21.)  Once you have these two variables you can connect to the Report Server (Line 25,) and then run your query (Line 27.)

As always, if you have any questions, abuse the comments area, so that we can all enjoy the useless banter of one-upping each other until the others don’t care anymore.

WebTrends Class

class WebTrends {

     private $user, $pass, $server, $port;
     private $database, $connect;

     public function __construct($user,$pass,$server,$port,$query) {

          $this->user     = $user;
          $this->pass     = $pass;
          $this->server   = $server;
          $this->port     = $port;

          $this->buildDatabaseString('WTSystem');

          $this->connect  = odbc_connect($this->database, '', '');
          $row    = $this->queryIt("CALL wtGetProfileList");

          $prof   = $row['PROFILE_GUID'];

          $row    = $this->queryIt("CALL wtGetTemplateList('".$prof."')");
          $db     = $row['TEMPLATE_GUID'];

          $this->buildDatabaseString($prof,$db);

          $this->connect  = odbc_connect($this->database, '', '');

          $row    = $this->queryIt($query);

          echo 'Results:<br/><br/>';
          print_r($row);

     }

     private function buildDatabaseString($prof,$db='WTSystem') {

          $this->database = "
          Driver={WebTrends ODBC Driver};
          DATABASE=$db;
          SERVER=$this->server;
          PORT=$this->port;
          AccountId=1;
          UID=$this->user;
          PASSWORD=$this->pass;
          ProfileGuid=$prof;
          SSL=0;
          ";

     }

     private function queryIt($query) {
          $result  = odbc_exec($this->connect, $query);
          $row  = odbc_fetch_array($result);
          return $row;
     }

     private function closeOdbc() {
          odbc_close_all();
     }

     public function __destruct() {
          $this->closeOdbc();
     }

}

$query = "SELECT * FROM visitstrend WHERE timeperiod='%YESTERDAY%'";

$wt  = new WebTrends('{USERNAME}','{PASSWORD}','{SERVER}',{PORT},$query);