Parse logs from Send Connector to collect Anonymous senders

[Alex_Riben]

Hi,

I'm trying to restrict usage of open relay for anonymous users in our company. And I need to figure out who exactly uses it, so I turned on logging, but I don't know how to parse it. In the logs I'm getting something like that:

2020-01-10T01:01:01.111X,Inbound Proxy Internal Send Connector,01C1Z111DD1X11Z1,212,192.168.1.1:21111,192.168.1.2:5565,*,,Proxying inbound session with session id 01C1Z111DD1X11Z1
2020-01-10T01:01:01.111X,Inbound Proxy Internal Send Connector,01C1Z111DD1X11Z1,213,192.168.1.1:21111,192.168.1.2:5565,>,RSET,
2020-01-10T01:01:01.112X,Inbound Proxy Internal Send Connector,01C1Z111DD1X11Z1,214,192.168.1.1:21111,192.168.1.2:5565,<,250 2.0.0 Resetting,
2020-01-10T01:01:01.112X,Inbound Proxy Internal Send Connector,01C1Z111DD1X11Z1,215,192.168.1.1:21111,192.168.1.2:5565,>,XPROXYFROM SID=08D7F721DC0D9A14 IP=215,192.168.1.1 PORT=21111 DOMAIN=CONTOSO.COM SEQNUM=1 PERMS=1077 AUTHsrc=Anonymous,
2020-01-10T01:01:01.113X,Inbound Proxy Internal Send Connector,01C1Z111DD1X11Z1,216,192.168.1.1:21111,192.168.1.2:5565,<,250 XProxyFrom accepted,
2020-01-10T01:01:01.113X,Inbound Proxy Internal Send Connector,01C1Z111DD1X11Z1,217,192.168.1.1:21111,192.168.1.2:5565,*,,sending message with RecordId 151516 and InternetMessageId <j6hd87fh-55h6-66h6-5g55-k9dj47gk704z@VM1203102312.contoso.com>
2020-01-10T01:01:01.113X,Inbound Proxy Internal Send Connector,01C1Z111DD1X11Z1,218,192.168.1.1:21111,192.168.1.2:5565,>,MAIL FROM:<test@contoso.com> SIZE=0 AUTH=<> XMESSAGEVALUE=MediumHigh,
2020-01-10T01:01:01.113X,Inbound Proxy Internal Send Connector,01C1Z111DD1X11Z1,219,192.168.1.1:21111,192.168.1.2:5565,>,RCPT TO:<receive@contoso.com>,
2020-01-10T01:01:01.115X,Inbound Proxy Internal Send Connector,01C1Z111DD1X11Z1,210,192.168.1.1:21111,192.168.1.2:5565,<,250 2.1.0 Sender OK,
2020-01-10T01:01:01.115X,Inbound Proxy Internal Send Connector,01C1Z111DD1X11Z1,211,192.168.1.1:21111,192.168.1.2:5565,<,250 2.1.5 Recipient OK,

I'm hunting for "AUTHsrc=Anonymous" records. It's IP, FROM and TO values. But the amount of records and logs are huge.

How do I parse it? I installed Log Parser Studio, but can't wrap my head around it.

[#2]

@Alex_RibenI would instead use Excel or PowerBI to do the data manipulation you are looking for.

1. Concatenate all the log files into one large CSV
2. Import the CSV to Excel or Power BI
3. If Excel, convert it to a table
4. Filter the column with SMTP commands for "contains AUTHsrc=Anonymous"
5. Then the Remote IP address will be an IP:Port combination, I would use the text to columns feature on that column, and use colon (:) as a delimiter.
6. Then use an Excel Pivot table on remote IP ranges and your end result would have the IPs that are sending. 

Now, the only thing you needed that this doesn't get you is the To and From addresses, which is a bit harder since its a few lines down in the log from the authentication, but now that you did the text to columns above, you can then sort/filter by the sending IP and filter for the "Mail From" and "Rcpt To" lines.

Hope this helps.

Chris

[#3]

@Chris Lehr 
I used the following query (below) and then moved ti XLS to parse it. Worked perfectly.

Thanks for the help.

SELECT session-id,data FROM '[LOGFILEPATH]'
WHERE data LIKE '%IP=%' OR data LIKE 'MAIL FROM:%' OR data LIKE 'RCPT TO:%'