Export watchdog to CSV

Question

I would like to export everything in the watchdog table to a CSV file occasionally using hook_cron(). I do not have access to PHPMyAdmin and don't want to waste time with modules like so.

Right now, I have all records for the watchdog table, but I can't figure out how to format messages properly.

$log = db_query("SELECT uid, type, message, variables, severity, location, timestamp FROM {watchdog}")->fetchAll();
dsm($log); 
// Output: "message => '%type: !message in %function (line %line of %file).'"
//         "variables => 'a:6:{s:5:"%type";s:6:"Notice";s:8:"!message";s:27:"Undefined index:..."

How the heck am I supposed to replace symbols in $log['message'] with what's in $log['variables']?

I have tried using t($log['message'], $log['variables']) and format_string($log['message'], $log['variables']) but this won't work.

Answer 1

Rather than "waste time" with modules, I'll spend a little time looking at how a module like https://www.drupal.org/project/views_watchdog handles something I want to do. Turns out it's like so...

function views_watchdog_format_message($message, $variables) {
  return $variables === 'N;' ? $message : t($message, unserialize($variables));
}

Answer 2

This is how dblog output message on dblog.admin.inc:

/**
 * Returns HTML for a log message.
 *
 * @param array $variables
 *   An associative array containing:
 *   - event: An object with at least the message and variables properties.
 *   - link: (optional) Format message as link, event->wid is required.
 *
 * @ingroup themeable
 */
function theme_dblog_message($variables) {
  $output = '';
  $event = $variables['event'];
  // Check for required properties.
  if (isset($event->message) && isset($event->variables)) {
    // Messages without variables or user specified text.
    if ($event->variables === 'N;') {
      $output = $event->message;
    }
    // Message to translate with injected variables.
    else {
      $output = t($event->message, unserialize($event->variables));
    }
    if ($variables['link'] && isset($event->wid)) {
      // Truncate message to 56 chars.
      $output = truncate_utf8(filter_xss($output, array()), 56, TRUE, TRUE);
      $output = l($output, 'admin/reports/event/' . $event->wid, array('html' => TRUE));
    }
  }
  return $output;
}

So you can do next:

module_load_include('inc', 'dblog', 'dblog.admin');
$result = db_query('SELECT w.*, u.name, u.uid FROM {watchdog} w INNER JOIN {users} u ON w.uid = u.uid')->fetchAll();

foreach ($result as $row) {
  $message4row = theme('dblog_message', array('event' => $row)));
  // Do something with $message4row for CSV output.
}