Here is a quick query to return Subject-DN from certificate used for authorization of the session.
SELECT t1.user_name as userName,
t1.host_mac as macAddress,
t2.attr_name as attrName,
t2.attr_value AS attrValue,
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:Subject-DN'
ORDER BY t1.timestamp DESC LIMIT 1
Result will look like:
Jun 4 14:47:17 clearpasxx.xxxx.local 2025-06-04 14:47:17,723
10.100.0.41 SYSLOG TLS Filter 0 1 0
username=user.xxx,macaddress=axxxxxxxxb,attrname=Certificate:Subject-DN,
attrvalue=emailAddress=user.xxx@xxx.com,ST=xxx,OU=xxxx,CN=
user.xxx@xxx.com,O=Xxxx,L=xxx,C=xx,timestamp=2025-06-04 14:38:28.776+02
Hope it helps a little bit.
It's from very old post Syslog Export Filter and Custom SQL | Security
Best, Gorazd
------------------------------
Gorazd Kikelj
MVP Guru 2025
------------------------------
Original Message:
Sent: Jun 03, 2025 07:05 PM
From: mayler
Subject: Logging EAP-TLS Authentication to External Syslog (CN, SAN, Serial #)
Did you have any luck figuring this out?
Original Message:
Sent: Jan 22, 2021 05:05 PM
From: cbjohns
Subject: Logging EAP-TLS Authentication to External Syslog (CN, SAN, Serial #)
We normally export all of our authentications sessions to an external syslog server for audit purposes. With our introduction of EAP-TLS, we're trying to accomplish the same thing and require logging of the specific certificates that were utilized - although certificate properties don't appear to be in the export-filters. Is anyone familiar with a custom SQL query to extract this information?
------------------------------
Chris
------------------------------