Intel,Altiris Group

I would like to create an automated report to email directors daily/weekly based off of a current Helpdesk Worker Report query, but when I copy the sql out after selecting 'last query results' 'save query' the sql in the 'expression' box will not work in a report where I select 'enter sql directly' and copy in the expression. There must be a better way... Once I have the report, I know how to set up the automated email

I am using Notification Server 6.0.6074(SP 3 +R11)

Unfortunately the SQL used in helpdesk doesn't always work when you try to use it as a real query against an SQL database.  Part of that is also the fact that inside Helpdesk it executes queries against the "Altiris_incidents" database and the Notification Server executes against the "Altiris" database.

Once you have a report that works you just need to setup a notification policy with an e-mail report link. 

What kind of report are you looking for I could help you build it if you'd like?

Assistance would be great!
I would like the report to present the following information:
1) Ticket #
2) Priority
3) Short Description of ticket
4)Time spent on the ticket
5) (this would probably be part of the automated email) Link to ticket within Altiris for additional information

I would like to present the emailed report in these 3 groups:

Active, Ongoing Tasks and Closed Today
1) Active is not closed and not considered ongoing
2) Ongoing is not closed and flagged as ongoing

I would also like to set up the report to query for individual technicians

Here's the basic SQL for this.  I'm not sure how you're flagging "ongoing", but hopefully this gets you along the way.

Select hd._ResourceGuid,
hd.workitem_number AS 'Ticket #',
hd.workitem_priority_lookup_value AS 'Priority',
hd.workitem_title AS 'Title',
hd.workitem_total_minutes_spent AS 'Time spent'

From hd_workitem_current_view as hd

Where hd.workitem_status_lookup_id != '600'

So if your "Ongoing" is another status you'll just need to modify the where statement to also exclude the lookup ID for your status like the '600'  is the id for the "closed" status.

By adding the _resourceGuid field it makes the basic report linkable on the NS. 

Now that you have a report you'll just need to use a "Notification Policy" to e-mail it to the appropriate people.  There should be some articles on that here on Connect.

This should get you started.  Let me know if you need more help. 

Thanks! This is a great start. can you assist with a few other details?

How can the report be specific to a worker (nt user account might be dbparfet)

How to set the report to pull data from today only? (including time spent today only)
Can comments (up to 200 characters) saved today be displayed? The time attributed to this comment also?
The default report format is left to right; is it possible to display the comment detail above under the line that lists the main ticket information. My manager would like:

Ticket Number -- Status -- Title
Today's time spent -- on this,Today's Comment
Repeated for each comment for this ticket today

Can the report be set up to present 3 separate sections (or would this be better done from the email automation configuration... I suppose this could be done by creating 3 sql reports perhaps and combine them in the automated email?)
Tickets not closed today
Tickets 'Ongoing' today - Status Ongoing ID is 375
Tickets status changed today to 'Closed'

Don't know if it would help or not...but I think you can add a "USE Altiris_Incidents" statement at the top of your report/notification policy to execute against the Helpdesk db (we don't have Helpdesk here, just going by general SQL writing).  Alternatively, in your FROM you can specify the SQL host and database:

SELECT ...
FROM [SQLSERVER\Instance].Altiris_Incidents.dbo.hd_workitem_current_view
JOIN ...
WHERE ...

etc.

Note you'll have to specify the full server name and database on each FROM or JOIN statement.

Also to get the three reports in one...not sure you can do this with the basic Notification policy.  If all the result sets have the same column names and field definitions (int, nvarchar(100), datetime, etc) then you can use the UNION operator to merge them all, but I think the auto-formatting of the Notification Policy will merge them all into one long table, if you use the %Results% operator.  Of course you could get trickier using the %DS:ColumnName% stuff...

Edit: corrected syntax on FROM statement.