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