Queries: Excel vs. ArcSight

Since ArcSight ESM 4.0, reports and trends have been based on queries. Considering that ESM runs on top of Oracle, a query in ESM is exactly what you think it is. Queries are an extremely flexible way to get at event data. But as the name implies, they go against the ARC_EVENT_DATA tablespace, and therefore you can't use them to build data monitors or rule conditions, since those engines run against data prior to insertion into the database.

Anyway, I've got a story about how cool queries are. And about how much of an Excel badass I am. And also about how queries are still better. Last month, I got a request from one of our architects who was running down an issue related to client VPN activity. Specifically, he wanted to know how many remote VPN users we had over time for a particular morning. Since we feed those logs to ESM, I was a logical person to ask for the information.

So I pulled up the relevant events in an active channel and realized that I wasn't going to be able to work this one out just sorting columns. So, without thinking, I exported the events and pulled them up in Excel. So here's the Excel badass part:



If you want to copy it, here it is:
=SUM(IF(FREQUENCY(MATCH(A2:A3653,A2:A3653,0),MATCH(A2:A3653,A2:A3653,0))>0,1))

So A is the column that usernames are in. This formula uses the MATCH function to create a list of usernames and then the FREQUENCY function to count the unique values in the match lists. You need two MATCH lists to make FREQUENCY happy because it requires two arguments, hence the redundancy. It took about an hour for me to put it together, most of that was spent finding the row numbers that corresponded to the time segment borders.

But as I finished it up and sent it off to the requesting architect, I thought, there must be an easier way. And of course there is. So here's how you do the same thing in ESM using queries:






















So, it's just EndTime with the hour function applied, and TargetUserName with the count function applied, and the Unique box (DISTINCT for the Oracle DBA's playing at home) checked. And then on the Conditions tab you create your filter to select only the events you want to query against. That's it.

Once the query is created, just run the Report Wizard and go. All told, it's about 90 seconds to the same thing with a query and report that it took an hour to do in Excel.

The 'Cyberwarfare' Problem

Last week I attended ArcSight's annual user conference in Washinton DC. More about that in a later post. During the conference, ArcSight hosted a panel discussion on cyberwarfare. In DC, where many of ArcSight's biggest customer are based, this is a hot topic, and there will be a lot of time spent discussing it and a lot of money spent on defending against it, maybe.

What struck me about the panel discussion were two comments, both made by James Lewis, one of the panelists, and a director at the Center for International and Strategic Studies. At one point, Mr. Lewis invoked Estonia as an example of state-sponsored cyberwarfare, and made the comment that, "the Russians are tickled that they got away with it." Not ten minutes later, an audience member asked a question about retaliation against cyber-attacks. Mr. Lewis responded to the question by pointing out the problem of attribution. That is, from the logs that the victim systems generated, the IP address(es) recorded can't reliably be used to identify the actual individual(s) responsible for the attack.

Now, I don't intend to pick on James Lewis. It just so happened that one person on the panel expressed the paradox of cyberwarfare. The attribution problem is a big problem for all outsider attacks, not just cyberwarfare. A decade ago, security analysts were calling it "the legal firewall" because US-based hackers would first hack computers in China, Indonesia, Venezuela, or another country that doesn't openly cooperate with US law enforcement, and then hack back into the US from there, causing an investigative barrier that would hinder or prevent an investigation being able to get back to the attacker's actual location.

So knowing that there's a very real problem with being able to identify the source country for Internet-based attacks, it stands to reason that using the same limited forensic data to not only identify the actual source of an attack, but to determine that it is in fact state-sponsored, and not, say, a grassroots attack armed by a teenager, is a stretch. And for that reason, the question of cyberwarfare is an open one. Until a government actually comes forward and claims responsiblity for an attack, it's unprovable.

So as the government spends $100M on cyberdefense over the next six months, it's important to try and answer the question, "What is the military actually defending against?" At the very least, it's fair to say nobody knows for certain.