Extracting Data From Sendmail

Shell Programming and Scripting
1

Hello,

Like many Unix shops, Our systems send Email alerts whenever things break. I have been tasked with writing a shell script to get the email alerts from Sendmail, extract the Date / Time, From, Subject, and message text from the emails and punch them into a MySQL DB. This will then be parsed and used to generate Error Stats and SLA reports.

My problem is I can't seem to find any info or blurb of code on how to extract this info from a Sendmail using a shell script, and my O'Rielly Sendmail book is MIA.

Anyone out there have any expierience with this and/or could guide me in the right direction?

Thanks in Advance,

ChrisA.

2

I'm not sure I follow what you are trying to do.

Do you mean to process the emails or the sendmail log?

3

I need to parse the individual emails as they come in, but I am not sure how to extract the data from sendmail. I could have the emails go to a special account, but I dont know how to get the need data out of the emails. If the email can be dumped to a file I could do it that way, but I am not as familiar with Sendmail as I would like.

4

I think perl can handle that, something like :

open(MYINPUTFILE, "<filename.in");
open(MYOUTPUTFILE, ">filename.out");
while(<MYINPUTFILE>)
 {
 my($line) = $_;
 chomp($line);
 if($line =~ m|(\d{5})(.{20})(\d\d)/(\d\d)/(\d\d)|)
 {
 my($zip,$name,$mm,$dd,$yy) = ($1,$2,$3,$4,$5);
 if($yy > 10)
 {$yy += 1900}
 else
 {$yy += 2000}
 my($first, $last) = split(/ /, $name);
 $line = sprintf("%-16s%-10s%02d/%02d/%04d%5d",
 $last,$first,$mm,$dd,$yy,$zip);
 print MYOUTPUTFILE "$line\n";
 }
 }
close(MYINPUTFILE);
close(MYOUTPUTFILE);

I took this example from random google page, therefore it does not describe exactly the task you want to achieve, but
open the file, parse the content as defined in regexp and output it in a different file. Then INSERT this in SQL. Also, you need to know where the sendmail store those e-mails, if this is one e-mail, or a single flat file, my example is "/var/spool/mail/root"
This is just a suggestion, there are more efficient ways to achieve that, I believe that someone will post another opinion soon.
Edit : Actually, I believe that Python would be more elegant way to solve this, but i'm not really expirienced with it, ghostdog74 is one of Python's experts here.
Hope this helps.