Parsing Report.csv to build a Whitelists with LOG-MD Free Edition & Professional - Using Excel

In order to take what looks like a large Report.csv with overwhelming events down to a manageable, useful and actionable report, the use of whitelists is highly recommended as discussed in our last blog.  In order to be productive at filtering out the "Good Junk" from your seemingly large report output, you need a way to filter and sort the data to help you group similar events together to make it simpler to build or edit your whitelists.  

Microsoft Excel is the best tool for this, yes there are other spreadsheet programs, but Excel's filter option allows for widening the filter choices box, unlike LibreOffice for example.  The Filter and Sort options in Excel will be your best friend when it comes to the LOG-MD main report - Report.csv.

Whichever whitelist you are creating or editing, selecting only the events that are relevant to the whitelist you are working on will speed you up and make your efforts more effective.  In the last blog we discussed the Command Line and Process Name whitelist "Whitelist_Log_Cmd_and_Process", so let's continue with how to begin to build a whitelist using Excel.

The first step is to filter out the events that are not relevant to the whitelist you are creating or editing.  Get to know this button under the DATA menu.

Using filtering allows you to hide or deselect items you do not want or need to see in the report. When working on a whitelist such as "Whitelist_Log_Cmd_and_Process" you only want Event ID 4688 or Event ID 1 (Sysmon).  Only LOG-MD Professional supports harvesting Sysmon logs.  Once you remove all other Event ID's then sorting is the next step to group similar events together.

Of course, do this on a clean system with all your typical applications to build your master whitelists, or use your clean lab system to baseline it.  You will be amazed how much normal noise can be filtered out once you begin the process.

First sort on the column that you are focusing on, for "Whitelist_Log_Cmd_and_Process" you would filter on Event ID 4688 and/or Event ID 1 (Sysmon) and focus first on the Process Command Line, and then Process Name last.  For "Whitelist_Log_IP_Address" you would filter on Event ID 5156 and/or Event ID 3 (Sysmon) and focus on Destination IP and then Source IP last.  For "Whitelist_Log_File_and_Registry" you would filter on Event ID 4663 (File Auditing) and Event ID 4657 (Reg Auditing) and focus on full path of the file or full registry key and value respectfully.  Once you do the Filtering and Sorting, it is just a matter of copy and pasting the cell data into the whitelist.  For example;

  • C:\Users\bob\AppData\Local\Google\Chrome\User Data\Default\Cache\

will filter everything below and including \cache, but does not filter anything located in

  • C:\Users\bob\AppData\Local\Google\Chrome\User Data\Default\

By using the Filter and Sort options on the appropriate columns, you can group similar items together and decide how granular to make the whitelist taking the most "Good Junk" out of the results.  Make a few passes at it, you do not need to get them all in the first pass, chip away at it until you are comfortable with the results.  Launch various programs you normally use or that normally execute over time to fill the logs and filter out the good so you can more easily find the bad.  

Another great tip is to bulk filter out all browser items (Chrome, FireFox, IE and Edge) as they are very noisy with cache, images, plugins and other browser data and tend to provide the least amount of value.  Yes, browsers can get compromised, but normal surfing will fill the logs with endless data with very little return.  Remember that whitelisted items are not thrown out, they are stored in the "Report_Whitelisted_out.csv" report for use later.  Browser items can always be post processed with a script to create a browser only report for your specific needs.

Use the comments option "#" in the whitelists to make the lists easier to read and separate things you filter out.  For example comments in whitelists can separate areas of files, commands or registry keys;

  • #  Browser data
  • #  User Applications
  • #  Program Files
  • #  Program Files (x86)
  • #  Windows
  • #  System32
  • #  SysWoW64
  • #  HKCU
  • #  HKLM

etc...

Give it a try and send us your comments.

Happy Hunting!