I have come across the same problem, unfortunately it's really hard to get the "Closed" date because there isn't a field for this in Altiris, just modified on(which you know). You really just need to notify the people getting this report that there isn't a concret way to get the closed date, they need to assume (which is bad) that the last modified date was when it was closed.

The way I work this is to use (or join) the hd_workitem_current_view to search for incidents modified between 2 dates with the status currently as "Closed". The last time those incidents were modified "should" be the closed date. Hope this helps, let me know if it doesn't.

Hi,

Thanks for replying.

I had to go for this:-

I look at the status of the call - if this is closed then I select back the highest version number where the version action is 'closed'.

This seems to do it for me. Our calls are being reopened, but we're trying to stop this practice!!

Try to use the distinct command in your query so you only get each ticket number once. That coupled with the max version and action=Closed should eliminate the duplicates/re-opened tickets.

Yep, already am.

The only problem we're having now, is with 'Quick Incidents', where a call can be created and closed in the same action. This means that there is an action of 'Create' and a status of 'closed'. I'll just have to deal with these seperately.

If I get what your saying, you should be able to add an 'OR' statement in your SQL to include those opened closed incidents.

For Example:
w/out OR statement:
select workitem_number
from hd_workitem_whatever_view
where *your code here*

w/ OR statement:
select workitem_number
from hd_workitem_whatever_view
where (*your code here* OR (workitem_status_lookup_id = 600
AND workitem_action = 'create'))

I can go into the details a little more if you would like to do this, I just don't have a whole lot of time today to write it up.