SQL Logging quirks with cisco-avpair

[Mike Manning]

Hi all,

I have a strange issue that I have seen pop up on a thread years ago but cannot for the life of me find the answer to it anymore.  I have a freeradius 3.0 server set up handling authentication requests from Cisco ASR's for our internet users.  We're sending through extended attributes in our AAA data from our ASR's and can see the details in our radacct files (details removed for privacy):

Mon Apr  4 00:01:09 2022
        Acct-Session-Id = "0003E6A0"
        Framed-Protocol = PPP
        Framed-IP-Address = x.x.x.x
        User-Name = "username at realm"
        X-Ascend-Connect-Progress = LAN-Session-Up
        Cisco-AVPair = "connect-progress=LAN Ses Up"
        X-Ascend-PreSession-Time = 1
        X-Ascend-Xmit-Rate = 1410065408
        Cisco-AVPair = "nas-tx-speed=1410065408"
        Cisco-AVPair = "nas-tx-speed-kbps=1410065"
        X-Ascend-Data-Rate = 1410065408
        Cisco-AVPair = "nas-rx-speed=1410065408"
        Cisco-AVPair = "nas-rx-speed-kbps=1410065"
        Acct-Session-Time = 888770
        Acct-Input-Gigawords = 151
        Acct-Output-Gigawords = 18
        Acct-Input-Octets = 1877053998
        Acct-Output-Octets = 3577481241
        WLAN-RF-Band = 83
        X-Ascend-Pre-Output-Octets = 53
        Acct-Input-Packets = 494627556
        Acct-Output-Packets = 336289465
        X-Ascend-Pre-Input-Packets = 4
        X-Ascend-Pre-Output-Packets = 4
        Acct-Authentic = RADIUS
        Acct-Status-Type = Interim-Update
        NAS-Port-Type = Virtual
        NAS-Port = 0
        NAS-Port-Id = "0/1/0/2066.123"
        Cisco-AVPair = "client-mac-address=7c5a.xxxx.xxxx"
        Cisco-AVPair = "circuit-id-tag=AVC000xxxxxxxx"
        Service-Type = Framed-User
        NAS-IP-Address = y.y.y.y
        Acct-Delay-Time = 0
        Event-Timestamp = "Apr  4 2022 00:01:09 AEST"
        Tmp-String-9 = "ai:"

As you can see in the radacct files, the Cisco-AVPair is being written correctly for the client-mac-address and circuit-id tag.  This also appears correctly in the /var/log/freeradius/radius.log file as I have the following 2 lines in radiusd.conf

        msg_goodpass = "Mac: %{Cisco-AVPair[0]}, Circuit ID: %{Cisco-AVPair[1]}"
        msg_badpass = "Mac: %{Cisco-AVPair[0]}, Circuit ID: %{Cisco-AVPair[1]}"

Mon Apr  4 15:23:43 2022 : Auth: (3463054) Login OK: [username at realm] (from client CLIENTNAME port 0) Mac: client-mac-address=7483.xxxx.yyyy, Circuit ID: circuit-id-tag=AVC00xxxxxxx

Here's the strange part.  I am logging the circuit-id and also mac address to the postauth table as follows in the SQL db.  Here is the extract from /etc/freeradius/3.0/mods-config/sql/main/mysql/queries.conf:

post-auth {
        # Write SQL queries to a logfile. This is potentially useful for bulk inserts
        # when used with the rlm_sql_null driver.
#       logfile = ${logdir}/post-auth.sql

        query = "\
                INSERT IGNORE INTO ${..postauth_table} \
                        (username, pass, reply, authdate, macaddress, circuitid) \
                VALUES ( \
                        '%{SQL-User-Name}', \
                        '%{%{User-Password}:-%{Chap-Password}}', \
                        '%{reply:Packet-Type}', \
                        '%S', \
                        '%{Cisco-AVPair[0]}', \
                        '%{Cisco-AVPair[1]}')"

This proceeds to log the details in the SQL DB however both cisco-avpair entries are prepended with a "3D" entry:

INSERT INTO `radpostauth` (`id`, `username`, `reply`, `authdate`, `macaddress`, `circuitid`) VALUES
(131788, 'username at realm', 'Access-Accept', '2022-04-04 05:08:41', 'client-mac-address=3D7c5a.xxxx.yyyy', 'circuit-id-tag=3DAVC0000xxxxxx');

This does not appear in any other log files, is there something to remove the "3D" header that these entries have when logging?

Cheers, Mike