From time to time, organizations want to know if there are any users that are not used. For clients using MySQL 5.1 and up, that can handle a 1% overhead for general logging, we will enable the general log for a period of time and analyze the users that connect.
Note: we have some extremely busy clients, and we very rarely have a problem turning the general log on, other than making sure we’re rotating and compressing logs so we do not run out of disk space.
Once we have the logs, I run this little perl tool I made — I call it genlog_users.pl:
#!/usr/bin/perl
my $infile=$ARGV[0];
my %seen=();
my @uniq=();
open (INPUT, “<$infile”);
while (<INPUT>) {
my $line=$_;
if ($line=~/Connect/) {
if ($line=~/(\S*@\S*)/) { push(@uniq, $1) unless $seen{$1}++; }
} # end if line matches Connect
}
close INPUT;
open (OUTPUT, “>>..users.txt”);
$,=”\n”;
print OUTPUT (keys %seen);
print OUTPUT (“\n”);
close OUTPUT;
———-
I hope it is useful for whoever stumbles on this; I know it has been useful for me in the past — it’s just doing some string matching, and I bet if I used Python it would be done in half the lines, but it’s already fewer than 20 lines, so it’s pretty small to begin with.
From time to time, organizations want to know if there are any users that are not used. For clients using MySQL 5.1 and up, that can handle a 1% overhead for general logging, we will enable the general log for a period of time and analyze the users that connect.
Note: we have some extremely busy clients, and we very rarely have a problem turning the general log on, other than making sure we’re rotating and compressing logs so we do not run out of disk space.
Once we have the logs, I run this little perl tool I made — I call it genlog_users.pl:
#!/usr/bin/perl
my $infile=$ARGV[0];
my %seen=();
my @uniq=();
open (INPUT, “<$infile”);
while (<INPUT>) {
my $line=$_;
if ($line=~/Connect/) {
if ($line=~/(\S*@\S*)/) { push(@uniq, $1) unless $seen{$1}++; }
} # end if line matches Connect
}
close INPUT;
open (OUTPUT, “>>..users.txt”);
$,=”\n”;
print OUTPUT (keys %seen);
print OUTPUT (“\n”);
close OUTPUT;
———-
I hope it is useful for whoever stumbles on this; I know it has been useful for me in the past — it’s just doing some string matching, and I bet if I used Python it would be done in half the lines, but it’s already fewer than 20 lines, so it’s pretty small to begin with.