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