History Table Incidents Database

gtoast · 09-12-2007, 04:14 PM

Does anyone know where I can find the closed date/time value of an Incident in the Altiris_Incidents database? I have looked trough all the tables without any success. I can find the created and modified but not the closed. I assume it is part of the history with I have yet to find as well. Any help would be greatly appreciated.

Thanks,

G

dominique · 09-12-2007, 06:44 PM

Hello,

There is no "Closed date" it is the "workitem_modified_on" which is used in relation with the status changed to "closed".

The information will be in the workitem_detail_view for the history, and in the workitem_current_view for the current information:

Two reports are running against these views they are under Repoprt --> Incident Management--> Incidents-->Helpdesk --> Helpdesk Activity. I have cutomized them for our environment by cloning them and removing extra information, the first one will give you the current status :
- Incident current status:

Code:

SELECT hd1.[workitem_number] as 'Id',  
 hd1.[workitem_created_on] as 'Create Date',  
 hd1.[workitem_modified_on] as 'Last Modified',  
 hd1.[workitem_status_lookup_value] as 'Status',  
 hd1.[workitem_priority_lookup_value] as 'Priority',  
 hd1.[workitem_category_tree_value] as 'Category',  
 hd1.[workitem_title] as 'Title'  
FROM dbo.HD_workitem_current_view hd1  
WHERE hd1.[workitem_status_lookup_value] LIKE 'closed'
ORDER BY 'Id'

the seocnd one will give you the incidents which have been closed or resolved then change to another status and back again to closed or resolved
- Incidents Resolved/Closed More Than Once

Code:

SELECT hd1.[workitem_number] as 'Id',
 hd1.[workitem_version] as 'Seq',
 hd1.[workitem_modified_on] as 'Date',
 hd1.[workitem_title] as 'Title',
 hd1.[workitem_status_lookup_value] as 'Status',
 hd1.[workitem_priority_lookup_value] as 'Priority',
 hd1.[workitem_category_tree_value] as 'Category'
FROM dbo.HD_workitem_detail_view hd1
WHERE datediff(dd, hd1.[workitem_created_on], getdate()) <= 365
 AND hd1.[workitem_status_lookup_value] LIKE 'closed'
 AND hd1.[workitem_number] IN (
  SELECT w1.workitem_number FROM HD_workitem_detail_view w2
  INNER JOIN HD_workitem_detail_view w1 ON w1.workitem_number = w2.workitem_number AND w1.workitem_version = w2.workitem_version + 1 AND 
    w2.workitem_status_lookup_id IN (100, 200, 300) AND w1.workitem_status_lookup_id IN (400, 500, 600)
  GROUP BY w1.workitem_number HAVING (COUNT(*) > 1)
 )
ORDER BY 'Id', 'Seq'

You could edit these queries and customized them for your project. By sorting by date you will have the last "closing date" which will be the last modified date with the status changed to "closed"

Thanks,

gtoast · 09-13-2007, 05:39 PM

Thank you for the quick reply - I was able to get the proper close date from the workitem_detail_view as you pointed out. The probem we were having is that tickets sometimes get modified after the close date. Corrections and so on. I have pasted the sql code incase anyone else runs into the same issue - from this code I was able to slice it by queue, worker and so on -

select cv.workitem_number as 'Ticket', cv.contact_name as 'User', dv.workitem_modified_on as 'Closed Date', cv.workitem_modified_on as 'Last Modified Date',
cv.workitem_status_lookup_value as 'Current Status', cv.assigned_to_worker_name as 'Assinged Worker',
cv.workitem_title as 'Ticket Title',
dv.workitem_comment as 'Close Comment', cv.workitem_comment as 'Last Comment'
from dbo.HD_workitem_detail_view dv
join dbo.HD_workitem_current_view cv On dv.workitem_number = cv.workitem_number
where dv.workitem_action = 'Closed'
and cv.workitem_status_lookup_id = '600' --Note: 600 equals closed
and dv.workitem_modified_on > '%StartDate%'
and dv.workitem_modified_on < '%EndDate%'
order by cv.workitem_number

dominique · 09-13-2007, 07:20 PM

Excellent, good catch on the workitem_action which is definetely a plus when as you said the tickets are modified after the closing date, it is also our case.

Thanks for sharing.

dbush · 11-13-2008, 03:04 AM

I was playing with this tonight and decided I had to get this for once and for all. The scenario I wanted to catch was the last time the incident was set to Closed in case it was re-opened and closed again. Other code I've found or made in the past would only get the first time it's closed or show multiple times when it was closed. This can break reports that are looking for time to final closure, SLA's, etc.

This code will return the incident details for the last time that the incident is set to closed, regardless of how many times it may go back and forth.

Code:

SELECT hdv.workitem_number as 'Number', hdv.workitem_version as 'Version', CONVERT(varchar, hdv.workitem_created_on, 100) as 'Created', CONVERT(varchar, hdv.workitem_modified_on, 100) as 'Closed'
FROM HD_workitem_detail_view hdv
JOIN
(SELECT hd1.workitem_number, ISNULL(hd3.workitem_version,1) as workitem_version
FROM hd_workitem_current_view hd1 
 LEFT OUTER JOIN 
  (SELECT max(workitem_version)+1 AS workitem_version, workitem_number 
   FROM hd_workitem_detail_view 
   WHERE workitem_status_lookup_id != 600
   GROUP BY workitem_number) hd3 ON hd3.workitem_number=hd1.workitem_number
WHERE hd1.workitem_status_lookup_id = 600) hdc ON hdc.workitem_number = hdv.workitem_number AND hdc.workitem_version = hdv.workitem_version

dominique · 11-13-2008, 11:19 AM

Hey David,

Excellent,
The test on

Quote:

SELECT max(workitem_version)+1 AS workitem_version

is it 100% safe?

I noticed and several of us that the workitem_version is not updated as "we" are expecting. Several time it remains the same depending on the action took on the incident. is it correct that a status change will surely change the version?

thanks for sharing.