Video Screencast Help
Endpoint Management Community Blog

Want to know all of the rules that have ever been triggered on an incident?

Created: 27 Dec 2006 • Updated: 11 Jun 2007 • 3 comments
SandyF's picture
0 0 Votes
Login to vote

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     

  1. Open up the Notification Server and create a new report and drop this code into it.
  2. Create a parameter (local or global).
    1. Call the parameter WorkNum (note that parameter names are case-sensitive so match it exactly).
    2. Select Basic for the Parameter type.
    3. Check "Prompt user for value when report is run".
    4. Put in something for the User prompt like "Incident Number:".
    5. Select Number as the Value type.
    6. Put in a reasonable default value like a recently created incident number.
  3. 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.

Comments 3 CommentsJump to latest comment

jgo's picture

Notes : I couldn't get this report to run and after every attempt I received an error about either rules or number.. so I had to drop the tables from SQL. So I just changed the code over to use temp tables... and it has been working.

Also make sure that you change the reference to incidents, to reference your altiris helpdesk database.

SELECT IDENTITY(INT) AS Number     
   INTO #Numbers     
/*Changed to Temp table with the addition of # */
        
CREATE UNIQUE CLUSTERED INDEX Number_ind     
   ON #Numbers(number)     
/*Changed to Temp table with the addition of # */
   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   
/*Changed to Temp table with the addition of # */  
FROM HD_workitem_detail_view     
INNER JOIN #Numbers   
/*Changed to Temp table with the addition of # */  
    ON SUBSTRING( ',' + workitem_rules_fired, Number, 1 ) = ','      
WHERE Number <Len(workitem_rules_fired) + 1     
   AND workitem_number like '%WorkNum%'
/*Changed to like to allow for more data to come back with %.*/
     
--Get Rule Names      
SELECT workitem_number as 'Incident',      
           WUCI_NAME as 'Rule',     
           Run as 'Run on'      
FROM #Rules  /*Changed to Temp table with the addition of #*/   
INNER JOIN 
/* Make sure you change Altiris_Incidents below to be your DB Name*/ 
Altiris_Incidents.dbo.WUCI_HISTORY     
/* Make sure you change Altiris_Incidents Above to be your DB Name*/ 
    ON [ID] = Value       
     
drop table #Numbers     
drop table #Rules   

John Golembiewski
Midwest Practice Principal
ITS Partners
Jgo@itsdelivers

-4
Login to vote
David Ekstrom's picture

Thanks for this. A couple of changes I had to make:

1) I needed to add "HD_" to prefix the workitem detail table.
INTO Rules
FROM HD_workitem_detail_view

2) The default Incidents db is "Altiris_Incidents" so the line should read:
INNER JOIN
Altiris_Incidents.dbo.WUCI_HISTORY

Now it works fine!

-1
Login to vote
MeinMK5's picture

Below is a simplified version of the above and should be a simple copy and paste into query analyzer:

SELECT wi.version AS 'Entry Number'
, wh.wuci_name AS 'Rule Name'
, wi.modified_on AS 'Time Fired'
FROM wuci_history wh
, workitem wi
WHERE ',' + wi.rules_fired + ',' LIKE '%,'+ CAST(wh.id AS VARCHAR(20)) + ',%'
AND wi.number = InsertWorkitemNumberHere

+1
Login to vote