Get a record from Windows NT Application Event Log

Question

Get a record from Windows NT Application Event Log

AER

SSCrazy

Points: 2763
More actions
October 30, 2002 at 2:05 pm

#105184

Hi everybody,
When a job which was created from DTS package fails it writes a log to the Windows NT Application Event Log.
Does anybody know a way of getting a record from Windows NT Application Event Log utilizing a SQL Server DTS Package?
Any help will be appreciated.
Thanks in advance.
Alex
November 4, 2002 at 8:00 am

This was removed by the editor as SPAM

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Antares686 SSC Guru Points: 125444 More actions · Answer 1

Not directly, there is an XP that can read the application log but you still have to parse the data yourself (doesn't work the same in 2K and I haven't figured out the difference yet). However a better solution would be to write your own process. EVen then thou you will still have to parse the Log data. Now if you need to know the failure details you can hook the output to a file in the DTS apckage properties or get when the error occurrs the error details in most cases (sorry have not done the later).

AER SSCrazy Points: 2763 More actions · Answer 2

James,

Thank you for your response.

Job failure was just an example of writing to Windows NT Event Log.

My goal is to get the record of deadlocking on my SQL Server from Windows NT Event Log.

For that purpose, I don't want to use an SQL Server Profiler or other tools to monitor my database the whole time.

I thought that a sequential calls to the Windows NT Event Log from the job, scheduled from DTS package would take a lot less resources.

Any suggestions how can I get the records of the deadlocks on SQL Server the easiest way?

Thank you in advance.

Alex

K. Brian Kelley SSC Guru Points: 114642 More actions · Answer 3

If you have PerlScript installed, you can use the Win32::EventLog package to interrogate the event log. PerScript is considered an ActiveX script like VBScript and JScript. PerlScript is part of the ActivePerl package. You can find it at http://www.activestate.com/.

What I've done is scan the event log for other errors, but I have separate Perl scripts to monitor certain processes.

K. Brian Kelley

http://www.truthsolutions.com/

Author: Start to Finish Guide to SQL Server Performance Monitoring

http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1

K. Brian Kelley
@kbriankelley

AER SSCrazy Points: 2763 More actions · Answer 4

Thank you Brian,

I don't have PerlScript installed on my machine. I will definitely look into it if there will be no other solutions.

But I would still prefer using my DTS scripting options rather than having a new program installed for that purpose.

Any other ideas?

Thank you.

Alex

K. Brian Kelley SSC Guru Points: 114642 More actions · Answer 5

The reason I use Perl is because the Win32::EventLog wraps the Windows API calls for the Event Log. It was the simplest solution for me. Here's an example of a script which checks the event log and then emails if an error is found. I typically run this every 5 minutes. If PerlScript is installed, you could use it through DTS.



use Win32::EventLog;

use Net::SMTP;



my $eventFlag = EVENTLOG_BACKWARDS_READ | EVENTLOG_SEQUENTIAL_READ;

my $eventHash;

my $timeLastEvent;



# Retrieve time of last event from file

if ( -e 'myserver.txt') {

open(TIMEFILE, 'myserver.txt') or die "Could not open time file."; 

if (eof(TIMEFILE))

{

$timeLastEvent = 0;

}

else

{

chomp($timeLastEvent = <TIMEFILE>);

}

close(TIMEFILE);

}

else

{

$timeLastEvent = 0;

}



# Open Application Log

my $event = new Win32::EventLog("Application", "myserver.mycompany.com") 

or die "Could not open event log.";



$Win32::EventLog::GetMessageText = 1;



my $timeGenerated = 9999999999;

my $foundNewEvent = 0;



# Process events since timeLastEvent

while (($timeGenerated > $timeLastEvent) and (!$foundNewEvent))

{

$event->Read( $eventFlag, 0, $eventHash) or die "Could not read event.";

$timeGenerated = $eventHash->{TimeGenerated};



# new event indicating error in sendmail

if (($eventHash->{EventType} == EVENTLOG_ERROR_TYPE) and ($timeGenerated > $timeLastEvent))

{

$foundNewEvent = 1;



$SMTPServer = Net::SMTP->new('mySMTP.mycompany.com');

$SMTPServer->mail('myself@mycompany.com');

$SMTPServer->recipient('myself@mycompany.com');

$SMTPServer->data();

$SMTPServer->datasend("FROM: Me <myself\@mycompany.com>\n");

$SMTPServer->datasend("TO: Me <myself\@mycompany.com>\n");

$SMTPServer->datasend("SUBJECT: New error in application log\n");

$SMTPServer->datasend("\n");

$SMTPServer->datasend("Time last event occurred: " . localtime($timeGenerated) . "\n");

$SMTPServer->datasend("\n");

$SMTPServer->datasend("Description:\n");

$SMTPServer->datasend("$eventHash->{Strings} \n");

$SMTPServer->datasend("\n");

$SMTPServer->dataend();



$SMTPServer->quit();

}

}



Win32::EventLog::CloseEventLog($event->{handle});



# update time of last event in file

if ($foundNewEvent)

{

open(TIMEFILE, '>myserver.txt') or die "Could not open time file."; 

print(TIMEFILE "$timeGenerated\n");

close(TIMEFILE);

}

K. Brian Kelley

http://www.truthsolutions.com/

Author: Start to Finish Guide to SQL Server Performance Monitoring

http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1

K. Brian Kelley
@kbriankelley

AER SSCrazy Points: 2763 More actions · Answer 6

Brian,

Thank you so much for your script. I think, we will use your advise.

The problem is, I never used a PerlScript before and not familiar with this program.

Should we buy it or this is a free software?

And what do we need to have in order your script would work: is it Visual Perl or Perl Device Kit or something else?

And how can I implement this code in DTS package?

Thank you in advance.

Alex

K. Brian Kelley SSC Guru Points: 114642 More actions · Answer 7

You'll just want ActivePerl. Here is a short "Getting Started" guide I put together:

http://www.truthsolutions.com/perl/getting_started.htm

As far as the DTS package, when ActivePerl is installed, you'll see an option for "PerlScript Language" in addition to the standard "JScript Language" and "VBScript Language" within the ActiveX Script Task Properties.

K. Brian Kelley

http://www.truthsolutions.com/

Author: Start to Finish Guide to SQL Server Performance Monitoring

http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1

K. Brian Kelley
@kbriankelley

AER SSCrazy Points: 2763 More actions · Answer 8

Brian,

Thank you so much for your time and all your help.

Now I realized what kind of person a real author should be. The way you explain the things I strongly beleive is a right way to write books; it's easy and understandable.

I will definitely buy your 'Start to Finish Guide to SQL Server Performance Monitoring' book if I'll find it on the shelves.

And I'll definitely follow your advise about ActivePerl and utilize this tool.

Thank you again.

Alex