Video Screencast Help
Symantec to Separate Into Two Focused, Industry-Leading Technology Companies. Learn more.

Notification Policy Queries always get no results

Created: 20 Feb 2013 • Updated: 11 Mar 2013 | 19 comments
This issue has been solved. See solution.

We are trying to enable our first Notification Policy to modify Incidents on a schedule but the Queries are failing to pull anything. I have tested the queries by copying them over to HelpDesk and in SQL (with a few modifications for the environment) and they work fine but when I run them as a Notification Policy in Tasks I get no results, just "This Notification Policy has been executed however the query did not return any rows, so none of the policy actions will perform their configured duties".

For reference, here is my query:

SELECT T0.[workitem_number] AS 'workitem_number'

FROM [HD_workitem_current_view] T0

WHERE T0.[workitem_status_lookup_id] = 550

AND T0.[workitem_assigned_to_worker_id] IN (361,263,300,354,350,256,392,266,326,313,417,342,281,250,258,269,316,192,439,306,359,233,346,401,467,412,441,297,319,365,340,328,398,377,344,348,275,272,278,443,374)

AND DATEDIFF(DAY, T0.[workitem_modified_on], GETDATE()) > 16

I initially built the query with straight SQL but then went back and built it with the Query Builder and got almost identical code. Both queries run fine in SQL on the server. I even tried removing the time paramter to increase the results but it made no difference. I have even tried running the queries in some of the built in and they also get no results.

I found some reference in this forum about 'enabling the client' if you have not been using Notification Policy Queries (in this case for Carbon Copy). Is there some setting I am missing?

Operating Systems:

Comments 19 CommentsJump to latest comment

Rob Moore's picture

Where do you have Notification policy? is it under Alert Manager or HelpDesk? And do you have any parameters set up?

rob

CygnusX-1's picture

It is under HelpDesk.  It was initially built with a fixed variable for the number of days but I changed it to get rid of the parameter in case that was causing an issue (as you can see above '16' is now in the query instead of the paramater name).

Rob Moore's picture

The only thing that comes to mind is credentials. What creds are you running this under? And does that account have permissions to the HD / Tickets?

I've checked the SQL and it looks good. I've run it against my DB (minus the Worker ID) and I get results from both the console and the SQL server.

Is your applicaton Identity defined as a worker in the HelpDesk?

rob

CygnusX-1's picture

My account has full admin rights on the server, as I did not setup the server I am not sure what account the Notification Policy is trying to run under.  Is there a quick way to check?

Rob Moore's picture

Policies run under the application identity, so you can check that under Configuration -> Server Settings -> Notification Server Settings -> application Identity.

Make sure that account is a worker in the HD. This is just a shot in the dark, but it's really the only thing left to check.

rob

CygnusX-1's picture

The account is in the standard Windows 'Administrators' group on the SQL server and in the 'Altiris Administrators' group on the NS.  The 'Altiris Administrators' should have all the rights that any 'Workers' group would have right?

Rob Moore's picture

That depends. Is the account defined as a worker in the helpdesk?

CygnusX-1's picture

Yes, it is in the list of workers in HelpDesk and the worker is marked Active.

Rob Moore's picture

So now I'm almost out of ideas.

If I understand this correctly, when you run the query in the SQL manager it returns results, but when you run it in the policy itself, it returns no results. Do I have that right, you are running the exact same query? When you run the policy, is there anything in the Logs?

What happens if you run it against the Incidents database instead of the views in the Altiris database?

As a last check, if you use this same SQL to create a report, does the report return any results?

I am assuming you have the Synchronize resource and incident data with Notification Server option checked under the Icident settings configuration...

rob

CygnusX-1's picture

When I run the query in SQL I have been changing HD_workitem_current_view to workitem_current_view.  I didn't realize that there was a View in the Altiris database, I spend all my time in the Altiris_Incidents table.

It seems the HD_workitem_current_view is quite messed up, the data in the columns are not correct, what should be a GUID is showing a date etc.

We do not have the Synchronize resource and incident data with Notification Server option checked.  Do you think that would get the data syncing properly again?

Rob Moore's picture

Turning on the syc will move data from the NS into the helpdesk, and from the helpdesk into the views in the NS. I believe this will also synch contact data (if you are importing AD users). The NS sends computer/Asset records and Userinfo into the Helpdesk, and the helpdesk will simply update the views in the Altiris system.

If you don't want to turn this on (and I'm not sure what the damage will be, if any - I'd be worried about the contacts info being updated), you can simply run the query against the NS incindets DB, you don't have to run them against the NS view. Just include the name of the incidents DB in front of the table name:

SELECT T0.[workitem_number] AS 'workitem_number'

FROM [<Incidents DB Name>.dbo.workitem_current_view] T0

WHERE T0.[workitem_status_lookup_id] = 550

AND T0.[workitem_assigned_to_worker_id] IN (361,263,300,354,350,256,392,266,326,313,417,342,281,250,258,269,316,192,439,306,359,233,346,401,467,412,441,297,319,365,340,328,398,377,344,348,275,272,278,443,374)

AND DATEDIFF(DAY, T0.[workitem_modified_on], GETDATE()) > 16

since the 2 DB's are on the same SQL server and serviced by the same account, you should be able to read the data from it no problem.

rob

*updated to use your query instead of the bogus one I had.

The Gaffer's picture

This sometimes happens when you customize the workitem table in Helpdesk. The views on the Notification Server database get out of sync with the views in the Helpdesk database. It is caused by the fact that SQL Server establishes the schema of the view when it is first defined. Changes to the underlying tables can cause the views to go out of sync. There is a stored procedure in SQL which you can use to get SQL Server to re-evalute the schemas of each view.

EXEC sp_refreshview HD_workitem_current_view

You will need to run this for each of the HD views in the NS database which is out of synch,

SOLUTION
CygnusX-1's picture

Much better solution than having to rebuild every query.  Should probably update this KB article https://www-secure.symantec.com/connect/articles/how-we-customized-our-helpdesk since I followed it to make our customizations.

CygnusX-1's picture

Any way to force it to use the Incidents database instead of Altiris?

Rob Moore's picture

Yes, that is what I wrote above. Use the Incident databae name and dbo in front of the table name:

<Incidents DB Name>.dbo.Workitem_current_view

CygnusX-1's picture

Sorry, I posted that before your reponse was visible. I tried that and it fails to run, I get 'Invalid Object Name'. It seems the connection the NS is making is specifically to the Altiris database and the connection can't see the <Incidents DB Name> database.

I have tried the alternate method, figuring out the column name for the data I need. Unfortunately it appears the workitem_status_lookup_id data isn't being copied into the view.

Is there a way to access the View definition and see where each field is being pulled from? I am guessing it is mapped to the <Incidents DB Name> database but the mappings are wrong. There doesn't appear to be anywhere in the Altiris database that contains a copy of the Incidents data.

Rob Moore's picture

Why don't you give me a call?

You should be able to pull it directly. I can show you my config on my server and you can compare it to yours. I have a GTM account we can use.

Rob

CygnusX-1's picture

Thanks for the offer... I just figured it out

USE <Incidents DB Name>

SELECT T0.[workitem_number] AS 'workitem_number'

FROM [workitem_current_view] T0

WHERE T0.[workitem_status_lookup_id] = 550

AND T0.[workitem_assigned_to_worker_id] IN (361,263,300,354,350,256,392,266,326,313,417,342,281,250,258,269,316,192,439,306,359,233,346,401,467,412,441,297,319,365,340,328,398,377,344,348,275,272,278,443,374)

AND DATEDIFF(DAY, T0.[workitem_modified_on], GETDATE()) > 16