Parsing exchange logs

%3CLINGO-SUB%20id%3D%22lingo-sub-1443325%22%20slang%3D%22en-US%22%3EParsing%20exchange%20logs%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1443325%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20I'm%20trying%20to%20parse%20exchange%20logs%20with%20log%20parser.%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FP%3E%3CP%3EHere%20I%20need%20to%20add%2C%20FROM%20and%20TO%20mail%20address.%20(RCPT%20To%20and%26nbsp%3BMAIL%20From)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-powershell%22%3E%3CCODE%3E.%2FLogParser.exe%20%22SELECT%20EXTRACT_PREFIX(remote-endpoint%2C0%2C'%3A')%20as%20IP%2CREVERSEDNS(EXTRACT_PREFIX(remote-endpoint%2C0%2C'%3A'))%20as%20Name%2C%20COUNT(*)%20AS%20Hits%2C%20TO_LOCALTIME(TO_TIMESTAMP(EXTRACT_PREFIX(TO_STRING(%5B%23Fields%3A%20date-time%5D)%2C0%2C'T')%2C%20'yyyy-MM-dd'))%20AS%20LogDate%20from%20'C%3A%5CProgram%20Files%5CMicrosoft%5CExchange%20Server%5CV14%5CTransportRoles%5CLogs%5CProtocolLog%5CSmtpReceive%5C*.log'%20WHERE%20data%20LIKE%20'%25EHLO%25'%20GROUP%20BY%20LogDate%2CIP%20ORDER%20BY%20Hits%20DESC%22%20-i%3ACSV%20-nSkipLines%3A4%20-O%3ACSV%20%26gt%3B%26gt%3B%20c%3A%5Ctemp%5CReceiveConnectorMailFlow.csv%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ELog%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-basic%22%3E%3CCODE%3E%23Fields%3A%20date-time%2Cconnector-id%2Csession-id%2Csequence-number%2Clocal-endpoint%2Cremote-endpoint%2Cevent%2Cdata%2Ccontext%0A2020-03-10T09%3A39%3A04.347Z%2CX%5CInbound%20SMTP%20relay%2C08D79C80806B8DD4%2C2%2C10.193.100.165%3A25%2C10.193.100.4%3A20706%2C%26gt%3B%2C%22220%20sxx%20Microsoft%20ESMTP%20MAIL%20Service%20ready%20at%20Tue%2C%2010%20Mar%202020%2010%3A39%3A04%20%2B0100%22%2C%0A2020-03-10T09%3A39%3A04.363Z%2CX%5CInbound%20SMTP%20relay%2C08D79C80806B8DD4%2C3%2C10.193.100.165%3A25%2C10.193.100.4%3A20706%2C%26lt%3B%2CEHLO%20%0A2020-03-10T09%3A39%3A04.363Z%2CX%5CInbound%20SMTP%20relay%2C08D79C80806B8DD4%2C4%2C10.193.100.165%3A25%2C10.193.100.4%3A20706%2C%26gt%3B%2C%0A2020-03-10T09%3A39%3A04.378Z%2CX%5CInbound%20SMTP%20relay%2C08D79C80806B8DD4%2C43%2C10.193.100.165%3A25%2C10.193.100.4%3A20706%2C%26lt%3B%2CMAIL%20From%3A%3CXX%3E%20SIZE%3D12124%2C%0A2020-03-10T09%3A39%3A04.378Z%2CX%5CInbound%20SMTP%20relay%2C08D79C80806B8DD4%2C46%2C10.193.100.165%3A25%2C10.193.100.4%3A20706%2C%26lt%3B%2CRCPT%20To%3A%3CXX%3E%20NOTIFY%3DNEVER%2C%3C%2FXX%3E%3C%2FXX%3E%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%3CBR%20%2F%3EThanks%3C%2FP%3E%3CP%3ETom%C3%A1s%20Esteban%20Corey%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1443325%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExchange%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExchange%20Server%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Occasional Contributor

Hello,

          I'm trying to parse exchange logs with log parser.

Here I need to add, FROM and TO mail address. (RCPT To and MAIL From)

 

./LogParser.exe "SELECT EXTRACT_PREFIX(remote-endpoint,0,':') as IP,REVERSEDNS(EXTRACT_PREFIX(remote-endpoint,0,':')) as Name, COUNT(*) AS Hits, TO_LOCALTIME(TO_TIMESTAMP(EXTRACT_PREFIX(TO_STRING([#Fields: date-time]),0,'T'), 'yyyy-MM-dd')) AS LogDate from 'C:\Program Files\Microsoft\Exchange Server\V14\TransportRoles\Logs\ProtocolLog\SmtpReceive\*.log' WHERE data LIKE '%EHLO%' GROUP BY LogDate,IP ORDER BY Hits DESC" -i:CSV -nSkipLines:4 -O:CSV >> c:\temp\ReceiveConnectorMailFlow.csv

 

 

Log

#Fields: date-time,connector-id,session-id,sequence-number,local-endpoint,remote-endpoint,event,data,context
2020-03-10T09:39:04.347Z,X\Inbound SMTP relay,08D79C80806B8DD4,2,10.193.100.165:25,10.193.100.4:20706,>,"220 sxx Microsoft ESMTP MAIL Service ready at Tue, 10 Mar 2020 10:39:04 +0100",
2020-03-10T09:39:04.363Z,X\Inbound SMTP relay,08D79C80806B8DD4,3,10.193.100.165:25,10.193.100.4:20706,<,EHLO 
2020-03-10T09:39:04.363Z,X\Inbound SMTP relay,08D79C80806B8DD4,4,10.193.100.165:25,10.193.100.4:20706,>,
2020-03-10T09:39:04.378Z,X\Inbound SMTP relay,08D79C80806B8DD4,43,10.193.100.165:25,10.193.100.4:20706,<,MAIL From:<xx@xx.com> SIZE=12124,
2020-03-10T09:39:04.378Z,X\Inbound SMTP relay,08D79C80806B8DD4,46,10.193.100.165:25,10.193.100.4:20706,<,RCPT To:<xx@xx.com> NOTIFY=NEVER,


Thanks

Tomás Esteban Corey

0 Replies