Convert these mails to excel

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
LVL 11
bsharathAsked:
Who is Participating?
 
NicksonKohConnect With a Mentor Commented:
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
 
NicksonKohCommented:
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
 
bsharathAuthor Commented:
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
Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

 
NicksonKohCommented:
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
 
bsharathAuthor Commented:
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
 
NicksonKohCommented:
Can paste the txt file here. 5-10 mails should be alright.
0
 
bsharathAuthor Commented:
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
 
bsharathAuthor Commented:
Thanks a lot for the wounderful help..


0
 
bsharathAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.