How to see all of the Helpdesk rules that have ever triggered on an incident?

Article:HOWTO6181  |  Created: 2006-12-27  |  Updated: 2006-12-27  |  Article URL http://www.symantec.com/docs/HOWTO6181
Article Type
How To


Question
How can you report all of the rules that have ever been triggered on an incident?

Answer

This report will work whether they were Notification Rules, Routing Rules, Incident Rules or Automation Rules, that had been triggered on an incident.  It will also show on what date.

1.  Open up the Notification Server and create a new report and drop the following code into it. 

use altiris_incidents -- Change this to whatever database you use for Helpdesk Incidents

if exists (SELECT * FROM INFORMATION_SCHEMA.TABLES where table_name = 'Temp_Numbers') drop table Temp_Numbers
if exists (SELECT * FROM INFORMATION_SCHEMA.TABLES where table_name = 'Temp_Rules') drop table Temp_Rules

SELECT IDENTITY(INT) AS Number    
   INTO Temp_Numbers    
   FROM sysobjects s1    
  CROSS JOIN sysobjects s2    
    
CREATE UNIQUE CLUSTERED INDEX Number_ind    
   ON Temp_Numbers(number)    
   WITH IGNORE_DUP_KEY    
    
SELECT top 1000 workitem_number, convert(char(10),workitem_modified_on,101) as 'Run',    
       CONVERT(int,SUBSTRING( workitem_rules_fired, Number,     
       CHARINDEX( ',', workitem_rules_fired + ',', Number ) - Number )) as Value    
INTO Temp_Rules    
FROM workitem_detail_view    
INNER JOIN Temp_Numbers    
    ON SUBSTRING( ',' + workitem_rules_fired, Number, 1 ) = ','     
WHERE Number <= Len(workitem_rules_fired) + 1    
   AND workitem_number = %WorkNum%     
    
--Get Rule Names     
SELECT workitem_number as 'Incident',     
           WUCI_NAME as 'Rule',    
           Run as 'Run on'     
FROM Temp_Rules    
INNER JOIN Altiris_Incidents.dbo.WUCI_HISTORY    
    ON [ID] = Value      
    
if exists (SELECT * FROM INFORMATION_SCHEMA.TABLES where table_name = 'Temp_Numbers') drop table Temp_Numbers
if exists (SELECT * FROM INFORMATION_SCHEMA.TABLES where table_name = 'Temp_Rules') drop table Temp_Rules

______________________________

2.  Create a parameter (local or global).
    a.  Call the parameter WorkNum (note that parameter names are case-sensitive so match it exactly).
    b.  Select Basic for the parameter type.
    c.  Check "Prompt user for value when report is run".
    d.  Put in something for the user prompt like "Incident Number".
    e.  Select Number as the value type.
    f.  Put in a reasonable default value such as "1".
3.  Save the report and give it a run.


Legacy ID



31381


Article URL http://www.symantec.com/docs/HOWTO6181


Terms of use for this information are found in Legal Notices