How can I add a specific Category to a report Query to create a Notification Policy

Paul Manfredi's picture
Paul Manfredi
April 2nd, 2008

I'm trying to create a notification policy that can source a report to tell me all the ASAP incidents that have not been set to open after 15 minutes.
So far, so good, here is the query I'm using with an age Global Parameter of 15 min:

SELECT DISTINCT hd1.[workitem_number] as 'Id',        
	CONVERT(VARCHAR(50),hd1.[workitem_created_on]) as 'Create Date',        
	CONVERT(VARCHAR(50),hd1.[workitem_modified_on]) as 'Last Modified',        
	datediff(%Time Unit%,hd1.[workitem_created_on],hd1.[workitem_modified_on]) as 'Age(%Time Unit%)',        
	isnull(dbo.fnLocalizeString('listitem.'+ hd1.[workitem_status_lookup_value], '77b56ff3-33e5-4bb0-bca6-de0e6dbb3cba', '%_culture%'), hd1.[workitem_status_lookup_value]) as 'Status',        
	isnull(dbo.fnLocalizeString('listitem.'+ hd1.[workitem_priority_lookup_value], '77b56ff3-33e5-4bb0-bca6-de0e6dbb3cba', '%_culture%'), hd1.[workitem_priority_lookup_value]) as 'Priority',        
  hd1.[workitem_category_tree_value] as 'Category',        
	isnull(dbo.fnLocalizeString('report.'+ hd1.[assigned_to_worker_name] , '77b56ff3-33e5-4bb0-bca6-de0e6dbb3cba', '%_culture%'),hd1.[assigned_to_worker_name]) as 'Worker',        
	hd1.[workitem_title] as 'Title'        
FROM dbo.HD_workitem_current_view hd1        
WHERE datediff(dd, hd1.[workitem_created_on], getdate()) <= %Number Days%        
	AND hd1.[workitem_status_lookup_id] IN (200)        
	AND hd1.[workitem_priority_lookup_id] IN (100)   
	AND datediff(%Time Unit%, hd1.[workitem_created_on], getdate()) >= %Age%        
ORDER BY 'Age(%Time Unit%)' DESC

My question is; Can I insert a specific Category in the query, in my case, two, (%Break-Fix%, and %Security%). IN order to generate an email Automated Action for those categories only. Help Please.

Paul Manfredi

Filed under: ,
0 votes
drew.ohara's picture
drew.ohara
38 weeks 3 days ago

Adding the Category Field to your WHERE statement

You should be able to with no problems. In my organization the field is called 'workitem_category_tree_value'(I'd make sure your system has the same field/db in Altiris_incidents. You can use it in your WHERE statement to only search for incidents with those categories. The sql you'd need to add is:

 

AND (hd1.[workitem_category_tree_value] like '%Break-Fix%'

 OR hd1.[workitem_category_tree_value] like '%Security%')

 

Let me know if this helps.

0 votes