Security

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. 

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? 

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? 

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. 

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? 

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.

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? 

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. 

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? 

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.

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? 

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. 

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? 

You said:

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).

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.

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? 

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. 

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? 

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.

You said:

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).

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.

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? 

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. 

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? 

Hi Herman, 

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.

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.

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. 

Thanks, Adam 

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.

You said:

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).

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.

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? 

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. 

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? 

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.

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? 

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. 

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?