How can I add a specific Category to a report Query to create a Notification Policy
Updated: 23 May 2010 | 1 comment
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
discussion Filed Under:
Comments
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.
Would you like to reply?
Login or Register to post your comment.