Contributor II

Syslog Export Filter and Custom SQL

I'm looking for a way to create SQL for a syslog export filter that will send the serial number of a valid TLS authentication to Clearpass. The serial number is present in the computed attributes of Access Tracker, so I'm hoping I can find the correct SQL syntax to fetch that same serial number and send it as part of an external syslog.


I browsed the various tips databases, tables and views but wasn't able to find anything related to the parsed certificate information.



Guru Elite

Re: Syslog Export Filter and Custom SQL

The serial number of the certificate?

| Tim Cappalli | Aruba Security | @timcappalli | |

NOTE: Answers and views expressed by me on this forum are my own and not necessarily the position of Aruba or Hewlett Packard Enterprise.
Contributor II

Re: Syslog Export Filter and Custom SQL

Correct - Certificate:Serial-Number.

Contributor II

Re: Syslog Export Filter and Custom SQL

I spent a decent amount of time browsing the various CPPM databases to no avail, but given their breadth I'm not sure I'm even looking in the right place!

Contributor II

Re: Syslog Export Filter and Custom SQL

Progress - I found SQL that returns the data I'm after (MAC, username, timestamp and certificate serial (without colons) - see below). However, placing this query in the export filter doesn't appear to work - I see no data in the logs now. On top of that, when I select a Data Filter and save the export filter, it doesn't save the changes. Not sure what I'm missing now.


SELECT t1.user_name as userName, t1.host_mac as macAddress, REPLACE(attr_value,':','') AS certSerial, t1.timestamp as timeStamp FROM tips_session_log_details t2 JOIN tips_dashboard_summary t1 ON t2.session_id = WHERE t2.attr_name = 'Certificate:Serial-Number' AND t1.host_mac = '%{Connection:Client-Mac-Address}' ORDER BY t1.timestamp DESC LIMIT 1
Search Airheads
Showing results for 
Search instead for 
Did you mean: