The Ji Village News

季庄新闻–Haidong Ji’s Blog

I’ve worked on and off with Perl, but really want to be more proficient with it. I will do more automation with Perl and will share it here for my reader and as a reference for myself. I just created a Perl tag on this blog.

Here is one script I cooked out today. It takes a directory / folder name as input, and return the latest or newest modified file. Suggestions welcome!

#use File::Copy;
use File::stat;
$dirname = shift or die "Please provide a directory to search for";
$timediff=0;
opendir DIR, "$dirname";
while (defined ($file = readdir(DIR)))
{
	if($file ne "." && $file ne "..")
	{
		$diff = time()-stat("$dirname/$file")->mtime;
		if($timediff == 0)
                       {
                       	$timediff=$diff;
                       	$newest=$file;
                       }
                if($diff<$timediff)
		       {
                       	$timediff=$diff;
                       	$newest=$file;
                       }
        }
}
print $newest,"\n";
#copy("$dirname/$newest", "c:/work/$newest");

This is mainly for my own reference. Save it as ListSysadmin.pl. Run ListSysadmin.pl SqlServerList.txt on command line. It lists all logins that has sa (sysadmin) rights on Sql Server instance, including individual domain members in a domain group that has sysadmin rights.

SqlServerList.txt should be a text file, with each individual line contains a Sql Server instance.

This script is an improvement over Linchi’s original scrip from his Real World SQL Server Administration with Perl. The original script only takes one instance as input parameter. Note: with my current site style sheet on my web site, I think it ignores the angle brackets, so you need to fix the script when you do copy and paste.

use strict;
use Win32::ODBC;
# Import the function from the module SQLDBA::Utility
use SQLDBA::Utility qw( dbaRemoveDuplicates );
# Import functions from the module Win32::NetAdmin
use Win32::NetAdmin qw( GetDomainController
                        GroupGetMembers
                        LocalGroupGetMembersWithDomain
                        UsersExist );
Main: {
   my $log = shift or die "***Err: $0 expects a file name.\n";
   open(LOG, "$log") or die "***Err: couldn't open $log.\n";
   while (<log>) {
      my $line = $_;
      if ($line =~ /\[?(\w+)/ix) {
         my $serverConn = $1;
         print "Instance: $1\n";
         # get all the accounts/logins with sysadmin role from SQL Server
         my $loginRef = getSQLSysadmins($serverConn);
         # in case the server name is followed by an instance name
         my ($server) = $serverConn =~ /([^\\]+)(\\.+)?/;
         # replace BUILTIN with the server name
         foreach (@{$loginRef->{groupSet}}) {
            s/BUILTIN\\/uc($server) . "\\"/e; # evaluate the right side before replace
         }
         # remove all local groups and get their members
         $loginRef = removeLocalGroups($loginRef);
         # get global group members
         $loginRef = getGlobalGroupMembers($loginRef);
         # now print out all the NT/SQL logins that are SQL sysadmin
         print "Listing all NT/SQL logins with the sysadmin rights ...\n";
         foreach (sort @{$loginRef->{resultSet}}) { print "\t$_\n"; }
               }
         }
   close(LOG);
} # Main
#########################
sub getSQLSysadmins {
    my $serverConn = shift or
       die "***Err: getSQLSysadmins() expects a server.";
   my $connStr = "Driver={SQL Server};Server=$serverConn;"  .
                  "Trusted_Connection=Yes;Database=master";
   my $conn = new Win32::ODBC ($connStr) or
      die "***Err: Can't connect to $serverConn. " . Win32::ODBC::Error();
   # get sysadmin logins (NT and SQL)
   my @logins = ();
   my $sql = q/SELECT loginname FROM master.dbo.syslogins
                WHERE sysadmin = 1
                  AND isntgroup = 0/;
   unless ($conn->Sql( $sql )) {
       @logins = (@logins, $conn->Data) while ($conn->FetchRow());
   } else {
       print "***Err: executing $sql. ", Win32::ODBC::Error(), "\n";
   }
   # get sysadmin nt groups
   my @groups = ();
   my $sql =  q/select loginname from master.dbo.syslogins
                 where sysadmin = 1
                   and isntgroup = 1/;
   unless ($conn->Sql( $sql )) {
       @groups = (@groups, $conn->Data) while ($conn->FetchRow());
   } else {
       print "***Err: executing $sql. ", Win32::ODBC::Error(), "\n";
   }
   $conn->Close();
   return { resultSet  => \@logins,
            groupSet   => \@groups,
            userSet    => undef };
} # getSQLSysadmins
###########################
sub removeLocalGroups {
    my $loginRef = shift or
         die "***Err: removeLocalGroups() expects a reference.";
    foreach my $group (@{$loginRef->{groupSet}}) {
        if (my $rc = getLocalGroupMembers($group)) {
            push @{$loginRef->{userSet}}, @{$rc};
        }
        else {
            push @{$loginRef->{userSet}}, $group;
        }
    }
    delete $loginRef->{groupSet};
    return $loginRef;
} # removeLocalGroups
##############################
sub getGlobalGroupMembers {
    my $loginRef = shift or
          die "***Err: getGlobalGroupMembers() expects a reference.\n";
    my @logins = ();
    foreach my $user (@{$loginRef->{userSet}}) {
        if ($user =~ /^NT\s+AUTHORITY\\/) {
            push @logins, $user;
            next;
        }
        my ($domain, $name) = $user =~ /^(.+?)\\(.+)$/;
        if ( UsersExist($domain, $name ) ) { # a user account
            push @logins, $user;
        }
        else {
            my $pdc;
            my @users;
            if (GetDomainController(undef, $domain, $pdc)) {
                $pdc =~ s/\\//g;
                if ( UsersExist($pdc, $name ) ) {
                    push @logins, $user;
                }
                else {  # $name is a global group account
                    # get users in the global group
                    if (GroupGetMembers($pdc, $name, \@users)) {
                        # prefix the users with the domain name
                        push @logins, map { "$domain\\$_"; } @users;
                    }
                    else {  # you shouldn't reach here
                        print "***Err: can't retrieve users for $domain\\$name.\n";
                        next;
                    }
                }
            }
            else {
                print "***Err: can't find domain controller for $domain.\n";
                next;
            }
        }
    }
    push @logins, @{$loginRef->{resultSet}};
    $loginRef->{resultSet} = dbaRemoveDuplicates(\@logins);
    return $loginRef;
} # getGlobalGroupMembers
################################
sub getLocalGroupMembers {
    my $group = shift or
        die "***Err: getLocalGroupMember() expects a group name.";
    my ($domain, $group_name) = $group =~ /^(.+?)\\(.+)$/  or
        die "***Err: $group must be qualified with a domain/machine name.";
    my @users = ();
    if (LocalGroupGetMembersWithDomain($domain, $group_name, \@users)) {
        return \@users;
    }
    else {
        return undef;
    }
}  # getLocalGroupMembers

One important method for performance tuning is to decrease number of logical reads, by adding, updating, and/or refreshing indexes and statistics. To see if those changes make a difference for you, it is important to look at query statistics.

Here is a sample output after set statistics io on:

Table 'Table1'. Scan count 0, logical reads 1673, physical reads 4, read-ahead reads 8, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Table2'. Scan count 0, logical reads 1673, physical reads 10, read-ahead reads 8, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Table3'. Scan count 1, logical reads 11871, physical reads 1, read-ahead reads 11865, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '#TempTable_____________________________________________________________________________________________________________000000000022'. Scan count 2, logical reads 276, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Table2'. Scan count 156, logical reads 1723, physical reads 1, read-ahead reads 8, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

I used to tally those numbers by hand. This is error prone, and can be time-consuming, especially if the output is big. So I cooked up a Perl script below for this:

#!/usr/bin/perl
use strict;
my $LogicalReads = 0;
Main: {
my $log = shift or die "***Err: $0 expects a file name.\n";
open(LOG, "$log") or die "***Err: couldn't open $log.\n";
while (<log>) {
findPattern($_);
}
close(LOG);
print "Total logical read is $LogicalReads\n";
} # Main
#######################
sub findPattern {
my $line = shift or die "***Err: function findPattern() expects a string.\n";
if ($line =~ /logical\sreads\s+(\d+)/ix) {
$LogicalReads = $LogicalReads + $1;
}
} # findPattern

Thanks Linchi and Tom for ideas!

I attended an introductory Perl course recently. I’ve always wanted to learn the language. It is a very powerful scripting tool with excellent regular expression support.

The training delivered by Daina Pettit from LearningTree was excellent. One nugget I got out of the class, that I think is worth half of the class cost, is this:

On command line, be it *nix or ActiveState on Windows, type:

perl -de 0

to open Perl debugger interactively. Perl will try to run any statement you type. It is a great way to learn.

-d launches the debugger. -e takes one line of program. When given 0, it will take whatever we type through the prompt.

Fellow SqlServerCentral author K. Brian Kelley wrote an excellent article on using Perl and Windows::ODBC module to manage Sql Server here. Brian is apparently a big fan of Perl.

As I am writing and publishing more and more blog entries, it becomes important that I have good and reliable backups. I know that if something happens and I cannot recover my entries and comments, I would be terribly upset. So I want to share my process here. Please feel free to share your backup methods by commenting. Hopefully somebody will find it useful.

Backup comprises two parts:

1. File backup

On a Linux machine with Apache, the default web files and directories reside in /var/www/html. Yours may be different. This include all php files and sub-directories like wp-content, wp-include, etc.

The easiest way to do it is to use tar command with -z to compress them. My post here gives you a pointer on tar.

These files are fairly static, so you do not need to back them up too often. You want to do file backup after you tried a new theme, installed a new plugin, etc. You should transfer this backup to a different box, in case the web host dies.

2. Database backup

For mysql database backup, you can use mysqldump, a utility that comes with mysql. mysqldump will iterate through all tables within the database, get their ddl, and dump all data in the form of insert statements. Below is the code to dump everything out and compress them using gzip:

mysqldump -q -e -hlocalhost -uLogin -pPassword MyBlogDb | gzip – > ./BlogDbBackup.sql.gz

Once again, gzip can shrink the size down pretty substantially. So do use it, especially if you have space issues.

There is a database backup plugin that comes with WordPress 2, which is the method I use. However, you do need to run chmod 777 on your database backup directory. If not, you will see the warning message of not enough privilege on your WordPress manage page. And you should be able to find out the backup directory name from that page, so you can run chmod.

In addition to the backup plugin, I also installed the wp-cron plugin. With wp-cron, I can schedule daily database backup and send it to an email address I defined. If I have a choice, I’d rather put everything in a shell script that backs everything up, compress them, and email the file to me. I then use cron to schedule it. However, since I am not root on my web host, I have had troubles setting up a smtp client for email, as documented here.

Now backup is done. The next step is to make sure my backup files work. I set up a standalone LAMP (Linux, Apache, MySql, PHP) box, extracted php files, restored the database by running:

mysql -hlocalhost -uLogin -p MyBlogDb < BlogDbBackup.sql

I then modified wp-config.php file. And behold, it worked. Even my Chinese entries displayed properly.

In a future post, I will share with you some of the lessons I learned in the process. Stay tuned…