1 #!/usr/bin/perl
2 #
3 use strict;
4 #no strict 'refs';
5 use warnings;
6 use Data::Dumper;
7 # Bsd pledge/unveil security modules
8 use OpenBSD::Pledge;
9 use OpenBSD::Unveil;
10 
11 # Database modules
12 use DBI;
13 use DBD::SQLite;
14 
15 # setup log level constents
16 use constant {
17     NONE => 0,
18     ERRORS => 1,
19     WARNINGS => 2,
20     ALL => 3,
21 };
22 my $verbose = ERRORS;
23 sub debug {
24 	my ($level, $msg) = @_;
25 	if ($verbose >= $level) { print "$msg\n"; }
26 }
27 
28 # 
29 use lib './';
30 
31 my ($ipTable, $nameTable) = init_ip_xref();
32 
33 while (my $username = shift) {  #param 1 should be the name of a user to generate a report from.
34 		my $dbFile = '/var/www/botnow/botnow.db';
35 		my $dbh = connectdb($dbFile);
36 		if (!defined($dbh))  {
37 			die "failed to connect to $dbFile";
38 		}
39 		my $stmt=qq{select * from bnc join irc on (bnc.ircid = irc.id) where username is ?};
40 		my $sth=$dbh->prepare($stmt);
41 		$sth->execute($username) or die "execution failed: $dbh->errstr()"; 
42 		while (my $row=$sth->fetchrow_hashref) {
43 			my $dossier =qq{
44 		Username: $row->{username}
45 		Email Address: $row->{email}
46 		$row->{hostmask}
47 		$row->{ctcpversion}
48 		$row->{ctcptime}
49 		Registration Date: $row->{date}
50 		};
51 			print $dossier;
52 			print "Same Email [" 
53 				. join(', ', @{$dbh->selectcol_arrayref(qq\Select username from bnc join irc on (bnc.ircid = irc.id) where email = ?\,undef,$row->{email})})
54 				. "]\n";
55 			print "Same Date ["
56 				. join(', ', @{$dbh->selectcol_arrayref(qq\Select username from bnc join irc on (bnc.ircid = irc.id) where date = ?\,undef,$row->{date})})
57 				. "]\n";
58 			print "Same Hostmask ["
59 				. join(', ', @{$dbh->selectcol_arrayref(qq\Select username from bnc join irc on (bnc.ircid = irc.id) where hostmask = ?\,undef,$row->{hostmask})})
60 				. "]\n";
61 			print Dumper($row);
62 			print "Frequency of connections from: \n" . Dumper($nameTable->{$username});
63 			print "Other Users connecting from: \n";
64 			foreach(keys(%{$nameTable->{$username}})) {
65 				print "$_ =>[" . join (', ', keys(%{$ipTable->{$_}})) . "]\n";
66 			}
67 		}
68 }
69 
70 
71 
72 
73 exit 0;
74 
75 sub connectdb {
76 	my $dbpath=shift;
77 	my $dsn      = "dbi:SQLite:dbname=$dbpath";
78 	my $user     = "";
79 	my $password = "";
80 	my $dbh = DBI->connect($dsn, $user, $password, {
81 		PrintError       => 1,
82 		RaiseError       => 1,
83 		AutoCommit       => 1,
84 		FetchHashKeyName => 'NAME_lc',
85 	}) or die "Couldn't connect to database: " . $DBI::errstr;
86 	if (!(-s "$dbpath")) {
87 		main::debug(ALL, "Cant locate $dbpath");
88 		exit 1;
89 	}
90 	main::debug(ALL, "connected to $dbpath");
91 	return $dbh;
92 }
93 # Read and index the znc log file.
94 sub init_ip_xref {
95 	# Get IP addresses
96 	my $ip2usernames={};
97 	my $usernames2ip={};
98 	open my $zncLog, '<', '/home/znc/home/znc/.znc/moddata/adminlog/znc.log' or die "Can't open znc log file";
99 	while (my $line = <$zncLog>) {
100 			if( $line =~/.*\[(.*)\] connected to ZNC from (.*)/) {
101 					my $name=$1;
102 					my $ip=$2;
103 					if (!defined($ip2usernames->{$ip})) {
104 						$ip2usernames->{$ip} = {};
105 					}
106 					if (!defined($ip2usernames->{$name})) {
107 						$ip2usernames->{$ip}->{$name}=0;
108 					}
109 					$ip2usernames->{$ip}->{$name}++;
110 					if (!defined($usernames2ip->{$name})) {
111 						$usernames2ip->{$name}={};
112 					}
113 					if (!defined($usernames2ip->{$name}->{$ip})) {
114 						$usernames2ip->{$name}->{$ip}=0;
115 					}
116 					$usernames2ip->{$name}->{$ip}++;
117 			}
118 	}
119 	close $zncLog;
120 	return $ip2usernames,$usernames2ip;
121 }