This is a public Forum  publicRSS


    See what status an incident was at a particular date
    Topic posted June 19, 2017 by JJMaster, last edited June 19, 2017 
    155 Views, 8 Comments
    See what status an incident was at a particular date


    So we have a requirement to count how many incidents had a status X at time Y. What's the easiest way to do this? I just need a figure - nothing more.

    My problem is that let's say we wanted to record everything prior to today (this date can be varied). Let's say we had 2,000 incidents that were "Updated" at 23:59 last night. We should have a figure of 2,000.

    However if any of those 2,000 were resolved today (of course, presuming no others were created, other status changes didn't happen etc) then my report would being back 2,000 less any that were resolved.

    Hopefully this makes sense




    • Jess Campbell

      Might be a good starting point.

    • Danette Beal

      Hi JJ,

      I would absolutely agree with Jess that you should review Bastiaan's cookbook recipe. This will get you down the right path.

      One piece of advice, if you don't know much about the transactions table, learn it! This is probably one of the most important tables connected to incidents that I've ever used when trying to report on KPI's. It can however be tricky because of the amount of records it holds and it is important to understand how to filter the table to narrow down exactly what you want to see.

      Bastiaan has done just this in the report Jess is referring to. By putting a filter on the transactions table directly to only look at transactions of created and status changed, you can then create a report that will show you all status changes for a given incident. Be aware, depending on the data set, this could still be too many records to display in one report and it can get bogged down quickly. By narrowly defining the time criteria for the report and only focusing on a smaller criteria, you will be more successful at running any reporting off the transactions table.

      Good Luck, and let us know if you have questions on Bastiaan's report!

      Danette, Community Manager

    • JJ

      Hi both and thanks very much - yes, I went to this before and got the report up and running.

      Maybe it's the heat but not sure how I would setup to get exactly what I need. So in the below, looking at 170618-000078 for example, I wouldn't know to say for date ending 18th it would count as 1 and date ending 19th it wouldn't count

      Reference # Status Date Created Duration
      170618-000080 Unresolved 18/06/2017 11.58 PM  
      170618-000079 Unresolved 18/06/2017 11.30 PM  
      170618-000079 Pending 19/06/2017 12.01 PM 12h 30m 42s
      170618-000078 Unresolved 18/06/2017 10.35 PM  
      170618-000078 Pending 19/06/2017 02.21 PM 15h 46m 14s
      170618-000078 Resolved at Tier 1 19/06/2017 03.32 PM 1h 10m 30s
    • Jess Campbell

      Transactions table is the way to capture a specific time.


      However, if you only want a count of incidents in a specific status at a specific time that is static, you could always just create a report that uses the incident table, with a count of distinct incidents and a filter on the status you want to count, or a column for the statuses. Then schedule a report to run and either export or publish the data at the time you want to capture.



    • Jess Campbell

      Here's an excel view of that definition:

    • JJ

      Thanks - it's not so much that I want to capture a specific status but effectively at the end of any given day (a day I pass into the filter), how many were X, how many were Y and how many were Z (where X, Y and Z are different statuses). Sometimes an incident can go through several statuses in a day o if I did a filter on a specific one, but it changed soon after, that would skew my results

      I was looking at scheduled report but that would only work for now onwards rather than retrospectively

      As I said, sure there's a fairly easy way but I just can't seem to put 1 and 1 together to make 2. Effectively looking for the last status of an incident on any given day... I think

    • Jess Campbell

      Yes, my suggestion works only for the future.  However, the changing statuses would not affect the snapshot of a scheduled report. If you run that report at 11:59, it will catch all incidents in the status they are in at 11:59. If you look at the report I mocked up, I captured the status of all open, in progress, and waiting incidents at the time I ran it. The numbers will have changed in the database by now, but those are my counts for that day.

      If you are trying to use the cookbook report as a framework, create your own new custom report and join the tables as they are in the time in status report. You do not need the script for the time in status calculation.

      Set the filters to include any incomplete statuses, and to filter only on the transactions you want, make a column for count(distinct incidents.i_id), make another column for the day you are capturing by formatting the date the transaction was created to 'YYYY/MM/DD'. If you leave off the column with the status itself, you will get a count of incidents in any open status with no duplicates for that day.

      That way, you can count the distinct number of incidents with a particular status on a particular day based on whether they have a transaction today. The limitation of using transactions for this this is that a status can start yesterday and remain unchanged today, so it would not be counted on today's report, as there would be no new transaction today. If it was updated tomorrow, then it would re-appear.


    • Sasi Gudimetta

      Hi JJ,

      I have attached a sample definition that I think might help.

      Check it once and see if it suits your requirement.