We're exploring the idea of sending all wireless RADIUS accounting directly to ClearPass - we currently use FreeRADIUS for this. For one SSID the accounting logs go directly to FreeRADIUS and eduroam proxies via ClearPass.
The FreeRADIUS servers speak to an internal database, using PostgreSQL, to record session data. This is so IT staff can view the authentications of their local users in a web interface. The internal database will need to react to 'Start', 'Interim-Update' and 'Stop' messages in which we have the existing SQL queries.
What is the best way of achieving this? Would it be via Syslog Export Filters on CPPM? Option two will also allow us to use our custom SQL queries. Can both options be used simultaneously or is it just one or the other?
Not fully sure what you try to achieve. Is it that ClearPass receives the accounting data from your network infrastructure, then needs to write that in an external Postgres database?
That is not what the Custom SQL is for. That is to write your own query to the Insight database (also Postgres) to fetch other attrbutes that are not in the list of fields, or do a custom JOIN or so. It's not to change data or connect to external databases.
I think you can proxy the radius accounting data from ClearPass to an external server (like your FreeRADIUS) to replicate it.
Or you could use the appexternal external database user to query the Insight database from your external application and filter on username.
If you don't care what users the data can be retrieved from, you could create allow Operator or Read Only Admin access to ClearPass and search accounting from there.
Thank you for the clarification, Herman.
So if we use the Syslog Export Filter option 2, using a custom SQL query, to fetch the attributes we require, that data will be stored in the Insight DB? We can then use the appexternal database credentials to query the Insight DB, from our external wireless database, for the custom information referecned in the custom SQL using the Syslog Export Filters? If the default [RADIUS Accounting Requests] filter doesn't provide all of the information required by our external database?
Close but not fully.
The Syslog Export filter attributes are pre-configured attributes that are queried/pulled from the Insight DB in ClearPass. With Custom SQL Query you can query the full Insight database, not just the fields that are pre-populated. It's not writing anything, just pulling out information similar to the pre-configured ones. And these syslog filters are used to send out syslog messages to an external syslog service.
But, yes you can use the appexternal to do similar queries from an external system (think there are API calls as well, and API would probably have preference over SQL queries that may be affected by changes in the database schema between ClearPass versions). These queries are fully independent from the syslog export filters, they just query the same data source (Insight database). Note that if you use the appexternal SQL account, the Insight database in ClearPass 6.11 and up is running on port 5433 (instead of the Postgres default 5432 which is stil used for the configuration and endpoint databases). If you run a query against the database directly, it will be raw SQL query and it's up to you what you want to pull out of it.
It's up to you to find out what works best, either send out the data proactively over syslog, then store it somewhere or make it externally queryable. Many SIEM systems would allow something like that.
Or proxy the accounting data so you have the accounting data also at an external system and make it queryable.
Or use the API or raw SQL queries against the Insight Database to get out the data, possibly from the frontend application that you wrote for admins to query the data.
Thanks, Herman. All useful information.
I've actually got this the wrong way round - I've been asking how to query ClearPass from the external database. Whereas, I should have been asking how ClearPass can query the Wireless Console PostgreSQL database and updating it, not the external database connecting into ClearPass and pulling information out.
We need to be able to execute SQL queries to INSERT/UPDATE rows in the external database when we receive accounting messages from the controllers.
Sorry, that's my fault! Good to know the other way round too though.
SQL queries that may be affected by changes in the database schema between ClearPass versions
Are these changes documented in the release notes? I expect queries are not automatically updated.
If they are not documented, it seems a customer should not depend on Syslog SQL query filters for logging, especially if there are contractual logging obligations (eduroam).
The database scheme may change between versions, and those changes are not documented because it's recommended to use the APIs which are either updated or documented that these change. Consider the direct SQL database access as an internal feature which can change at any point of time. I think most products that I know normally don't even expose the database, or they don't document either.
Authentication sources that use the internal SQL databases should be updated when you upgrade and there are changes.
I'm not sure when I last had to change my external queries, but API or built-in features would have preference, if you use direct SQL access you would need to do proper testing when you upgrade ClearPass if your queries still work. And custom SQL queries for external syslog (note: these do not log into an external database, they pull additional attributes from the Insight Database that may not be available by default) are probably not needed.
I agree that you should avoid them whenever possible, and in most cases you can. This is more like a corner-case feature in my view.
Consider us a corner-case! I believe we'll use APIs eventually but not at this time.
I've been asking how to query ClearPass from the external database. Whereas, I should have been asking how ClearPass can query the external PostgreSQL database and update it, not the external database connecting into ClearPass and pulling information out.
Is this done by creating the external database as an auth source and applying the custom SQL filters under the attributes tab? If so, how does this tie in with the service? As currently we have a service for eduroam authentication proxy to FreeRADIUS (with the accounting checkbox enabled). Will we need to have a separate service now for the accounting which will be dealt with directly by ClearPass - which we will then be able to apply the filters for custom SQL queries in order to update the external database. Instead of combining the authentications and accounting messages in a single service, considering auths will continue to proxy to FreeRADIUS, but the accounting messages will be dealt with by ClearPass.
Updating an external SQL database from ClearPass on accounting is not a feature in ClearPass. You could open a feature request in the Aruba Innovation zone.
ClearPass stores accounting data in the Insight Database, which you can query through API (or Postgres SQL); and you can send the accounting data out as syslog messages.
If you need the same data in another database, you can use either option to receive syslog and have a process the writes in your database, or create something that replicates through API calls to ClearPass and SQL INSERTS to your database. Or have your frontend query the ClearPass Insight API or SQL database as appexternal to avoid the replication.
Hi Herman, Do you have any further advice regarding my previous message, please?
© Copyright 2023 Hewlett Packard Enterprise Development LPAll Rights Reserved.