IBM Support

How to find out all the records which are NOT in "Closed" state using ClearQuest APIs and SQL query

Technote (FAQ)


How do you find out all the records which are NOT in "Closed" state using IBM Rational ClearQuest APIs and SQL query?


This is required to get an instant and a brief report on the records which are not yet Closed without logging into IBM Rational ClearQuest Client.


The Perl Script below uses the ClearQuest API and can be run on Windows.

Note: The ClearQuest Native Client must be installed on this machine.

This script iterates through all the ClearQuest Records to find all the Records which are not in Closed state. This script will print Record ID, Owner of the Record and Owner's Email to the command prompt.

Steps to run this script:

  1. Navigate to the location where CQperl.exe is installed.
    By default: C:\Program Files\IBM\RationalSDLC\ClearQuest\CQperl.exe

  2. Save the following Perl script text to notepad and save with a .pl extension.


    All source code and/or binaries attached to this document are referred to here as "the Program". IBM is not providing program services of any kind for the Program. IBM is providing the Program on an "AS IS" basis without warranty of any kind. IBM WILL NOT BE LIABLE FOR ANY ACTUAL, DIRECT, SPECIAL, INCIDENTAL, OR INDIRECT DAMAGES OR FOR ANY ECONOMIC CONSEQUENTIAL DAMAGES (INCLUDING LOST PROFITS OR SAVINGS), EVEN IF IBM, OR ITS RESELLER, HAS BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES.

    #!C:\Program Files\IBM\RationalSDLC\ClearQuest\CQperl.exe
    # Make sure you have correct path for CQperl.exe

    use CQPerlExt;

    # Create a Rational ClearQuest admin session
    my $sessionObj = CQSession::Build();

    # Logon as admin
    # Provide correct values for admin password, User Database, and Schema Repository name
    $sessionObj->UserLogon("admin", "<AdminPassword>", "<UserDatabase>",

    my $sqlstring = "select distinct T1.dbid,,T8.login_name,,T8.fullname from Defect
    1,users T8,statedef T2 where T1.owner = T8.dbid and T1.state = and (T1.dbid <> 0 and
    ( <> 'Closed' or T1.state = 0)))";
    my $ResultSet = $sessionObj-> BuildSQLQuery($sqlstring);

    $recordCount = $ResultSet->GetRecordCount();

    for ($i=0;$i<$recordCount;$i++) {
    $status = $ResultSet->MoveNext();

    $ClearQuestID[$i]= $ResultSet->GetColumnValue(2);
    $Owner[$i] = $ResultSet->GetColumnValue(3);
    $OwnerEmail[$i] = $ResultSet->GetColumnValue(4);
    print "ID-$ClearQuestID[$i]  *** Owner-$Owner[$i] *** Email-$OwnerEmail[$i]\n";
    #Unbuild the Admin session

  3. Run the script using the following command:

    cqperl <>

Document information

More support for: Rational ClearQuest

Software version:,,

Operating system(s): Windows

Reference #: 1646732

Modified date: 23 August 2013