1.5.1 Last Login Time Query

This example illustrates how to filter results using a comparison. The query below returns all users who have not logged in since July 8, 1999 at 6:45 pm. Notice how the literal timestamp value is expressed. It is contained within braces which serve as escape sequence delimiters. The ts indicates the value is a timestamp. The actual string representing the timestamp is contained within the single quotes.

SQL Command

   SELECT CN, lastLoginTime FROM inetOrgPerson WHERE
   lastLoginTime < {ts ’1999-07-08 18:45:00.00’}
   

Report

1.5.2 Sorting Query

This example illustrates the use of the ORDER BY clause to sort a table on multiple columns.

SQL Command

   SELECT NDS_Context, SN FROM inetOrgPerson ORDER BY
   NDS_Context, SN
   

Report

1.5.3 ACL Attribute Query

This sample illustrates the break up of the ACL attribute into multiple columns. The ACL_Trustee, ACL_Attribute, ACL_Read, ACL_Write columns all come from the value of the ACL attribute. For a complete list of all ACL columns see the Data Type Mappings table. A value of true (one) in a privilege column such as ACL_Read or ACL_Write indicates the privilege is granted to the indicated trustee. A value of false (zero) indicates the trustee does not have that privilege. The OU (Organization Unit) attribute is the naming attribute for the Organization class.

An ACL attribute query requires the 8.5x version of eDirectory. To obtain a beta copy, see the Novell's public beta site:

      http://qasupport.provo.novell.com/beta/public/
   

SQL Command

   SELECT OU, ACL_Trustee, ACL_Attribute, ACL_Read, ACL_Write
   FROM OrganizationalUnit
   

Report

1.5.4 Restricted Effective Rights Query

The query below illustrates how to perform several restrictions when querying the Effective Rights table. First, the like operator is used to restrict the trustees to those in the FewUsers Organization. The Object is restricted to dward0.FewUsers by a simple equality comparison. Finally, the attributes are limited to [Entry Rights]. This query lists the rights of the trustee to operate on the dward0.FewUsers entry as a whole. The query is further restricted to retrieve the value of only the Browse and Delete privileges. The result table illustrates the typical situation where all trustees have browse privileges but none have delete privileges.

The Effective Rights table requires the 8.7 version of eDirectory. To obtain a Beta copy, see the Novell web site (http://www.novell.com/).

SQL Command

   SELECT ObjectName, TrusteeName, Attribute, Browse, "Delete"
   FROM EffectiveRights 
   WHERE TrusteeContext = ’ou=Sales,o=Fred’’s Widgets’ 
   AND objectName = ’cn=bjones’
   AND Attribute = ’[Entry Rights]’
   

The query produces a result table similar to the following. The zeros in the rights columns mean FALSE (the trustee does not have this right), and the ones, TRUE (the trustee does have this right).

Report

1.5.5 Join Query

The following query joins a base class table with an auxiliary class table. It reads the O column which is part of the Organization table and the Synchronized Up To_Time column which is part of the Partition table.

   SELECT O, "Synchroinzed Up To_Time"
   

   FROM Organization, Partition
   

   WHERE Organization.NDS_FullName=Partition.NDS_FullName