Commit Diff


commit - a87dee89d63a5dbcb0f7de0504736635f69c7449
commit + ad6945892a92f22013160738eab1ca84508118e3
blob - /dev/null
blob + 0787940b1cd48a055ccb438a0060cf656d5ca77b (mode 644)
--- /dev/null
+++ report.orig
@@ -0,0 +1,121 @@
+#!/usr/bin/perl
+#
+use strict;
+#no strict 'refs';
+use warnings;
+use Data::Dumper;
+# Bsd pledge/unveil security modules
+use OpenBSD::Pledge;
+use OpenBSD::Unveil;
+
+# Database modules
+use DBI;
+use DBD::SQLite;
+
+# setup log level constents
+use constant {
+    NONE => 0,
+    ERRORS => 1,
+    WARNINGS => 2,
+    ALL => 3,
+};
+my $verbose = ERRORS;
+sub debug {
+	my ($level, $msg) = @_;
+	if ($verbose >= $level) { print "$msg\n"; }
+}
+
+# 
+use lib './';
+
+my ($ipTable, $nameTable) = init_ip_xref();
+
+while (my $username = shift) {  #param 1 should be the name of a user to generate a report from.
+		my $dbFile = '/var/www/botnow/botnow.db';
+		my $dbh = connectdb($dbFile);
+		if (!defined($dbh))  {
+			die "failed to connect to $dbFile";
+		}
+		my $stmt=qq{select * from bnc join irc on (bnc.ircid = irc.id) where username is ?};
+		my $sth=$dbh->prepare($stmt);
+		$sth->execute($username) or die "execution failed: $dbh->errstr()"; 
+		while (my $row=$sth->fetchrow_hashref) {
+			my $dossier =qq{
+		Username: $row->{username}
+		Email Address: $row->{email}
+		$row->{hostmask}
+		$row->{ctcpversion}
+		$row->{ctcptime}
+		Registration Date: $row->{date}
+		};
+			print $dossier;
+			print "Same Email [" 
+				. join(', ', @{$dbh->selectcol_arrayref(qq\Select username from bnc join irc on (bnc.ircid = irc.id) where email = ?\,undef,$row->{email})})
+				. "]\n";
+			print "Same Date ["
+				. join(', ', @{$dbh->selectcol_arrayref(qq\Select username from bnc join irc on (bnc.ircid = irc.id) where date = ?\,undef,$row->{date})})
+				. "]\n";
+			print "Same Hostmask ["
+				. join(', ', @{$dbh->selectcol_arrayref(qq\Select username from bnc join irc on (bnc.ircid = irc.id) where hostmask = ?\,undef,$row->{hostmask})})
+				. "]\n";
+			print Dumper($row);
+			print "Frequency of connections from: \n" . Dumper($nameTable->{$username});
+			print "Other Users connecting from: \n";
+			foreach(keys(%{$nameTable->{$username}})) {
+				print "$_ =>[" . join (', ', keys(%{$ipTable->{$_}})) . "]\n";
+			}
+		}
+}
+
+
+
+
+exit 0;
+
+sub connectdb {
+	my $dbpath=shift;
+	my $dsn      = "dbi:SQLite:dbname=$dbpath";
+	my $user     = "";
+	my $password = "";
+	my $dbh = DBI->connect($dsn, $user, $password, {
+		PrintError       => 1,
+		RaiseError       => 1,
+		AutoCommit       => 1,
+		FetchHashKeyName => 'NAME_lc',
+	}) or die "Couldn't connect to database: " . $DBI::errstr;
+	if (!(-s "$dbpath")) {
+		main::debug(ALL, "Cant locate $dbpath");
+		exit 1;
+	}
+	main::debug(ALL, "connected to $dbpath");
+	return $dbh;
+}
+# Read and index the znc log file.
+sub init_ip_xref {
+	# Get IP addresses
+	my $ip2usernames={};
+	my $usernames2ip={};
+	open my $zncLog, '<', '/home/znc/home/znc/.znc/moddata/adminlog/znc.log' or die "Can't open znc log file";
+	while (my $line = <$zncLog>) {
+			if( $line =~/.*\[(.*)\] connected to ZNC from (.*)/) {
+					my $name=$1;
+					my $ip=$2;
+					if (!defined($ip2usernames->{$ip})) {
+						$ip2usernames->{$ip} = {};
+					}
+					if (!defined($ip2usernames->{$name})) {
+						$ip2usernames->{$ip}->{$name}=0;
+					}
+					$ip2usernames->{$ip}->{$name}++;
+					if (!defined($usernames2ip->{$name})) {
+						$usernames2ip->{$name}={};
+					}
+					if (!defined($usernames2ip->{$name}->{$ip})) {
+						$usernames2ip->{$name}->{$ip}=0;
+					}
+					$usernames2ip->{$name}->{$ip}++;
+			}
+	}
+	close $zncLog;
+	return $ip2usernames,$usernames2ip;
+}