Another Abnormal File Format
Last post Dec 09, 2004 07:32 AM by Anonymous
Dec 07, 2004 02:46 PM|Anonymous|LINK
Our blocked spam filter files that are blocked are .emls but can be read by notepad without any problems. If you open one you will see something like below. I've replaced our e-mail server and IP with other characters
Received: from ??? ([0.0.0.0]) by whsex03.whsnv.net with Microsoft SMTPSVC(5.0.2195.6713);
Tue, 7 Dec 2004 09:04:01 -0800
Received: from ??? ([0.0.0.0]) by whsmi01 with Microsoft SMTPSVC(5.0.2195.6713);
Tue, 7 Dec 2004 09:04:05 -0800
Received: From crete.t-reks.com ([22.214.171.124]) by ??? (WebShield SMTP v4.5 MR1a);
id 1102439044109; Tue, 7 Dec 2004 09:04:04 -0800
Received: (crete.t-reks.com 90991 invoked from network) with SMTP id <1zUzSvCKUZv5LM7gaplrpwz4C8vtXHtSoIcvL>; Tue, 07 Dec 2004 09:13:30 -0800
Content-Type: multipart/alternative; boundary="_----------=_11024396103792599"
From: "Grouplotto" <BreakTheOdds@reply.savestimes.com>
Subject: Your GroupLotto cash transfer* has been approved
Date: Tue, 07 Dec 2004 09:13:30 -0800
I'd like to gather the information in the lines above in bold Red and dump them into a SQL DB where it's far easier to search for the information. I have tried every trick you mentioned in the posts started by Mac about Parsing Abnormal Log File but get
errors. Extract_Token is not even recognized. I've been trying all flavors of the -i:textline!
Any help would be appreciated. By the way I am using LP 2.0 with Windows XP, Sp2. Most of our server environment is Win2k and there are no plans to do any upgrades this budget year. Do you recommend upgrading to Parser 2.2 when it is released?
TEXTWORD and TEXTLINE input formats
Dec 07, 2004 07:29 PM|Anonymous|LINK
First off, EXTRACT_TOKEN is in 2.1, and it DOES work on W2K (it's the installer that will refuse to install to a W2K box, but you can install on any box and copy LogParser.exe to the W2K box).
Those lines CAN be parsed easily with TEXTLINE and EXTRACT_TOKEN, with something like the following:
SELECT EXTRACT_TOKEN(Text, 1, ':') WHERE Text LIKE 'From:%' OR Text LIKE 'To:%' OR ....
Dec 07, 2004 07:37 PM|Anonymous|LINK
Dec 07, 2004 10:41 PM|Anonymous|LINK
I can't see a way to do this using Log Parser exclusively. Mike and Gabriele are both right (of course), but if I'm interpreting your problem correctly, Gabriele's solution won't produce the desired effect. The syntax Gabriele suggested will yield a single
output column, which A) won't let you differentiate "To", "From", "Subject", or "Date" entries, and B) will truncate output for any lines that contain a 2nd colon (":").
What you're really trying to do is "PIVOT" the values into separate columns - i.e., one row per .EML file, with a [FROM] column, a [TO] column, a Subject column, and a [Date] column, right? This can't be done without using a correlated subquery, which
isn't supported in Log Parser 2.x. But you could use a similar approach based on the LIKE syntax Gabriele suggested and then do the correlation query within SQL Server. I'd suggest something like this:
Dec 08, 2004 02:07 PM|Anonymous|LINK
Many thanks to all for your WONDERFUL suggestions. I will work on them soon.
Gabriele, when is your Log Parser book going to be released?
Dec 09, 2004 07:32 AM|Anonymous|LINK
The book release is actually tied to the tool release, which had a slight slip.
The tool is currently under review by windows security officials, and the improvements suggested will make Log Parser a better tool, more reliable, and more secure.
There's two possible scenarios here....best case: tool releases next week; worst case: tool releases in January, in the week between the 10th and the 15th.......