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.

Kiwi Syslog Server (Full version is $260 bucks)
SQL Server w/Reporting Services (You should already have if you have Edgesight)

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    06/26/2010:11:41:06 GMT ns PPE-0 : SSLVPN CLISEC_EXP_EVAL 104254 : User wireless: – Client IP – Vserver – Client security expression CLIENT.SVC(MpsSvc) EXISTS evaluated to FALSE(3)

06-26-2010    12:16:05    Local7.Error    06/26/2010:11:41:06 GMT ns PPE-0 : SSLVPN CLISEC_EXP_EVAL 104253 : User wireless: – Client IP – Vserver – Client security expression CLIENT.SVC(MCVSRte).VERSION == 9.0.0 -frequency 5 evaluated to FALSE(3)

06-26-2010    12:16:05    Local7.Error    06/26/2010:11:41:06 GMT ns PPE-0 : SSLVPN CLISEC_EXP_EVAL 104252 : User wireless: – Client IP – Vserver – Client security expression CLIENT.APPLICATION.AV(McafeeVirusScanEnterprise).VERSION == 7.0 -frequency 5 evaluated to FALSE(3)

06-26-2010    12:16:05    Local7.Error    06/26/2010:11:41:06 GMT ns PPE-0 : SSLVPN CLISEC_EXP_EVAL 104251 : User wireless: – Client IP – Vserver – Client security expression CLIENT.APPLICATION.AV(McafeeVirusScan).VERSION == 7.0 -frequency 5 evaluated to FALSE(3)

06-26-2010    12:16:05    Local7.Error    06/26/2010:11:41:06 GMT ns PPE-0 : SSLVPN CLISEC_EXP_EVAL 104250 : User wireless: – Client IP – Vserver – 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 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

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’
    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 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)

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



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 )

Google+ photo

You are commenting using your Google+ 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 )


Connecting to %s

%d bloggers like this: