At Altiris I wrote a report for myself to see all of the rules, whether they were Notification Rules, Routing Rules, Incident Rules or Automation Rules, that had been triggered on an incident and on what date.
Here is the code I used:
SELECT IDENTITY(INT) AS Number INTO Numbers FROM sysobjects s1 CROSS JOIN sysobjects s2 CREATE UNIQUE CLUSTERED INDEX Number_ind ON 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 Rules FROM HD_workitem_detail_view INNER JOIN 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 Rules INNER JOIN Altiris_IS_Incidents.dbo.WUCI_HISTORY ON [ID] = Value drop table Numbers drop table Rules
- Open up the Notification Server and create a new report and drop this code into it.
- Create a parameter (local or global).
- Call the parameter WorkNum (note that parameter names are case-sensitive so match it exactly).
- Select Basic for the Parameter type.
- Check "Prompt user for value when report is run".
- Put in something for the User prompt like "Incident Number:".
- Select Number as the Value type.
- Put in a reasonable default value like a recently created incident number.
- Save the report and give it a run.
By the way, if the report gets interrupted there is a chance you will get an error the next time you try to run it that the "Numbers" or the "Rules" table(s) already exist. Temporarily COPY the last two line of the code, the drop table commands, to the top of the report and run the report one time. Then remove the lines you just added and run it again. The errors should be gone.