J
jdelisle

  • Nov 17, 2020
  • Joined Nov 13, 2015
  • 0 best answers
  • I'm a long-time osTicket user and have updated from back in the 1.8 days all the way to 1.14.3.

    My custom email templates are based on those old 1.xx templates from years ago, and I'm thinking they've probably been improved since then so I'd like to re-do my customizations starting from the current email templates.

    Is there a way to install the latest 1.14.3 email templates? It seems they are not being installed or updated by the upgrader.

  • Our not-for-profit organization provides new mothers with gently used baby clothes and supplies. COVID-19 requires us to cease operation for obvious reasons. I need to alert all the people waiting for baby items about this.

    I'm looking for a way to:

    1. Change all open tickets to status "On hold - COVID-19"
    2. Send a COVID-19 canned response

    I have several hundred open tickets, so doing this by hand is unattractive.. Is there a way to do this with filters, or with a little PHP?

    Thanks!

  • Hello - I'm looking for some help, and am making a suggestion.

    My suggestion is simple: osTicket needs full reCaptcha support.

    Why? We run a public osTicket installation (recently upgraded to 1.12), and someone has decided to run a distributed brute-force attack against it. They appear to have automated this across approx. 3,000+ systems distributed globally, but primarily in Russia, China, and North America. They're guessing passwords against legitimate accounts and appear to coordinate with each other as they cycle accounts. We use the LDAP plugin for account authentication against OpenLDAP.

    From the Apache logs, I see over 3,000 unique IPs POSTing to /scp/login.php, but do not see anything helpful like "failed attempt against ID xyz from IP abc". In order to see what was happening I use Apache mod_security to dump and log POSTed data, but this is not great for server performance, doesn't tell me if an attempt succeeded or failed, etc.

    I speculate that because they're using so many unique IPs, and because they're using many different IDs when attempting to login, that it's not triggering the "X failed logins in Y time" logic and not effectively blocking the attack.

    I'm wondering:

    1. What built-in functionality, plugins, or mods should I be aware of that might help?
    2. How can I enable (preferably) reCaptcha on all pages where authentication takes place, for agents AND users? This should effectively stop the brute-force attack.
    3. How can I output log messages to a file (file or syslog, but database/email logs are not helpful to me) indicating that a failed login attempt has occurred? I need to know the ID they attempted and the source IP of the attempting system. This would enable me to use Fail2Ban to dynamically firewall bad-actors.

    If the attack were not distributed, this would be much easier as I could just block IPs that visit the login page too many times a day.. but some IPs only hit it once or twice a day, much like a typical user would.

    Looking for ideas..

    Thanks

  • I have the same issue:

    PHP 7.2.10
    mysql Ver 15.1 Distrib 5.5.60-MariaDB

    [SELECT COUNT(DISTINCT CASE WHEN A1.dept_id = 7 THEN A1.ticket_id END) AS q20, COUNT(DISTINCT CASE WHEN A1.dept_id = 7 THEN A1.ticket_id END) AS q21, COUNT(DISTINCT CASE WHEN A1.dept_id = 7 THEN A1.ticket_id END) AS q22, COUNT(DISTINCT CASE WHEN A2.state = 'open' AND A2.state = 'open' AND A1.dept_id = 7 THEN A1.ticket_id END) AS q23, COUNT(DISTINCT CASE WHEN A1.dept_id = 7 THEN A1.ticket_id END) AS q24, COUNT(DISTINCT CASE WHEN THEN A1.ticket_id END) AS q25, COUNT(DISTINCT CASE WHEN A1.dept_id = 7 THEN A1.ticket_id END) AS q26, COUNT(DISTINCT CASE WHEN A2.state = 'open' AND A1.isanswered = '1' AND A2.state = 'open' AND A1.isanswered = '1' AND A2.id IN (11) THEN A1.ticket_id END) AS q30, COUNT(DISTINCT CASE WHEN A2.state = 'open' THEN A1.ticket_id END) AS q1, COUNT(DISTINCT CASE WHEN A2.state = 'open' AND A2.state = 'open' AND A1.isanswered = '0' THEN A1.ticket_id END) AS q2, COUNT(DISTINCT CASE WHEN (A1.team_id IN (8) OR A1.staff_id IN (9)) AND A2.state = 'open' AND (A1.team_id IN (8) OR A1.staff_id IN (9)) AND A2.state = 'open' AND A1.staff_id IN (9) THEN A1.ticket_id END) AS q6, COUNT(DISTINCT CASE WHEN A2.state = 'closed' AND A2.state = 'closed' AND A1.closed BETWEEN '2019-06-15 00:00:00' AND '2019-06-15 23:59:59' THEN A1.ticket_id END) AS q9, COUNT(DISTINCT CASE WHEN A2.state = 'open' AND A2.state = 'open' AND A1.isanswered = '1' THEN A1.ticket_id END) AS q3, COUNT(DISTINCT CASE WHEN (A1.team_id IN (8) OR A1.staff_id IN (9)) AND A2.state = 'open' AND (A1.team_id IN (8) OR A1.staff_id IN (9)) AND A2.state = 'open' AND NOT A1.staff_id IN (9) THEN A1.ticket_id END) AS q7, COUNT(DISTINCT CASE WHEN A2.state = 'closed' AND A2.state = 'closed' AND A1.closed BETWEEN '2019-06-14 00:00:00' AND '2019-06-14 23:59:59' THEN A1.ticket_id END) AS q10, COUNT(DISTINCT CASE WHEN A2.state = 'open' AND A2.state = 'open' AND A1.isoverdue = '1' THEN A1.ticket_id END) AS q4, COUNT(DISTINCT CASE WHEN (A1.team_id IN (8) OR A1.staff_id IN (9)) AND A2.state = 'open' THEN A1.ticket_id END) AS q5, COUNT(DISTINCT CASE WHEN A2.state = 'closed' AND A2.state = 'closed' AND A1.closed BETWEEN '2019-06-10 00:00:00' AND '2019-06-16 23:59:59' THEN A1.ticket_id END) AS q11, COUNT(DISTINCT CASE WHEN A2.state = 'closed' THEN A1.ticket_id END) AS q8, COUNT(DISTINCT CASE WHEN A2.state = 'closed' AND A2.state = 'closed' AND A1.closed BETWEEN '2019-06-01 00:00:00' AND '2019-06-30 23:59:59' THEN A1.ticket_id END) AS q12, COUNT(DISTINCT CASE WHEN A2.state = 'closed' AND A2.state = 'closed' AND A1.closed BETWEEN '2019-04-01 00:00:00' AND '2019-06-30 23:59:59' THEN A1.ticket_id END) AS q13, COUNT(DISTINCT CASE WHEN A2.state = 'closed' AND A2.state = 'closed' AND A1.closed BETWEEN '2019-01-01 00:00:00' AND '2019-12-31 23:59:59' THEN A1.ticket_id END) AS q14 FROM ost_ticket A1 JOIN ost_ticket_status A2 ON (A1.status_id = A2.id) LEFT JOIN ost_thread A3 ON (A3.object_type = 'T' AND A1.ticket_id = A3.object_id) LEFT JOIN ost_thread_referral A4 ON (A3.id = A4.thread_id) LEFT JOIN ost_staff A5 ON (A4.object_type = 'S' AND A4.object_id = A5.staff_id) LEFT JOIN ost_team A6 ON (A4.object_type = 'E' AND A4.object_id = A6.team_id) LEFT JOIN ost_department A7 ON (A4.object_type = 'D' AND A4.object_id = A7.id) WHERE (A2.state = 'open' AND (A1.staff_id = 9 OR A5.staff_id = 9 OR A1.team_id IN (8) OR A6.team_id IN (8))) OR A1.dept_id IN (4, 7, 11) OR A7.id IN (4, 7, 11)]

    You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'THEN A1.ticket_id END) AS q25, COUNT(DISTINCT CASE WHEN A1.dept_id = 7 THE' at line 1<br />
    <br />
    ---- Backtrace ----<br />
    #0 (root)/include/mysqli.php(199): osTicket->logDBError('DB Error #1064', '[SELECT COUNT(D...')<br />
    #1 (root)/include/class.orm.php(3455): db_query('SELECT COUNT(DI...', true, true)<br />
    #2 (root)/include/class.orm.php(3502): MySqlExecutor->execute()<br />
    #3 (root)/include/class.orm.php(2085): MySqlExecutor->getArray()<br />
    #4 (root)/include/class.orm.php(2035): HashArrayIterator->{closure}()<br />
    #5 (root)/include/class.orm.php(2014): CallbackSimpleIterator->next()<br />
    #6 (root)/include/class.orm.php(2023): CallbackSimpleIterator->rewind()<br />
    #7 (root)/include/class.orm.php(1695): CallbackSimpleIterator->valid()<br />
    #8 (root)/include/class.orm.php(1705): CachedResultSet->fillTo(9223372036854775807)<br />
    #9 (root)/include/class.orm.php(1328): CachedResultSet->asArray()<br />
    #10 (root)/include/class.orm.php(1351): QuerySet->all()<br />
    #11 (root)/include/class.search.php(925): QuerySet->one()<br />
    #12 (root)/include/class.search.php(865): SavedQueue::counts(Object(StaffSession), true)<br />
    #13 (root)/include/staff/templates/queue-tickets.tmpl.php(96): SavedQueue->getCount(Object(StaffSession))<br />
    #14 (root)/scp/tickets.php(526): require_once('/opt/osTicket-1...')<br />
    #15 {main}

  • Same issue here - moved to Amazon SES, and now all replies generate new tickets.

    I've confirmed my subject lines are the same [#12345] ticket number format.

  • Just here to express interest in a SAML plugin too.  That'd be great!  Modern SSO support is critical for people using cloud Identity as a Service.

  • @[deleted] -  Thanks, hope it comes in helpful to someone.  One thing I should mention is that the SQL above doesn't update the ticket "Request Thread" (the history of changes made to the ticket, things like "System assigned this ticket to team ABC").  Some shops might have audit requirements, or maybe infosec concerns, and might be concerned about this.  It is doable, but the SQL above would need considerable modification to add an entry along the lines of "System changed SLA from X to Y".

  • >In the UI you could perform a search for those records,If only... Unfortunately an advanced-search interface bug prevents me from doing exactly that.  I need to change the status based on a choice made in a custom field.  See https://github.com/osTicket/osTicket/issues/2625Anyhow, here's what I ended up doing in SQL, in case someone else runs into this.   In my situation, the user has selected a custom-field drop-down where the value is one of "Boy", "Girl", or other options that do not include the words "Boy" or "Girl".  Based on the value of that field, I need to set the status to "Waitlist - Boy", "Waitlist - Girl", or "Waitlist - Other".   This is why there are three SQL statements below.You'll need to have some SQL knowledge, and figure out the IDs of various elements in the data, like the status IDs, the form IDs, the field IDs, etc.OTHERupdate  ost_ticket, ost_form_entry, ost_form_entry_values, ost_ticket_statusset ost_ticket.status_id = 14 ## The status ID for "Waitlist - Other" where ost_ticket.status_id = 1 AND ## I only want to affect "Open" tickets, you could substitute any status ID here ost_ticket.status_id = ost_ticket_status.id AND ost_form_entry.form_id=8 AND ## This is the ID of the form that holds my field ost_form_entry.object_type = "T" AND ## This ensures the entry is related to a Ticket ost_ticket.ticket_id = ost_form_entry.object_id AND ost_form_entry_values.entry_id = ost_form_entry.id AND ost_form_entry_values.field_id = 41 AND ## I know this is the field that contains "Boy", "Girl", or other stuff ost_form_entry_values.value not like "%girl%" AND ## Since I'm targeting the other non-boy non-girl values, I negate these two to get results without boy or girl ost_form_entry_values.value not like "%boy%";Looks like I can't paste the other SQL commands here, I'm over my character limit on this post.. So.. just adjust the SQL above to filter for different values, and adjust the status ID to assign the correct status based on those values. Hope this helps someone.

  • I decided to do this in SQL.  In case someone else needs this, here's what I did:update ost_ticket, ost_ticket_status set  ost_ticket.sla_id = "2",  ost_ticket.isoverdue=0,  ost_ticket.est_duedate=date_add(ost_ticket.created, interval 8760 hour) where  ost_ticket.topic_id=12 and  ost_ticket.status_id = ost_ticket_status.id and  ost_ticket_status.state = "open";This finds all tickets that are help topic ID 12 and in a status where the ticket is in an open state, and then unsets the over-due flag, changes the SLA plan to the correct SLA plan ID, and sets the due-date to the ticket created date +1 year (same as the SLA plan, 1 year before it's past-due).Thanks

  • I need to change the ticket status of about 300 tickets, based on the value of a custom form field.  This is easy to do for new tickets, using filters.  However that only applies to newly created tickets, not existing tickets, and I need to modify the existing ones.Can I do this via sql somehow, or through any other means of automating it?Thanks

  • Hello,I have about 300 tickets that need to have their SLA adjusted.  I've defined a new SLA, and now I want to apply it to all the existing tickets.Is there an automated way I can do this?  Perhaps directly in the database, by modifying the "sla_id" of each ticket?Thanks

  • I really hope we can add a custom English language pack, to allow administrators to tailor their osTicket environment to their purpose.For example, I don't have staff, I have volunteers.  I don't have tickets, I have requests.  I don't have customers, I have applicants.  You get the idea.I've made this work so far by adding a custom language based on the en_GB translation, and search/replacing things in there.I'd like to be able to add it as us_EN, replacing osTicket terminology with my own.

  • @[deleted], I think you're right, the first step I recommended (getting the latest LDAP2 etc.) may be redundant.

  • @[deleted] - Super!  I'm not great with git.  Thanks for your help and suggestions with this and the other issues I reported over at the project github page.Is there a maintainer of the LDAP plugin who we could notify to update the LDAP2 package to address the other part of the problem, so people can just download the phar and not need to jump through these hoops?

  • Step-by-step to fix the osTicket 1.10 rc3 auth-ldap issue:Update your installed php-pear-Net-LDAP2 package to 2.2.0 or greater.  On CentOS 7, I did this by first installing the Remi repo, then uninstalling my old php-pear-Net-LDAP2 package, then installing the new package from the Remi repo.  See this page for details: https://pkgs.org/centos-7/remi-x86_64/php-pear-Net-LDAP2-2.2.0-1.el7.remi.noarch.rpm.html  I should note that I don't know if this is even necessary, since when you hydrate in the next steps, it might be downloading v. 2.2.0+ from somewhere.  I don't know... but this is what I did.Install the osTicket-plugins stuff:  git clone --branch develop https://github.com/osTicket/osTicket-plugins.gitedit make.php - search for http://pear.php.net and change it to https://pear.php.netphp make.php hydratephp -dphar.readonly=0 make.php build auth-ldapcp auth-ldap.phar /your/osticket/plugins/directorysystemctl httpd restartThis fixed my issue, and auth-ldap.phar worked perfectly to authenticate users just as it did in 1.10 rc2.

  • I'm trying to use a specific Thank-you page per Help Topic.  osTicket Versionv1.10-rc.2 (231f11e) —  Up to dateWeb Server SoftwareApache/2.4.6 (CentOS) OpenSSL/1.0.1e-fips mod_wsgi/3.4 Python/2.7.5MySQL Version5.5.44PHP Version5.4.16I have the default system installed Thank-you page, and have added several others specifically for various Help Topics.In Settings -> Company -> Site Pages, Default Thank-you Page is set to "Thank You".  In each of my Manage -> Help Topic -> (some help topic) -> New Ticket Options -> Thank-you Page, I have set to one of my custom thank you pages.In spite of having set a Thank You page at the Help Topic level, the Company level default is overruling my choice.  I've even tried disabling the system default "Thank you" page, and it still uses that one.   When I create a new ticket (as a not logged in user), I am directed to the system default thank-you page, even when it's disabled.  This happens no matter how I set the Thank You page at the Help Topic level.Any suggestions appreciated - I have very different needs between Help Topics and this is necessary.

  • I'd be interested in this one too, is there a location where mods are generally made available for download?  Some sort of mod repo?

  • Thanks ntozier and Chefkeks.  I retrieved the OAuth plugin from osticket.com/download-edge as a PHAR file, so I assumed it was "legit".Anyhow, I've decided to steer clear of it since it sounds like it's maybe not ready for prime-time, and I've moved to LDAP.In case someone googling finds this, and wants to get Google Apps for Domains authentication working with osTicket, the easiest solution I found was to use a third-party IDaaS/ DaaS service, use them to manage my cloud-based identities, and have them sync to Google Apps.  I did this so I could expose the IDs via LDAP without needing to run my own LDAP server.  Then, I enable LDAP on that service, install the LDAP plugin on osTicket, and point it at that service.  I ended up using JumpCloud, and it's working great!  They offer LDAP and free use for a small number of users, and have nicely documented LDAP configs.  It's working really well.

  • That would work - I'll need to export it from MySQL to Fail2Ban somehow, but it'll do!  Any chance you know of a way to get osTicket to log directly to a text file?