AAA, NAC, Guest Access & BYOD

The following filter queries in the authorization source "[Time Source]" is fetching date time from insighdb in UTC. How to fetch Date/Time in different time zones other than UTC?

Sample Output:

insightdb=> select date_trunc('hour', localtimestamp(0)) as today,
date_trunc('hour', localtimestamp(0)+ interval '1 days') as one_day,
date_trunc('hour', localtimestamp(0)+interval '1 week') as one_week,
date_trunc('hour', localtimestamp(0)+ interval '1 month') as one_month,
date_trunc('hour', localtimestamp(0)+ interval '6 months') as six_months;
        today        |       one_day       |      one_week       |      one_month      |     six_months
---------------------+---------------------+---------------------+---------------------+---------------------
 2016-12-20 19:00:00 | 2016-12-21 19:00:00 | 2016-12-27 19:00:00 | 2017-01-20 19:00:00 | 2017-06-20 19:00:00

The below output will confirm the default time of ClearPass database is in UTC.

insightdb=> select now();
             now
------------------------------
 2016-12-20 19:51:01.14256+00
 

From the ClearPass version 6.6.x and higher, the database time is set to UTC by default despite of whatever the time zone the server is in. The data will be stored in UTC and fetched in the same format, so It will not affect any functionalities in the server.

However, when you try to validate date/time from an external server against local db date/time, you may need conversion from UTC to local time.

For example: Consider the "Endpoint:Last Check In" time fetched from MDM server is in IST and you need convert the local DB time to IST to validate the "last check in" time should be within last 24 hours.

Last Check In attribute sample from Configuration >> Identity >> Endpoints.

The below filter query will help you to convert the local time to IST.

# select localtimestamp at time zone '+05:30' ;

    timezone
 2016-12-21 01:42:29.834674+00
 

The query can further be modified to achieve the above requirement.

# select date_trunc('hour', (localtimestamp(0) at time zone '+05:30') - interval '1 days') AS last_24hours;

   last_24hours
 2016-12-20 01:00:00+00
 

Another example:

# select date_trunc('hour', (localtimestamp(0) at time zone '+05:30') + interval '12 hours') AS now_plus_12hours;

    now_plus_12hours
2016-12-21 13:00:00+00

The filter query can be added in the [TIme Source] as shown below.

Below is an example condition to use the filter query result in an Enforcement Policy  to check whether the endpoint last check in time is within last 24 hours and allow access.