Tips on How To Detect Encrypted Files (or The Battle against Ransomware)

The Problem

cryptolocker

Even with multiple lines of defense in place, someone in your network is inevitably going to click on that attachment or link prompting them to open that scan or document they requested (if they would briefly stop and think about it, they actually did not scan or request any document) and the next thing you know every document on the shared drive is encrypted. Welcome to the wonderful world of ransomware!

The only real solution to this problem up until now once the damage has been done is to have a good backup (and/or Volume Shadow Copy). Although I have heard stories that you can actually pay the ransom fee and you will get your files back that may work for home users, but I doubt very much that an organization with hundreds of thousands of files infected is going to get off the hook for $500.

So for now restoring from the latest backup seems to be the best line of defense against ransomware, I do however anticipate a future where things will become much harder to protect against. For example, what if a crafty hacker was able to detect documents that are important, but are only opened once a year for review?

What can you do?

  1. For Windows workstations turn on System Protection and also make sure users either do not store documents locally (that is what I recommend) or have a backup in place.
  2. From servers: As I mentioned above, make sure you have backups and/or Volume Shadow Copy turned on.
  3. Read this
  4. This Scan Tool should be useful to identify encrypted files (I have never used it, though).
  5. Use Powershell to test files for Crypto. Typically it is easy to find out who is infected by checking the owner of an encrypted file and also recording the time when the file was last modified. The script below can be modified accordingly to scan through your shared folders and find what has been encrypted. Edit the file, then Dot-Source the file to your Powershell session (http://mikepfeiffer.net/2010/06/how-to-add-functions-to-your-powershell-session/) then run it by using:
    Get-Postcrypto Path
    Example Get-Postcrypto “E:\shared\Company”

 

HOW TO Upload a .csv file to a Sharepoint 2013 List

I recently had to write a simple Powershell script to upload Work Order data from a Maintenance Department stored in .csv format to a Sharepoint 2013 list (We wrote a cool Filemaker Go app to record the Work Orders on iPads). The script first deletes the contents of the list before uploading (Function: Delete_List). What I do not like about this approach is that the ID continues to increment and does not reset to 1. I am still looking for a solution for that.

I struggled a little bit with the DateTime formatting because I was getting an error “specified cast is not valid” but converting the date field to String solved the problem (see below).

Using SQL CASE and GROUP BY in the Same SELECT Statement

According to Wikipedia SQL has been around for a loooong time, since 1970 to be precise and it shows in the syntax. However, due to the fact that it is the most effective way to retrieve information from a relational database you cannot get around knowing the ins-and-outs of this complicated language.

Fortunately though, you can get a lot of help out there on the Internet when it comes to building complicated queries. Without sites like Stackoverflow and W3Schools I would be hopelessly lost even though I have been building SQL queries for years now. There is always something new to learn.

RTagAnd just recently I was faced with a new challenge: How to use the SQL CASE statement and GROUP BY function in one query. The requirement was to create a report that aggregated sales and cost of goods sold in an Epicor ERP System with a Microsoft SQL Server database back end. I am using the powerful reporting tool R-Tag to build Pivot Tables and Pivot Graphs to generate monthly trend reports for a Client’s sales team.

Google research lead me to some good articles that lead me on the right track:

Breakdown of this SELECT statement (note that the syntax may be specific to R-Tag and will need adjusting for other development environments such as Crystal):

I want to show sales (“extended_price”) and cost of goods sold (“cogs_amount”) for the selected period (“PeriodFromDate” to “PeriodToDate”) and sales engineer (“SalesEngineer”) aggregated into two categories:

  • SalesCategory1 if “product_group_id” is equal to  ‘GROUP1’, ‘GROUP2’, ‘GROUPX’ or ‘MISC’
  • SalesCategory2 otherwise

There are three parameters to determine the start and end dates for the report and the assigned sales engineer:

  • {@PeriodFromDate}
  • {@PeriodToDate}
  •  {@SalesEngineer}

 

Simple Powershell Script to Find Files in a Folder of a Certain Size