Channel Logging to mySQL

Release your script here.
State:
- Author
- What it does
- Perhaps a little guide.
- Also, if the snippet is of any use to the community, it has a chance of getting posted on the main page, so don't hesitate to submit/ask!

Channel Logging to mySQL

Postby Dandy » Thu Jun 05, 2008 10:31 pm

Code: Select all
setudef flag mysqllog
setudef flag mysqllogsearch
load /usr/lib/mysqltcl-3.02/libmysqltcl3.02.so
set db_handle [mysql::connect -host localhost -user [b]user[/b]  -password [b]password[/b] -db [b]database[/b]]

proc log:add {channel nick uhost type arg} {
   global db_handle;
   set channel [string tolower $channel]
   set channel [mysql::escape $db_handle $channel]
   set nick [mysql::escape $db_handle $nick]
   set uhost [mysql::escape $db_handle $uhost]
   set arg [mysql::escape $db_handle $arg]
   
   set result [mysql::exec $db_handle "INSERT INTO log (created,channel,nick,uhost,type,arg) VALUES (NOW(),'$channel','$nick','$uhost','$type','$arg')"]
   if {$result != 1} {
      putlog "Couldn't execute $sql on the server!"
   }
}
proc log:pubmsg {n u h c t} {
   if {[channel get $c mysqllog]} {
      if {![string match "!*" $t] && ![string match ".*" $t] && ![string match "\$*" $t]} {
         log:add $c $n $u "MESSAGE" $t
      }
   }
}
bind pubm - * log:pubmsg;
proc log:pubnotc {n u h t c} {
   if {$c ne $::botnick} {
           if {[channel get $c mysqllog]} {
                   log:add $c $n $u "NOTICE" $t
           }
   }
}
bind notc - * log:pubnotc;
proc log:join {n u h c} {
        if {[channel get $c mysqllog]} {
                log:add $c $n $u "JOIN" ""
        }
}
bind join - * log:join;
proc log:part {n u h c t} {
        if {[channel get $c mysqllog]} {
                log:add $c $n $u "PART" $t
        }
}
bind part - * log:part;
proc log:topic {n u h c t} {
        if {[channel get $c mysqllog]} {
                log:add $c $n $u "TOPIC" $t
        }
}
bind topc - * log:topic;
proc log:kick {n u h c v msg} {
        if {[channel get $c mysqllog]} {
                log:add $c $n $u "KICK" "$v $msg"
        }
}
bind kick - * log:kick;



proc log:quit {n u h c t} {
        if {[channel get $c mysqllog]} {
                log:add $c $n $u "QUIT" $t
        }
}
bind sign - * log:quit;
proc log:nick {n u h c t} {
        if {[channel get $c mysqllog]} {
                log:add $c $n $u "NICK" $t
        }
}
bind nick - * log:nick;

proc log:mode {n u h c m v} {
        if {[channel get $c mysqllog]} {
                log:add $c $n $u "MODE" "$m $v"
        }
}
bind mode - * log:mode;

proc log:ctcp {n u h c k t} {
   if {$c ne $::botnick} {
           if {[channel get $c mysqllog]} {
                   log:add $c $n $u "CTCP" "$k: $t"
           }
   }
}
bind ctcp - * log:ctcp;

proc log:ctcpreply {n u h c k t} {
   if {$c ne $::botnick} {
           if {[channel get $c mysqllog]} {
                   log:add $c $n $u "CTCP-REPLY" "$k: $t"
           }   
   }
}
bind ctcr - * log:ctcpreply;
proc log:split {n u h c } {
        if {[channel get $c mysqllog]} {
                log:add $c $n $u "SPLIT" ""
        }
}
bind splt - * log:split;
proc log:rejoin {n u h c} {
        if {[channel get $c mysqllog]} {
                log:add $c $n $u "REJOIN" ""
        }
}
bind rejn - * log:rejoin;

bind pub o !logsearch log:search
proc log:search {n u h c in} {
   if {[channel get $c mysqllogsearch]} {
   set a $in
   set mode " "
   set chan $c;
   foreach txt $in {
      if {$txt eq "-today"} {
         set matchdate " date(created) = curdate() AND"
         set a [lrange $a 1 end]
      } else {
         set matchdate ""
      }
      if {$txt eq "-bool"} {
         set mode " IN BOOLEAN MODE"
         set a [lrange $a 1 end]
      }      
      if {$txt eq "-exp"} {
         set mode " WITH QUERY EXPANSION"
         set a [lrange $a 1 end]
      }
      if {$txt eq "-chan"} {   
         set chan [lindex $a 1]
         set a [lrange $a 2 end]
      }
   }
   set result [mysql::sel $::db_handle "SELECT id,date_format(created,'%Y-%m-%d %H:%i') created,channel,nick,uhost,type,arg,MATCH(`arg`,`nick`) AGAINST('[mysql::escape $::db_handle $a]'$mode) `match` FROM `log`  WHERE$matchdate channel = '[mysql::escape $chan]' HAVING `match` > 0  ORDER BY `match` DESC LIMIT 3" -list]


   if {[llength $result] > 0} {
      putserv "PRIVMSG $c :I have some results for you $n";
      #id created channel nick uhost type arg
      foreach res $result {
         switch [string tolower [lindex $res 5]] {
            message {
               putserv "PRIVMSG $c :[lindex $res 1] <[lindex $res 3]> [lindex $res 6]";
            }
            topic {
               putserv "PRIVMSG $c :[lindex $res 1] * [lindex $res 3] set channel topic to '[lindex $res 6]'";
            }
            notice {
               putserv "PRIVMSG $c :[lindex $res 1] -[lindex $res 3]@[lindex $res 2]- [lindex $res 6]";
            }
            kick {
               putserv "PRIVMSG $c :[lindex $res 1] * [lindex [lindex $res 6] 0] was kicked out of the channel by [lindex $res 3] ([lrange [lindex $res 6] 1 end])";
            }
            quit {
               putserv "PRIVMSG $c :[lindex $res 1] [lindex $res 3] quit irc ([lindex $res 6])";
            }
            part {
               putserv "PRIVMSG $c :[lindex $res 1] [lindex $res 3] left the channel ([lindex $res 6])";
            }
            join {
               putserv "PRIVMSG $c :[lindex $res 1] [lindex $res 3] joined the channel";
            }
            mode {
               putserv "PRIVMSG $c :[lindex $res 1] [lindex $res 3] sets mode: [lindex $res 6]";
            }
            ctcp {
               putserv "PRIVMSG $c :[lindex $res 1] \[[lindex $res 3]\] [lindex $res 6]";
            }
            ctcr {
               putserv "PRIVMSG $c :[lindex $res 1] \[[lindex $res 3] reply\] [lindex $res 6]";
            }
            split {
               putserv "PRIVMSG $c :[lindex $res 1] [lindex $res 3] left the channel in a netsplit";
            }
            rejoin {
               putserv "PRIVMSG $c :[lindex $res 1] [lindex $res 3] rejoined the channel after a netsplit";
            }
            nick {
               putserv "PRIVMSG $c :[lindex $res 1] [lindex $res 3] changed nick to [lindex $res 6]";
            }
         }

      }
   } else {
      putserv "PRIVMSG $c :Didn't find anything, $n";
   }
   }
   
}


######################## Credit for the rest of the script goes to Sumsar website: http://www.sp00fed.dk /forum

bind PUB m !log log:public

proc log:public {nick uhost hand chan arg} {
   set s [lindex $arg 0]
   switch $s {
      on {
         if {[channel get $chan mysqllog]} {
            putserv "PRIVMSG $chan :Logging is already \002active\002 for $chan"
         } else {
            channel set $chan +mysqllog
            putserv "PRIVMSG $chan :Logging is now \002active\002 for $chan"
         }
      }
      off {
         channel set $chan -mysqllog
         putserv "PRIVMSG $chan :Logging is \002no longer active\002 for $chan"
      }
      default {
         if {[channel get $chan mysqllog]} {
            putserv "PRIVMSG $chan :Logging is \002active\002 for $chan"
         } else {
            putserv "PRIVMSG $chan :Logging is \002not active\002 for $chan"
         }
      }
   }
}



Mysql Table:
Code: Select all
DROP TABLE IF EXISTS `log`;
CREATE TABLE `log` (
  `id` int(11) NOT NULL auto_increment,
  `created` datetime default NULL,
  `channel` varchar(255) default NULL,
  `nick` varchar(20) default NULL,
  `uhost` varchar(255) default NULL,
  `type` varchar(20) default NULL,
  `arg` varchar(255) default NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;


Requires mysqltcl 3.02 (maybe less?)

Features:
Logs every event to an mysql table.
Searching in logs from IRC (!logsearch requires global op)
Turning logging on and off (!log on/off requires global master)

For !logsearch the channel must have set +mysqllogsearch, and to enable logging the channel must have +mysqllog (!log on/off)

Examples on using !logsearch:
!logsearch <parameters> <search words>

Parameters are:
-chan #channel : search in the given channel
-bool : search using boolean mode you can include and exclude words by +/-
-exp : query expansion, searches and extra time in the table for more expanded results
-today : only search for events today.

eg.
!logsearch -chan #takeover -today -bool +Sumsar -elite
!logsearch -today Dandy owns
!logsearch -exp -chan #Dandy Takes search to the next level
Dandy
 

Re: Channel Logging to mySQL

Postby Dandy » Thu Jun 05, 2008 10:33 pm

PHP Online Viewing of the log:

Code: Select all
<?php
mysql_connect("host","user","pass");
mysql_select_db("database");
function utf8($str) { return mb_convert_encoding($str,'iso-8859-1'); }
header("Content-type: text/html; charset=iso-8859-1");
//$channels = mysql_query("SELECT distinct channel FROM log") or die(mysql_error());
//$c = array();
//while ($r = mysql_fetch_object($channels)) { $c[] = $r->channel; }

//$dates = mysql_query("SELECT distinct date_format(created,'%d-%m-%Y') date FROM log") or die(mysql_error());
//$d = array();
//while ($r = mysql_fetch_object($dates)) { $d[] = $r->date; }*/
$select = mysql_query("SELECT distinct concat(date_format(created,'%d-%m-%Y'),'_',channel) `select` FROM log ORDER BY channel,created");
$s = array();
while ($r = mysql_fetch_object($select)) {
   $s[] = $r->select;
}
?><!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
    <head>
        <title>eggdrop - log</title>
        <link rel="stylesheet" type="text/css" href="/style.css" />
    <body>
    <form action="<?php echo $_SERVER['PHP_SELF']; ?>" method="get">
   <strong>Select:</strong><br />
   <?php
          print('<select name="select">');
          foreach ($s as $se) {
              if (base64_decode($_GET['select']) == $se) {
                  printf('<option value="%s" selected="selected">%s</option>',base64_encode($se),$se);
              } else {
                  printf('<option value="%s">%s</option>',base64_encode($se),$se);
              }
          }
          print('</select>');
        ?><br />
        <strong>Search:</strong><br />
        <input type="text" name="word" value="<?php echo $_GET['word']; ?>" /><br />
        <input type="submit" name="query" value="Find" />
        <strong>Show:</strong>&nbsp;
   <input type="checkbox" name="show[]" value="message" id="check_message" <?php if (!is_array($_GET['show']) || in_array('message',$_GET['show'])) echo ' checked="checked"'; ?> /><label for="check_message">Message</label>
   <input type="checkbox" name="show[]" value="notice" id="check_notice" <?php if (!is_array($_GET['show']) || in_array('notice',$_GET['show'])) echo ' checked="checked"'; ?> /><label for="check_notice">Notice</label>
   <input type="checkbox" name="show[]" value="join" id="check_join" <?php if (!is_array($_GET['show']) || in_array('join',$_GET['show'])) echo ' checked="checked"'; ?> /><label for="check_join">Join</label>
   <input type="checkbox" name="show[]" value="part" id="check_part" <?php if (!is_array($_GET['show']) || in_array('part',$_GET['show'])) echo ' checked="checked"'; ?> /><label for="check_part">Part</label>
   <input type="checkbox" name="show[]" value="quit" id="check_quit" <?php if (!is_array($_GET['show']) || in_array('quit',$_GET['show'])) echo ' checked="checked"'; ?> /><label for="check_quit">Quit</label>
   <input type="checkbox" name="show[]" value="rejoin" id="check_rejoin" <?php if (!is_array($_GET['show']) || in_array('rejoin',$_GET['show'])) echo ' checked="checked"'; ?> /><label for="check_rejoin">Net Rejoin</label>
   <input type="checkbox" name="show[]" value="split" id="check_split" <?php if (!is_array($_GET['show']) || in_array('split',$_GET['show'])) echo ' checked="checked"'; ?> /><label for="check_split">Net split</label>
   <input type="checkbox" name="show[]" value="ctcp" id="check_ctcp" <?php if (!is_array($_GET['show']) || in_array('ctcp',$_GET['show'])) echo ' checked="checked"'; ?> /><label for="check_ctcp">Ctcp</label>
   <input type="checkbox" name="show[]" value="ctcp-reply" id="check_ctcr" <?php if (!is_array($_GET['show']) || in_array('ctcp-reply',$_GET['show'])) echo ' checked="checked"'; ?> /><label for="check_ctcr">Ctcp-reply</label>
   <input type="checkbox" name="show[]" value="kick" id="check_kick" <?php if (!is_array($_GET['show']) || in_array('kick',$_GET['show'])) echo ' checked="checked"'; ?> /><label for="check_kick">Kick</label>
   <input type="checkbox" name="show[]" value="mode" id="check_mode" <?php if (!is_array($_GET['show']) || in_array('mode',$_GET['show'])) echo ' checked="checked"'; ?> /><label for="check_mode">Mode</label>
   <input type="checkbox" name="show[]" value="topic" id="check_topic" <?php if (!is_array($_GET['show']) || in_array('topic',$_GET['show'])) echo ' checked="checked"'; ?> /><label for="check_topic">Topic</label>
   <input type="checkbox" name="show[]" value="nick" id="check_nick" <?php if (!is_array($_GET['show']) || in_array('nick',$_GET['show'])) echo ' checked="checked"'; ?> /><label for="check_nick">Nick change</label>

    </form>
<?php
    if (isset($_GET['query']) && $_GET['query'] == "Find") {
       
        if (preg_match('/^([0-9]{1,2})-([0-9]{1,2})-([0-9]{4})_(.*?)$/',base64_decode($_GET['select']),$match))
        {
            $show = $_GET['show'];
       foreach ($show as $k => $v) { if (!in_array($v,array('message','notice','join','part','quit','rejoin','split','ctcp','ctcp-reply','kick','mode','topic','nick'))) { unset($show[k]); } else { $show[$k] = "'".strtoupper($v)."'"; } }
       if (count($show)) {
            $sql = sprintf("SELECT date_format(created,'%%d-%%m-%%Y %%H:%%i') created,type,arg,channel,nick,uhost FROM log WHERE date(created) = '%s-%s-%s' AND channel = '%s' AND type IN (%s)",$match[3],$match[2],$match[1],mysql_real_escape_string($match[4]),join(",",$show));
            if (!empty($_GET['word'])) {
                $sql .= sprintf(" AND match(`arg`,`nick`) AGAINST('%s')",mysql_real_escape_string($_GET['word']));
            }
            $sql .= " ORDER BY id";
            $q = mysql_query($sql) or die(mysql_error());
            while ($r = mysql_fetch_object($q)) {
                $r->arg = utf8(htmlspecialchars($r->arg));
                switch (strtolower($r->type)) {
                    case "message":
                        printf("<span class='type_message'>%s <strong>&lt;%s&gt;</strong> %s</span>",$r->created,$r->nick,$r->arg);
                        echo "<br />\n";
                        break;
                    case "notice":
                        printf("<span class='type_notice'>%s <strong>-%s@%s-</strong> %s</span>",$r->created,$r->nick,$r->channel,$r->arg);
                        echo "<br />\n";
                        break;
                    case "nick":
                        printf("<span class='type_nick'>%s * %s changed nick to %s</span>",$r->created,$r->nick,$r->arg);
                        echo "<br />\n";
                        break;
                    case "quit":
                        printf("<span class='type_quit'>%s * %s has quit irc (%s)</span>",$r->created,$r->nick,$r->arg);
                        echo "<br />\n";
                        break;                       
                    case "split":
                        printf("<span class='type_quit'>%s * %s was split of the network</span>",$r->created,$r->nick);
                        echo "<br />\n";
                        break;                       
                    case "rejoin":
                        printf("<span class='type_join'>%s * %s returned from a netsplit</span>",$r->created,$r->nick);
                        echo "<br />\n";
                        break;
                    case "join":
                        printf("<span class='type_join'>%s * %s has joined the channel.</span>",$r->created,$r->nick);
                        echo "<br />\n";
                        break;
                    case "kick":
                        $exp = explode(" ",$r->arg,2);
                        printf("<span class='type_kick'>%s * %s was kicked out of the channel by %s (%s).</span>",$r->created,$exp[0],$r->nick,$exp[1]);
                        echo "<br />\n";
                        break;
                    case "part":
                        printf("<span class='type_part'>%s * %s left the channel (%s)</span>",$r->created,$r->nick,$r->arg);
                        echo "<br />\n";
                        break;
                    case "topic":
                        if ($r->nick == "*") {
            continue;
                        } else {
                            printf("<span class='type_topic'>%s * %s set channel topic to \"%s\"</span>",$r->created,$r->nick,$r->arg);
                            echo "<br />\n";
                        }
                        break;
                    case "mode":
                        printf("<span class='type_mode'>%s * %s set channel modes: %s</span>",$r->created,$r->nick,$r->arg);
                        echo "<br />\n";
                        break;
                    case "ctcp":
                        $exp = explode(" ",$r->arg,2);
                       
                        if (strtoupper($exp[0]) == "ACTION:") {
                            printf("<span class='type_action'>%s * %s %s</span>",$r->created,$r->nick,$exp[1]);
                        } else {
                            printf("<span class='type_ctcp'>%s [%s] %s</span>",$r->created,$r->nick,$r->arg);
                        }
                        echo "<br />\n";
                        break;
                    case "ctcp-reply":
                        printf("<span class='type_ctcr'>%s [%s reply] %s</span>",$r->created,$r->nick,$r->arg);
                        echo "<br />\n";
                        break;

                       
                                               
                }
}
            }
        }
    }
?>
</body>
</html>



Enjoy the script.
Dandy
 

Re: Channel Logging to mySQL

Postby Cristian » Fri Jun 06, 2008 1:59 pm

nice scripts, I love it. Keep up the good work ;)
Cristian
proof of advance
proof of advance
 
Posts: 282
Joined: Sun Nov 04, 2007 3:02 pm
Location: Denmark
Authnick: Sumsar

Re: Channel Logging to mySQL

Postby Dandy » Fri Jun 06, 2008 2:29 pm

Thanks Sumsar, one day i will catch up with you on the tcl skills
Dandy
 

Re: Channel Logging to mySQL

Postby Cristian » Sat Jun 07, 2008 2:53 pm

and I on your php skills ;)
Cristian
proof of advance
proof of advance
 
Posts: 282
Joined: Sun Nov 04, 2007 3:02 pm
Location: Denmark
Authnick: Sumsar

Re: Channel Logging to mySQL

Postby Lukemob » Sat Jun 07, 2008 7:40 pm

Your PHP coding is good Dandy, I like it!
Lukemob
 

Re: Channel Logging to mySQL

Postby Dandy » Sat Jun 07, 2008 7:58 pm

Thanks lukemob
Dandy
 

Re: Channel Logging to mySQL

Postby Lukemob » Sun Jun 08, 2008 12:52 pm

Art is needed to be appreciated! ;-)
Lukemob
 

Re: Channel Logging to mySQL

Postby daveyw » Fri Jul 11, 2008 7:34 pm

I found 1 "bug".

- When somebody says something on IRC the bot will save it inclusive the colors.
- But the PHP log page doesn't know the mIRC colors so we see the blocks.

Now I got 2 ideas

- Just fix it that we see on the PHP page also the colors
OR
- Just remove the colors, so he will only save the text, modes etc without any colors.
daveyw
 

Re: Channel Logging to mySQL

Postby Dandy » Fri Jul 11, 2008 7:41 pm

I fixed that, but i happend to delete the php and css file.
Dandy
 

Next

Return to TCL Scripting Release

Who is online

Users browsing this forum: No registered users and 0 guests

cron