How to parse query results from initial selections

General question about the package, problems, solutions
Chin May
Posts: 10
Joined: Mon Jun 04, 2012 2:48 pm

How to parse query results from initial selections

Postby Chin May » Mon Jun 04, 2012 3:00 pm

Hello:

I created an email notification rule and got it to work. I have a problem though: I am not sure how to parse the results of query?

This is what I want to do:
1. Run ever 24 hours and find *all* records that match a certain criteria (I created a query for it called it "DailyReport"
2. I want to email the results of this query to a person.
So, this is what I did:
[*]First, I created a query called "DailyReport" and saved it in PublicQuery\DailyReport
[*]Second, I added udb property (TN_Query-Defect: PublicQuery\DailyReport)
[*]Third, I created email rule with PesudoAction "DailyReportNotification"
[*]Fourth, I set condition = $headline ne ' '
(basically, I want to select all results of the query, so I created a condition that would match everything)
[*]Fifth, In "Subject & Body" tab, I don't know how to parse/iterate through results of this query and insert it in body of email. If I don't do anything, then it sends a blank email. If I write something like:

Code: Select all

Here are query results:
Headline : $headline
Defect : $defect_number

then it only shows the first result (the first record of the query). But there are many results. So, how do I display all results from the query?

What I want is something like this:

Code: Select all

Here are query results:
record # 1
Headline : $headline[1]
Defect : $defect_number[1]
------------------------------
record # 2
Headline : $headline[2]
Defect : $defect_number[2]


Basically, *all* records in one email.
Please help.

Pavel
Posts: 281
Joined: Fri Dec 12, 2008 12:19 am

Re: How to parse query results from initial selections

Postby Pavel » Mon Jun 04, 2012 3:11 pm

Chin May wrote:[*]Fourth, I set condition = $headline ne ' '
(basically, I want to select all results of the query, so I created a condition that would match everything)

There is no need. Empty condition field would match everything.

Chin May wrote:then it only shows the first result (the first record of the query). But there are many results. So, how do I display all results from the query?

Timed notifications have different purpose: they trigger email rules for individual records. If you would like to send consolidated reports, the easiest way would be to create new Record type (for instance, reports) where you could specify reports or queries that you would like to execute and send to particular address(es), and configure timed notification for that record type instead.

Pavel

Chin May
Posts: 10
Joined: Mon Jun 04, 2012 2:48 pm

Re: How to parse query results from initial selections

Postby Chin May » Mon Jun 04, 2012 3:17 pm

Pavel wrote:Timed notifications have different purpose: they trigger email rules for individual records. If you would like to send consolidated reports, the easiest way would be to create new Record type (for instance, reports) where you could specify reports or queries that you would like to execute and send to particular address(es), and configure timed notification for that record type instead.

Pavel

Thanks a lot for helping me out. Sorry, I am new to CQ in general. I am not sure I understand this. How do I create a new record type that consolidates results? If you could point me to some help documentation for this, that'd be nice.

After I create such a record, how would I configure email notification to send emails to result of this new record type?

Pavel
Posts: 281
Joined: Fri Dec 12, 2008 12:19 am

Re: How to parse query results from initial selections

Postby Pavel » Mon Jun 04, 2012 3:30 pm

Chin May wrote:Thanks a lot for helping me out. Sorry, I am new to CQ in general. I am not sure I understand this. How do I create a new record type that consolidates results? If you could point me to some help documentation for this, that'd be nice.

You can check CQ Designer documentation. Basically, what would you need to to is to create stateless record type that at least would have some unique ID (name), and another field that would carry path to the query that you would like to execute. It might also make sense to add "Active" field that would allow you to deactivate some reports without deleting them to the database.
You can easily add desired queries or reports to the list for regular notification by submitting new records of the defined record types.

Chin May wrote:After I create such a record, how would I configure email notification to send emails to result of this new record type?

When your record type is created, you can configure timed notification for all active "report" records to run query and send results to desired addressee.

I provided you with information that should be sufficient to implement notification solution that would satisfy your requirements. Providing you with completed implementation is beyond the free community support level. It can be done on commercial basis only, sorry.

Thank you,
Pavel

Chin May
Posts: 10
Joined: Mon Jun 04, 2012 2:48 pm

Re: How to parse query results from initial selections

Postby Chin May » Mon Jun 04, 2012 3:34 pm

Actually, I looked into it. And found out how to create a new report and new report format. But I need Crystal Reports for that. Is it not possible to simply parse/read the results of the query and send them all in body of one email using some Perl code?

Basically, all I want to do is: run a query (publicquery\dailyreport) that shows all open defects. And email the result of this query to pre-specified email address. So, if I am able to run this query using Email Notification Package, there ought to be a way to display all these results into one email?

Maybe the results of query become stored in some object like QueryResults?
And then I can simply loop throughQueryResults?
For eg, something like this:

Code: Select all

$QueryResults->MoveNext() == $CQPerlExt::CQ_SUCCESS

Chin May
Posts: 10
Joined: Mon Jun 04, 2012 2:48 pm

Re: How to parse query results from initial selections

Postby Chin May » Mon Jun 04, 2012 3:41 pm

Pavel wrote:I provided you with information that should be sufficient to implement notification solution that would satisfy your requirements. Providing you with completed implementation is beyond the free community support level. It can be done on commercial basis only, sorry.
Thank you,
Pavel


Thanks, Pavel.
I checked CQ documentation and learned that to create a new report you need to do two things:
1. select the query (I aleady created a query)
2. select the report formtat (but to create a report format, I need to author a report format using Crystal Reports and I don't have it installed).
I sent you a PM about commercial support level/paid support. But not sure if you got it. It shows in my "outbox" instead of "sent" items.

Pavel
Posts: 281
Joined: Fri Dec 12, 2008 12:19 am

Re: How to parse query results from initial selections

Postby Pavel » Tue Jun 05, 2012 1:56 pm

One of the options, if you have CM Server (CQWeb) version 7.1 or later, would be using data-pull reports. You could send report URL to the users in this case.

Another option would be to create a new ClearQuest entity in the schema, new record type, to manage queries that you would like to execute automatically and send results to the users.
For instance, this record type can have the following fields:
Name (SHORT_STRING) - unique identifier of the scheduled query
Frequency (SHORT_STRING) - choice list: daily, weekly, monthly, etc
Active (INT) - active/inactive 1/0 checkbox to deactivate entry without removing it from the database
Path (MULTILINE_STRING) - path to the query in the workspace. Multiline type is preferred to avoid 254 symbols limitation.

After creating the record type, applying Email Notification package to the record, and upgrading user database, you can configure timed notifications for the new record type. The scope can include all active records of the new record type. No condition is required. The following user-defined function can be used to execute query and extract query result in the form of HTML table (we can call it "RETURN_QUERY_RESULTS_AS_HTML_TABLE"):

Code: Select all

my $query_path = shift;

# change path to UNIX style
$query_path =~ s/\\+/\//g;
# resulting HTML table
my $table = "<TABLE BORDER=\"1\" CELLPADDING=\"10\">\n";
# query fields
my @fields     = ();
# get workspace
my $ws         = $session->GetWorkSpace();
# get query definition
my $qdef       = $ws->GetQueryDef($query_path);
my $field_defs = $qdef->GetQueryFieldDefs();
my $count      = $field_defs->Count();
for( my $i = 1; $i < $count; $i++ ){
  my $field_def = $field_defs->Item($i);
  my $name      = $field_def->GetLabel();
  my $type      = $field_def->GetFieldType();
  push(@fields, { name => $name, type => $type });
}

# Create Table Header row
$table .= '<TR>';
foreach my $field (@fields){
  $table .= "<TH><b>$field->{name}</b></TH>";
}
$table .= "</TR>\n";

# execute query
my $resultset = $session->BuildResultSet($qdef);
$resultset->Execute();

# create data rows
while( $resultset->MoveNext() == $CQPerlExt::CQ_SUCCESS ){
  $table .= '<TR>';
  for( my $i = 2; $i <= $count; $i++ ){
    my $value = $resultset->GetColumnValue($i);
    $table .= "<TD>$value</TD>";
  }
  $table .= "</TR>\n";
}

$table .= "</TABLE>\n";
#return HTML table
return $table;


Then, in the timed notification rule body field you can use something like:

Code: Select all

<HTML><BODY>
something you would like to to say about scheduled query $Name
RETURN_QUERY_RESULTS_AS_HTML_TABLE($Path)
</BODY></HTML>

You need to add Content-Type: text/html; charset=us-ascii to the rule's Header Add-in field to specify content type as well.

Timed Notification triggers the rule, all query records are selected, query executed and formatted as HTML table, and email is sent to the users.
From that moment, it is easy to maintain: you can add new scheduled queries just by submitting new records of the "scheduled queries" record type and specifying new query path, and you can update content of the emails by editing queries in the workspace.

Pavel

Chin May
Posts: 10
Joined: Mon Jun 04, 2012 2:48 pm

Re: How to parse query results from initial selections

Postby Chin May » Tue Jun 05, 2012 3:24 pm

I've a follow-up question: where does this user-defined function go and how does it get executed? How can it be simply called from Rule-Body?

I know I can create "hooks". But where will I put this user-defined-function code? In some hook? If so, then how will this hook get executed on scheduled time? Or did you mean, I can put this user defined function as sort of a "global script" and then call it from Rule Body?

You have taught me something new! So, once again, thanks a lot!
Pavel wrote:you can add new scheduled queries just by submitting new records of the "scheduled queries" record type and specifying new query path,

Not sure I understood this fully.

Pavel wrote:and you can update content of the emails by editing queries in the workspace.

editing queries in workspace? Not sure I understood this.

Pavel
Posts: 281
Joined: Fri Dec 12, 2008 12:19 am

Re: How to parse query results from initial selections

Postby Pavel » Tue Jun 05, 2012 3:45 pm

Chin May wrote:I've a follow-up question: where does this user-defined function go and how does it get executed? How can it be simply called from Rule-Body?

Creating user-defined function for the package means submitting udb_property record with function name as the record name and function code as the record value.
Please check this presentation, page 14, for details.

Chin May wrote:
Pavel wrote:you can add new scheduled queries just by submitting new records of the "scheduled queries" record type and specifying new query path,

Not sure I understood this fully.

You created a record type, lets call it "scheduled_queries". Now, you can submit new records of this type for all different kind of reports that you need to generate. Each record would have it's own name, and it would have path to the query you created in the public workspace (Public Queries) that you would like to execute regularly to get and send data.
Timed notification would be triggered for all scheduled_queries records, for each record query will be executed, formatted, and sent as email.

Chin May wrote:
Pavel wrote:and you can update content of the emails by editing queries in the workspace.

editing queries in workspace? Not sure I understood this.

It means that you can edit query you saved in "Public Queries", add fields, rename column labels, change sort order and/or filer, etc. No email notification rule modification is required, all changes will take an effect immediately after the query is saved.

Pavel

Chin May
Posts: 10
Joined: Mon Jun 04, 2012 2:48 pm

Re: How to parse query results from initial selections

Postby Chin May » Mon Jun 11, 2012 4:06 pm

Thanks a lot for your help, Pavel. This is EXTREMELY helpful! can't thank you enough.

It seems like your code is missing something though. In the new record type, you asked me to create a field for "Frequency". But the perl-code you attached does not look into "frequency" field at all. So, what's the point of creating that field?

Also, the querypath field: should the path be entered in double quotes? (Path is a reserved field, so I changed Path to QueryPath in my record) For eg:

Code: Select all

"Public Queries\MyDailyQuery"

or

Code: Select all

Public Queries\MyDailyQuery


Also, I keep getting this error ("Create default query") when I try to run timednotification (following steps from your post):

Code: Select all

C:\>cqperl TimedNotification.pl "username" "password" "DB" "DBSMSM" "DailyNotification"
Start: 2012-06-11 16:40:08
Create default query
End  : 2012-06-11 16:40:09


This query was working before on "Defect" record type. But ever since I added this new record type, I have started getting this error. I created the new record type following your instructions. Saved it.
- created the fields like you posted
- added the record type to TN_RecordTypes (udb_property) record
- added a user defined function/perl code myfunc { to udb_property as well
- created new email notification rule with
pseudoaction: DailyNotification
html header
body:
<

Code: Select all

HTML><BODY>
something you would like to to say about scheduled query $Name
myfunc($QueryPath)
</BODY></HTML>

Pavel
Posts: 281
Joined: Fri Dec 12, 2008 12:19 am

Re: How to parse query results from initial selections

Postby Pavel » Mon Jun 11, 2012 5:25 pm

Chin May wrote:Also, the querypath field: should the path be entered in double quotes? (Path is a reserved field, so I changed Path to QueryPath in my record) For eg:

Code: Select all

"Public Queries\MyDailyQuery"

or

Code: Select all

Public Queries\MyDailyQuery



No double quotes are required in the field.

Chin May wrote:Also, I keep getting this error ("Create default query") when I try to run timednotification (following steps from your post):

Code: Select all

C:\>cqperl TimedNotification.pl "username" "password" "DB" "DBSMSM" "DailyNotification"
Start: 2012-06-11 16:40:08
Create default query
End  : 2012-06-11 16:40:09


This query was working before on "Defect" record type. But ever since I added this new record type, I have started getting this error. I created the new record type following your instructions.



you need to configure timed email notification for the newly created record type. Take "defect" configuration as an example.

Chin May wrote:
- created the fields like you posted
- added the record type to TN_RecordTypes (udb_property) record
- added a user defined function/perl code myfunc { to udb_property as well

You do not need to add "myfunc", just function body. Everything in red on the slide was "imaginary".
You can also take a look in the other examples, here: http://cqadmin.org/wiki/Notification_Examples

Pavel

Chin May
Posts: 10
Joined: Mon Jun 04, 2012 2:48 pm

Re: How to parse query results from initial selections

Postby Chin May » Tue Jun 12, 2012 10:53 am

Hello:

Thanks. But it just hangs now. To make it easy for everyone, I am attaching screenshots of all the steps I took below.

1. I created a new record type called "EmailReports" and a record form. I created the fields as you mentioned
(Name(short string), QueryPath(multiline string), Frequency(short string), Active(int))
Screenshot below.
Image

2. I created new UDB property TN_RecordTypes as below:
Image

3. Created a default query as below:
Image

4. Created a user defined function called FORMAT_QUERY_AS_HTML as below:
Image

5. Created new email rule as below:
Image
and called FORMAT_QUERY_AS_HTML($QueryPath)
Image

6. Executed email rule using command line (triggering pseudoaction "DailyNotification")
Image

But it didn't work.
Did I miss any step or did I do something wrong anywhere?

Pavel
Posts: 281
Joined: Fri Dec 12, 2008 12:19 am

Re: How to parse query results from initial selections

Postby Pavel » Tue Jun 12, 2012 11:09 am

Chin May wrote:3. Created a default query as below:
Image

If you run this query, does it return your EmailReports record?

Chin May wrote:6. Executed email rule using command line (triggering pseudoaction "DailyNotification")
Image

But it didn't work.
Did I miss any step or did I do something wrong anywhere?

Are there any messages created in the email queue (en_email_message records)?
Also, you can try running TimedNotification in debugger to see what is going wrong in your case.

Pavel

Chin May
Posts: 10
Joined: Mon Jun 04, 2012 2:48 pm

Re: How to parse query results from initial selections

Postby Chin May » Tue Jun 12, 2012 4:16 pm

Pavel wrote:
Chin May wrote:3. Created a default query as below:
Image

If you run this query, does it return your EmailReports record?

yes, if I run this public-query, it shows list of records in CQ.

Pavel wrote:
Chin May wrote:6. Executed email rule using command line (triggering pseudoaction "DailyNotification")
Image

But it didn't work.
Did I miss any step or did I do something wrong anywhere?

Are there any messages created in the email queue (en_email_message records)?
Also, you can try running TimedNotification in debugger to see what is going wrong in your case.

Pavel

No. No email messages are created ever since I changed the TN_RecordType's value to "EmailReports". Email was being sent, and email records were being created when I was using "Defect" as TN_RecordTypes.

What's the command/flag for running TimedNotification.pl in debug mode?
cqperl timednotification.pl --debug " " " " " " "DailityNotification"
Something like that?

Pavel
Posts: 281
Joined: Fri Dec 12, 2008 12:19 am

Re: How to parse query results from initial selections

Postby Pavel » Wed Jun 13, 2012 6:52 am

Chin May wrote:
Pavel wrote:
Chin May wrote:3. Created a default query as below:
Image

If you run this query, does it return your EmailReports record?

yes, if I run this public-query, it shows list of records in CQ.

Does it show a list of EmailReports records?

Chin May wrote:
Pavel wrote:
Chin May wrote:6. Executed email rule using command line (triggering pseudoaction "DailyNotification")
Image

But it didn't work.
Did I miss any step or did I do something wrong anywhere?

Are there any messages created in the email queue (en_email_message records)?
Also, you can try running TimedNotification in debugger to see what is going wrong in your case.

Pavel

No. No email messages are created ever since I changed the TN_RecordType's value to "EmailReports". Email was being sent, and email records were being created when I was using "Defect" as TN_RecordTypes.

What's the command/flag for running TimedNotification.pl in debug mode?
cqperl timednotification.pl --debug " " " " " " "DailityNotification"
Something like that?

Something like cqperl -d timednotification.pl " " " " " " DailityNotification
You can use some IDE. For instance, Eclipse + EPIC plugin, which is free, or any of the commercial IDE for Perl (Komodo, etc).
You can also add debug print to the script.

Pavel

Chin May
Posts: 10
Joined: Mon Jun 04, 2012 2:48 pm

Re: How to parse query results from initial selections

Postby Chin May » Wed Jun 13, 2012 8:59 am

Pavel wrote:
Chin May wrote:
Pavel wrote:If you run this query, does it return your EmailReports record?

yes, if I run this public-query, it shows list of records in CQ.

Does it show a list of EmailReports records?

No, that query shows a list of "Defects" (not a list of EmailReports). Because EmailReports only contains list of public-queries that I want to execute. These public-queries are from Defect record type.

For Debugger: I am not familiar with running a debugger but if you tell me which line numbers should I add "print statement", I will add it.

Pavel
Posts: 281
Joined: Fri Dec 12, 2008 12:19 am

Re: How to parse query results from initial selections

Postby Pavel » Wed Jun 13, 2012 9:21 am

Chin May wrote:
Pavel wrote:Does it show a list of EmailReports records?

No, that query shows a list of "Defects" (not a list of EmailReports)

This property, TN_Query-{record type}-{pseudo action name}, must contain a query that defines scope for the timed notification rule, i.e. EmailReports records that will be processed by the timed notification script. Since you provided there a query for a different record type, none of the EmailReports records are selected for timed notification.

Pavel

Chin May
Posts: 10
Joined: Mon Jun 04, 2012 2:48 pm

Re: How to parse query results from initial selections

Postby Chin May » Wed Jun 13, 2012 11:00 am

Pavel wrote:
Chin May wrote:
Pavel wrote:Does it show a list of EmailReports records?

No, that query shows a list of "Defects" (not a list of EmailReports)

This property, TN_Query-{record type}-{pseudo action name}, must contain a query that defines scope for the timed notification rule, i.e. EmailReports records that will be processed by the timed notification script. Since you provided there a query for a different record type, none of the EmailReports records are selected for timed notification.

Pavel

wow! it works now. thanks!


Return to “Email Notification Package Q&A”

Who is online

Users browsing this forum: No registered users and 1 guest

cron