Closed day?

fari.sah · 10-03-2007, 07:22 AM

Hi!
We have created a report that follows up our SLA. The report needs created_on, and closed_on. But guess what, there is no column for closed_on in Altiris

Why?

OK, I took the value from modified_on and completed my query with is_last = 1 hoping that would do the trick. Well, it did! So far so good.

Months later, last friday, I discovered that Altiris creates a new row in database each time a worker change somthing in the system, whatever it's a open or closed incident. I get why Altiris do that, but it mess with my report! It creates a new modified_on and the query's is_last = 1 mess it up! The close date change in my report

We have changed category text for some, we have deleted "clients" from the application, we have changed the client in incidents, ... all these have created a new row and we get a new modified_on date, it realy messed up our statistics

We changed the category text for 'System\Invoice Manager\....' to 'System\IM\...' at 20070523 14:03:00
That change messed up 783 incidents

I have tried to get the same result by the following query, but I doesn't work.

SELECT number AS incidentnr
,status_lookup_id
,CONVERT (char (10), created_on, 120) AS created_on
,modified_on AS modified_on
,category_tree_value
,version

FROM dbo.workitem

WHERE category_tree_value LIKE 'System\I%'
AND action = 'closed'
AND modified_on BETWEEN '20070523 00:00:00' AND '20070523 14:03:00'

ORDER BY category_tree_value

This script works, but I can not create a view for each category

SELECT number AS incidentnr
,status_lookup_id
,CONVERT (char (10), created_on, 120) AS created_on
,modified_on AS modified_on
,version

FROM dbo.workitem

WHERE category_tree_value LIKE 'System\Invoice Manager\%'
AND action = 'closed'
AND modified_on < '20070523 14:03:00'

Can someone help us cleaning up the mess?
My questions are:
1. Why doesn't Altiris create a column for closed_on?
2. How do I solve this problem?
3. I've seen that 'closed:' in the application has the correct date. How those that work?

dbush · 10-03-2007, 11:01 AM

Hello Fari and welcome to the forums!

You actually helped me a little. What you're asking for I've always known exactly how I'd do it if I ever had to, but somehow as of yet have not been asked to. Think it's the luck of the draw.

At any rate, in theory what you want to do is read all the incidents out of the workitem_detail_view, order by modified date, then select the first one where it says closed. There's a couple catches here as a worker could put it to closed then move it back, but I imagine we could make the SQL fancy enough to avoid that.

I never dug into the Closed: date and I think that leads right where we want to go. If you open up the WorkItemView.ascx page found in .\Helpdesk\AeXHD\templates then there's a label for this field called lblClosedOn. In the line it references sidSqlClosedOn. If you go to the formats.xml file found in .\Helpdesk\AeXHD\bin and do a search for sidSqlClosedOn, you'll see the SQL that is being used to generate this date. I believe this gives you what you're wanting and you can work from there.

Let us know how it goes and if you finish that report and want to export it and save it to this forum that may be helpful for others in the future!

Good luck,
David

fari.sah · 10-04-2007, 02:42 AM

Thanks for answer. I think I have another solution that works, I don't like it, but it as sayed it works. I couldn't make your sugesstions to work perfektly, got tierd and fixed another workaround.

I really what a column for closed_on. I have worked with Magic TSD and they had closed_on and it worked perfect. I am going to contact Altiris and ask them nicely to alter tables and views!

I have created a view based on following. Our SLA report is not in Altiris, but in our own time-reporting application. We have other data in that database that we need for our SLA.

SELECT workitem_category_tree_value
,workitem_number
,workitem_status_lookup_id
,workitem_action
,CONVERT (char (10), workitem_created_on, 120) AS created_on
,CONVERT (char (10), workitem_modified_on, 120) AS modified_on
FROM hd_workitem_detail_view
WHERE workitem_status_lookup_id = 600
AND workitem_action = 'closed'
--AND (workitem_category_tree_value LIKE 'System\INV%' OR workitem_category_tree_value LIKE 'System\IM%')
ORDER BY workitem_category_tree_value

fari.sah · 10-04-2007, 03:44 AM

https://kb.altiris.com/display/1/ind...L=&r=0.7768213

chopper18 · 10-11-2007, 05:23 PM

I had the same reaction when I found out Helpdesk did not contain a 'closed on' work item view field. I was really surprised when I was customizing my screens, that I couldn't use that field.

For me it would be extremely useful for consoles and forms, in addition to reports.

Thank you so much Fari, for getting in that feature request. I don't know why I didn't take action sooner! At any rate, I've subscribed to the request, showing my interest as a customer, in getting this feature implimented. I couldn't get the URL to work, so here's a new one that might. Article ID: 36365

Mike

https://kb.altiris.com/display/1/kb/...65&n=1&s=&link=#

dbush · 10-11-2007, 06:51 PM

Here's the link:

https://kb.altiris.com/article.asp?article=36365&p=1

(On the right-hand side, next to Email, is a ( Link ). Right-click that Link and do Copy Shortcut to get a good link to an Altiris KB. )

Good request to subscribe to.

David

fari.sah · 10-12-2007, 01:20 AM

Thanks, but I can not take credit for that. Somone else beet me to it!

jmadigan · 01-10-2008, 07:05 PM

Kind of old but I'm actually working on this right now. I haven't really touched the helpdesk since I moved positions but they want me to upgrade to SP5 and add the service catalog form that janjira thaweekan made. I thought I'd go through and clean up some stuff that was bothering me and this has always been one of the top.

What I've done so far is add a closed_on field like somebody mentioned.

Code:

alter table workitem add closed_on datetime not null default (N'')
Go
exec sp_addviewdef N'workitem_only_view', N'workitem.closed_on AS workitem_closed_on', N''
GO
EXEC sp_createHDDview 'workitem_only_view', 'workitem'
GO
EXEC sp_createHDDview 'workitem_detail_view', 'workitem_only_view'
GO
if exists (select * from sysobjects where id = 
object_id(N'[dbo].[workitem_current_view]') and OBJECTPROPERTY(id,
N'IsView') = 1)
drop view [dbo].[workitem_current_view]
GO
CREATE VIEW dbo.workitem_current_view
AS
SELECT workitem_detail_view.*
FROM workitem_detail_view
WHERE (workitem_is_last = N'1')
GO

That is all the sql queries needed to add the closed_on to workitem and create the views. Next I ran this to copy the date in modified_on into closed_on when the status for that line is 400 or 600 (requested or closed)

Code:

update workitem
set closed_on = modified_on
where status_lookup_value_id in (400, 600)

Next I created 2 incident rules. One for setting the closed_on to the modified_on date when the status changes and is equal to 400 or 600. The other to clear the closed_on date if the status changes and is not equal to 400 or 600. Basically if it reopens, I want to lose the closed on date.

This is the two incident rules

Code:

<Helpdesk>
<IncidentRules>
  <IncidentRule>
    <Guid>ebcc625c-a0b6-4d03-8664-a15e92f39757</Guid>
    <Name>Set Closed Date</Name>
    <Description />
    <Status>active</Status>
    <Rank>1</Rank>
    <IsTerminal>no</IsTerminal>
    <ApplyTo>all</ApplyTo>
    <IncidentEdits>
      <SetValue field="workitem_closed_on" value="WORKITEM(workitem_modified_on)" />
    </IncidentEdits>
    <Criteria>
      <Criterion field="workitem_status_lookup_id" op="optOneOf" value="400,600" type="uint" when_changed="true" />
    </Criteria>
  </IncidentRule>
  <IncidentRule>
    <Guid>8c7429b0-289a-4640-bf6a-653c004a27e4</Guid>
    <Name>Clear Closed Date</Name>
    <Description />
    <Status>active</Status>
    <Rank>0</Rank>
    <IsTerminal>no</IsTerminal>
    <ApplyTo>all</ApplyTo>
    <IncidentEdits>
      <SetValue field="workitem_closed_on" value="" />
    </IncidentEdits>
    <Criteria>
      <Criterion field="workitem_status_lookup_id" op="optNoneOf" value="400,600" type="uint" when_changed="true" />
    </Criteria>
  </IncidentRule>
</IncidentRules>
</Helpdesk>

What I'm working on at the moment is adding it to the list of search criteria but for now you can search by choosing "Advanced Condition" and then 'workitem_closed_on' is on/before/after/etc 'YYYYMMDD' as Date.

I haven't full tested this yet since this is in my test environment but it appears to do it's job. Let me know if i'm forgetting something. There's still a bunch of stuff I need to fix (mostly bad custom coding I did the first time).

fari.sah · 01-11-2008, 01:38 AM

Thanks!
I will test that on a test database. However I don't think that I deploy that on the production database. What happens when they send a new version? Do Altiris support after altering a production database (table)?

However I have created a view for our SLA.

Code:

USE [Altiris_Incidents]
CREATE VIEW [dbo].[vw_closed_incidents]
AS
SELECT number AS incidentnr
,status_lookup_id
,created_on
,modified_on AS modified_on
,version
,action
,category_tree_value
FROM dbo.workitem
WHERE status_lookup_id = 600
AND action = 'closed'

jmadigan · 01-11-2008, 10:16 AM

I probably have more custom fields in the workitem table than I do standard fields. They don't change the databases around but if they did, I'm sure they will have some method to preserve the changes. After all the means that we add custom fields is in the Helpdesk manual so we're following Altiris' recommend procedure.

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