Project Poindexter: Endpoint Analysis Log Harvesting
About four years ago management wanted to know which users were failing their endpoint analysis scans and to what extent we were compliant with endpoint analysis. We spent over $30K on a product called “Clear2View” and it did some rudimentary scans logging for us but the data was not very easy to query even though it was located in a SQL Database and the reporting features were, in my opinion, only so-so. With that, it appears as though Clear2View has gone away and many of us are left wondering how we will get our EPA Scan data on the new AGEE platform. We have been able to get past this dilemma by harvesting the Syslog Data from the AGEE and parsing it into a SQL Server and then integrating it with Business Intelligence.
As with other “Project Poindexter” posts, we will cover how to grab EPA Scan results from SYSLOG and write them to a SQL Server then report on them at a cost considerably less than $30K.
Materials:
Kiwi Syslog Server (Full version is $260 bucks)
SQL Server w/Reporting Services (You should already have if you have Edgesight)
Skills:
Some vbscript or parsing skills, although I will provide the parsing script to you.
The ability to take my SQL Syntax and edit it so that it suites your scans/environment.
The ability to upload an RDL to Reporting Services and map it to a data souce.
So getting started, here is an Example:
So, at home with the VPX and some test vm’s I set up the following scans:
As you can see, I am testing for the McAfee suite(a canned scan) and to see if the Windows Firewall is running.
Results: Here are the results that come into KIWI.
06-26-2010 12:16:05 Local7.Error 192.168.1.75 06/26/2010:11:41:06 GMT ns PPE-0 : SSLVPN CLISEC_EXP_EVAL 104254 : User wireless: – Client IP 192.168.1.50 – Vserver 192.168.1.100:443 – Client security expression CLIENT.SVC(MpsSvc) EXISTS evaluated to FALSE(3)
06-26-2010 12:16:05 Local7.Error 192.168.1.75 06/26/2010:11:41:06 GMT ns PPE-0 : SSLVPN CLISEC_EXP_EVAL 104253 : User wireless: – Client IP 192.168.1.50 – Vserver 192.168.1.100:443 – Client security expression CLIENT.SVC(MCVSRte).VERSION == 9.0.0 -frequency 5 evaluated to FALSE(3)
06-26-2010 12:16:05 Local7.Error 192.168.1.75 06/26/2010:11:41:06 GMT ns PPE-0 : SSLVPN CLISEC_EXP_EVAL 104252 : User wireless: – Client IP 192.168.1.50 – Vserver 192.168.1.100:443 – Client security expression CLIENT.APPLICATION.AV(McafeeVirusScanEnterprise).VERSION == 7.0 -frequency 5 evaluated to FALSE(3)
06-26-2010 12:16:05 Local7.Error 192.168.1.75 06/26/2010:11:41:06 GMT ns PPE-0 : SSLVPN CLISEC_EXP_EVAL 104251 : User wireless: – Client IP 192.168.1.50 – Vserver 192.168.1.100:443 – Client security expression CLIENT.APPLICATION.AV(McafeeVirusScan).VERSION == 7.0 -frequency 5 evaluated to FALSE(3)
06-26-2010 12:16:05 Local7.Error 192.168.1.75 06/26/2010:11:41:06 GMT ns PPE-0 : SSLVPN CLISEC_EXP_EVAL 104250 : User wireless: – Client IP 192.168.1.50 – Vserver 192.168.1.100:443 – Client security expression CLIENT.APPLICATION.AV(McafeeNetshield).VERSION == 7.0 -frequency 5 evaluated to FALSE(3)
So next let’s take these results and get them parsed then logged to SQL Server:
Create a new Rule called “EPA Scans” and create one filter with three actions.
The First Filter is called “Filter Text – CLISEC” and set it up to filter message text for “CLISEC”
The first Action is “DISPLAY”
The second Action is “Parse Data” (Note Check all the boxes for Read and Write and Browse to the location of the Parsing Script which you can get at http://www.ctxsupport.com and go to the “ACCESS GATEWAY forum)
The third Action is called “Write to SQL” which will require a custom data format so let’s cover those steps:
Custom Data Format:
Create a custom DB Format called EPA_SCANS, it should appear as follows: (Note the Field names AND the data types as they are very important)
Now that you have created your custom DB format go back to your “Write to SQL” action
Make sure that your DNS Connect String is correct and make sure that you name the table EPA_SCANS under database table name and that you use the Custom DB Format EPA_Scans then click on “Create Table”
Once this is done you should be all set, log into your VPN/AGEE Address and look for the results by running a simple SQL Query:
select * from epa_scans
order by msgdatetime desc
You should see something like the following:
Note that in the results I include 7 columns. I always include the entire log in the msgtext column for several reasons, among them Security statutes may dictate that you must have all of the log available and there are instances where parsed logs are not admissible in court. For this endeavor, it is your choice, I have habit of just leaving it in.
Also, my goal of setting up the logging was so that the Service Desk staff could look at the results and tell the end users what the problem is. To deal with that issue let’s take a look at the actual scans:
CLIENT.APPLICATION.AV(McafeeNetshield).VERSION == 7.0 -frequency 5 CLIENT.APPLICATION.AV(McafeeVirusScan).VERSION == 7.0 -frequency 5 CLIENT.APPLICATION.AV(McafeeVirusScanEnterprise).VERSION == 7.0 -frequency 5 CLIENT.SVC(MCVSRte).VERSION == 9.0.0 -frequency 5
CLIENT.SVC(MpsSvc) EXISTS
As you can see from the scans above, a Level I engineer may not have a very easy time with this so we are going to change our SQL up a little bit so that we have a more friendly description of the scan so that when someone calls the helpdesk saying they cannot get to a resource due to a failed scan, the person on the phone with them can give them a clear explanation of what the issue is.
So let’s shake up our SQL just a little:
select msgdatetime, userid, clientip, scan=
case Scan
when ‘CLIENT.SVC(MCVSRte).VERSION == 9.0.0 -frequency 5′ then ‘Antivirus Service Check’
when ‘CLIENT.APPLICATION.AV(McafeeVirusScanEnterprise).VERSION == 7.0 -frequency 5 ‘ then ‘Antivirus ENT.Version Check’
when ‘CLIENT.APPLICATION.AV(McafeeVirusScan).VERSION == 7.0 -frequency 5′ then ‘Antivirus Std. Version Check’
when ‘CLIENT.APPLICATION.AV(McafeeNetshield).VERSION == 7.0 -frequency 5′ then ‘Netshield Version 7 Check’
when ‘CLIENT.SVC(MpsSvc) EXISTS’ then ‘Check Microsoft Firewall Service’
end,
Result
from epa_scans
order by msgdatetime desc
WordPress has a habit of placing double quotes on single quotes so it is not likely you can just paste this into your query so I will include this in the Access Gateway area of http://ctxsupport.com. At any rate note the following:
We are taking the cryptic “ CLIENT.APPLICATION.AV(McafeeVirusScanEnterprise).VERSION == 7.0 -frequency 5″ Text and converting it into a more easily interpreted ‘Antivirus ENT.Version Check’Your SQL Query, and eventually your SQL Reporting services reports will appear as follows:
Also, your SQL Report will appear as follows:
Note that the failures are RED which will alert your staff and also note how much more logical and more intpretable the SCAN information is. You could also rig up a self service by providing a link on the scan sending the user to the place to either innoculate their system or instructions on how to turn on their Microsoft Firewall.
Again all parsing scripts, RDL’s and SQL Queries are located here
Why is this even important:
Well, as the security screw gets tighter and tighter more and more restrictions are going to be placed on both internal and remote access systems. It will be a disaster to deploy endpoint analysis on a large scale without being able to at least give the support staff the ability to tell the users why they did not get access to a resource. We plan on taking this to the next level and providing an HTML Injection rule so that when a user goes straight to Web Interface because they failed a scan, there is a popup button that tells them they failed with a URL to the report above letting them know what scan failed, and eventually, a hyperlink to take them to a remediation page (Be it instructions or updated signatures).
Also, I believe, there never was a Clear2View for the AGEE anyway so those of us with the AGEE version were kind of left out of that game. This process sets you up with all the business intelligence you need to support NAC-like endpoint analysis and also allows you to report on the level of compliance for your company or agency. Oh…and it only costs $260 bucks plus some time (which I understand is expensive)
IMPORTANT NOTE/DISCLAIMER:
Obiviously, Citrix will not support this but also, you WILL HAVE to be able to edit the SQL Statement both within the Query Analyzer AND the RDL file otherwiseyour report will not show proper data. You do need to have some SQL proficiency to pull this off but you do not have to be a full fledge DBA. If you are a parnter, this could be a very nice value-add for a customer if you have a few hours left in an engagement. It was not excessively difficult to do.
Also, I don’t run all of the scans that everyone else may or may not run. There may be an instance where a particular scan does not parse properly, if so, shoot me an email and I will see if I can’t figure it out.
As with the VPN Logging, I plan on producing a video walkthru of this entire task. I should have some head down time at the begining of Next month to walk through it.
This literally took 45 minutes to set up once I had the Parsing scripts and my SQL Figured out. If you run into a problem, feel free to shoot me an email.
Thanks for reading
John
Project Poindexter:VPN Logs
Total Information Awareness with your Netscaler/AGEE
Harvesting VPN Logs with the Netscaler:
When I first heard about Total Information Awareness I was a little concerned. Like a lot of my current team, I am one of those libertarians who really isn’t keen on his personal life being correlated and analyzed and a program that is overseen by unelected officials. That said, as an individual responsible for the security and integrity of information systems as well as a person who’s own personally identifiable information is in the databases of my bank, doctor and employer, I do believe I am entitled to know what is going on and I would like to think the stewards of my information are also informed of what is going on with regards to my own data. For this reason, I decided to start looking into how I could better monitor activity on my Netscaler and I wanted to provide an accompanying guide to my SCIFNET post/video showing how you can compartmentalize sensitive data using the VPX or a regular MPX class Netscaler.
Most engineers are fully aware that the Netscaler platform is capable of sending information to a syslog server. This in and of itself is not that significant as many network/Unix based appliances can syslog. What I want to discuss in this post is how to use a very cheap syslog server to set up a fully functional log consolidation system that includes parsing specific records and writing them to a relational database.
I find a certain amount of frustration with today’s six figure price tag event correlation systems and if you can only respond to a breach by doing “Find and Next” on a 90GB ASCII file, needless to say, that is not the most agile way to respond and not where it needs to be to react to an INFOSEC related incident. As with the Admiral Poindexter’s vision, proper analysis of events can be an instrumental tool in the defense of your information systems.
Below is an example of a typical VPN log from your Netscaler/AGEE appliance:
06/15/2010:05:59:38 ns PPE-0 : SSLVPN HTTPREQUEST 94167 : Context wireless@192.168.1.50 – SessionId: 5- http://www.veoh.com User wireless : Group(s) SCIF-NET USERS : Vserver 192.168.1.100:443 – 06/15/2010:05:59:38 GET /service/getUpdate.xml?clientGUID=01BACADF-CE85-48CD-8270-B8A183C27464&VEOH_GUIDE_AUTH=am1zYXpib3k6MTI3ODAyODkyMTM1NzpyZWdp – -
Using KIWI Syslog server’s parsing capability, I will actually parse this data and write it into a SQL Server database to allow for very easy queries and eventually dashboards showing accountability and key data.
I have had engineers ask me how to get things like Client IP Address and what they have accessed. I will provide a parsing script that will pull from the example above, the following: (As in the case of the log above)
Context: wireless@192.168.1.50
Destination: http://www.veoh.com
Payload: GET /service/getUpdate.xml?clientGUID=01BACADF-CE85-48CD-8270
*I have also included “Assigned_IP” in case any of you assign ip addresses instead of NATing. If you are able to get the Destination of where a user was going, the need to account for every IP Address may become less important but some folks insist on not NATing their users. If so, the parse script will grab their IP’s as well.
And just to show you that I do have the data you can see in the screen print below of the SQL Query:
Uh, John…who cares?
Well, most of the time you really shouldn’t need to do a lot of tracking of where your users are going but in some higher security environments being able to account for where users have gone could be very important. Say you hosted http://www.veoh.com (a site I hate but for the purpose of this lab, their malware…err…client was installed on the laptop I was testing with) and someone said that the system had been compromised. You could immediately obtain every user ID and IP Address that accessed that site and what the payload that they ran against it was. You would see the XSS or SQL Injection string immediately. You would also note a system that had malware and was trying to get in over one of the SMB “Whipping boys” (445, 135-139).
Parsing data vs. just throwing it all into a flat file and waiting for an auditor to ask for it?
As I stated previously, the ability to have your data in a relational database can give you a number of advantages, not just pretty tables and eventually dashboards but you also open the door to the following:
- Geospatial analysis of incoming IP Addresses (by cross referencing context with geospatial data from iptolocation.com or other free geospatial ip-to-location data.
- An actual count of the number of concurrent users on a system within a block of time including historical reporting and trending.
- The number of times a “Deny” policy has been tripped and who tripped it. If you are compartmentalizing your data and you want to know who tried to access something they are not allowed to.
- Your sensitive data is on wiki leaks and you want to know every user who accessed the resource the data resides on, when and what ports they used?
- And lastly, find out who is going ” \\webserver\c$” to your web server instead of “http://webserver”
So what do I log?
Well, I log basically everything but for VPN I log three different events into two different tables, I log all HTTP based traffic, normal UDP/TCP based connections and I also have a separate table for all of my “DENIED_BY_POLICY” Events.
Here is an example of an HTTPREQUEST log:
06/15/2010:11:59:58 ns PPE-0 : SSLVPN HTTPREQUEST 110352 : Context wireless@192.168.1.50 – SessionId: 5- http://www.veoh.com User wireless : Group(s) SCIF-NET USERS : Vserver 192.168.1.100:443 – 06/15/2010:11:59:58 GET /service/getUpdate.xml?clientGUID=01BACADF-CE85-48CD-8270-B8A183C27464&VEOH_GUIDE_AUTH=am1zYXpib3k6MTI3ODAyODkyMTM1NzpyZWdp – -
Here is an example of TCP/UDPFlow statistics:
06/15/2010:12:18:16 ns PPE-0 : SSLVPN UDPFLOWSTAT 111065 : Context wireless@192.168.1.50 – SessionId: 5- User wireless – Client_ip 192.168.1.50 – Nat_ip 192.168.1.85 – Vserver 192.168.1.100:443 – Source 127.100.0.5:53052 – Destination 239.255.255.250:1900 – Start_time “06/15/2010:12:15:32 ” – End_time “06/15/2010:12:18:16 ” – Duration 00:02:44 – Total_bytes_send 1729 – Total_bytes_recv 0 – Access Allowed – Group(s) “SCIF-NET USERS”
Here is an example of a DENIED_BY_POLICY event: (Over HTTP)
06/15/2010:10:17:14 ns PPE-0 : SSLVPN HTTP_RESOURCEACCESS_DENIED 106151 : Context wireless@192.168.1.50 – SessionId: 5- User wireless – Vserver 192.168.1.100:443 – Total_bytes_send 420 – Remote_host pt.veoh.com – Denied_url POST /tracker/update.jsp – Denied_by_policy “Problem-Site” – Group(s) “SCIF-NET USERS”
Let’s talk a little about the “DENIED_BY_POLICY” logs
Here is a Scenario: I have a problem website that I do not want any of my users to go to so I create a policy called “Problem-Site” denying access to the IP of the problem site.
For the log above, I parse the following:
Context: wireless@192.168.1.50
Destination: pt.veoh.com
Policy: Problem-Site
Payload: POST /tracker/update.jsp
I also log non-http denies as well, these appear like the following:
06/14/2010:21:08:03 ns PPE-0 : SSLVPN NONHTTP_RESOURCEACCESS_DENIED 69761 : Context wireless@192.168.1.50 – SessionId: 5- User wireless – Client_ip 192.168.1.50 – Nat_ip “Mapped Ip” – Vserver 192.168.1.100:443 – Source 192.168.1.50:50343 – Destination 10.10.10.30:139 – Total_bytes_send 291 – Total_bytes_recv 0 – Denied_by_policy “TOP-SECRET-DENY” – Group(s) “SCIF-NET USERS”
Here is a Scenario: You read a story in “wired.com” about some kid who tried to give a bunch of sensitive data to a hacker or even wiki leaks and you are concerned about your own data being accessed without authorization. You want to monitor all attempts to get unauthorized access and you want to note them, or, since they are in SQL Server w/reporting services, create a dashboard that goes RED when a particular policy is tripped.
Another scenario would be to actually monitor successes and note the “Context”, if most users who access data provided by the “TOP-SECRET-ALLOW” policy come from a specific network ID, say 10.105.28.0/24 and you start seeing access from 10.111.13.68 then you can see if a user ID has been compromised, you can also query and see how often a user accesses data from which IP Addresses. If someone’s account is compromised, it would show up as coming from another IP as it is less likely that they are sitting at the user’s terminal.
In the log above I parse the following:
Context: wireless@192.168.1.50
Destination: 10.10.10.30:139 (note the :139 indicating an attempt to use SMB)
Policy: TOP-SECRET-DENY
Payload: (Blank if not HTTP)
Below is an example of Reporting Services dashboard that refreshes every minute:(Note, I have a particular Policy that turns red in this dashboard to alert me of an important breach attempt)
| Time | Appliance | Context | Destination | Policy | Payload |
| 12:37 | 192.168.1.75 | wireless@192.168.1.50 | 10.10.10.30 :3389 | TOP-SECRET-DENY | |
| 12:37 | 192.168.1.75 | wireless@192.168.1.50 | 10.10.10.30 :3389 | TOP-SECRET-DENY | |
| 12:37 | 192.168.1.75 | wireless@192.168.1.50 | 10.10.10.30:3389 | TOP-SECRET-DENY | |
| 12:37 | 192.168.1.75 | wireless@192.168.1.50 | 10.10.10.30:3389 | TOP-SECRET-DENY | |
| 12:37 | 192.168.1.75 | wireless@192.168.1.50 | pt.veoh.com | Problem-Site | POST /tracker/update.jsp |
| 12:37 | 192.168.1.75 | wireless@192.168.1.50 | 10.10.10.30:139 | TOP-SECRET-DENY |
What You need:
- You need an incumbent SQL Server Environment, you need Reporting Services if you want dashboards (If you have edgesight you should already have this)
- You need to be able to set up an ODBC Connection, remember if it is a 64-bit server/workstation you need to use the ODBC tool in %Systemroot%\sysWOW64
- You need to be able to set up a database connection in Reporting Services
- $245 bucks for a full version of KIWI, if you buy a Netscaler you can afford a full version of KIWI, I will cover several solutions that will make this the best $245 you have ever spent.
How to set it up:
Once you brow beat your cheap boss into spending the $245 on KIWI you perform the following steps:
Go to http://www.ctxsupport.com/forums/showthread.php?36-Parsing-Scripts-for-VPN-Data-Mining-on-AGEE and download all of the files. (Follow the instructions in the post)
Create a Database called Syslog with a username and password that has DBO privileges and create an ODBC Data Source on the server hosting KIWI for the syslog database and name it syslogd.
After renaming Netscaler.txt to Netscaler.ini go to KIWI and import the ini file.
On each rule, go to the “Write to SQL” Action and click “Create Table”
On each rule, go to the “Parse Data” Action and click “Browse” to upload the parsing script that goes with each rule. (Check all checkboxes under “Read and Write”
Conlcusion:
Once this is done you will be able to collect a ton of information that is very useful and it beats the hell out of a 90GB ASCII file or just writing everything into a single event correlation system without the ability to query on certain columns. All of the parsing scripts write the entire log to the msgtext column so you still have the original log if there is every any questions. Being able to parse key information in a specific column will give you a considerably higher level of agility when searching for information about a particular user, IP Address, destination or Security Policy.
If there is a worm that is sending a particular payload over http, you are one query away from finding out every infected IP Address. If an auditor asks you how many users have accessed a sensitive server you are a query away from providing that information. I will supplement this post with a video of the entire setup from start to finish on citrix.utipu.com within the next two weeks (Hopefullly).
Also, I tried this in a home based lab (I cannot use my logs from work) so please, if you have any issues getting it to work, let me know so I can set up better instructions. And keep in mind, I have not looked at this with ICAPROXY logs, I am hoping to do that ASAP, there may be a supplement to this that includes a different script and maybe a different table for ICAPROXY logs. I am waiting on an enhancement request before I tackle ICAProxy logs (They will come across as “SSLVPN” but the log does look different than standard VPN logs).
And most importantly, I am not a Developer, I am a poor-man’s DBA and am a marginal scripter at best, if you can write a better parsing script please let me know!!
Thanks for reading
John Smith
