1 2 Previous Next 18 Replies Latest reply: Dec 24, 2011 12:11 AM by oku RSS

    Calculating duration of incident, with clock stoppage

    Rookie

      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

        • 1. Re: Calculating duration of incident, with clock stoppage
          Rookie

          Okay, I'm getting a little further with this now

           

          It turns out that I can calculate the business time that has elapsed for a given Incident, using the Incident.GetBusinessTime method - as far as I can tell, this should take account of the SLA that is relevant to each Incident. I can also check whether each Incident has a status of 'Resolved' or 'Closed', extract the date/time that this occurred, and use this in my business time calculation.

           

          However I still have the problem of being able to calculate the total time over which the clock was stopped for a given Incident. Does anyone have any ideas how I can access this information ?

           

          Thanks,

           

          Phil H

          • 2. Re: Calculating duration of incident, with clock stoppage
            Adam Wilden Expert

            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.

            • 3. Re: Calculating duration of incident, with clock stoppage
              Rookie

              Thanks for that Adam,

               

              It's a shame that I can't do what I need directly within LANDesk - I guess I'll have a look at Crystal Reports

               

              Phil H

              • 4. Re: Calculating duration of incident, with clock stoppage
                Stu McNeill Employee

                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

                • 5. Re: Calculating duration of incident, with clock stoppage
                  Rookie

                  Thanks for that information Stu,

                   

                  As it turns out, I've just managed to solve the problem using a SQL Stored Procedure that is able to traverse the audit trail and perform the necessary calculation - so no need to dive into complex modifications of our processes!

                   

                  Is there a particular reason that LANDesk doesn't provide the functionality to calculate incident duration, with clock stoppage ? I would've thought that this would be a fairly fundamental thing to calculate for a service desk, but perhaps I'm misunderstanding the issue.

                   

                  Phil H

                  • 6. Re: Calculating duration of incident, with clock stoppage
                    mhz Apprentice

                    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?

                    • 7. Re: Calculating duration of incident, with clock stoppage
                      mhz Apprentice

                      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!

                      • 8. Re: Calculating duration of incident, with clock stoppage
                        JulianWigman Expert

                        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

                        • 9. Re: Calculating duration of incident, with clock stoppage
                          Apprentice

                          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!!

                          • 10. Re: Calculating duration of incident, with clock stoppage
                            JulianWigman Expert

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

                            • 11. Re: Calculating duration of incident, with clock stoppage
                              JulianWigman Expert

                              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

                              • 12. Re: Calculating duration of incident, with clock stoppage
                                Apprentice

                                Sorry to hear that Julian

                                • 13. Re: Calculating duration of incident, with clock stoppage
                                  mhz Apprentice

                                  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.

                                  • 14. Re: Calculating duration of incident, with clock stoppage
                                    JulianWigman Expert

                                    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