Skip navigation
1 2 Previous Next 6670 Views 18 Replies Latest reply: Dec 24, 2011 12:11 AM by oku RSS
Rookie 42 posts since
Apr 13, 2010

Has received 1 of 9 achievements.
Currently Being Moderated

May 7, 2010 2:14 AM

Calculating duration of incident, with clock stoppage

I'm reasonably new to LANDesk, and what it can do, so I apologise if this question has already been addressed elsewhere - I have looked around the forums but couldn't find any information that quite fitted what I need to do ...

 

I need to be able to calculate the duration for each Incident but only as it contributes to the service window, i.e. if the duration for an Incident is 2 days, and the service window is 7 hours/day, then the actual duration is 14 hours. I also need to be able to subtract any Clock Stoppage for each Incident. This is further complicated by the fact that the Incidents can be for one of a number of services, and each service uses a different service window.

 

I was wondering what the best way to perform this calculation is (assuming that this can be done by LANDesk) ? I see that different Calendars can be defined within the Administration->Service Level Management component, but how can these then be used in the above calculation ?

 

All help gratefully received

 

Phil H

  • Adam Wilden Expert 508 posts since
    Jan 16, 2009

    Has received 4 of 9 achievements.
    Currently Being Moderated
    2. May 11, 2010 4:14 AM (in response to philh)
    Re: Calculating duration of incident, with clock stoppage

    Hi,

     

    AFAIK this isn't possible via LANDesk.  The calculations you are using are teh same as us and apparently as far as they can go.  We were advised to look to Crystal Reports to get toital time - clock stopped but haven't had a chance yet.

     

    I could be wrong

     

     

    Cheers - Adam.

  • Stu McNeill SupportEmployee 1,072 posts since
    Nov 11, 2008

    Has received 7 of 9 achievements.
    Currently Being Moderated
    4. May 24, 2010 9:24 AM (in response to philh)
    Re: Calculating duration of incident, with clock stoppage

    Hi Phil,

     

    Just to confirm Adam is correct here, its not possible to see the Stop and Start Clock actions as part of a calculation.

     

    However just to add confusion this is technically possible, as long as your Stop and Start Clock actions are always automatic actions in your process preceeded by actions that create collection items (for example a With Customer action to stop, a Back From Customer action to re-start).

     

    With some careful looping and DateTime minus DateTime calculations you can potentially find out the time elapsed between each instance of the stopping and starting.  The result would be a TimeSpan value which could be subtracted from the result of the GetBusinessTime().

     

    I can't really go into any more detail as the calculation would totally depend on the process design and even then be a lot of work, but it is definitely possible!

     

    Thanks

  • Rookie 28 posts since
    Nov 2, 2010

    Has received 1 of 9 achievements.
    Currently Being Moderated
    6. Dec 21, 2011 4:19 PM (in response to philh)
    Re: Calculating duration of incident, with clock stoppage

    Phil, I am also charged with calculating the SLA "business time" via SQL. Can you share this stored procedure?  I have written my own code for parsing the audit trail and calculating the duration between "Stop Clock" and prior "Start Clock" rows. However, it takes an extreme amount of time to perform the query, and that is only the "clock" portion.

     

    I have not yet undertaken parsing up the business hours calendar and factoring in holidays.  Can you share?

  • Rookie 28 posts since
    Nov 2, 2010

    Has received 1 of 9 achievements.
    Currently Being Moderated
    7. Dec 21, 2011 4:41 PM (in response to philh)
    Re: Calculating duration of incident, with clock stoppage

    Since I do a lot of SQL, it was suggested to me by support that I could write a database trigger for the incident object, which would check for a change on the attribute:

     

    [im_incident].[im_is_clock_stopped]

     

    then calculate the duration since the last update (with some other considerations, status, etc.) and store/add-to a duration value stored in some other custom attribute. I guess I'm going to be pursuing this for computing the "with customer" time going forward. But I still have 3 months worth of data since implementation which won't have this computation, so I'm hoping to hear from Phil about how you did it in SQL!

  • JulianWigman Expert 585 posts since
    Apr 24, 2009

    Has received 7 of 9 achievements.
    Currently Being Moderated
    8. Dec 22, 2011 1:46 AM (in response to philh)
    Re: Calculating duration of incident, with clock stoppage

    So if you can get at this information in SQL then maybe you could also write as a SQL function (myFunction), linked to a SQL view (a simple select * from myFunction),  create a ServiceDesk data connection to this view to give a "virtual business object",  drop that virtual BO onto your Incident process,  automatically populate the related objects value (so it links the two together) and voila a protracted route to be able to query that data in querytool, on form etc etc.

     

    That should work as I've done the same for direct AD data lookups on forms in one of my solutions.

     

    Julian

  • Terry_Courtney Apprentice 55 posts since
    Aug 4, 2010

    Has received 2 of 9 achievements.
    Currently Being Moderated
    9. Dec 22, 2011 4:26 AM (in response to philh)
    Re: Calculating duration of incident, with clock stoppage

    Julian thats a very clever way around it WOW!

     

    I do think however that this would be an excellent ER and it would get my vote, I think something like this in an advanced ITBM product like this should be out of the box! This is something I feel my management might be gnashing their teeth about soon!!

  • JulianWigman Expert 585 posts since
    Apr 24, 2009

    Has received 7 of 9 achievements.

    Thanks.  Probably my last ServiceDesk tip then as out of a job from today 

  • JulianWigman Expert 585 posts since
    Apr 24, 2009

    Has received 7 of 9 achievements.

    Ok,

     

    One tip I didnt mention,  the linking field may look like a normal related object and looks like a dropdown on a form  but if you were to look in the DB at that field you are not going to see the usual GUID but in fact some XML tags; data connection name,  connection attribute plus value etc

     

    You'll probably want to be able to set this linking field automatically and for this a BOO calculation would be appropriate.  However in my particular case I also needed to be able to manually edit this too so chose to do via a trigger.

     

    So in an update trigger i set (in my example my linking field was on tps_user) the field as below.  Here it takes the FQDN (usr_fqdn)field off of the profile and creates the link.

     

    set tps_user.tps_adLink = '<Ref Name="_ActiveDirectory._user"><Att Name="_distinguishedName"><Value>'  + tps_user.usr_fqdn + '</Value></Att></Ref>'

     

    The obove is the XML for an AD linke but I'd expect something similar for other connection types.

     

    The best way is manually drop down the linking field and select and save,  then look in the DB field to see the format and then use that string in your calculation/trigger.

     

    Voila.

     

    Regards

     

    Julian

  • Terry_Courtney Apprentice 55 posts since
    Aug 4, 2010

    Has received 2 of 9 achievements.

    Sorry to hear that Julian

  • Rookie 28 posts since
    Nov 2, 2010

    Has received 1 of 9 achievements.

    Yeah... an enhancement request??  I think I don't even get to have Christmas if I don't have this all solved first. My management expected this as the FIRST metric on the first report. But they(we) didn't know that such critical information would not be offered out of the box. They put off report design to a later phase of implementation, and assumed that all such metrics would be available retroactively on all the data.

     

    Julian, good luck on the next step of your career then. With your skill it shouldn't be too hard I would think. The front page says Landesk is hiring... but I don't know if you want to do that! Thanks for your input, and I hope you've changed your email address to something personal so you'll still be able to use the site.

  • JulianWigman Expert 585 posts since
    Apr 24, 2009

    Has received 7 of 9 achievements.
    Currently Being Moderated
    14. Dec 22, 2011 2:43 PM (in response to mhz)
    Re: Calculating duration of incident, with clock stoppage

    Chaps,  if you want to do this too and not in crystal you can use reporting services too.  I managed quite easily to call the incidentduration crufl (dll) from reporting services to get the total duration in support hours.

     

    Julian

1 2 Previous Next

More Like This

  • Retrieving data ...

Bookmarked By (2)

Legend

  • Correct Answers - 20 points
  • Helpful Answers - 10 points
LANDESK Community powered by Jive SBS® 4.5.7.1  |  Legal Notices  |  Privacy Policy  |  Icon 

TweeterOn Twitter  |  Icon FacebookOn Facebook © 2007 LANDESK Software