Forum Discussion
chrislehrPatriot
May 21, 2020Brass Contributor
Alex_RibenI would instead use Excel or PowerBI to do the data manipulation you are looking for.
- Concatenate all the log files into one large CSV
- Import the CSV to Excel or Power BI
- If Excel, convert it to a table
- Filter the column with SMTP commands for "contains AUTHsrc=Anonymous"
- 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.
- 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
- Mister_RockMay 25, 2020Copper Contributor
chrislehrPatriot
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:%'