White Papers
Abstract
This white paper explains how to achieve automation of sending a query result report through an email to specified users in IBM Rational ClearQuest.
Content
Author: Manoj Goyal
Table of Contents:
Introduction IBM Rational ClearQuest Installation Creating a ClearQuest User Database Automating the report generation and notification THE INFORMATION CONTAINED IN THIS DOCUMENT IS PROVIDED FOR INFORMATIONAL PURPOSES ONLY. WHILE EFFORTS WERE MADE TO VERIFY THE COMPLETENESS AND ACCURACY OF THE INFORMATION CONTAINED IN THIS DOCUMENT, IT IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, EXPRESS OR IMPLIED. IN ADDITION, THIS INFORMATION IS BASED ON IBM’S CURRENT PRODUCT PLANS AND STRATEGY, WHICH ARE SUBJECT TO CHANGE BY IBM WITHOUT NOTICE. IBM SHALL NOT BE RESPONSIBLE FOR ANY DAMAGES ARISING OUT OF THE USE OF, OR OTHERWISE RELATED TO, THIS DOCUMENT OR ANY OTHER DOCUMENTATION. NOTHING CONTAINED IN THIS DOCUMENT IS INTENDED TO, NOR SHALL HAVE THE EFFECT OF, CREATING ANY WARRANTIES OR REDOCUMENTS FROM IBM (OR ITS SUPPLIERS OR LICENSORS), OR ALTERING THE TERMS AND CONDITIONS OF ANY AGREEMENT OR LICENSE GOVERNING THE USE OF IBM PRODUCTS OR SOFTWARE.
Introduction
IBM Rational ClearQuest allows writing queries in Clients. Using filters in these queries, desired records can be fetched from ClearQuest Database. Further, the query results can be exported to spreadsheet and mailed as an attachment to specific users. This whole process is manual and time consuming.
This white paper explains how to customize and automate this process. This solution enables to run a Public or Private folder query (static filters only) using ClearQuest APIs, capture the query results in html format and email the html report to specific users. In this white paper you will learn how to use ClearQuest APIs to login, to perform specific tasks in ClearQuest, create a quick report on Records and to send email notifications. This mechanism uses Perl as the scripting language and IBM Rational ClearQuest APIs.
How does this solution work
This solution uses CQperl as the scripting language and IBM Rational ClearQuest APIs.
This solution works in the following way:
Pre-Requisites
To use this solution IBM Rational ClearQuest must be installed and configured on a Microsoft Windows computer.
To use this solution IBM Rational ClearQuest must be installed and configured on a Microsoft Windows computer.
The basis of the integration is to use CQPerl which comes as an in-built package with IBM Rational ClearQuest installation. You must install the IBM Rational ClearQuest Windows Client or the eclipse client to be able to create queries.
If you have installed IBM Rational ClearQuest Web components, then queries can also be created using ClearQuest Web.
IBM Rational ClearQuest Installation
Install IBM Rational ClearQuest on Microsoft Windows. For detailed steps refer to the Rational ClearQuest product documentation.
Creating a Schema Repository
An existing Schema Repository can be used, if any. Otherwise, a new Schema Repository must be created.
You can find information on creating a schema repository here.
Creating a ClearQuest User Database
Once you have created a schema repository; you should create User Databases. The information on how to create a User Database can be found here.
Creating a Query
Create a query with necessary records and filters. There is a reference of this activity here.
For the purpose of this white paper a Personal query named "AllSubmitted" for the Record Type Defect has been created. This query returns all the Defects which are in Submitted state.
Note -
(1.) If you want to create a query in Public Folder, you must have "Public Folder" privileges.
(2.) Further, you can build queries programmatically and use them in your script accordingly. For this you may need to modify the script in your way.
Working with queries
Fig 1: Example query named “AllSubmitted” for the Record Type Defect in Submitted state
Automating the report generation and notification
Topics for automating the report generation and notification:
Verify Cqperl installation
Make sure that CQPerl is installed on your computer as a part of ClearQuest installation. To verify CQPerl is installed, run the following command on the command prompt.
>cqperl -v
Output of this command would look like:
This is perl, v5.8.6 built for MSWin32-x86-multi-thread
Copyright 1987-2004, Larry Wall
Perl may be copied only under the terms of either the Artistic License or the
GNU General Public License, which may be found in the Perl 5 source kit.
Complete documentation for Perl, including FAQ lists, should be found on
this system using `man perl' or `perldoc perl'. If you have access to the
Internet, point your browser at http://www.perl.org/, the Perl Home Page.
If you are not getting such output, please check if CQPerl.exe is available in your default installation directory-
C:\Program Files\IBM\RationalSDLC\ClearQuest\CQperl.exe
If you have changed the default installation directory, please check if this file is there. If you do not find CQPerl.exe anywhere in your system, please reinstall ClearQuest again.
Note – To get more information on Cqperl, use the following command-
>cqperl -V
Perl Module
In this example we have used Perl module MIME::Lite to send emails. This module is not installed with the default installation of ClearQuest. You must install this module explicitly on your computer.
Reference:
http://search.cpan.org/~rjbs/MIME-Lite-3.029/lib/MIME/Lite.pm
http://cpansearch.perl.org/src/RJBS/MIME-Lite-3.029/lib/MIME/Lite.pm
Note: The purpose of using Perl Module MIME::Lite is to send the HTML file as an attachment. ClearQuest API MailMsg is not having this feature as of now.
PackageRev Object
Writing the Perl Script
Open a text editor and paste the following code. Save the file with .pl extension.
For example, Reporttest.pl.
● Ensure to supply the correct values to the highlighted variables below based on your environment.
For Example- adminPasswd, UserDatabase, SchemaRepoName, SMTP server name, etc.
########################################
#!C:\Program Files\IBM\RationalSDLC\ClearQuest\CQperl.exe
# Make sure correct path of CQperl.exe has been given
use CQPerlExt; # Needed for general CQ API calls.
# The below variables @ClearQuestID, @Headline, @Severity and @State correspond to the display fields of the stored query.
my @ClearQuestID = ();
my @Headline = ();
my @Severity = ();
my @State = ();
# Building a ClearQuest session
my $session = CQPerlExt::CQSession_Build();
# Providing "admin" credentials to login to the schema
# Please note that the logon user does not need to be "admin"; it can be any user who can access the ClearQuest and have permissions to run the desired query.
$session->UserLogon("admin","<adminPasswd>","<UserDatabase>","<SchemaRepoName>");
# Returns the session's Workspace object
my $workspace = $session->GetWorkSpace();
# Returns the QueryDef associated with the specified query.
# Specify your query path if other than Personal Queries/AllSubmitted
my $querydef = $workspace->GetQueryDef("Personal Queries/AllSubmitted");
# Creating the resultset and getting the total number of records in the result
my $resultset = $session->BuildResultSet($querydef);
$resultset->EnableRecordCount();
$resultset->Execute();
$recordCount = $resultset->GetRecordCount();
# Iterate through the resultset to fetch information for each record
for ($i=0;$i<$recordCount;$i++) {
$status = $resultset->MoveNext();
# Column 1 is used by ClearQuest itself. Resultset starts with Column 2.
# Please check the results of your query in client
# Make sure these fields correspond to the correct column
$ClearQuestID[$i]= $resultset->GetColumnValue(2);
$Headline[$i] = $resultset->GetColumnValue(3);
$Severity[$i] = $resultset->GetColumnValue(4);
$State[$i] = $resultset->GetColumnValue(5);
}
# Generating a html file with the result set
my @data =();
for ($i=0;$i<$recordCount;$i++) {
push(@data, '<br /><table border=\"2\" CELLSPACING=\"10\">');
push(@data, "<tr><td><b>ClearQuest ID -</b> $ClearQuestID[$i]</b></td>\n");
push(@data, "<td><b>State -</b> $State[$i]</td></tr>\n");
push(@data, "<tr><td><b>Headline -</b> $Headline[$i]</td>\n");
push(@data, "<br /><td><b>Project -</b> $Severity[$i]</td></tr>\n\n");
chomp(@data);
}
#html file will be created in the current directory
#In this example current directory is C:\Test
open (FH, '>SubmittedRecords.html');
foreach (@data){
print FH "$_";
}
close FH;
sendEmail();
# Unbuild the session
CQPerlExt::CQSession_Unbuild($session);
sub sendEmail {
$data1 = "Defects listed in the attachment are in the “Submitted” state !<br />";
# $data1 .= "<br />Regards,\n";
# $data1 .= "<br />CQ-Admin<br />";
$subject ="$recordCount Defects in Submitted state";
# The email will be sent from do.not.reply@test.com id. You can modify it as per your needs.
my $from = ' do.not.reply@test.com';
# Please provide a correct email id here
# This could be a group email id also if you want to send email to a group of users. Please modify it accordingly.
my $to = 'testuser@test.com';
my $data = "$data1";
# ccfield is commented here, if you want to have someone here, please specify the email id.
# my $ccfield = '';
# use strict;
use MIME::Lite;
# SendTo email id
# create a new MIME Lite based email
my $msg = MIME::Lite->new
(
Subject => "$subject",
From => "$from",
To => "$to",
Cc => "$ccfield",
Bcc => '',
Type => 'text/html',
Data => "$data"
);
$msg->attach (
Type => 'text/html',
Path => 'C:\Test\SubmittedRecords.html',
Filename => 'SubmittedRecords.html',
Disposition => 'attachment'
);
#Provide correct name of your SMTP server
$msg->send('smtp', "smtp.mail.test.com", Timeout=>60);
}
#############################################
Execution
Topics for Execution:
Executing the script
● Go to the directory where you have saved the Perl file and run it.
>cqperl ReportTest.pl
Note: You can modify this Perl code by adding some print statements to check the data during run time.
Output File
A sample of the file created by this script-
Fig 2: Sample html file generated through the script
Sample Email
Fig 3: Sample Email format which is sent through the script which includes report file as an attachment
Scheduling the task
You can customize the script to include any of your custom queries and email notify the users. Further, you may schedule to run this script on a daily, weekly or monthly basis using the Windows Scheduler.
One can access Task Scheduler in the following way-
(I) Start → All Programs → Administrative Tools → Task Scheduler
OR
(II) Start → Control Panel → System and Security → Administrative Tools → Schedule Task
More Information on Scheduling a task on Windows can be found here.
References
Conclude the document by recapping what was accomplished. If necessary, include a glossary of important terms.
Original Publication Date
14 November 2014
Was this topic helpful?
Document Information
Modified date:
17 June 2018
UID
swg27042148