How to use dates in reports, web query columns and filters

Summary: This article will explain how to use Date\Time attributes in filters and columns in automation, reporting and web query rules.   

Applies to: Cayosoft Administrator 6.3.1 or later

ID: KB20190909-1

---

There are multiple attributes in Active Directory that store Date/Time value, such as accountExpires, lastLogonTimestamp or pwdLastSet. That Date/Time value is stored in Active Directory in a form of large integer value, which is hard to read and use in filters. In Cayosoft Administrator, in addition to these AD attributes you can also use a "wrapper property" - a wrapper that provides the same value in a more useful Date/Time format. Instead of using the AD attribute, please use the corresponding wrapper property, as described below. 

AD Attribute	Wrapper Property
pwdLastSet	PasswordLastSet
accountExpires	AccountExpirationDate
whenCreated	CreationTimeStamp
whenChanged	ModifiedTimeStamp
lastLogonTimeStamp	LastLogonDate

 

Filtering by dates

Example 1: Use AD Users reporting rule to list all user accounts whose password was changed in the last 5 days.

We will use PasswordLastSet attribute wrapper to get the date when the user's password was changed.

1. In Cayosoft Administrative console navigate to AD Users report rule
2. Browse for Other Query Settings section
3. Add PasswordLastSet to the Properties to display setting
   
4. In Initialization Script set this script: 

   {$global:DatePeriod = (Get-Date).AddDays(-5)}

5. In Query Criteria click "..." and build the following query criteria: (PasswordLastSet - Not equal - "Null") And (PasswordLastSet - Greater than or equal - $DatePeriod)
6. Click Save Changes

 

Example 2: Use AD Users reporting rule to list all user accounts whose password was last changed before 09/01/2019.

1. Repeat steps 1-3 from Example 1.
2. In Initialization Script set this script: 

   {$global:DatePeriod = (Get-Date 09/01/2019)}

3. In Query Criteria specify this filter: (PasswordLastSet - Not equal - "Null") And (PasswordLastSet - Less than - $DatePeriod)
4. Click Save Changes

 

Adding column with Date\Time property to the report output

Example: Use AD Users reporting rule to list all user with the columns, displaying when user last changed his password and when account is set to expire.

1. In Cayosoft Administrative console navigate to AD Users report rule
2. Browse for Other Query Settings section
3. To display Date\Time attribute as a report column, add the corresponding attribute wrappers PasswordLastSet and AccountExpirationDate to Properties to display.
4. Click Save Changes

 After that, AD Users report will have PasswordLastSet and AccountExpirationDate columns.

 

Adding a column with Date\Time property to the web query grid view

Example: In AD Users web query add columns, displaying when a user last changed his password and when an account is set to expire. 

1. In Administrator Console navigate to Home > Configuration > Web Portal > Virtual Admin Units.
2. Click Active Directory > AD Users web query.
3. Browse for the More Options section.
4. To display the Date\Time attribute as a web query column, add the corresponding attribute wrappers PasswordLastSet and AccountExpirationDate to Properties to display.
5. Click Save Changes.

After that, AD Users web query will have PasswordLastSet and AccountExpirationDate columns.

 

 

---

Related Articles

KB20180410-1 How to use Criteria Builder operators for Query Criteria and Filter rule settings