From Seapine Labs

Contents 1 Overview 2 Query Limitations 3 MS SQL Server 4 MySQL

[edit] Overview

Here you'll find a sample T-SQL query and Excel report that you can use to report on your TestTrack Studio automation rules.

Download an Excel sample report.

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!

[edit] Query 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.

[edit] 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.idrecord

[edit] MySQL

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.idrecord

Note: Seapine does not provide support for sample reports.