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