Parsing data from Outlook email

[skysel]

So the situation is as following:

- The kind of email I get is report (daily statistics)

- I want to extract relevant data into DB or Excel, so later I can generate monthly/yearly report based on it.

- I would like extracted table Breakdown of Syslog messages by severity and Kiwi Syslog Server Statistics, I would let Breakdown by sending host out of parsing.

- I am aware of Outlook UDF and ability to read email body with it

The trouble is, I am unsure how to parse data below to suit my needs / what kind of approach to use for that. My guess would be to first read date from the 3rd line, so we know for what day statistics apply. Secondly, I would go with matching text i.e. "Messages received - Total:" and match it with Excel row. Table would be designed:

- 1st column Messages received total, messages received - last 24 hours, etc...

- 2nd column would be "Date", 2nd row of column 2 would be the numbers according to below..

That's how much I figured that it should be. Any more pointers / examples would be highly appreciated.

The body of email appears like this:

expandcollapse popup

///  Kiwi Syslog Server Statistics       ///
---------------------------------------------------
24 hour period ending on: Mon, 25 Mar 2013 00:00:02 Syslog Server started on: Thu, 07 Mar 2013 07:48:35
Syslog Server uptime:    17 days, 16 hours, 10 minutes
---------------------------------------------------
+ Messages received - Total:         5199162
+ Messages received - Last 24 hours: 246856 Messages received - Since
+ Midnight: 244632
+ Messages received - Last hour:     10015
+ Message queue overflow - Last hour: 0
+ Messages received - This hour:     1900
+ Message queue overflow - This hour: 0
+ Messages per hour - Average:   10207
+ Messages forwarded:                0
+ Messages logged to disk:       244760
+ Errors - Logging to disk:      0
+ Errors - Invalid priority tag:     0
+ Errors - No priority tag:      0
+ Errors - Oversize message:         91096
+ Disk space remaining on drive E: 31727 MB
---------------------------------------------------

     Breakdown of Syslog messages by sending host
+--------------------------+------------+------------+
| Top 20 Hosts           | Messages | Percentage |
+--------------------------+------------+------------+
| server1.domain.local|  79238 |     32,39% |
| server2.domain.local|  60212 |     24,61% |
| server3.domain.local|  18084 |     7,39% |
| server4.domain.local|  17341 |     7,09% |
| 10.10.10.1             |   12223 |     5,00% |
| 10.10.10.2             |   11869 |     4,85% |
| server5.domain.local|  8093 |  3,31% |
| server6.domain.local|  4945 |  2,02% |
| server7.domain.local|  4001 |  1,64% |
| server8.domain.local|  3952 |  1,62% |
| server9.domain.local|  3872 |  1,58% |
| server10.domain.local|     3520 |  1,44% |
| server11.domain.local|     3413 |  1,40% |
| server12.domain.local|     2936 |  1,20% |
| server13.domain.local|     2908 |  1,19% |
| server14.domain.local|     2737 |  1,12% |
| server15.domain.local|     1777 |  0,73% |
| server16.domain.local|     1164 |  0,48% |
| server17.domain.local|     1085 |  0,44% |
| server18.domain.local|     1007 |  0,41% |
| All others (4)         |   255 |   0,10% |
+--------------------------+------------+------------+

Breakdown of Syslog messages by severity
+--------------------+------------+------------+
| Message Level  | Messages | Percentage |
+--------------------+------------+------------+
| 0 - Emerg      |       0 |     0,00% |
| 1 - Alert      |       0 |     0,00% |
| 2 - Critical   |   208 |   0,09% |
| 3 - Error      |   198 |   0,08% |
| 4 - Warning    |   697 |   0,28% |
| 5 - Notice         | 216086 |  88,33% |
| 6 - Info       |   27443 |     11,22% |
| 7 - Debug      |       0 |     0,00% |
+--------------------+------------+------------+

Edited March 25, 2013 by skysel

[water]

Doesn't the Kiwi Syslog server provide some kind of API to access this information without processing emails?

[skysel]

Doesn't the Kiwi Syslog server provide some kind of API to access this information without processing emails?

I've contacted their technical support if they have any solution for monthly / yearly reports, but they answered that there isn't an option yet and that they might add it in future releases. I checked out example scripts on it, but figured it would be much less hassle doing it in AutoIT than in Syslog server. Documentation and website are poorly constructed on their side...

[kylomas]

skysel,

Is there any way to directly access the syslog, api or otherwise? (as water asked)...

kylomas

[water]

I would do something like this.

You have 3 blocks of data. Each one can be identified by some kind of "starting line":

1) "24 hour period ending on:"

2) "Breakdown of Syslog messages by sending host"

3) "Breakdown of Syslog messages by severity"

Take the mail body and assign it to an array.

Define a Global variable that defines the currently processed position in the array ($i).

Loop through the array until one of the header lines is being found. If header line for block 1 is found call _process1.

This function processes all lines till the endline "---------------------------------------------------" is found. The function

increments $i as well.

Then return to the main loop and process the next section.

$aLines = ... ; Assign the lines of the mail to an array
$iSection = 1
For $i = 1 to $aLines[0]
    Switch $iSection
        Case 1
            If StringLeft($aLines[$i],25) = "24 hour period ending on:" Then 
                _Process1()
                $iSection = 2
            EndIf
        Case 2
            If StringStripWS($aLines[$i], 3) = "Breakdown of Syslog messages by sending host" Then
                _Process2()
                $iSection = 3
            EndIf
        Case 2
            If StringStripWS($aLines[$i], 3) = "Breakdown of Syslog messages by severity" Then
                _Process3()
            EndIf
    EndSwitch
Next

Func _Process1()
    ; Process all lines from
    ;   24 hour period ending on: Mon, 25 Mar 2013 00:00:02 Syslog Server started on: Thu, 07 Mar 2013 07:48:35
    ; To
    ;   ---------------------------------------------------
    ; and increment $i
EndFunc

Edited March 25, 2013 by water

[skysel]

Thank you @water, I will look into it today/tomorrow and come back if I'll have more questions (which I'm sure I'll have :-)). @kylomas, I googled for Kiwi syslog API, but it throws me to the CatTools page which is not the same product as the syslog. And as I stated previous, their website and documentation is really badly scattered..