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

Fix Statistics page?

Hi,

we upgraded to 1.7 and whilst doing so, one of the mail collectors re downloaded 200+ tickets.

I have now deleted all these, but the statistics page now has 200+ tickets created on one day.

how can i clear this down? can i modify the data that is stored in the SQL, if so where would it be?

Comments

  • MattToner;38755 said:
    Hi,

    we upgraded to 1.7 and whilst doing so, one of the mail collectors re downloaded 200+ tickets.

    I have now deleted all these, but the statistics page now has 200+ tickets created on one day.

    how can i clear this down? can i modify the data that is stored in the SQL, if so where would it be?
    If i remember correctly the dashboard/statistics page gets its data from the ticket_event table.
  • perfect, found it and modified the table to suit the 'correct' results.

    Thank you.
  • I have to thank you for this, too.
    I did the same thing to my own ticket data, as well.
  • Similar problem

    Hi,

    I have the same issue. I have deleted all tickets from the application but the statistics still shows the old tickets. However, when I deleted the records in table ticket_event, this is what I ended up with in the dashboard. https://www.dropbox.com/s/stlxlhyee20dyv0/osticket%20issue.JPG

    Any idea why everything is 1?
    I want to zero everything.
  • sayian;38926 said:
    Hi,

    I have the same issue. I have deleted all tickets from the application but the statistics still shows the old tickets. However, when I deleted the records in table ticket_event, this is what I ended up with in the dashboard. https://www.dropbox.com/s/stlxlhyee20dyv0/osticket%20issue.JPG

    Any idea why everything is 1?
    I want to zero everything.
    I think I read something similar.
    Did you create any new tickets AFTER you cleared out all the ticket events?
  • No I haven't when I posted the screenshot above. There was zero tickets.

    The next screenshot is after I created 1 ticket in Support department:
    https://www.dropbox.com/s/wu0mi0b3dz50c6m/osticket%20issue2.JPG

    And this screenshot is after I closed this ticket:
    https://www.dropbox.com/s/8wfbax5sxijr0ve/osticket%20issue3.JPG


    Any idea?
    oh and btw what does these red and green dots mean exactly?


    Edit:
    I did a clean installation and it's the same issue. This screenshot is from the clean installation
    https://www.dropbox.com/s/004tjrepgnyfk9j/osticket%20issue4.JPG
  • On empty databases (or no valid data) the dashboard reports 1's instead of 0's its a known issue.
  • ntozier;39004 said:
    On empty databases (or no valid data) the dashboard reports 1's instead of 0's its a known issue.

    I made the following changes in ajax.reports.php and the issue was resolved.

    Right after
            # XXX: Die if $group not in $groups


    I replaced
            $queries=array(
    array(5, 'SELECT '.$info['fields'].',
    COUNT(*)-COUNT(NULLIF(A1.state, \"created\")) AS Opened,
    COUNT(*)-COUNT(NULLIF(A1.state, \"assigned\")) AS Assigned,
    COUNT(*)-COUNT(NULLIF(A1.state, \"overdue\")) AS Overdue,
    COUNT(*)-COUNT(NULLIF(A1.state, \"closed\")) AS Closed,
    COUNT(*)-COUNT(NULLIF(A1.state, \"reopened\")) AS Reopened
    FROM '.$info['table'].' T1
    LEFT JOIN '.TICKET_EVENT_TABLE.' A1
    ON (A1.'.$info['pk'].'=T1.'.$info['pk'].'
    AND NOT annulled
    AND (A1.timestamp BETWEEN '.$start.' AND '.$stop.'))
    LEFT JOIN '.STAFF_TABLE.' S1 ON (S1.staff_id=A1.staff_id)
    WHERE '.$info['filter'].'
    GROUP BY T1.'.$info['pk'].'
    ORDER BY '.$info['sort']),

    with this
            $queries=array(
    array(5, 'SELECT '.$info['fields'].',
    SUM(CASE WHEN A1.state = \"created\" THEN 1 ELSE 0 END) AS Opened,
    SUM(CASE WHEN A1.state = \"assigned\" THEN 1 ELSE 0 END) AS Assigned,
    SUM(CASE WHEN A1.state = \"overdue\" THEN 1 ELSE 0 END) AS Overdue,
    SUM(CASE WHEN A1.state = \"closed\" THEN 1 ELSE 0 END) AS Closed,
    SUM(CASE WHEN A1.state = \"reopened\" THEN 1 ELSE 0 END) AS Reopened
    FROM '.$info['table'].' T1
    LEFT JOIN '.TICKET_EVENT_TABLE.' A1 ON (A1.'.$info['pk'].'=T1.'.$info['pk'].' AND NOT annulled AND (A1.timestamp BETWEEN '.$start.' AND '.$stop.'))
    LEFT JOIN '.STAFF_TABLE.' S1 ON (S1.staff_id=A1.staff_id)
    WHERE '.$info['filter'].'
    GROUP BY T1.'.$info['pk'].'
    ORDER BY '.$info['sort']),


    I have only 1 ticket in my db since we just started using it and it shows correctly under what department and helptopic we have some data the rest shows 0, if some1 can try it on bulk data we can confirm to have solved this issue.
  • Was the above code change found to work? We are looking into correcting the 0's and 1's issue as well.

    Edit:
    We made the above coding change and it has worked as expected.
  • I just tried the fix and it worked for me (merali780's fix)
  • Thanks
    merali780;40634 said:
    I made the following changes in ajax.reports.php and the issue was resolved.

    Right after
            # XXX: Die if $group not in $groups


    I replaced
            $queries=array(
    array(5, 'SELECT '.$info['fields'].',
    COUNT(*)-COUNT(NULLIF(A1.state, \"created\")) AS Opened,
    COUNT(*)-COUNT(NULLIF(A1.state, \"assigned\")) AS Assigned,
    COUNT(*)-COUNT(NULLIF(A1.state, \"overdue\")) AS Overdue,
    COUNT(*)-COUNT(NULLIF(A1.state, \"closed\")) AS Closed,
    COUNT(*)-COUNT(NULLIF(A1.state, \"reopened\")) AS Reopened
    FROM '.$info['table'].' T1
    LEFT JOIN '.TICKET_EVENT_TABLE.' A1
    ON (A1.'.$info['pk'].'=T1.'.$info['pk'].'
    AND NOT annulled
    AND (A1.timestamp BETWEEN '.$start.' AND '.$stop.'))
    LEFT JOIN '.STAFF_TABLE.' S1 ON (S1.staff_id=A1.staff_id)
    WHERE '.$info['filter'].'
    GROUP BY T1.'.$info['pk'].'
    ORDER BY '.$info['sort']),

    with this
            $queries=array(
    array(5, 'SELECT '.$info['fields'].',
    SUM(CASE WHEN A1.state = \"created\" THEN 1 ELSE 0 END) AS Opened,
    SUM(CASE WHEN A1.state = \"assigned\" THEN 1 ELSE 0 END) AS Assigned,
    SUM(CASE WHEN A1.state = \"overdue\" THEN 1 ELSE 0 END) AS Overdue,
    SUM(CASE WHEN A1.state = \"closed\" THEN 1 ELSE 0 END) AS Closed,
    SUM(CASE WHEN A1.state = \"reopened\" THEN 1 ELSE 0 END) AS Reopened
    FROM '.$info['table'].' T1
    LEFT JOIN '.TICKET_EVENT_TABLE.' A1 ON (A1.'.$info['pk'].'=T1.'.$info['pk'].' AND NOT annulled AND (A1.timestamp BETWEEN '.$start.' AND '.$stop.'))
    LEFT JOIN '.STAFF_TABLE.' S1 ON (S1.staff_id=A1.staff_id)
    WHERE '.$info['filter'].'
    GROUP BY T1.'.$info['pk'].'
    ORDER BY '.$info['sort']),


    I have only 1 ticket in my db since we just started using it and it shows correctly under what department and helptopic we have some data the rest shows 0, if some1 can try it on bulk data we can confirm to have solved this issue.

    Thanks!!! it's working!
  • Hi,

    I tried this. it worked well and statistics was also cleared out with nothing showing.

    But immediately I started getting DB error # 1064 alerts. And after creating fresh new tickets, its not reflecting on statistics page.

    So kindly help, I kinda messed it up now :(
  • I have tried this code and I am also getting "DB error # 1064 alerts". Does anybody know when this issue may be fixed, are Osticket currently working towards a solution?

     

    Cheers

  • @DanRhodes osTicket is not working on a solution for a mod written by a community member.  You would be best served by contacting the author and getting them to update their code.  Especially since 1.8.1 has been released.  1.7 is really old at this point and being phased out.
  • With this, for v1.9.14:
     - Thanks to @merali780
            $queries=array(
    array(5, 'SELECT '.$info['fields'].',
    SUM(CASE WHEN A1.state = "created" THEN 1 ELSE 0 END) AS Opened,
    SUM(CASE WHEN A1.state = "assigned" THEN 1 ELSE 0 END) AS Assigned,
    SUM(CASE WHEN A1.state = "overdue" THEN 1 ELSE 0 END) AS Overdue,
    SUM(CASE WHEN A1.state = "closed" THEN 1 ELSE 0 END) AS Closed,
    SUM(CASE WHEN A1.state = "reopened" THEN 1 ELSE 0 END) AS Reopened
    FROM '.$info['table'].' T1
    LEFT JOIN '.TICKET_EVENT_TABLE.' A1 ON (A1.'.$info['pk'].'=T1.'.$info['pk'].' AND NOT annulled AND (A1.timestamp BETWEEN '.$start.' AND '.$stop.'))
    LEFT JOIN '.STAFF_TABLE.' S1 ON (S1.staff_id=A1.staff_id)
    WHERE '.$info['filter'].'
    GROUP BY T1.'.$info['pk'].'
    ORDER BY '.$info['sort']),
Sign In or Register to comment.