Security

Reply
iva
New Contributor

ClearPass - NAS-Port-Id to external Syslog

We have configured ClearPass to send a lot of useful information to our log server (Splunk) using Syslog Export Filters provided (http://support.arubanetworks.com/Documentation/tabid/77/DMXModule/512/Command/Core_Download/Default.aspx?EntryId=15500). One very useful attribute is missing though: "Radius:IETF:NAS-Port-Id". This would allow us to determine e.g. which device is connected to which Switchport.

 

Can anybody give me the custom sql query syntax that I need for ClearPass to forward the NAS-Port-Id to an external Syslog server. I have found the following syntax (http://www.jakesbazaar.com/2016/08/04/aruba-clearpass-sql-filters/), but it keeps giving me a syntax error.

Re: ClearPass - NAS-Port-Id to external Syslog

I haven't tested this but it was accepted as a filter. It's pretty much the one posted on jakesbazaar.com/ but it wasn't accepted initially. 

 

Not sure why this happened but all I did to get CPPM to accept it was to replace the ' marks around 'Radius:IETF:NAS-Port-Id' with ", then changed them back to ' and it was accepted.

 

I removed the start and end time as that wasn't being accepted either.

SELECT tips_dashboard_summary.id as session_id, source as req_source ,user_name,service_name,alerts_present,nas_ip,nas_port,conn_status,login_status,error_code,host_mac as mac_address,tips_dashboard_summary.timestamp,tips_dashboard_summary.write_timestamp,attr_value,attr_name FROM tips_dashboard_summary INNER JOIN tips_session_log_details ON tips_dashboard_summary.id = session_id where attr_name = 'Radius:IETF:NAS-Port-Id';

Caveat, I'm no SQL expert so I'd recommend someone taking a look at the command to make sure it wont put too much strain on your CPPM server.

 

Cheers

James

Cheers
James

-------------------------------------------------------
-------------------@whereisjrw-------------------
------------------------blog-------------------------
ACCX #540 | ACMX #353 | ACDX #216
-----------Mobility First Expert #11----------
-------------------------------------------------------

If a reply adequately addresses your issue, please click on the "Accept as Solution" and "Give Kudos" button so this information can benefit other users via search.
iva
New Contributor

Re: ClearPass - NAS-Port-Id to external Syslog

Thanks for the reply....I will try to test it soon!

iva
New Contributor

Re: ClearPass - NAS-Port-Id to external Syslog

Hey James

 

I finally got around to testing your DB query. The good news: it working! I am receiving logs ocntaining the NAS Port ID. The bad news: there are countless logs per second for the same client, similar to the log shown at the bottom. This will produce way too much overhead and I have deactivated the export filter as a result.

 

Is there any way to optimize this?

 

Kind Regards

 

 

Dec 20 15:06:44 10.7.10.223 2016-12-20 15:06:44,170 10.1.8.230 CPPM_RADIUS_NAS-Port-ID 61201 1 0 session_id=R000e2ee5-01-585921db,req_source=RADIUS,user_name=d47856004231,service_name=svc_swl_wired_client_lan,alerts_present=0,nas_ip=10.1.9.152,nas_port=50140,conn_status=Unknown,login_status=ACCEPT,error_code=0,mac_address=d47856004231,timestamp=2016-12-20 13:19:39+01,write_timestamp=2016-12-20 13:19:40.228309+01,attr_value=GigabitEthernet1/0/40,attr_name=Radius:IETF:NAS-Port-Id

Re: ClearPass - NAS-Port-Id to external Syslog

Probably, but I'm not that good at SQL queries to be honest!

Hopefully someone else will chip in.

Cheers
James
Cheers
James

-------------------------------------------------------
-------------------@whereisjrw-------------------
------------------------blog-------------------------
ACCX #540 | ACMX #353 | ACDX #216
-----------Mobility First Expert #11----------
-------------------------------------------------------

If a reply adequately addresses your issue, please click on the "Accept as Solution" and "Give Kudos" button so this information can benefit other users via search.
New Contributor

Re: ClearPass - NAS-Port-Id to external Syslog

This query timed out on me when attempting to use the provided SQL.  Has anyone else had luck using this?  Also, if there is a way to get the port information seperate and join it to other auth information via a unique ID.  This is something I would be able to accomplish utilizing our SIEM platform.

 

Thanks,

Greg

Search Airheads
cancel
Showing results for 
Search instead for 
Did you mean: