Security: October 2009 Archives

Working with Bluecoat files in the raw can be time-consuming. Findstr and grep only work so fast. Windows grep is slow. I know SQL syntax OK, so I tend to dump logfiles into databases to analyze them for activity. There are certainly other ways to do it, such as using a reporting tool for Bluecoat. (Splunk's free Bluecoat application, e.g.).

Theoretically, Bluecoat logfiles are the same as W3C web server log files that logparser can consume via the -i:W3C directive.

You can see the fields in a Bluecoat log below.

#Fields: date time time-taken c-ip cs-username cs-auth-group x-exception-id sc-filter-result cs-categories cs(Referer) sc-status s-action cs-method rs(Content-Type) cs-uri-scheme cs-host cs-uri-port cs-uri-path cs-uri-query cs-uri-extension cs(User-Agent) s-ip sc-bytes cs-bytes x-virus-id

For some reason, Bluecoat leaves two spaces between cs(Referrer) and sc-Status, so all the columns to the right of sc(Referrer) past that will be one off. BlueCoat also leaves spaces in cs-categories and surrounds them with quotation marks, so you need to specify -dQuotes:on. Logparser doesn't have a quick and easy way to handle the double-spaces issue, so I wrote a VB Script to handle it. (VBScript is pretty quick at text handling and it's much faster than using search and replace in WordPad or Notepad on a 500-1000 MB File.)

Here's the VBScript:
'start

Set objFSO = CreateObject("Scripting.FileSystemObject")
'change this line to wherever you want to read the input from.
Set objTextFile = objFSO.OpenTextFile("c:\myBluecoatlog.log",1)
Set objNewFile = objFSO.CreateTextFile("c:\myCleanBlueCoatlog.log")
Do Until objTextFile.AtEndOfStream

myString = objTextFile.Readline
objNewFile.WriteLine(Replace (myString, " ", " "))
Loop
'end vbscript
Here's the logparser file:
-------------------start
SELECT TO_LOCALTIME(TO_TIMESTAMP(date, time)) AS date,
time-taken,
c-ip,
cs-username,
cs-auth-group,
x-exception-id,
sc-filter-result,
cs-categories,
cs(Referer) AS Referer,
sc-status AS scStatus,
s-action,
cs-method,
rs(Content-Type) AS ContentType,
cs-uri-scheme,
cs-host,
cs-uri-port,
cs-uri-path,
cs-uri-query,
cs-uri-extension,
cs(User-Agent) AS UserAgent,
s-ip,
sc-bytes,
cs-bytes,
x-virus-id

INTO BlueCoat4
FROM c:\myCleanBlueCoatlog.log
------------------end
And here's the command line for logparser. (Save the logparser file as c:\scripts\log\bluecoat.sql)

logparser file:c:\scripts\log\bluecoat.sql -i:W3C -o:SQL -server:sqlservername -database:BLUECOAT -createtable:ON -dQuotes:ON

Statistics:
-----------

Elements processed: 613076
Elements output: 613076
Execution time: 241.20 seconds (00:04:1.20)
About 2500 lines/sec. Processor utilization is almost zero for SQL and logparser, so it's all about disk time.

The above is from a file that's 310,935,417 bytes large. That means BlueCoat logs are about 507 bytes per line, or 0.5k per line before compression. The last time I checked BlueCoat gz compression, it was about 15% of the original file size. Compressed, the line would cost you 76 bytes.

Niether did I. Hardly anyone knows, because few people take DHS seriously, and nobody outside of the Federal government has said "Cyber" since the nineties. I attended a computer security conference recently and listened to a panel of current and former federal officials speak about "Cyber" security. They might one day be able to secure government systems, but they're a long way off from protecting you and me online. One of the few things they can do to protect us is to stage a public awareness campaign -- thus we have Cybersecurity Awareness Month.

Why doesn't Google have a Cybersecurity graphic? Online providers don't want you to think about security. Banks don't want you to think about online security. If you thought about security when you signed up for online banking, you might not do it. Without the regulatory agencies, the banks would leave you liable for all losses -- event those caused by the bank's own security lapses, as happened in the UK.

A banking-industry consultant at the same conference said two striking things:

1. Bank marketers fought tooth and nail against FFIEC regulations requiring two-factor authentication for online banking logons. (That means you need your password AND something else to log on.) Banking marketers want to make easy for you (or a hacker) to log on and transfer funds.


2. Banking customer service representatives are just as dumb as the customers when it comes to online security.

If your bank account gets hacked, your bank isn't going to be of much help. They might get some money back, but in most cases, they won't. Your money's gone. The same goes for any other account of yours that gets hacked, whether it's Facebook, GMail, or Yahoo. Nobody's going to help you much.

So take the time now to do a few things to ensure your online security.

1. Use antivirus and make sure it's up to date. If you're on Windows, there are several free antivirus packages available, such as Microsoft Security Essentials , Avast , and Avira . Password-stealing viruses infect computers every day. If you want to tweak out on antivirus effectiveness comparisons, go here.


2. Patch your computer. It doesn't matter if you're windows, mac, unix, linux or bsd. Patch.


3. Change your banking password. Change your email password, because all your password resets go there. Change your security questions, because those reset your passwords. If you're using the same password from college, and your college system gets hacked and reveals your password, then they will find your other accounts.


4. Realize that you are a hundred times more likely to fall for a phishing email than you are to click on an online ad. (Phishing emails are now so common that you might get one that coincides with a recent transaction, making you think it's real.) Now that banks have increased their online security, the hackers are targeting you -- the soft spot.


5. Also realize there are are now office buildings full of professional hackers working in shifts trying to get to your money. (Another panelist, Chris Roberts, talked about research he had done observing the building in an unnamed country in Eastern Europe. Some of his work is available on McAfee's hacker-commerce site.)


6. Don't use unsecured wireless networks. Secure your home wireless network. (Replace WEP encryption with WPA or WPA2.)