#  Example 5.26. Using Win32::OLE to populate a spreadsheet with server errors. 
#  ----------------------------------------
#  From "Win32 Perl Programming: The Standard Extensions" by Dave Roth
#  Published by Macmillan Technical Publishing.
#  ISBN # 1-57870-067-1

use Win32::OLE qw( with );
use Win32::OLE::Variant;
use Win32::EventLog;
use Win32::NetAdmin;
use Win32::OLE::Const 'Microsoft Excel';

$Win32::EventLog::GetMessageText = 1;
$DateFormat = "DDD mmm dd, yyyy -- hh:mm:ss";
$Class = "Excel.Application";
$Domain = "";

@EVENT_SOURCES = ( "System", "Application" );
$EVENT_TYPE = EVENTLOG_ERROR_TYPE | EVENTLOG_WARNING_TYPE;

$Dir = "c:\\temp";
$FileName = "AdminReport.xls";
$File = "$Dir\\$FileName";

$SecPerDay = 24 * 60 * 60;
$Now = time();
$TimeLimit = $Now - ( $SecPerDay * 1);

$Excel = GetApplication( $Class ) || Error( "Could not start $Class" );
$Book = GetWorkbook( $Excel ) || Error( "Could not obtain a workbook" );
$Sheet = GetWorksheet( $Book ) || Error( "Could not obtain a worksheet" );

print "Fetching list of servers...\n";
Win32::NetAdmin::GetServers( '', $Domain, SV_TYPE_DOMAIN_CTRL |  SV_TYPE_DOMAIN_BAKCTRL , \@Servers );

$Row = 3;
foreach $Machine ( sort( @Servers ) )
{
  print "Processing $Machine.\n";
  $Sheet->Range( "A" . ++$Row )->{Value} = $Machine;
  $Sheet->Range( "A$Row" )->{Font}->{Bold} = 1;
  map{ ProcessLog( $Machine, $_, $EVENT_TYPE ); } ( @EVENT_SOURCES );
  $Row++;
}
ShutDownSheet( $Sheet );


sub ProcessLog
{
  my ( $Machine, $Source, $Type )= @_;
  my $Flag, $Num, %Hash;
  my $Event = new Win32::EventLog( $Source, $Machine ) || 
    sub
    { 
      $Sheet->Range( "B" . ++$Row )->{Value} = "Unable to connect.";
      return; 
    };
  if( $Event->GetNumber( $Num ) )
  {
    my $iCount = 0;
    $Flag = EVENTLOG_BACKWARDS_READ | EVENTLOG_SEQUENTIAL_READ;
    do
    {
      if( $Event->Read( $Flag, $Num, \%Hash ) )
      {
        print "\r  $Source records processed: ", ++$iCount, " " x 10;
        if( $Hash{EventType} & $Type )
        {
          my ( $EventType, $Color, $Time );

          if( $Hash{EventType} == EVENTLOG_ERROR_TYPE )
          {
            $EventType = "Error";
            $Color = 3;     # Red
          }
          elsif( $Hash{EventType} == EVENTLOG_WARNING_TYPE )
          {
            $EventType = "Warning";
            $Color = 53;     # Red-Orange
          }
          elsif( $Hash{EventType} == EVENTLOG_INFORMATION_TYPE )
          {
            $EventType = "Information";
            $Color = 1;     # Black
          }
          $Row++;
          # Format the time so that we can create a date based variant
          $Time = "" . localtime( $Hash{TimeGenerated} );
          $Time =~ s/^.*?\s+(.*?)\s+(.*?)\s+(.*?)\s+(.*)/$1 $2 $4 $3/;
          $Sheet->Range( "B$Row:H$Row" )->{Value} = [
            "$Source: $EventType",
            $Hash{Source},
            ($Hash{Event}) ? $Hash{Event}:"None",
            ($Hash{User}) ? $Hash{User}:"N/A",
            $Hash{Computer},
            new Win32::OLE::Variant( VT_DATE, $Time ),
            $Hash{Message}
          ];
          $Sheet->Range( "B$Row" )->{Font}->{ColorIndex} = $Color;
        }
      }
      # This will cause the next reading of the registry to move to the
      # next record automatically.
      $Num = 0;
    } while( $TimeLimit < $Hash{TimeGenerated} );
    print "\n";
    Win32::EventLog::CloseEventLog( $Event->{handle} );
  }
}

sub GetApplication
{
  my( $Class ) = @_;
  my( $Application );

  if( ! ( $Application = Win32::OLE->GetActiveObject( $Class ) ) )
  {
    $Application = new Win32::OLE( $Class , "Quit" ) || die;
  }
  $Application->{Visible} = 1;
  return( $Application );
}

sub GetWorkbook
{
  my( $Application ) = @_;
  my $Book;
  if( ! ( $Book = $Application->Workbooks( $FileName ) ) )
  {
    if( ! ( $Book = $Application->Workbooks->Open( $File ) ) )
    {
      my $Temp = $Application->{SheetsInNewWorkbook};
      $Application->{SheetsInNewWorkbook} = 1;

      $Book = $Application->Workbooks->Add();
      $Book->SaveAs( $File );

      $Application->{SheetsInNewWorkbook} = $Temp;
      $UseSheetNumber = 1;
    }
  }
  # Make the workbook window visible (Office 2000)
  $Book->{Application}->Windows( $Book->{Name} )->{Visible} = 1;
  return( $Book );
}

sub GetWorksheet
{
  my( $Book ) = @_;
  my( $Sheet );
  if( $UseSheetNumber )
  {
    $Sheet = $Book->Worksheets( $UseSheetNumber );
  }
  else
  {
    $Sheet = $Book->Worksheets()->Add();
  }
  SetupWorksheet( $Sheet );
  return( $Sheet );
}

sub SetupWorksheet
{
  my( $Sheet ) = @_;
  my( $Range );
  my( $Date, $Name, $iCount, @Date );
  @Date = localtime();
  $Date = sprintf( "%04d.%02d.%02d", $Date[5] + 1900, $Date[4] + 1, $Date[3] );
  $Name = $Date;
  $iCount = 1;
  while( $Sheet->{Parent}->Worksheets( $Name ) )
  {
    $Name = "$Date #" . ++$iCount;
  }

  $Sheet->{Name} = $Name;
  $Range = $Sheet->Range( "A1" );
  $Range->{Value} = "Server Error Logs for the morning of $Date";
  $Range->{Font}->{Size}=24;
  $Range->{Font}->{ColorIndex} = 6;    # Yellow
  $Sheet->Rows("1:1")->{Interior}->{ColorIndex} = 5;  # Blue
  $Range = $Sheet->Range("A3:H3");
  $Range->{Value} = [ 
    "Server", "Type", "Source", "Event", "User", 
    "Computer", "Time", "Description" ];
  with( $Range->{Font},
    Bold  => 1,
    Italic => 1,
    Size  => 16
  );
  $Range->{HorizontalAlignment} => xlCenter;
  with( $Sheet->Columns( "G" ),
    NumberFormat    => $DateFormat,
    HorizontalAlignment => xlCenter
  );
}

sub ShutDownSheet
{
  my( $Sheet ) = @_;
  $Sheet->Columns( "B:G" )->AutoFit();
  $Sheet->Columns( "H:H" )->{ColumnWidth} = 72;
  $Sheet->Rows()->AutoFit();
  $Sheet->{Parent}->Save();
  $Sheet->{Parent}->Close();
}

sub Error
{
  my( $Error ) = @_;
  print "$Error\n";
  exit();
}