Jump to content
Sign in to follow this  
skysel

Parsing data from Outlook email

Recommended Posts

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:

///  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 by skysel

Share this post


Link to post
Share on other sites
water

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


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites
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...

Share this post


Link to post
Share on other sites
kylomas

skysel,

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

kylomas


Forum Rules         Procedure for posting code

"I like pigs.  Dogs look up to us.  Cats look down on us.  Pigs treat us as equals."

- Sir Winston Churchill

Share this post


Link to post
Share on other sites
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 by water

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites
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..

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this  

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.