Hi, getting a weird one for a while here...
When using advanced search sometimes it results in no tickets. Even when running queries I KNOW will pass (All tickets in x department that are open).

Could it be the space used for attachments maybe?

I was also playing around with the datepicker .js since it seems to pick a different date format than is in the database, but that might just be a red herring.

OSTicket version: v1.14.1 (f1e9e88)
Web Server: Microsoft-IIS/8.5
MySQL: 10.2.8
PHP: 7.2.27
Schema Version: 4bd47d94b10bd8a6bab35c119dadf41f
Space Used: 1407.02 MiB
Space used for attachments: 1322.52 MiB

@SeanZF

You'll have to dump the query and then run it manually to see if there are results. You can dump the query by preparing the search and before clicking search apply the following:

diff --git a/include/class.orm.php b/include/class.orm.php
index 7ac7145..110ab43 100644
--- a/include/class.orm.php
+++ b/include/class.orm.php
@@ -3456,6 +3456,7 @@ class MySqlExecutor
 extends MySqlPreparedExecutor {
     function execute() {
         $sql = $this->__toString();
+        var_dump(print $sql);
         if (!($this->stmt = db_query($sql, true, !$this->unbuffered)))
             throw new InconsistentModelException(
                 'Unable to prepare query: '.db_error().' '.$sql);

After applying, you can click Search and see the SQL query that runs to find the tickets. You can then copy the query and run it manually.

Cheers.

    KevinTheJedi , this is through the actual OSTicket search function, not through the back end database.

    If I run my queries through mysql they all pass no problem.

    Is this something that should be put in a php page?

    I'm not sure how to apply the query. Paste this directly into the search box? Like SQL Injection?

    Thanks for the reply, I'm happy to test anything that can help.

      @SeanZF

      this is through the actual OSTicket search function, not through the back end database.

      If I run my queries through mysql they all pass no problem.

      Right, but you needed to ensure that the query actually returns results in the database to eliminate this being the source of the issue. Since you say the queries run successfully in MySQL then that is not the issue.

      Is this something that should be put in a php page?

      I'm not sure how to apply the query. Paste this directly into the search box? Like SQL Injection?

      No, you cannot perform SQL injection as we have safety measure in place preventing that. You run the queries via MySQL on your osTicket database.

      Run a search and look at the error logs (IIS error logs, PHP error logs, Browser Console Logs (via Inspect mode), osTicket System logs) and see if you can find any related errors. If no errors, look at your server resources to see if the memory usage peaks or if the server runs out of disk space, etc. Also, see if PHP is configured with a maximum request time limit or maximum request size limit.

      Cheers.

        KevinTheJedi Took me a minute, since we don't use Git here to manage OSticket, but I added the string that dumps the SQL query into the browser.

        I'll try to include as much info as I can...

        In the browser console window SCRIPT1004: SCRIPT1004: Expected ';'

        No errors in IIS.

        No errors that I can see in PHP.

        No errors in OSTicket logs, unless there's hidden logging outside of the admin panel.

        Running the query manually (after removing in(1) at the end of this query) it returns nothing as well.

        The query:
        SELECT A1.numberASnumber, A1.ticket_idASticket_id, A1.sourceASsource, A1.ticket_pidASticket_pid, A1.flagsASflags, A1.createdAScreated, C1.subjectAScdatasubject, A1.isoverdueASisoverdue, A5.nameASusername, C1.priorityAScdatapriority, C2.firstnameASstafffirstname, C2.lastnameASstafflastname, C3.nameASteamname, A1.team_idASteam_id, MAX(A4.value) ASentriesanswers!36value, MAX(A8.value) ASuserorgentriesanswers!87value, A1.isanswered= '0' AS2GkBZxz, (SELECT COUNT(R0.id) AScountFROMost_threadQ7 JOINost_ticketQ8 ON (Q7.object_type= 'T' AND Q7.object_id= Q8.ticket_id) LEFT JOINost_thread_entryR0 ON (Q7.id= R0.thread_id) WHERE Q8.ticket_id= A1.ticket_idAND NOT R0.flags& 4 != 0) AS _thread_count, (SELECT COUNT(R1.id) AScountFROMost_threadQ7 JOINost_ticketQ8 ON (Q7.object_type= 'T' AND Q7.object_id= Q8.ticket_id) LEFT JOINost_thread_entryR0 ON (Q7.id= R0.thread_id) LEFT JOINost_attachmentR1 ON (R1.type= 'H' AND R0.id= R1.object_id) WHERE Q8.ticket_id= A1.ticket_idAND R1.inline= 0) AS _att_count, COALESCE(C2.lastname, C2.firstname, C3.name, 'zzz') ASassigneeFROMost_ticketA1 JOINost_ticket_statusA2 ON (A1.status_id= A2.id) JOINost_form_entryA3 ON (A3.object_type= 'T' AND A1.ticket_id= A3.object_id) LEFT JOINost_form_entry_valuesA4 ON (A3.id= A4.entry_idAND A4.field_id= 36) JOINost_userA5 ON (A1.user_id= A5.id) LEFT JOINost_organizationA6 ON (A5.org_id= A6.id) LEFT JOINost_form_entryA7 ON (A6.id= A7.object_idAND A7.object_type= 'O') LEFT JOINost_form_entry_valuesA8 ON (A7.id= A8.entry_idAND A8.field_id= 87) LEFT JOINost_threadB0 ON (B0.object_type= 'T' AND A1.ticket_id= B0.object_id) LEFT JOINost_thread_referralB1 ON (B0.id= B1.thread_id) LEFT JOINost_staffB2 ON (B1.object_type= 'S' AND B1.object_id= B2.staff_id) LEFT JOINost_threadB3 ON (A1.ticket_id= B3.object_idAND B3.object_type= 'C') LEFT JOINost_thread_referralB4 ON (B3.id= B4.thread_id) LEFT JOINost_staffB5 ON (B4.object_type= 'S' AND B4.object_id= B5.staff_id) LEFT JOINost_teamB6 ON (B1.object_type= 'E' AND B1.object_id= B6.team_id) LEFT JOINost_teamB7 ON (B4.object_type= 'E' AND B4.object_id= B7.team_id) LEFT JOINost_departmentB8 ON (B1.object_type= 'D' AND B1.object_id= B8.id) LEFT JOINost_departmentC0 ON (B4.object_type= 'D' AND B4.object_id= C0.id) LEFT JOINost_ticket__cdataC1 ON (A1.ticket_id= C1.ticket_id) LEFT JOINost_staffC2 ON (A1.staff_id= C2.staff_id) LEFT JOINost_teamC3 ON (A1.team_id= C3.team_id) WHERE A2.idIN (1, 3) AND A2.stateIN ('open', 'closed') AND A1.dept_id= 1 AND A1.topic_id= 23 AND A4.valueREGEXP '\"?1489(\"|,|$)|\"?717(\"|,|$)|\"?741(\"|,|$)|\"?752(\"|,|$)|\"?780(\"|,|$)|\"?798(\"|,|$)|\"?809(\"|,|$)' AND A8.valueREGEXP '\"?1874(\"|,|$)|\"?1875(\"|,|$)' AND ((A2.state= 'open' AND (A1.staff_id= 103 OR B2.staff_id= 103 OR B3.object_type= 'C' AND B5.staff_id= 103 OR A1.team_idIN (12) OR B6.team_idIN (12) OR B3.object_type= 'C' AND B7.team_idIN (12))) OR A1.dept_idIN (1, 13) OR B8.idIN (1, 13) OR B3.object_type= 'C' AND C0.idIN (1, 13)) GROUP BY A1.ticket_idORDER BY A1.createdDESC LIMIT 50int(1)

          @SeanZF

          Well, there you go. If the Query returns no results then there will be no results in osTicket. Apparently, you don't have tickets matching the criteria or you don't have access to the tickets matching the criteria.

          Cheers.

            KevinTheJedi Odd thing about that is that through the picker I'm selecting things that logically are true. I can view a ticket that matches all of the criteria, but the search still results in 0.

            I re-read your instructions and tried again, and I get an error when executing the query (but that might be because it gets broken up by PHP after the fact).

            When I get a search that fails again I'll repeat these steps. We use advanced search a lot, so it won't be hard.

              Write a Reply...