Export all attributes of an authentication request from access tracker to a syslog server.

Requirement : 

We want to export authentication details along with Radius:IETF, Computed and Authorization attributes to the external Syslog server.

The default Filter and columns will not allow us to export authentication records with attributes.

Solution : 

We have to construct our custom SQL query to include the fields we need and the attributes that we want to export to external Syslog server.

Problem:

The attributes are stored in multiple rows for each session id (i.e. for each authentication).

To achieve the requirement, we are making use of PostgreSQL array aggregate function i.e. array_agg().

Paste the following SQL query under Custom SQL field. 

select a.id,a.user_name, a.service_name, a.host_mac, a.timestamp, array_agg(b.attr_name) as attributes, array_agg(b.attr_value) as values from tips_dashboard_summary a inner join tips_session_log_details b on a.id = b.session_id where b.attr_name in ('Radius:Aruba:Aruba-Essid-Name', 'Radius:Aruba:Aruba-Location-Id') and (a.timestamp >= --START-TIME--) and (a.timestamp <= --END-TIME--) group by 1,2,3,4;

Note: You can add the fields that you want by editing the query. Check the end of the article for the scheme of two tables used in above query.

Authentication requests seen on access tracker. 

We can see the syslog information received by syslog server with the Radius:IETF attributes. We can add computed as well as authorization attributes.

Schema of two tables used in the SQL query for your reference: 

Disclaimer: 

1. Recommended best practice would be to run query on server which is not handling much authentications.

          Pay caution when using the query. Monitor the server initially when using the query.

          If it is not contributing to the load on the server too much, you can continue using this. 

          The execution plan of the query used:

      2. The attributes exported would be in an array.