Best Practices

I'm curious what others do to avoid the following scenario for any reporting on whether or not ticket due dates are met.

Since the DUE_DATE field is only a date, and the TIME_CLOSED field is a date-time, one can query if one date is greater than or less than the other, but one can never query if one is greater than OR equal to, or less than OR equal to the other.

So, in a situation where you want to count the amount of tickets closed by due date like so:

SUM(IF(TIME_CLOSED<=DUE_DATE,1,0))

and you consider an example time opened date as:

2012-07-26 13:59:59

and a time closed date as:

2012-07-27 13:59:58

and a due date was set for 24 hours after ticket was created:

2012-07-27

then

SUM(IF(2012-07-27 13:59:58<=2012-07-27,1,0))

will return 0 and would then not be picked up in a report as being closed on time.

Another question I'd like to pose to you all deals with escalations.  In other service desk systems I've worked with, if a ticket is placed in a stalled state, the ticket will never escalate, and you can use whether it was escalated or not as a performance reporting metric.  With Kace, the ticket escalates regardless of a stalled state.  A stalled state merely stops escalation emails being sent.  So, that knocks using 'escalations' out as a reporting metric as well, since if a ticket is set in a stalled state by a ticket owner 'waiting on customer', for example, and it surpasses the escalation time set, the ticket escalates and shows up in ecalation count reports for that particular ticket owner.  

One solution may be to have the same ticket rule you created to set a particular ticket in a stalled state depending on a status ('with customer' for example) also update the TIME_OPENED filed to '0000-00-00 00:00:00'.  But that is not optimal because any ticket that is saved with that particular status, even if it was escalated prior to being saved in that particular stalled state, would be dropped from the reports as well... 

Another solution may be to create a new priority level that has no escalation and if a ticket is set in a status of 'waiting on customer' it automatically gets dropped down to a lower level priority that has no escalations... 

Or I suppose, I could simply not use escalations as a metric at all and use them simply to send nudging emails after a certain date, which I guess is their sole purpose in Kace ServiceDesk.  

So I could use the due date vs close date... er... hmmm I guess I'll have to create a custom 'due date' field and hide the Kace built in one...  What do you all do to dance around this issue?  And why wouldn't Kace simply add a time stamp to the due date field?  Or am I missing something obvious here?