Reporting and Analytics for Service

Get Involved. Join the Conversation.


    Pavol Procka
    Chat Low Volumes Alert
    Topic posted September 12, 2019 by Pavol ProckaSilver Crown: 22,500+ Points, last edited September 12, 2019 
    40 Views, 17 Comments
    Chat Low Volumes Alert
    Alert to fire if no chats have been received in a Queue

    Our management has put forward a requirement to alert if no chats have been received in a queue for a specific amount of time. So far so good, this part is simple. The problem is, it should only fire if at the same time, there were chats in that queue the previous day (if there were none the day before, it would be assumed the channel is closed). Any ideas how to aproach this?

    Many Thanks





    • Gursimran Singh Saini

      Root Table: queues
      Child Table: chats, chats2
      Yes, you have to join the "chats" table twice with "queues" table.

      Two filters in AND node:
      - chats.requested = previous day
      - chats2.requested = current day

      Create an Exception:
      if(chats.chat_id IS NOT NULL & date_diff(sysdate(),max(chats2.requested)) / 60 <= 30 | chats2.chat_id IS NULL, 1, 0)
      Which translates to: (Chats were requested yesterday) AND (the latest chat today was requested at least 30 minutes ago OR no chat was requested today at all)

      Create a Report Schedule with an Alert. The Alert will fire only if the Exception created above is met at least 1 time.

      There you go.


    • Pavol Procka

      Hi Gursimran,


      thanks for the tip, and I tried, however the problem is that the alerts do not seem to fire if the report is empty.

      The requirement has even changed slightly, management actually wants to be alerted if there were no chats in a queue for the last two days. So no chats -2 days & no chats -1 day -> send alert today, however this does not change the fact that I am trying to alert based on an empty report. Tried setting "Display 0 if No Value" but it still is not displaying anything when I run the report. Not sure if this is "works as designed" or maybe a bug that would be worth reporting?

      I tried a few combinations of the exception but still no luck. Any other suggestions?


    • Sebastiaan Draaisma

      Maybe use a date_group in your report. This will show 0 values

      This function lets you group records together according to a date range that you specify, and include rows for date ranges that have no associated records.

    • Pavol Procka

      Hi Sebastiaan,


      that seems to work if there is anything in at least one of the result rows... when I run it for a queue that had 1 chat 2 days ago and 0 chats yesterday, then it shows me 2 days ago = 1 and 1 day ago = 0.

      But when I tried running it for a queue that had no chats in teh last two days, the report still comes out empty, instead of showing two rows of 0s....

    • Sebastiaan Draaisma

      I had to test it to believe it but you are right :-)

      Another alternative may be to enter some data through a custom script.
      Custom script counts the rows in the report and if null, enter some data in the exit object

      • Pavol Procka

        I was afraid you might say that :(

        Anyway, is this actually the expected behaviour? I did select to display the result as 0 in the column format. I am wondering whether this is maybe a reason for a helpdesk case?

        I even tried adding an another column, for which i specified only the date filter to look back 3 days (so not referenced in the variables) which does have the same data for the 3 days, it still does not display anything even though the exception only references the data in the other column.

        I am attaching the report in case anyone wants to have a look whether my logic is flaw somewhere.

        The dates in the filters and the variables I selected are in April, as that was a date where i found the scenario I am looking for for one of our chat queues (-3 days = 1 chat, -2 days = 0 chats, -1 day = 0 chats, today = fire alert).

        You will see I even tried to force a result by adding conditional formatting for the column and even added totals, but still all comes out empty.


    • Sebastiaan Draaisma

      Try the following in your Custom Script Finish tab.
      It will generate some text with a 0 value, just to see if your alert gets triggered when the report has some text in it.

      $exit_obj[0][0]->val = "Some text";
      $exit_obj[0][1]->val = 0;

      It got me thinking... an Alert is mostly used instead of a schedule where you only want to receive the schedule when there is in fact data to show. Would you not be able to use a scheduled report instead? (since you also want to see a report with no data)

      • Pavol Procka

        Thanks, but unfortunately that didn't do it as well. After I add the script, it shows me the below when I just run the report, however the alert still did not fire...

      • Pavol Procka

        And yes, I though about making it a schedule, but the management did not like it.

        Apparently, it is to prevent that people who would be responsible for escalating when they recieve the alert dismiss or ignore it - mainly because they already receive a lot of scheduled reports and if it is one of those that comes every day, they might not realize it is necessary to escalate.

        • Sebastiaan Draaisma

          I understand.
          Maybe a different approach could be to combine important reports in a dashboard (one per business unit) and schedule the dashboard. That way they would receive 1 PDF file per day containing the various important reports as 1 report. I myself do this for system configurations. One large PDF that contains various reports.

          • Pavol Procka

            Thanks a lot for your efforst! Looks like I might have to try and push the idea of the schedule in the end - which might prove difficult as they believe displaying 0 values should be a basic functionality (which I agree with). Would you know if this is the expected behaviour, or where i can find out whether it is?

    • Sebastiaan Draaisma

      I see the flag so I assume the exception works.
      In that case I'm out of ideas as to use an alert. The only alternative I can think of is a regular schedule instead of an alert.

      • Pavol Procka

        The flags are actually from the conditional formatting.

        • Sebastiaan Draaisma

          In that case, see if the exception gets triggered (add an icon to test or show matching percentage) as the exception is responsible for the Alert function

          • Pavol Procka

            Did that and actually had to change it from equals 0 to IS NULL in order for the exception to start formatting the correct rows. I then tried to fire an alert if it is met once, and then even tried simplifying it to display all three lines (by changing the variables to include the same days as the general filter)  and firing the alert if the exception is met twice, but it still doesnt fire it.

    • Sebastiaan Draaisma

      Then I'm afraid it can not be done with an Alert as you already added data through a custom script and had your exception working with the alert on 'Fire when exception met 1 time'