Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

In this Discussion

osTicket v1.10 (stable) and Maintenance Release v1.9.15 are now available! Go get it now

Send one notification mail once a week instead of using "alert for flagged ticket" option

Hi,

 

Is there some kind
of possibility to send one mail, once a week which include some kind of summary
of all the open tickets in the body (the ones that haven't been assigned or
commented on)

 

Purpose: a kind of
reminder thing for 'forgotten tickets'

But the problem is
we don't wanna work with the option "alert if you've got a flagged
ticket" because this wil sort of spam my inbox. We don't need an individual notification mail for every ticket.

 

I just need one
mail, each week for example with some kind of summary of the open tickets, or
tickets that haven't been commented on.

 

Anny thoughts? Thanks for helping me out.

 

Regards

 

Thanks in advance!

Comments

  • Hello @hansknapp, my suggestion would be to create an SQL query with what you need, based on set rules, e.g., select only overdue tickets (do calculation yourself within sql query), add ticket id's, subject, body, etc. 

    Query the database and put output in a file or similar and send the file or similar via email to your specified e-mail address. This can be put in a shell script or similar (if windows), run via cron or scheduler, set it up and forget about it :)

    This does not offer really cool looking report obv, but it is good enough to see the information you need. Currently there is no default functionality like this in current iteration of osTicket that I am aware of, there might be mods out in the wild, e.g., reports mod by Scott (not yet released for v1.10 to general public), but I am not sure about weekly reports via email support.


  • edited January 30

    It's more like an SLA - check, but instead of after X- days sending a reminder (when the ticket becomes overdue) i want to send a reminder or something like a cron job that scans once a week if there are unassigned tickets, so one summary e-mail notification each week of all open (or overdue tickets)

  • Thanks for commenting @sperrow, but is there maybe an alternative plugin like Reports or so, but free? I've been searching for such an implementation, but couldn't find a guide for this.

    My SLQ skills aren't that good, maybe you know an online project / tutorial / other plugin you know of? Something like Reports but an free alternative?

    thanks in advance


  • edited January 30
    Same idea applies, its quite simple, make your own SQL query and you run the query on all tickets, matching results will be sent in email to you at your specified time.

    Something like this, given you you ost_ default prefix for the db:

    SELECT ost_ticket.number AS "Ticket Number", ost_user.name AS "Created By", ost_department.name AS "Assigned Department", ost_ticket.created AS "Ticket Created", ost_ticket.est_duedate AS "Estimated duedate", ost_ticket.lastupdate AS "Last Ticket Update", ost_sla.name as "SLA" FROM ost_ticket, ost_user, ost_department, ost_sla WHERE ost_ticket.user_id=ost_user.id AND ost_ticket.dept_id=ost_department.id AND ost_ticket.sla_id=ost_sla.id AND ost_ticket.status_id=1 AND ost_ticket.staff_id=0;

    It would show up something like this:
    +---------------+----------------+---------------------+---------------------+---------------------+---------------------+--------------+
    | Ticket Number | Created By     | Assigned Department | Ticket Created      | Estimated duedate   | Last Ticket Update  | SLA          |
    +---------------+----------------+---------------------+---------------------+---------------------+---------------------+--------------+

    You can control what you want to see by changing status_id and staff_id, status_id=0 is open, staff_id=0 means no staff has been assigned.

    You can even create separate query, to show only out of SLA:

    SELECT ost_ticket.number AS "Ticket Number", ost_user.name AS "Created By", ost_department.name AS "Assigned Department", ost_ticket.created AS "Ticket Created", ost_ticket.est_duedate AS "Estimated duedate", ost_ticket.lastupdate AS "Last Ticket Update", ost_sla.name as "SLA" FROM ost_ticket, ost_user, ost_department, ost_sla WHERE ost_ticket.user_id=ost_user.id AND ost_ticket.dept_id=ost_department.id AND ost_ticket.sla_id=ost_sla.id AND ost_ticket.status_id=1 AND ost_ticket.staff_id=0 AND ost_ticket.isoverdue=1;

    After this you can easily insert this into a shell script .sh, something like this:

    #!/bin/bash

    #Some variables
    YOURDB=ost_ticket_database_you_got
    YOUREMAIL =yours@only.com

    echo
    echo
    echo "##############################"
    echo "# Current unassigned tickets #"
    echo "##############################"
    echo
    echo

    mysql -u root -e 'SELECT ost_ticket.number AS "Ticket Number", ost_user.name AS "Created By", ost_department.name AS "Assigned Department", ost_ticket.created AS "Ticket Created", ost_ticket.est_duedate AS "Estimated duedate", ost_ticket.lastupdate AS "Last Ticket Update", ost_sla.name as "SLA" FROM ost_ticket, ost_user, ost_department, ost_sla WHERE ost_ticket.user_id=ost_user.id AND ost_ticket.dept_id=ost_department.id AND ost_ticket.sla_id=ost_sla.id AND ost_ticket.status_id=1 AND ost_ticket.staff_id=0' $YOURDB

    echo
    echo
    echo "##############################"
    echo "# Current tickets out of SLA #"
    echo "##############################"
    echo
    echo
    mysql -u root -e 'SELECT ost_ticket.number AS "Ticket Number", ost_user.name AS "Created By", ost_department.name AS "Assigned Department", ost_ticket.created AS "Ticket Created", ost_ticket.est_duedate AS "Estimated duedate", ost_ticket.lastupdate AS "Last Ticket Update", ost_sla.name as "SLA" FROM ost_ticket, ost_user, ost_department, ost_sla WHERE ost_ticket.user_id=ost_user.id AND ost_ticket.dept_id=ost_department.id AND ost_ticket.sla_id=ost_sla.id AND ost_ticket.status_id=1 AND ost_ticket.isoverdue=1' $YOURDB

    # Lets send this to email
    mail -s "Weekly Report" $YOUREMAIL < /tmp/output

    You might want to change it up a little bit, e.g., if you are using a DB password, etc, you might want to make a local user who can do the select for extra security.

    Then all you have to do is set up a scron script like this:
    0 6 * * Mon /your/path/to/shellscript.sh > /tmp/output

    As I said, quite simple :)
Sign In or Register to comment.