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