[ Is there any easy way to show tickets closed in a week? ] - otrs - OTRS mailing lists

newer
Ticket updates to groups

[ Is there any easy way to show tickets closed in a week? ]

older
OTRS Mail Relay function

BCWINFO

6 Mar 2008 6 Mar '08

10:41 a.m.

This is for my boss! ;-) I tried Search but I couldn't do that. Any suggestions? Diego

Attachments:

attachment.html (text/html — 218 bytes)

Reply

Sign in to reply online Use email software

Show replies by date

Willem Gerber

6 Mar 6 Mar

11:07 a.m.

Youll need to run a sql query on the database. This is what we do select tn, title, owner, customer_id, FROM_UNIXTIME(escalation_solution_time) time_completed,create_time, floor((escalation_solution_time-create_time_unix)/86400) as days, (floor((escalation_solution_time-create_time_unix)/3600)-(floor((escalation_solution_time-create_time_unix)/86400)*24)) as hours, (floor((escalation_solution_time-create_time_unix)/60 )-(floor((escalation_solution_time-create_time_unix)/3600)*60)) as minutes, ( (escalation_solution_time-create_time_unix)-(floor((escalation_solution_time-create_time_unix)/60)*60)) as seconds from ticketsview where queuename = 'loyalty' and state = 'Closed successful' and datediff(FROM_UNIXTIME(escalation_solution_time), now()) >= -30 Obviously change the quename to which one you want to check aswell as change the -30 too -7 that would show you the tickets closed in the past 7 days. Regards Willem Gerber BCWINFO wrote:

This is for my boss!

;-)

I tried Search but I couldn't do that.

Any suggestions?

Diego ------------------------------------------------------------------------

_______________________________________________ OTRS mailing list: otrs - Webpage: http://otrs.org/ Archive: http://lists.otrs.org/pipermail/otrs To unsubscribe: http://lists.otrs.org/cgi-bin/listinfo/otrs Support or consulting for your OTRS system? => http://www.otrs.com/

-- Avoid the Gates of Hell. Use Linux -- unknown source

Reply

Sign in to reply online Use email software

Andrew Stesin

11:14 a.m.

It's easy but needs a bit of daily work. 1. Establish some timestamp at which daily data is to be taken. Say 8:00. 2. Take Excel and create a table with the following columns: - column A: date - column B: the rest taken - column C: new - column D: closed - column E: the rest given 3. Let's start from today: 2008.03.06. At 8:00 daily do the following: - write `2008.03.05' into cell A2 - we are making a daily report for the 24 hr. period between 08:00 05.03.2008 and 08:00 06.03.2008 - skip the cell B2 (we do not know, how many tickets were open at 08:00 04.03 and were taken into further processing) - search for the tickets, which were created in the last 24 hours, find the quantity, fill into the cell C2 - search for the tickets, which are in open/delayed/... state at the moment (anything but "closed" state). Fill into the cell E2. 4. The day after: - write `2008.03.06' into cell A2 - we are making a daily report for the 24 hr. period between 08:00 06.03.2008 and 08:00 07.03.2008 - put the formula =E2 into the cell B3. Today we know, that column E from yesterday gives us an exact quantity of non-closed tickets at 08:00 - search for the tickets, which were created in the last 24 hours, find the quantity, fill into the cell C3 - search for the tickets, which are in open/delayed/... state at the moment (anything but "closed" state). Fill into the cell E3. - put the formula =E3-(B3+C3) into the cell D3 Voila, you have your data for a daily report for your boss :) Repeat daily! :) WBR, Andrew 2008/3/6, BCWINFO :

This is for my boss!

;-)

I tried Search but I couldn't do that.

Any suggestions?

Diego

Reply

Sign in to reply online Use email software

Andrew Stesin

11:15 a.m.

2008/3/6, Andrew Stesin :

4. The day after:

- write `2008.03.06' into cell A2 - we are making a daily report for

Sorry for misprint, of course we are working with 3-rd row now, need to be A3 :)

Reply

Sign in to reply online Use email software

BCWINFO

12 Mar 12 Mar

2:06 p.m.

Andrew, it's perfect. But I need to know, for example, what are these tickets. It's important to have the subject for this tickets in hands. On Thu, Mar 6, 2008 at 8:14 AM, Andrew Stesin wrote:

It's easy but needs a bit of daily work.

1. Establish some timestamp at which daily data is to be taken. Say 8:00.

2. Take Excel and create a table with the following columns:

- column A: date - column B: the rest taken - column C: new - column D: closed - column E: the rest given

3. Let's start from today: 2008.03.06. At 8:00 daily do the following:

- write `2008.03.05' into cell A2 - we are making a daily report for the 24 hr. period between 08:00 05.03.2008 and 08:00 06.03.2008

- skip the cell B2 (we do not know, how many tickets were open at 08:00 04.03 and were taken into further processing)

- search for the tickets, which were created in the last 24 hours, find the quantity, fill into the cell C2

- search for the tickets, which are in open/delayed/... state at the moment (anything but "closed" state). Fill into the cell E2.

4. The day after:

- write `2008.03.06' into cell A2 - we are making a daily report for the 24 hr. period between 08:00 06.03.2008 and 08:00 07.03.2008

- put the formula =E2 into the cell B3. Today we know, that column E from yesterday gives us an exact quantity of non-closed tickets at 08:00

- search for the tickets, which were created in the last 24 hours, find the quantity, fill into the cell C3

- search for the tickets, which are in open/delayed/... state at the moment (anything but "closed" state). Fill into the cell E3.

- put the formula =E3-(B3+C3) into the cell D3

Voila, you have your data for a daily report for your boss :) Repeat daily! :)

WBR, Andrew

2008/3/6, BCWINFO :

...
This is for my boss!

;-)

I tried Search but I couldn't do that.

Any suggestions?

Diego

OTRS mailing list: otrs - Webpage: http://otrs.org/ Archive: http://lists.otrs.org/pipermail/otrs To unsubscribe: http://lists.otrs.org/cgi-bin/listinfo/otrs Support or consulting for your OTRS system? => http://www.otrs.com/

Reply

Sign in to reply online Use email software

Joshua Scott

6 Mar 6 Mar

2:14 p.m.

Have you tried the stats module? You should be able to easily create a report that will give you that information, but I personally have a php boilerplate app that I customize with SQL queries whenever I get a request like this from management, and send the link to them so they can run the report whenever they want. Josh On Thu, Mar 6, 2008 at 4:41 AM, BCWINFO wrote:

This is for my boss!

;-)

I tried Search but I couldn't do that.

Any suggestions?

Diego

_______________________________________________ OTRS mailing list: otrs - Webpage: http://otrs.org/ Archive: http://lists.otrs.org/pipermail/otrs To unsubscribe: http://lists.otrs.org/cgi-bin/listinfo/otrs Support or consulting for your OTRS system? => http://www.otrs.com/

Reply

Sign in to reply online Use email software

BCWINFO

12 Mar 12 Mar

1:07 p.m.

Tried search and it works fine. But there is not where to specify, for example, "closed last week". Using stats I only have numbers and this is not enough for me and for my boss. :( On Thu, Mar 6, 2008 at 11:14 AM, Joshua Scott wrote:

Have you tried the stats module?

You should be able to easily create a report that will give you that information, but I personally have a php boilerplate app that I customize with SQL queries whenever I get a request like this from management, and send the link to them so they can run the report whenever they want.

Josh

Reply

Sign in to reply online Use email software

6339

Age (days ago)

6345

Last active (days ago)

Download

6 comments

4 participants

tags

participants (4)

Andrew Stesin
BCWINFO
Joshua Scott
Willem Gerber