We are excited to announce that the IIS.NET Forums are moving to the new Microsoft Q&A experience. Learn more >

Another Abnormal File FormatRSS

5 replies

Last post Dec 09, 2004 07:32 AM by Anonymous

  • Another Abnormal File Format

    Dec 07, 2004 02:46 PM|Anonymous|LINK

    Hi,

    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 ([63.251.59.24]) 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-Transfer-Encoding: binary
    Content-Type: multipart/alternative; boundary="_----------=_11024396103792599"
    MIME-Version: 1.0
    From: "Grouplotto" <BreakTheOdds@reply.savestimes.com>
    To: jrosales2@washoehealth.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?

    Thank you.

    Cabby

    TEXTWORD and TEXTLINE input formats

  • Re: Another Abnormal File Format

    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 ....

    TEXTWORD and TEXTLINE input formats

  • Re: Another Abnormal File Format

    Dec 07, 2004 07:37 PM|Anonymous|LINK

    Even if you don't have another box handy, you can get 2.1 installed on a W2K box. See http://logparser.com/InstantKB/article.aspx?id=10000 .

    TEXTWORD and TEXTLINE input formats

  • Re: Another Abnormal File Format

    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:

    1. Eexecute the following command (on a single line):

      LogParser "SELECT LogFileName, Text INTO [myTable] FROM *.eml WHERE Text LIKE 'From:%' OR Text LIKE 'To:%' OR Text LIKE 'Subject:%' OR Text LIKE 'Date:%'" -i:TEXTLINE -o:SQL -oConnString:[your connection string]
    2. Execute the following T-SQL query to pivot the results into a SQL view with distinct "per EML file" rows:

      CREATE VIEW EML
        AS
      SELECT LogFilename,
         (SELECT (RIGHT(Text, LEN(Text) - LEN('FROM:')-1))
          FROM myTable
          WHERE myTable.LogfileName = T.LogFilename and myTable.TEXT LIKE 'From%') AS [FromField],
         (SELECT (RIGHT(Text, LEN(Text) - LEN('To:')-1))
          FROM myTable
          WHERE myTable.LogfileName = T.LogFilename and myTable.TEXT LIKE 'To%') AS [ToField],
         (SELECT (RIGHT(Text, LEN(Text) - LEN('Subject:')-1))
          FROM myTable
          WHERE myTable.LogfileName = T.LogFilename and myTable.TEXT LIKE 'Subject%') AS [Subject],
         (SELECT CONVERT(datetime,SUBSTRING(Text,LEN('Date: MMM,')+2, LEN(Text) - LEN('Date: MMM,') - 7), 113) 
          FROM myTable
          WHERE myTable.LogfileName = T.LogFilename and myTable.TEXT LIKE 'Date%') AS [DateField],
         (SELECT CAST(SUBSTRING(Text,LEN(TEXT)-4,3) as INT) 
          FROM myTable
          WHERE myTable.LogfileName = T.LogFilename and myTable.TEXT LIKE 'Date%') AS [TimeZone]
      FROM myTable T
      GROUP BY LogFilename
    3. Query against the EML view, rather than the table

    TEXTWORD and TEXTLINE input formats

  • Re: Another Abnormal File Format

    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?

     

    Again, thanks.

    Cabby

    TEXTWORD and TEXTLINE input formats

  • Re: Another Abnormal File Format

    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.......

    TEXTWORD and TEXTLINE input formats