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