?
Solved

Convert these mails to excel

Posted on 2007-10-08
9
Medium Priority
?
1,104 Views
Last Modified: 2012-05-05
Hi,

I get such mails

User: DEVELOPMENT\Popu
Scan: On-access
Machine: DEV-CHEN-PC206

Scanning "C:\Program Files\Common Files\system\ado\msadox.dll" returned SAV Interface error 0xa0040202: Scan failed.

I get mails as such...
In my Mailbox

Machine: DEV-CHEN-PC206

Process "C:\Program Files\Common Files\system\ado\msadox.dll".

Is there a way a script can scan all mails in that folder and get me just the machinenames "DEV-CHEN-PC206" in columA
and the Process ""C:\Program Files\Common Files\system\ado\msadox.dll" exhibiting suspicious behavior pattern 'HIPS/RegMod-001"  in Colum B
So that i can sort and use this script to easier the task...
Any way...
If required i can save all msg file in a folder in D:\foldername

Regards
SHarath
0
Comment
Question by:bsharath
  • 5
  • 4
9 Comments
 
LVL 17

Expert Comment

by:NicksonKoh
ID: 20038736
Please provide a clear example of the desired result in excel.

This sentence is confusing

"and the Process ""C:\Program Files\Common Files\system\ado\msadox.dll" exhibiting suspicious behavior pattern 'HIPS/RegMod-001"  in Colum B"
0
 
LVL 11

Author Comment

by:bsharath
ID: 20038767
Hi,

I have saved a mail that i get

From:      Sophos@plc.com on behalf of Sophos Anti-Virus
[sav@ACCAPP.Develop.Group.co.uk]
Sent:      Monday, October 08, 2007 9:30 PM
To:      Sharath Reddy
Subject:      SAV message from: ACCAPP

User: NT AUTHORITY\SYSTEM
Scan: Daily
Machine: ACCAPP

File "C:\WINDOWS\system32\DWRCS.EXE" has been identified as suspicious file of
type 'Sus/Behav-1014'.
      Please send a sample to Sophos.

The attempt to delete the infected file "C:\WINDOWS\system32\DWRCS.EXE"
failed. The user does not have the rights to perform the action on the
infected file.

I need headers in excel for

Sent                                                                Machine                File          

Monday, October 08, 2007 9:30 PM                ACCAPP             "C:\WINDOWS\system32\DWRCS.EXE"

0
 
LVL 17

Expert Comment

by:NicksonKoh
ID: 20038850
Hi,

Using the similar technique as previous answer, save the mail to sample.txt, add the references and use the below code.

Sub Main()

Dim sFilename As String, sFileText As String
sFilename = "c:\temp\sample.txt"
sFileText = ReadFile(sFilename)

Dim re As RegExp
Dim matches As MatchCollection
Dim m As Match
Dim n As Integer  'Counter
Dim sht As Worksheet

Set sht = Application.ThisWorkbook.Worksheets("Sheet1")

Set re = New RegExp
re.IgnoreCase = False
re.Global = True
 
re.Pattern = "Sent:\s*([^\r\n]+)?[\r\n]+([\s\S]*)?"
re.Pattern = re.Pattern & "Machine:\s*([^\r\n]+)?[\r\n]+([\s\S]*)?"
re.Pattern = re.Pattern & "(File ""(.+)?"")"
Debug.Print (re.Pattern)

Set matches = re.Execute(sFileText)
'Debug.Print (matches.Count)

n = 2
For Each m In matches
    'Write the Data
    sht.Cells(n, 1).Value = m.SubMatches(0)
    sht.Cells(n, 2).Value = m.SubMatches(2)
    sht.Cells(n, 3).Value = m.SubMatches(5)
   
    n = n + 1
Next

End Sub

Function ReadFile(ByVal filename As String) As String

  Dim objFSO As Scripting.FileSystemObject
  Set objFSO = New Scripting.FileSystemObject
  If objFSO.FileExists(filename) Then
    Set objTStream = objFSO.OpenTextFile(filename, ForReading)
    strText = objTStream.ReadAll
    Set objTStream = Nothing
    Set objFSO = Nothing
   
    ReadFile = strText
    End If
       
End Function

0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 11

Author Comment

by:bsharath
ID: 20038930
Hi this works fine if there is only 1 entry .I just put about 20 mails data into the txt file and it starts hanging .I even left it run for 15 min and still no output...
0
 
LVL 17

Expert Comment

by:NicksonKoh
ID: 20038943
Can paste the txt file here. 5-10 mails should be alright.
0
 
LVL 11

Author Comment

by:bsharath
ID: 20038972
Here the data is.


From:      sophos@plc.com on behalf of Sophos Anti-Virus [sav@ACCAPP.deve.Group.co]
Sent:      Monday, October 08, 2007 9:30 PM
To:      Sharath
Subject:      SAV message from: ACCAPP

User: NT AUTHORITY\SYSTEM
Scan: Daily
Machine: ACCAPP

File "C:\WINDOWS\system32\DWRCS.EXE" has been identified as suspicious file of type 'Sus/Behav-1014'.
      Please send a sample to Sophos.

The attempt to delete the infected file "C:\WINDOWS\system32\DWRCS.EXE" failed. The user does not have the rights to perform the action on the infected file.



From:      sophos@plc.com on behalf of Sophos Anti-Virus [sav@ACCAPP.deve.Group.co]
Sent:      Sunday, October 07, 2007 9:30 PM
To:      Sharath
Subject:      SAV message from: ACCAPP

User: NT AUTHORITY\SYSTEM
Scan: Daily
Machine: ACCAPP

File "C:\WINDOWS\system32\DWRCS.EXE" has been identified as suspicious file of type 'Sus/Behav-1014'.
      Please send a sample to Sophos.

The attempt to delete the infected file "C:\WINDOWS\system32\DWRCS.EXE" failed. The user does not have the rights to perform the action on the infected file.



From:      sophos@plc.com on behalf of Sophos Anti-Virus [sav@ACCAPP.deve.Group.co]
Sent:      Saturday, October 06, 2007 9:30 PM
To:      Sharath
Subject:      SAV message from: ACCAPP

User: NT AUTHORITY\SYSTEM
Scan: Daily
Machine: ACCAPP

File "C:\WINDOWS\system32\DWRCS.EXE" has been identified as suspicious file of type 'Sus/Behav-1014'.
      Please send a sample to Sophos.

The attempt to delete the infected file "C:\WINDOWS\system32\DWRCS.EXE" failed. The user does not have the rights to perform the action on the infected file.



From:      sophos@plc.com on behalf of Sophos Anti-Virus [sav@ACCAPP.deve.Group.co]
Sent:      Friday, October 05, 2007 9:30 PM
To:      Sharath
Subject:      SAV message from: ACCAPP

User: NT AUTHORITY\SYSTEM
Scan: Daily
Machine: ACCAPP

File "C:\WINDOWS\system32\DWRCS.EXE" has been identified as suspicious file of type 'Sus/Behav-1014'.
      Please send a sample to Sophos.

The attempt to delete the infected file "C:\WINDOWS\system32\DWRCS.EXE" failed. The user does not have the rights to perform the action on the infected file.



From:      sophos@plc.com on behalf of Sophos Anti-Virus [sav@ACCAPP.deve.Group.co]
Sent:      Thursday, October 04, 2007 9:30 PM
To:      Sharath
Subject:      SAV message from: ACCAPP

User: NT AUTHORITY\SYSTEM
Scan: Daily
Machine: ACCAPP

File "C:\WINDOWS\system32\DWRCS.EXE" has been identified as suspicious file of type 'Sus/Behav-1014'.
      Please send a sample to Sophos.

The attempt to delete the infected file "C:\WINDOWS\system32\DWRCS.EXE" failed. The user does not have the rights to perform the action on the infected file.



From:      sophos@plc.com on behalf of Sophos Anti-Virus [sav@ACCAPP.deve.Group.co]
Sent:      Wednesday, October 03, 2007 9:31 PM
To:      Sharath
Subject:      SAV message from: ACCAPP

User: NT AUTHORITY\SYSTEM
Scan: Daily
Machine: ACCAPP

Scanning "C:\WINDOWS\SoftwareDistribution\EventCache\{1EC6AA57-D272-4A8D-9B55-AEA2249D32CB}.bin" returned SAV Interface error 0xa0040210: The file could not be accessed.

File "C:\WINDOWS\system32\DWRCS.EXE" has been identified as suspicious file of type 'Sus/Behav-1014'.
      Please send a sample to Sophos.

The attempt to delete the infected file "C:\WINDOWS\system32\DWRCS.EXE" failed. The user does not have the rights to perform the action on the infected file.



From:      sophos@plc.com on behalf of Sophos Anti-Virus [sav@ACCAPP.deve.Group.co]
Sent:      Tuesday, October 02, 2007 9:30 PM
To:      Sharath
Subject:      SAV message from: ACCAPP

User: NT AUTHORITY\SYSTEM
Scan: Daily
Machine: ACCAPP

File "C:\WINDOWS\system32\DWRCS.EXE" has been identified as suspicious file of type 'Sus/Behav-1014'.
      Please send a sample to Sophos.

The attempt to delete the infected file "C:\WINDOWS\system32\DWRCS.EXE" failed. The user does not have the rights to perform the action on the infected file.


For this data i just get this in excel.

Monday, October 08, 2007 9:30 PM      ACCAPP      C:\WINDOWS\system32\DWRCS.EXE

If i put the same data say 10 times more it hangs
0
 
LVL 17

Accepted Solution

by:
NicksonKoh earned 2000 total points
ID: 20039108
Hi,

Here u go.


Sub Main()

Dim sFilename As String, sFileText As String
sFilename = "c:\temp\sample.txt"
sFileText = ReadFile(sFilename)

Dim re As RegExp
Dim matches As MatchCollection
Dim matchesMail As MatchCollection
Dim m As Match
Dim mMail As Match
Dim n As Integer  'Counter
Dim sht As Worksheet

Set sht = Application.ThisWorkbook.Worksheets("Sheet1")

Set re = New RegExp
re.IgnoreCase = False
re.Global = True

'Add ~~~ delimiter to each mail section
re.Pattern = "([\r\n]+From:)"
sFileText = re.Replace(sFileText, vbCr & "~~~$1")
sFileText = sFileText & "~~~"

'Start Row for writing result
n = 2

'Split the Mail Section
re.Pattern = "From:[\s\S]+?~~~"
Set matchesMail = re.Execute(sFileText)

'Expression for retrieving the content in each mail
re.Pattern = "Sent:\s*([^\r\n]+)?[\r\n]+([\s\S]*)?"
re.Pattern = re.Pattern & "Machine:\s*([^\r\n]+)?[\r\n]+([\s\S]*)?"
re.Pattern = re.Pattern & "(File ""(.+)?"")"

'Process mail by mail
For Each mMail In matchesMail
   
    Set matches = re.Execute(mMail)
    'Debug.Print (matches.Count)
   
    For Each m In matches
        'Write the Data
        sht.Cells(n, 1).Value = m.SubMatches(0)
        sht.Cells(n, 2).Value = m.SubMatches(2)
        sht.Cells(n, 3).Value = m.SubMatches(5)
       
        n = n + 1
    Next
Next

End Sub

Function ReadFile(ByVal filename As String) As String

  Dim objFSO As Scripting.FileSystemObject
  Set objFSO = New Scripting.FileSystemObject
  If objFSO.FileExists(filename) Then
    Set objTStream = objFSO.OpenTextFile(filename, ForReading)
    strText = objTStream.ReadAll
    Set objTStream = Nothing
    Set objFSO = Nothing
   
    ReadFile = strText
    End If
       
End Function

0
 
LVL 11

Author Comment

by:bsharath
ID: 20039155
Thanks a lot for the wounderful help..


0
 
LVL 11

Author Comment

by:bsharath
ID: 20039177
Hi wanted another macro which can convert all mails in specified folder in outlook to excel

Sendername    Sent to    Date   Time    Subject   body

If there is a way i can post a new Q...Please let me know...

I tried exporting outlook folder to pst but dont like the way i get the data.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

807 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question