Project Poindexter: (Non-Citrix Related) Grabbing Pix URL logs checking them for malware.

This is my first non-Citrix related post, I don’t plan on making it a habit but someone suggested that I post this in case it is valuable to other INFOSEC types. 

Let me start off by saying I am not a traditional security guy, I don’t have an abundance of hacking skills, I am not a black hat, white hat etc. I did work in Security for awhile as the Event Correlation guy for a year and have been trying to leverage digital epidemiology as a way to secure my systems. As I have stated in previous blogs, we have a better chance of curing the common cold than getting rid of malware and 0-day’s. In fact, I would say there are two kinds of systems, breached and about to get breached. This is the way you have to approach malware in my opinion. What surprised me with the Aurora breach was that it appears as though the INFOSEC community spends the lion’s share, if not all, of their time on ingress and completely ignores egress. When I look at the Google breach I see an attack that should have been mitigated within 24 hours.

Over the years I have deployed or viewed a number of event correlation utilities, most of them costing in excess of $250K for a large implementation.  What I generally did not like about shrink wrapped solutions and what I am most concerned about in the IT industry is the de-emphasis on heuristics and a dependance on an automated process to detect a problem.  In my opinion, an “Event Correlator” is not an appliance, it is an IT Person looking at a series of logs and events and saying “Holy shit! What the HELL is that!”.  The fact is, false positives make a lot of really expensive security software completely useless and a stored procedure or IDS/IPS cannot do as good of a job as a human being who can look at a series of logs and make an interpretation.  What I want to provide here is some of the heavy lifting that can then be use by a human to determin if there is an issue. 

The purpose of this post is to show people how I grabbed Syslog data from my pix allowing me to grab the URI Stem of all outgoing sessions and log them into a SQL Server. Afterward, I will be able to run key queries to be able to troll for .exe, .dll, .tgz and any other problem extensions. Also, I can upload the latest malware list data and cross reference it with the information in my database which will allow me to see if any of my systems are phoning home to a botnet master, malware distribution site, etc. This is basically a take on my post on monitoring APT with Edgesight.

The first order of business is to get the logs to the syslog server. I start by creating a filter that will grab the logs. (See Below)

The next step is to parse the incoming data into separate columns in my database. This is done by setting up a custom db format for the purpose of these logs. The parse script is provided below:
Also, check all checkboxes below “Read” and “Write”

Parsing Script: (Cut and paste it to a text file then use that text file in the dialog box above)
Function Main()
Main = “OK”
Dim MyMsg
Dim Source
Dim Destination
Dim Payload

With Fields
Source = “”
Destination = “”
Payload = “”    

MyMsg = .VarCleanMessageText

If ( Instr( MyMsg, “%PIX” ) ) Then
SourceBeg = Instr( MyMsg, “: “) + 2
SourceEnd = Instr( SourceBeg, MyMsg, “Accessed”)
Source = Mid( MyMsg, SourceBeg, SourceEnd – SourceBeg)
DSTBeg = Instr( MyMsg, “URL”) + 3
DSTEnd = Instr( DSTBeg, MyMsg, “:”)
Destination = Mid( MyMsg, DSTBeg, DSTEnd – DSTBeg)
End IF    
.VarCustom01 = Source
.VarCustom02 = Destination
.VarCustom03 = Payload

End With
End Function

The last step is to write the data to SQL but first let’s do a few tasks to prepare the table.

  1. Set up an ODBC connection to a SQL Server and create a database called “Syslog” and connect to it with an account that has dbo privilages.
  2. Create the Custom DB Format for grabbing URL’s

Note that this table will have five columns, msgdatetime, msghostname, msgtext, source, destination and payload. (The last column, payload, is not working yet but I will show you how to get the payload later)

3. Once this is done, create an action called “Write to SQL” and select “PIX_URL” from the custom data fromat list and name the table “PIX_URL” then select “Create Table”

Okay, so now that we have the data writing to SQL Server, let’s look at a month’s worth of data on one of my systems:

This query will give you the payload and the number of times the payload has been accessed. Using the having function I am going to ask for every uri-stem that has been accessed more than 5 times in the last month.

select substring(msgtext,41, 2048)as “Payload”, count(substring(msgtext,41, 2048))
from pix_url
group by substring(msgtext,41, 2048)
having count(substring(msgtext,41, 2048)) > 5
order by count(substring(msgtext,41, 2048)) desc 

The idea behind this is that if you note 1000 records to “” you may want to do something about it. You can also download the data, import it into SQL and cross reference that data to ensure that you are not communicating with any noted malware sites. Depending on the response of this blog, I may post those instructions as well.

 And here are what the results look like:

Another query I like to run is one looking for executable files in the URI-stem.

select Msghostname as “Firewall”, Source, Destination, substring(msgtext,41, 2048) as “Payload”
from pix_url
where msgtext like ‘%.exe%’
order by msgdatetime desc

This will allow me to troll for executables that my internal users are accessing, as with most versions of malware, this should show itself early on during the breach.

So how do you monitor?

Well, you don’t have to sit there with query analyzer open all day but you can set up SQL Server Reporting Services to do this chore for you and deliver a dashboard to operations personnel. Here is a quick view of a dashboard that refreshes ever 5 seconds and turns RED when “.exe” is in the URI-Stem. In this scenario, you would be able to investigate the executable that is being downloaded by the client and ensure that it is not malware. You can test this yourself once you set it up by going to any site and typing “/test.exe” at the end.

Again, I am not a traditional security guy so this could be utterly useless, I am not the PIX guy at my job, I AM the PIX guy at home though. Also, I have found it very useful to check for Malware and 0-Day’s that my anti-virus does not pick up. While I cannot speak with as much authority as a number of CISSP’s and INFOSEC guru’s, I can say that the continued ignorance surrounding egress will allow malware to run amuck. As I stated in a previous blog, it is foolish to beat your chest at the millions of packets you keep out while the few that get in can take anything they want, and leave unmolested. Just like a store has to let some people in then focus on ensuring no one leaves with anything they didn’t pay for, IT Security needs to ease over to this mentality and keep track of what is leaving its networks and where it is being sent. At any rate, if this has value to anyone let me know, I will include the RDL (Report File) online for download if anyone wants to set it up. I know a lot of PIX guys aren’t necessarily web/database guys so if you have any questions, feel free to ask.

Thanks for reading,



Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: