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.

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(https://www.dropbox.com/s/stlxlhyee20dyv0/osticket%20issue.JPG)

Any idea why everything is 1?

I want to zero everything.

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(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(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(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(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.

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.',

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.' T1

LEFT JOIN '.TICKET_EVENT_TABLE.' A1

ON (A1.'.$info.'=T1.'.$info.'

AND NOT annulled

AND (A1.timestamp BETWEEN '.$start.' AND '.$stop.'))

LEFT JOIN '.STAFF_TABLE.' S1 ON (S1.staff_id=A1.staff_id)

WHERE '.$info.'

GROUP BY T1.'.$info.'

ORDER BY '.$info),

with this

$queries=array(

array(5, 'SELECT '.$info.',

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.' T1

LEFT JOIN '.TICKET_EVENT_TABLE.' A1 ON (A1.'.$info.'=T1.'.$info.' AND NOT annulled AND (A1.timestamp BETWEEN '.$start.' AND '.$stop.'))

LEFT JOIN '.STAFF_TABLE.' S1 ON (S1.staff_id=A1.staff_id)

WHERE '.$info.'

GROUP BY T1.'.$info.'

ORDER BY '.$info),

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

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.',

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.' T1

LEFT JOIN '.TICKET_EVENT_TABLE.' A1

ON (A1.'.$info.'=T1.'.$info.'

AND NOT annulled

AND (A1.timestamp BETWEEN '.$start.' AND '.$stop.'))

LEFT JOIN '.STAFF_TABLE.' S1 ON (S1.staff_id=A1.staff_id)

WHERE '.$info.'

GROUP BY T1.'.$info.'

ORDER BY '.$info),

with this

$queries=array(

array(5, 'SELECT '.$info.',

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.' T1

LEFT JOIN '.TICKET_EVENT_TABLE.' A1 ON (A1.'.$info.'=T1.'.$info.' AND NOT annulled AND (A1.timestamp BETWEEN '.$start.' AND '.$stop.'))

LEFT JOIN '.STAFF_TABLE.' S1 ON (S1.staff_id=A1.staff_id)

WHERE '.$info.'

GROUP BY T1.'.$info.'

ORDER BY '.$info),

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.', 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.' T1 LEFT JOIN '.TICKET_EVENT_TABLE.' A1 ON (A1.'.$info.'=T1.'.$info.' AND NOT annulled AND (A1.timestamp BETWEEN '.$start.' AND '.$stop.')) LEFT JOIN '.STAFF_TABLE.' S1 ON (S1.staff_id=A1.staff_id) WHERE '.$info.' GROUP BY T1.'.$info.' ORDER BY '.$info),