1 2 3 Previous Next 35 Replies Latest reply on Jan 20, 2020 10:47 AM by Sonia Punchhi

    Is there any possible way to calculate the time spent on each status? I'm using Smart Reporting 9.1

    Raluca Mlaceanu
      Share This:

      s there any possible way to calculate the time spent on each status? I'm using Smart Reporting 9.1. It seems that i'm missing the history on status..Is this stored somewhere?

        • 1. Re: Is there any possible way to calculate the time spent on each status? I'm using Smart Reporting 9.1
          Phillip Brockhaus

          It may be better to calculate it at the time of status change and store it instead. That would probably be way better for performance. Then you could just include the already-calculated data in your reporting.

          • 3. Re: Is there any possible way to calculate the time spent on each status? I'm using Smart Reporting 9.1
            Raluca Mlaceanu

            Hi,

             

            Yes, for the incident management. I can't find a field which stores that info. For example i would like to see:

             

            INC1: Status new 

            INC1: Status Assigned

            INC1: Status Pending

            INC1: Status In progress

            INC1: Status Closed

             

            And for each interaction I will have different timestamps in order to calculate the time spent on each status.

             

            Is this possible?

             

            Thanks,

            Raluca

            • 4. Re: Is there any possible way to calculate the time spent on each status? I'm using Smart Reporting 9.1
              Marek Ceizel

              Hi Raluca,

               

              This can be much more tricky than you think. The problem is that the Status is not changing always straight forward but skipping. You can go in progress, then pending, then again into in progress, then maybe assigned (no idea). And this behavior is nowhere written.

               

              - Audit From is not really useful as it will be hard to get the info from there.

              - If you will always go through these statuses not changing back and forth, then you will he ok with the Hxxx DB Table content where is the timestamp of each status change. But that will be for sure not the case

              - I'm not so good in SLM so it can be there is some function which observe/store this information.

               

              At the end you can create a custom workflow and save this information on status change into a custom form. There you can also implement the calculations in case the status was changed to e.g. in progress several times. Keep in mind also the business times in case it matters for your observation.

               

              regards

              Marek

              2 of 2 people found this helpful
              • 6. Re: Is there any possible way to calculate the time spent on each status? I'm using Smart Reporting 9.1
                Phillip Brockhaus

                Carl - The solution in the thread that you linked to is tricky and will perhaps have a heavy impact on the database every time someone tries to run the report.

                 

                While it would take a developer to implement it, I think the solution proposed by Marek and me is cleaner will be much less stressful on the database.

                 

                Make a new table for recording status times. Every time the status on a record changes, push to the new Status Times form.

                Depending on how you want to implement it, either always create a new record, (more granular data), or, create a new record if a record for that request id/status doesn't already exist, otherwise, modify the existing record. (Simpler report.)

                • 7. Re: Is there any possible way to calculate the time spent on each status? I'm using Smart Reporting 9.1
                  Carl Wilson

                  Phillip Brockhaus - If you are running Smart Reporting standalone and on Read Only replica, then the performance aspect does not come into play.

                   

                  I was pointing out an alternative and something that was being discussed in another thread that didn't require customisation to the system, where a user can then make an informed decision based on the setup and which path they wanted to go.

                   

                  People like options

                   

                  Cheers

                  Carl

                  1 of 1 people found this helpful
                  • 8. Re: Is there any possible way to calculate the time spent on each status? I'm using Smart Reporting 9.1
                    Phillip Brockhaus

                    Sure.

                     

                    We are having real problems with Smart Reporting performance even though we are using a read-only copy of our DB.

                     

                    We have very heavy usage.

                     

                    The solution you linked to is completely valid.

                    • 9. Re: Is there any possible way to calculate the time spent on each status? I'm using Smart Reporting 9.1
                      Sinisa Mikor

                      I wouldn't quite call it a solution myself -- it's an explanation of what is being recorded by default, and how report creator can access that information from Smart Reporting, along with reminder that the difficult part (calculating differences and totals) isn't dealt with yet; given Smart Reporting's limitations, it might be necessary to join audit log to itself in order to be able to do that, thus even more stress would be put on Smart Reporting server and database.  Had performance been listed as primary requirement, I would have advocated the same method you mentioned in your first reply -- keeping count of time spent in each status and updating it whenever transition between two states occurs as well.

                      1 of 1 people found this helpful
                      • 10. Re: Is there any possible way to calculate the time spent on each status? I'm using Smart Reporting 9.1
                        Sinisa Mikor

                        Surprisingly, report runs pretty fast, type conversions notwithstanding; it probably helps that Status is always the first attribute listed in log field (that I've found) so instead of LIKE "%<string>%", it looks for "<string>%".  Actually, Smart Reporting wouldn't allow me to use nested aggregating functions so I've used one report to determine durations of time between status changes incident had gone through (which may be interesting to see on its own), then used it as view for second report which sums up those numbers according to status value to displayed results Raluca is looking for.  First draft isn't absolutely accurate (report calculates duration of last status as difference between current time and transition time, but in a few very rare cases this doesn't end up in results), so if anyone is interested in seeing it, please wait till I've solved this issue.

                        2 of 2 people found this helpful
                        • 11. Re: Is there any possible way to calculate the time spent on each status? I'm using Smart Reporting 9.1
                          Phillip Brockhaus

                          I'm just curious - What is the size of the dataset you are running against?

                           

                          We have nearly 4 million records in our incident table in a heavily modified ITSM system and a bunch of other records in other tables.

                           

                          Our users are killing our smart reporting server...

                          • 12. Re: Is there any possible way to calculate the time spent on each status? I'm using Smart Reporting 9.1
                            Sinisa Mikor

                            The most I've used in testing is about ten thousand incidents with maybe twice or thrice as many status changes on average -- Smart Reporting claims that it takes 0 seconds to run it on fairly weak though lightly used hardware setup.

                             

                            Have you already looked into culling views at join level?  It seems to me that in order to be "accessible", Smart Reporting uses SELECT DISTINCT to get rid of excess records -- this may give correct results, but happens at the end, so after all those superfluous records have been read.  I've applied this to a report recently and its running time changed from two or more minutes of nail-biting and hoping it will end well to about three easy seconds.

                            1 of 1 people found this helpful
                            • 13. Re: Is there any possible way to calculate the time spent on each status? I'm using Smart Reporting 9.1
                              Phillip Brockhaus

                              I'll talk to our Smart Reporting guy about this. Thanks for the suggestion.

                              • 14. Re: Is there any possible way to calculate the time spent on each status? I'm using Smart Reporting 9.1
                                Sinisa Mikor

                                Here is how I've defined view and report to display time incident spent in each status (illustration uses an example from testing environment where number of transitions and durations are intentionally exaggerated).

                                 

                                As suggested before, incidents from HPD:Help Desk are first joined to audit logs from HPD:HelpDesk_AuditLogSystem by matching Entry ID from incident to Original Request ID from audit log (this should be one-to-many inner join to keep number of records in result as low as possible), then another instance of audit log form is joined to first instance if Original Request ID is equal in both instances, and Audit Date in first instance is earlier than the one in second instance -- this still results in too many records (aggregation isn't available in view definition so culling will have to be done in report), but it allows calculation of status' duration for each "audit log" record because it prepares (up to) two datetime values in each record.

                                 

                                Each possible status value requires one calculated field which is used to calculate minimum duration using:

                                    CASE

                                          WHEN

                                              Log1 LIKE 'Status: <Selection Value>  (<ID>)%'

                                          THEN

                                              MIN (Duration)

                                    END

                                where Log1 comes from first instance of audit log form, <Selection Value> and <ID> need to be replaced with values defined for each status value (e.g. Assigned, and 1), and Duration is another calculated field which does not have to be displayed in report, but must be defined in it (MIN is used only to ensure that only "neighboring" status transitions are taken into account during calculation because they couldn't be discarded via view's definition), and it is defined as:

                                    CASE

                                          WHEN

                                              Log2 LIKE 'Status: %'

                                          THEN

                                              Closed interval

                                          ELSE

                                              Open interval

                                    END

                                which decides depending on Log value from second instance which of two other calculated fields (closed interval, and open interval) should be displayed -- those define formulas for calculating duration between two status transitions as date difference (in seconds) between audit date from second instance and audit date from first instance

                                     DATEDIFF('ss', `HPD:HelpDesk_AuditLogSystem`.`Audit Date`, `HPD:HelpDesk_AuditLogSystem 1`.`Audit Date`)

                                and duration between last status transition and now as

                                    DATEDIFF('ss', `HPD:HelpDesk_AuditLogSystem`.`Audit Date`, CurrentDate())

                                 

                                Report will probably include Incident Number from HPD:Help Desk and calculated fields for each status value, and an easy way to arrange transitions chronologically is to include (hidden) Request ID from first instance of audit log form as second column as in my example; if only total duration per status value needs to be displayed, this report can be used as view for another report which would sum up durations per status value -- if maximum number of records is restricted, some may be left out from last incident in first report, and its totals in second report will inherit that error.

                                 

                                For those who prefer SQL Statements, here it is (WHERE clause can be used as hidden filter in report, but it would be better to use it as condition in view's definition).

                                SELECT DISTINCT
                                     `HPD:Help Desk`.`Incident Number`,
                                     `HPD:HelpDesk_AuditLogSystem`.`Request ID`,
                                     CASE
                                          WHEN
                                               `HPD:HelpDesk_AuditLogSystem`.`Log` LIKE 'Status: Assigned (1)%'
                                          THEN
                                               MIN(
                                                    CASE
                                                         WHEN
                                                              `HPD:HelpDesk_AuditLogSystem 1`.`Log` LIKE 'Status: %'
                                                         THEN
                                                              DATEDIFF('ss', `HPD:HelpDesk_AuditLogSystem`.`Audit Date`, `HPD:HelpDesk_AuditLogSystem 1`.`Audit Date`)
                                                         ELSE
                                                              DATEDIFF('ss', `HPD:HelpDesk_AuditLogSystem`.`Audit Date`, CurrentDate())
                                                         END
                                               )
                                     END,

                                     CASE
                                          WHEN
                                               `HPD:HelpDesk_AuditLogSystem`.`Log` LIKE 'Status: In Progress (2)%'
                                          THEN
                                               MIN(
                                                    CASE
                                                         WHEN
                                                              `HPD:HelpDesk_AuditLogSystem 1`.`Log` LIKE 'Status: %'
                                                         THEN
                                                              DATEDIFF('ss', `HPD:HelpDesk_AuditLogSystem`.`Audit Date`, `HPD:HelpDesk_AuditLogSystem 1`.`Audit Date`)
                                                         ELSE
                                                              DATEDIFF('ss', `HPD:HelpDesk_AuditLogSystem`.`Audit Date`, CurrentDate())
                                                         END
                                               )
                                     END,
                                     CASE
                                          WHEN
                                               `HPD:HelpDesk_AuditLogSystem`.`Log` LIKE 'Status: Pending (3)%'
                                          THEN
                                               MIN(
                                                    CASE
                                                         WHEN
                                                              `HPD:HelpDesk_AuditLogSystem 1`.`Log` LIKE 'Status: %'
                                                         THEN
                                                              DATEDIFF('ss', `HPD:HelpDesk_AuditLogSystem`.`Audit Date`, `HPD:HelpDesk_AuditLogSystem 1`.`Audit Date`)
                                                         ELSE
                                                              DATEDIFF('ss', `HPD:HelpDesk_AuditLogSystem`.`Audit Date`, CurrentDate())
                                                         END
                                               )
                                     END,
                                     CASE
                                          WHEN
                                               `HPD:HelpDesk_AuditLogSystem`.`Log` LIKE 'Status: Resolved (4)%'
                                          THEN
                                               MIN(
                                                    CASE
                                                         WHEN
                                                              `HPD:HelpDesk_AuditLogSystem 1`.`Log` LIKE 'Status: %'
                                                         THEN
                                                              DATEDIFF('ss', `HPD:HelpDesk_AuditLogSystem`.`Audit Date`, `HPD:HelpDesk_AuditLogSystem 1`.`Audit Date`)
                                                         ELSE
                                                              DATEDIFF('ss', `HPD:HelpDesk_AuditLogSystem`.`Audit Date`, CurrentDate())
                                                         END
                                               )
                                     END,
                                     CASE
                                          WHEN
                                               `HPD:HelpDesk_AuditLogSystem`.`Log` LIKE 'Status: Closed (5)%'
                                          THEN
                                               MIN(
                                                    CASE
                                                         WHEN
                                                              `HPD:HelpDesk_AuditLogSystem 1`.`Log` LIKE 'Status: %'
                                                         THEN
                                                              DATEDIFF('ss', `HPD:HelpDesk_AuditLogSystem`.`Audit Date`, `HPD:HelpDesk_AuditLogSystem 1`.`Audit Date`)
                                                         ELSE
                                                              DATEDIFF('ss', `HPD:HelpDesk_AuditLogSystem`.`Audit Date`, CurrentDate())
                                                         END
                                               )
                                     END,
                                     CASE
                                          WHEN
                                               `HPD:HelpDesk_AuditLogSystem`.`Log` LIKE 'Status: Cancelled (6)%'
                                          THEN
                                               MIN(
                                                    CASE
                                                         WHEN
                                                              `HPD:HelpDesk_AuditLogSystem 1`.`Log` LIKE 'Status: %'
                                                         THEN
                                                              DATEDIFF('ss', `HPD:HelpDesk_AuditLogSystem`.`Audit Date`, `HPD:HelpDesk_AuditLogSystem 1`.`Audit Date`)
                                                         ELSE
                                                              DATEDIFF('ss', `HPD:HelpDesk_AuditLogSystem`.`Audit Date`, CurrentDate())
                                                         END
                                               )
                                     END,
                                FROM
                                     `AR System Schema`.`HPD:Help Desk` INNER JOIN
                                     `AR System Schema`.`HPD:HelpDesk_AuditLogSystem` ON (
                                          `HPD:Help Desk`.`Entry ID` = `HPD:HelpDesk_AuditLogSystem`.`Original Request ID`
                                     ) LEFT OUTER JOIN
                                     `AR System Schema`.`HPD:HelpDesk_AuditLogSystem` AS `HPD:HelpDesk_AuditLogSystem 1` ON (
                                          `HPD:HelpDesk_AuditLogSystem`.`Original Request ID` = `HPD:HelpDesk_AuditLogSystem 1`.`Original Request ID` AND
                                          `HPD:HelpDesk_AuditLogSystem`.`Audit Date` < `HPD:HelpDesk_AuditLogSystem 1`.`Audit Date`
                                     )
                                WHERE
                                     `HPD:HelpDesk_AuditLogSystem`.`Fields Changed` LIKE ';Status;%'
                                GROUP BY
                                     `HPD:HelpDesk_AuditLogSystem`.`Request ID`,
                                     `HPD:Help Desk`.`Incident Number`,

                                 

                                     `HPD:HelpDesk_AuditLogSystem`.`Log`

                                2 of 2 people found this helpful
                                1 2 3 Previous Next