Security

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.

Thanks!

Correct - Certificate:Serial-Number.

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!

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 = t1.id WHERE t2.attr_name = 'Certificate:Serial-Number' AND t1.host_mac = '%{Connection:Client-Mac-Address}' ORDER BY t1.timestamp DESC LIMIT 1