January 25, 2008
Email sign up
Automation Rules Report
Helix ALM
Works with TestTrack 2008 and later
Will not work with TestTrack 7.6 and earlier
Here you'll find a sample T-SQL query and Excel report that you can use to report on your TestTrack Studio automation rules.
This was written for TestTrack Studio 2008, and won't work with earlier versions. With 2008, we completely re-did the automation rules table structure so it's not even close to what you'll see if you're using an earlier version. You should upgrade, the 2008 release is really cool!
Download an Excel sample report. View in SQL Server Reporting ServiceQuery Limitations
- Does not filter by object type (defect, test case, test run) or display object type information. If you want to do that, you can add Triggers.EntityType to the WHERE or add another column that displays which entity the trigger is setup on.
- Will not show all of the recpients of an email or auto-assign if there are more than 1. You could use a UDF/SP to do that, wouldn't be difficult.
MS SQL Server
This was written against SQL Server 2005, will probably work against other versions as well.select
tr.name as 'Name',
isnull(convert(varchar(16),tr.createdate,101), '<not set>') as 'Created',
isnull(convert(varchar(16),tr.lasteval,101), '<never>') as 'Last Executed',
isnull(uOwnr.name,'<not set>') as 'Owner', -- Should be the user that created it.
isnull(f.name,'<none>') as 'Filter', -- Filter, if one is set
case enabled
when 1 then 'Active'
else 'Inactive'
end as 'Active?',
trigorder as 'Order',
case ruletype -- 4 types of triggers
when 1 then 'Notification'
when 2 then 'Trigger (pre-save)'
when 3 then 'Trigger (post-save)'
when 4 then 'Escalation'
else '<unknown>'
end as 'Type',
case -- action that fires the trigger
when condtype = 1 then 'State Transition'
when condtype = 2 then 'Defect Created'
when condtype = 3 then 'Defect Merged'
when condtype = 4 then 'Defect Assigned a Number'
when condtype = 5 then 'Defect Renumbered'
when condtype = 6 then 'Defect Changed'
when condtype = 7 then 'Defect Event Changed'
when condtype = 8 then 'Defect Assigned'
when condtype = 9 then 'Test Run Generated'
when tr.ruletype = 4 then
case (select period from trigschd schd where schd.projectid = ta.projectid and schd.triggerid = tr.idrecord)
when 1 then 'Weekly'
when 2 then 'Monthly'
when 3 then 'Manually'
else 'Daily'
end
else '<n/a>'
end as 'Run Condition',
case ta.type -- Action type. ODBC UG is incorrect on these value mappings
when 5 then 'Event'
when 3 then 'Notify'
when 4 then 'Modify'
when 1 then 'Run'
when 2 then 'Prevent'
else '<unknown>'
end as 'Action',
case -- Any further details we can provide about the rule?
when ta.type = 5 then 'Apply event ' + (select name from events e where e.projectid = ta.projectid and e.idrecord = ta.actoptid)
when ta.type = 3 then 'Email template: ' + (select name from emailtmp e where e.projectid = ta.projectid and e.idrecord = ta.actoptid)
when ta.type = 2 then ta.actoptstr
when ta.type = 1 then ta.actoptstr
else '<n/a>'
end as 'Action Details',
case -- If we're notifying people, what's the logic?
when ta.type = 3 then -- Email recipient(s)
(select top 1 -- Many-to-one, so let's just grab the first one. These equate to the boxes checked on the email dlg.
case target -- We could use a sp or udf, to get all of these values on one line.
when 1 then 'EnteredBy'
when 2 then 'AssignedTo'
when 3 then 'FoundBy'
when 4 then 'ModifiedBy'
when 5 then 'LastUserEvent'
when 6 then 'User(s)'
when 7 then 'Group'
when 8 then 'CurrentUser'
when 9 then 'Unassigned'
else '<unknown>'
end
from actntgt act where act.projectid = ta.projectid and act.actionid = ta.idrecord)
else '<n/a>'
end as 'Notification Recipient(s)',
-- Various check-box values for the action, could be cleaned up with better de-limiting
case ta.onlyifasgn when 1 then '-Only Notify Assigned User-' else '' end +
case ta.entifasgnd when 1 then '-Only if Assigned-' else '' end +
case ta.ownchgs when 1 then '-Notify Self on Changes-' else '' end +
case ta.trackemail when 1 then '-Track Email-' else '' end as 'Action Flags',
-- Various check-box values for the trigger, could be cleaned up
case stopifpass when 1 then '-Stop Processing on Pass-' else '' end +
case applyonce when 1 then '-Apply Once-' else '' end +
case runonce when 1 then '-Run Once-' else '' end +
case runonimp when 1 then '-Run on Import-' else '' end as 'Trigger Flags'
from trgactn ta, triggers tr
left join (
select idrecord, projectid, name from filter
) f
on tr.projectid = f.projectid and tr.filterid = f.idrecord
left join (
select idrecord, projectid, firstname + ' ' + lastname as 'name' from users
) uOwnr
on tr.projectid = uOwnr.projectid and tr.ownerid = uOwnr.idrecord
where
tr.projectid = ta.projectid
and tr.idrecord = ta.triggerid
order by tr.idrecordMySQL
This was written against MySQL 5.0, will probably work against other versions as well.select
tr.name as 'Name',
ifnull(date_format(tr.createdate, '%m/%d/%Y'), '<not set>') as 'Created',
ifnull(date_format(tr.lasteval, '%m/%d/%Y'), '<never>') as 'Last Executed',
ifnull(uOwnr.name,'<not set>') as 'Owner', -- Should be the user that created it.
ifnull(f.name,'<none>') as 'Filter', -- Filter, if one is set
case enabled
when 1 then 'Active'
else 'Inactive'
end as 'Active?',
trigorder as 'Order',
case ruletype -- 4 types of triggers
when 1 then 'Notification'
when 2 then 'Trigger (pre-save)'
when 3 then 'Trigger (post-save)'
when 4 then 'Escalation'
else '<unknown>'
end as 'Type',
case -- action that fires the trigger
when condtype = 1 then 'State Transition'
when condtype = 2 then 'Defect Created'
when condtype = 3 then 'Defect Merged'
when condtype = 4 then 'Defect Assigned a Number'
when condtype = 5 then 'Defect Renumbered'
when condtype = 6 then 'Defect Changed'
when condtype = 7 then 'Defect Event Changed'
when condtype = 8 then 'Defect Assigned'
when condtype = 9 then 'Test Run Generated'
when tr.ruletype = 4 then
case (select period from trigschd schd where schd.projectid = ta.projectid and schd.triggerid = tr.idrecord)
when 1 then 'Weekly'
when 2 then 'Monthly'
when 3 then 'Manually'
else 'Daily'
end
else '<n/a>'
end as 'Run Condition',
case ta.type -- Action type. ODBC UG is incorrect on these value mappings
when 5 then 'Event'
when 3 then 'Notify'
when 4 then 'Modify'
when 1 then 'Run'
when 2 then 'Prevent'
else '<unknown>'
end as 'Action',
case -- Any further details we can provide about the rule?
when ta.type = 5 then concat('Apply event ', (select name from events e where e.projectid = ta.projectid and e.idrecord = ta.actoptid))
when ta.type = 3 then concat('Email template: ', (select name from emailtmp e where e.projectid = ta.projectid and e.idrecord = ta.actoptid))
when ta.type = 2 then ta.actoptstr
when ta.type = 1 then ta.actoptstr
else '<n/a>'
end as 'Action Details',
case -- If we're notifying people, what's the logic?
when ta.type = 3 then -- Email recipient(s)
(select -- Many-to-one, so let's just grab the first one. These equate to the boxes checked on the email dlg.
case target -- We could use a sp or udf, to get all of these values on one line.
when 1 then 'EnteredBy'
when 2 then 'AssignedTo'
when 3 then 'FoundBy'
when 4 then 'ModifiedBy'
when 5 then 'LastUserEvent'
when 6 then 'User(s)'
when 7 then 'Group'
when 8 then 'CurrentUser'
when 9 then 'Unassigned'
else '<unknown>'
end
from actntgt act where act.projectid = ta.projectid and act.actionid = ta.idrecord limit 1)
else '<n/a>'
end as 'Notification Recipient(s)',
-- Various check-box values for the action, could be cleaned up with better de-limiting
concat(case ta.onlyifasgn when 1 then '-Only Notify Assigned User-' else '' end,
case ta.entifasgnd when 1 then '-Only if Assigned-' else '' end,
case ta.ownchgs when 1 then '-Notify Self on Changes-' else '' end,
case ta.trackemail when 1 then '-Track Email-' else '' end) as 'Action Flags',
-- Various check-box values for the trigger, could be cleaned up
concat(case stopifpass when 1 then '-Stop Processing on Pass-' else '' end,
case applyonce when 1 then '-Apply Once-' else '' end,
case runonce when 1 then '-Run Once-' else '' end,
case runonimp when 1 then '-Run on Import-' else '' end) as 'Trigger Flags'
from trgactn ta, triggers tr
left join (
select idrecord, projectid, name from filter
) f
on tr.projectid = f.projectid and tr.filterid = f.idrecord
left join (
select idrecord, projectid, firstname + ' ' + lastname as 'name' from users
) uOwnr
on tr.projectid = uOwnr.projectid and tr.ownerid = uOwnr.idrecord
where
tr.projectid = ta.projectid
and tr.idrecord = ta.triggerid
order by tr.idrecordNote: Seapine does not provide support for sample reports.