Showing posts with label tools. Show all posts
Showing posts with label tools. Show all posts

Monday, January 19, 2015

Using SQL to Sift Through Data

Many things that I have found in the past was accomplished through trial and error, and when I find something that works I get really excited. SQL is lovely and it ranks right up there with regex as one of the need-to-know skills in anyone's tool box.

The problem: tons of log files with multiple columns and thousands of rows, and you only need two or three of those columns.

So what do you do, open every single file, select the columns you want, copy and paste the columns into a new spreadsheet? No.

The solution: SQL, or more specifically Log Parser. It already comes with many queries and scripts you can use, and it also allows you to use SQL to sift through many different types of files. Don't know SQL? No problem, it's easy and the below command will start you off in the right direction (make sure you select the correct file type next to the Log Type option):
  • SELECT hostname, ipaddress FROM '\Files\*.csv'
It may ask you to specify a folder when you attempt to run the query. SQL uses three basic commands to search for data; SELECT, FROM, and WHERE. The above command is basically asking to select the hostname and ipaddress columns from the following files (in this case the files are in the \Files folder), and returning the results.

After the query is finished the results will be displayed in the top window, which you can then export the to a new file. No more opening every single file and searching for columns; this simple tool and query can be used for many different purposes.

Further reading:

Monday, January 5, 2015

Finding Malicious Activity

Prior to going crazy with tons of network indicators, we need to know how to search through many text based log files in order to find malicious activity. How do we doing this? Regular expressions (regex) are the answer. Knowing how to write a regex query is not required (so long as you already have a query you can use), but it is certainly helpful (seriously though, learn it). A trend with this blog is that most of my work will be done on a Windows machine, unless there is no Windows alternative to a piece of software.

In order to dig through thousands of lines of text we will use a tool called grepWin. As its name implies, it is a Windows based grep tool and will allow us to dig through files using regex or text based searches. As we know, finding threats is never a one-shot deal. We may find a new indicator and we will need to dig through old logs files (again, and again, and...) to see if there has been past activity that we may have missed.

So where do we find pre-made regex queries? There are a few sites, such as; MalwareSigs, CoffeeShopSecurity, and many Snort rules. Grab those expressions, toss them into grepWin and press the search button. Too easy right? OK, grepWin is pretty straight forward. Make sure you enter the location of the files in the "Search in" field (preferably these files should be in a comma separated format). In the "Search" section, choose "Regex search" and enter the regex query into the "Search for" field; if you click the / button it will allow for multiple lines of regex which comes in very useful. Under the "Limit search" section, select "All sizes" and "Include subfolders."

Before you press that search button, you want to make sure your regex will work. The grepWin application allows you to test the query, but I prefer something a bit more visual https://regex101.com. It tests your query against a text string and will also give you an explanation of the regex query, which is great when you are learning, and it will let you know if there is a match. Let's do a quick test run on that website and use the following information:
Regular Expression: \/([a-z]{3})\?alpha=
Test String: hXXp://api.browseburst.com/gdi?alpha=0/
The regex query will search for the Mudrop infection, and the test string is/was an actual host used by Mudrop (don't go to the site). Read through the explanation on the right if you are unfamiliar with regex. Also, notice how it matched on "/gdi?alpha=" in the URI. Now that we know the regex will work, go ahead and click the search button in grepWin and, depending on how many files you have, grab a cup of coffee.

If grepWin found any hits, it will display them in the "Search results" window. On the bottom right of the application you will see "Files" and "Content." Files will show all of individual files with content that matched the query, and Content will show each individual line within those files. You can open each file by right clicking the line and selecting Open. After digging through your search results you can grab any unique indicators to create new alerts and use those indicators to dig deeper to find more malicious activity.

That's it, pretty straight forward. There are, of course, many other tools that can be used to find malicious activity but this is a good start if you are fresh. When performing these kinds of searches, always keep notes of the regex strings you have used as well as any unique indicators. If you really want to have some fun, grab about 10 through 20 regex strings and run a multi-line search on your log files. You may be surprised, or saddened, by what you find.