open ticket report monthly
Moderator: crythias
-
- Znuny expert
- Posts: 171
- Joined: 24 Apr 2013, 12:29
- Znuny Version: OTRS3.3
- Real Name: kalyanachakravarthy M P
- Company: TATA Technologies
- Location: Pune,India
- Contact:
open ticket report monthly
Hi ,
we are using last few month OTRS. now trying monthly report but report showing wrong data.
if any one month ticket is open and other month ticket close.final report showing only close status.
Please any help how to find SQL query month wise that means one ticket worked two months we need two moth last status of tickets.
we are using last few month OTRS. now trying monthly report but report showing wrong data.
if any one month ticket is open and other month ticket close.final report showing only close status.
Please any help how to find SQL query month wise that means one ticket worked two months we need two moth last status of tickets.
-
- Moderator
- Posts: 10169
- Joined: 04 May 2010, 18:38
- Znuny Version: 5.0.x
- Location: SouthWest Florida, USA
- Contact:
Re: open ticket report monthly
eh.
What do you want?
Define your parameters in English....
In the last two months, I need the State of all tickets? That seems big...
In the last two months, I need the State of all tickets that have been closed in the last two months? Oh... but they're closed.
In the last two months, I need the State of all tickets that have been created in the last two months?
What do you want?
Define your parameters in English....
In the last two months, I need the State of all tickets? That seems big...
In the last two months, I need the State of all tickets that have been closed in the last two months? Oh... but they're closed.
In the last two months, I need the State of all tickets that have been created in the last two months?
Code: Select all
select tn, title, ts.name from ticket left join ticket_state ts on ts.id=ticket.ticket_state_id where ticket.create_time between "2014-02-01" and "2014-04-01"
OTRS 6.0.x (private/testing/public) on Linux with MySQL database.
Please edit your signature to include your OTRS version, Operating System, and database type.
Click Subscribe Topic below to get notifications. Consider amending your topic title to include [SOLVED] if it is so.
Need help? Before you ask
Please edit your signature to include your OTRS version, Operating System, and database type.
Click Subscribe Topic below to get notifications. Consider amending your topic title to include [SOLVED] if it is so.
Need help? Before you ask
-
- Moderator
- Posts: 10169
- Joined: 04 May 2010, 18:38
- Znuny Version: 5.0.x
- Location: SouthWest Florida, USA
- Contact:
Re: open ticket report monthly
OTRS 6.0.x (private/testing/public) on Linux with MySQL database.
Please edit your signature to include your OTRS version, Operating System, and database type.
Click Subscribe Topic below to get notifications. Consider amending your topic title to include [SOLVED] if it is so.
Need help? Before you ask
Please edit your signature to include your OTRS version, Operating System, and database type.
Click Subscribe Topic below to get notifications. Consider amending your topic title to include [SOLVED] if it is so.
Need help? Before you ask
-
- Znuny expert
- Posts: 171
- Joined: 24 Apr 2013, 12:29
- Znuny Version: OTRS3.3
- Real Name: kalyanachakravarthy M P
- Company: TATA Technologies
- Location: Pune,India
- Contact:
Re: open ticket report monthly
Hi crythaias,crythias wrote:Also, viewtopic.php?f=53&t=14242#p67562
Code: Select all
SELECT u.login owner, ts.name state, th.name fromto, th.create_time createtime, th.ticket_id ticket_id
FROM `ticket_history` th
LEFT JOIN users u ON u.id = th.owner_id
LEFT JOIN ticket_state ts ON ts.id = th.state_id
WHERE th.history_type_id = '27'
OR th.history_type_id = '1'
ORDER BY ticket_id
Example:
ticket_id -- create time -- state
1 -- 12-02-2014 -- new
1 -- 16-02-2014 -- Open
1 -- 17-02-2014 -- hold(open)
1 -- 1-03-2014 -- close
we are trying Report is :
if month of february report
1 -- 17-02-2014 -- hold(open)
if month of March report
1 -- 1-03-2014 -- close
any know help this query of monthly report ticket status
-
- Znuny expert
- Posts: 171
- Joined: 24 Apr 2013, 12:29
- Znuny Version: OTRS3.3
- Real Name: kalyanachakravarthy M P
- Company: TATA Technologies
- Location: Pune,India
- Contact:
Re: open ticket report monthly
Code: Select all
SELECT ticket.tn, ticket.id,ticket_history.state_id,ticket_state.name, max(ticket_history.change_time) AS change_time
FROM ticket_history
LEFT JOIN ticket ON (ticket.id = ticket_history.ticket_id)
LEFT JOIN ticket_state ON (ticket_state.id = ticket_history.state_id)
LEFT JOIN ticket_state_type ON (ticket_state_type.id = ticket_state.type_id)
WHERE ticket_history.history_type_id = 27 AND
ticket_state_type.name !="closed"
GROUP BY ticket.tn;
At the moment it just groups the records and only gets the max date and not the row associated with the max date
-
- Moderator
- Posts: 10169
- Joined: 04 May 2010, 18:38
- Znuny Version: 5.0.x
- Location: SouthWest Florida, USA
- Contact:
Re: open ticket report monthly
According to my demo data, it pulls 5 columns. For every ticket number, it shows the state of the ticket.kalyana1315 wrote:At the moment it just groups the records and only gets the max date and not the row associated with the max date
OTRS 6.0.x (private/testing/public) on Linux with MySQL database.
Please edit your signature to include your OTRS version, Operating System, and database type.
Click Subscribe Topic below to get notifications. Consider amending your topic title to include [SOLVED] if it is so.
Need help? Before you ask
Please edit your signature to include your OTRS version, Operating System, and database type.
Click Subscribe Topic below to get notifications. Consider amending your topic title to include [SOLVED] if it is so.
Need help? Before you ask
-
- Znuny expert
- Posts: 171
- Joined: 24 Apr 2013, 12:29
- Znuny Version: OTRS3.3
- Real Name: kalyanachakravarthy M P
- Company: TATA Technologies
- Location: Pune,India
- Contact:
Re: open ticket report monthly
database:crythias wrote:According to my demo data, it pulls 5 columns. For every ticket number, it shows the state of the ticket.kalyana1315 wrote:At the moment it just groups the records and only gets the max date and not the row associated with the max date
'100005', ----------- '6', ----------- 'open', ---------------'2013-06-21 12:43:09'
'100005', -----------'6', -----------'review update',- --- '2013-06-21 12:44:44'
'100005', -----------'6', ----------- 'work in progress',--- '2013-06-21 12:47:03'
'100005', ----------- '6', -----------'open', ----------- ----'2013-06-21 13:04:31'
'100005', ----------- '6', ----------- 'Hold', ----------- ----'2013-06-21 13:16:46'
'100005', ----------- '6', ----------- 'review update',----- '2013-06-21 16:30:19'
'100045', -----------'46', ---------- 'work in progress',--- '2013-08-28 19:36:40'
'100045', ----------- '46', ----------- 'Hold', -------------- '2013-10-15 15:47:03'
'100045', ----------- '46', ----------- 'work in progress',--- '2013-12-11 13:03:22'
'100045', ----------- '46', -----------'closed', ----------- '2014-02-10 13:50:20'
output :
100005 ----------- 6 ----------- open --------------- 2013-06-21 16:30:19
100045 ----------- 46 ----------work in progress-- 2014-02-10 13:50:20
only gets the max date and not the row associated with the max date ?
-
- Moderator
- Posts: 10169
- Joined: 04 May 2010, 18:38
- Znuny Version: 5.0.x
- Location: SouthWest Florida, USA
- Contact:
Re: open ticket report monthly
order by change_time desc
OTRS 6.0.x (private/testing/public) on Linux with MySQL database.
Please edit your signature to include your OTRS version, Operating System, and database type.
Click Subscribe Topic below to get notifications. Consider amending your topic title to include [SOLVED] if it is so.
Need help? Before you ask
Please edit your signature to include your OTRS version, Operating System, and database type.
Click Subscribe Topic below to get notifications. Consider amending your topic title to include [SOLVED] if it is so.
Need help? Before you ask
-
- Znuny expert
- Posts: 171
- Joined: 24 Apr 2013, 12:29
- Znuny Version: OTRS3.3
- Real Name: kalyanachakravarthy M P
- Company: TATA Technologies
- Location: Pune,India
- Contact:
Re: open ticket report monthly
Hi crythias ,crythias wrote:order by change_time desc
change_time or create time shows correct but other rows first value only show this query
-
- Moderator
- Posts: 10169
- Joined: 04 May 2010, 18:38
- Znuny Version: 5.0.x
- Location: SouthWest Florida, USA
- Contact:
Re: open ticket report monthly
Sorry... let me search this as it's not an OTRS question.
OTRS 6.0.x (private/testing/public) on Linux with MySQL database.
Please edit your signature to include your OTRS version, Operating System, and database type.
Click Subscribe Topic below to get notifications. Consider amending your topic title to include [SOLVED] if it is so.
Need help? Before you ask
Please edit your signature to include your OTRS version, Operating System, and database type.
Click Subscribe Topic below to get notifications. Consider amending your topic title to include [SOLVED] if it is so.
Need help? Before you ask
-
- Znuny expert
- Posts: 171
- Joined: 24 Apr 2013, 12:29
- Znuny Version: OTRS3.3
- Real Name: kalyanachakravarthy M P
- Company: TATA Technologies
- Location: Pune,India
- Contact:
Re: open ticket report monthly
hicrythias wrote:Sorry... let me search this as it's not an OTRS question.
any SQL query for OTRS Number open ticket last month with last update state status.
-
- Moderator
- Posts: 10169
- Joined: 04 May 2010, 18:38
- Znuny Version: 5.0.x
- Location: SouthWest Florida, USA
- Contact:
Re: open ticket report monthly
The data you seek is OTRS data, yes. The sql query to obtain that information is not unique to OTRS.
You are using a query to grab distinct or group by based upon the max value of a column (regardless of data) for that group.
That's not an OTRS question. That's a SQL question.
You are using a query to grab distinct or group by based upon the max value of a column (regardless of data) for that group.
That's not an OTRS question. That's a SQL question.
OTRS 6.0.x (private/testing/public) on Linux with MySQL database.
Please edit your signature to include your OTRS version, Operating System, and database type.
Click Subscribe Topic below to get notifications. Consider amending your topic title to include [SOLVED] if it is so.
Need help? Before you ask
Please edit your signature to include your OTRS version, Operating System, and database type.
Click Subscribe Topic below to get notifications. Consider amending your topic title to include [SOLVED] if it is so.
Need help? Before you ask
-
- Moderator
- Posts: 10169
- Joined: 04 May 2010, 18:38
- Znuny Version: 5.0.x
- Location: SouthWest Florida, USA
- Contact:
Re: open ticket report monthly
from the search, I found this post
which means ...
which means ...
Code: Select all
SELECT ticket.tn, ticket.id,ticket_history.state_id,ticket_state.name, ticket.change_time AS change_time FROM ticket_history LEFT JOIN ticket ON (ticket.id = ticket_history.ticket_id) INNER JOIN (SELECT ticket.tn, MAX(ticket.change_time) as max_change FROM ticket_history LEFT JOIN ticket on ticket.id = ticket_history.ticket_id group by ticket.tn) grpmax ON ticket.tn=grpmax.tn AND ticket.change_time = grpmax.max_change LEFT JOIN ticket_state ON (ticket_state.id = ticket_history.state_id) LEFT JOIN ticket_state_type ON (ticket_state_type.id = ticket_state.type_id) WHERE ticket_history.history_type_id = 27 AND ticket_state_type.name !="closed" GROUP BY ticket.tn
OTRS 6.0.x (private/testing/public) on Linux with MySQL database.
Please edit your signature to include your OTRS version, Operating System, and database type.
Click Subscribe Topic below to get notifications. Consider amending your topic title to include [SOLVED] if it is so.
Need help? Before you ask
Please edit your signature to include your OTRS version, Operating System, and database type.
Click Subscribe Topic below to get notifications. Consider amending your topic title to include [SOLVED] if it is so.
Need help? Before you ask
-
- Znuny expert
- Posts: 171
- Joined: 24 Apr 2013, 12:29
- Znuny Version: OTRS3.3
- Real Name: kalyanachakravarthy M P
- Company: TATA Technologies
- Location: Pune,India
- Contact:
Re: open ticket report monthly
crythias wrote:from the search, I found this post
which means ...
Code: Select all
SELECT ticket.tn, ticket.id,ticket_history.state_id,ticket_state.name, ticket.change_time AS change_time FROM ticket_history LEFT JOIN ticket ON (ticket.id = ticket_history.ticket_id) INNER JOIN (SELECT ticket.tn, MAX(ticket.change_time) as max_change FROM ticket_history LEFT JOIN ticket on ticket.id = ticket_history.ticket_id group by ticket.tn) grpmax ON ticket.tn=grpmax.tn AND ticket.change_time = grpmax.max_change LEFT JOIN ticket_state ON (ticket_state.id = ticket_history.state_id) LEFT JOIN ticket_state_type ON (ticket_state_type.id = ticket_state.type_id) WHERE ticket_history.history_type_id = 27 AND ticket_state_type.name !="closed" GROUP BY ticket.tn
Hi crythias,
above query also show wrong values. ticket change time showing last change time and same problem not the row associated with the max date
-
- Moderator
- Posts: 10169
- Joined: 04 May 2010, 18:38
- Znuny Version: 5.0.x
- Location: SouthWest Florida, USA
- Contact:
Re: open ticket report monthly
Code: Select all
SELECT ticket.tn,
ticket.id,
ticket_history.state_id,
ticket_state.name,
ticket.change_time AS change_time
FROM ticket_history
LEFT JOIN ticket
ON ( ticket.id = ticket_history.ticket_id )
INNER JOIN (SELECT ticket.tn,
Max(ticket.change_time) AS max_change
FROM ticket_history
LEFT JOIN ticket
ON ticket.id = ticket_history.ticket_id
LEFT JOIN ticket_state
ON ( ticket_state.id = ticket_history.state_id )
LEFT JOIN ticket_state_type
ON ( ticket_state_type.id = ticket_state.type_id )
WHERE ticket_history.history_type_id = 27
AND ticket_state_type.name != "closed"
GROUP BY ticket.tn) grpmax
ON ticket.tn = grpmax.tn
AND ticket.change_time = grpmax.max_change
LEFT JOIN ticket_state
ON ( ticket_state.id = ticket_history.state_id )
LEFT JOIN ticket_state_type
ON ( ticket_state_type.id = ticket_state.type_id )
WHERE ticket_history.history_type_id = 27
AND ticket_state_type.name != "closed"
GROUP BY ticket.tn
OTRS 6.0.x (private/testing/public) on Linux with MySQL database.
Please edit your signature to include your OTRS version, Operating System, and database type.
Click Subscribe Topic below to get notifications. Consider amending your topic title to include [SOLVED] if it is so.
Need help? Before you ask
Please edit your signature to include your OTRS version, Operating System, and database type.
Click Subscribe Topic below to get notifications. Consider amending your topic title to include [SOLVED] if it is so.
Need help? Before you ask
-
- Znuny expert
- Posts: 171
- Joined: 24 Apr 2013, 12:29
- Znuny Version: OTRS3.3
- Real Name: kalyanachakravarthy M P
- Company: TATA Technologies
- Location: Pune,India
- Contact:
Re: open ticket report monthly
crythias wrote:Code: Select all
SELECT ticket.tn, ticket.id, ticket_history.state_id, ticket_state.name, ticket.change_time AS change_time FROM ticket_history LEFT JOIN ticket ON ( ticket.id = ticket_history.ticket_id ) INNER JOIN (SELECT ticket.tn, Max(ticket.change_time) AS max_change FROM ticket_history LEFT JOIN ticket ON ticket.id = ticket_history.ticket_id LEFT JOIN ticket_state ON ( ticket_state.id = ticket_history.state_id ) LEFT JOIN ticket_state_type ON ( ticket_state_type.id = ticket_state.type_id ) WHERE ticket_history.history_type_id = 27 AND ticket_state_type.name != "closed" GROUP BY ticket.tn) grpmax ON ticket.tn = grpmax.tn AND ticket.change_time = grpmax.max_change LEFT JOIN ticket_state ON ( ticket_state.id = ticket_history.state_id ) LEFT JOIN ticket_state_type ON ( ticket_state_type.id = ticket_state.type_id ) WHERE ticket_history.history_type_id = 27 AND ticket_state_type.name != "closed" GROUP BY ticket.tn
crythias thank you very much your help above query some error i got
Error Code: 1054. Unknown column 'ticket_state_type.name' in 'on clause'
i am working this query i will update soon
-
- Moderator
- Posts: 10169
- Joined: 04 May 2010, 18:38
- Znuny Version: 5.0.x
- Location: SouthWest Florida, USA
- Contact:
Re: open ticket report monthly
unable to replicate. copy/paste in phpmyadmin yielded no errors.
OTRS 6.0.x (private/testing/public) on Linux with MySQL database.
Please edit your signature to include your OTRS version, Operating System, and database type.
Click Subscribe Topic below to get notifications. Consider amending your topic title to include [SOLVED] if it is so.
Need help? Before you ask
Please edit your signature to include your OTRS version, Operating System, and database type.
Click Subscribe Topic below to get notifications. Consider amending your topic title to include [SOLVED] if it is so.
Need help? Before you ask
-
- Znuny expert
- Posts: 171
- Joined: 24 Apr 2013, 12:29
- Znuny Version: OTRS3.3
- Real Name: kalyanachakravarthy M P
- Company: TATA Technologies
- Location: Pune,India
- Contact:
Re: open ticket report monthly
Hicrythias wrote:unable to replicate. copy/paste in phpmyadmin yielded no errors.
final i got result the row associated with the max date
Code: Select all
SELECT ticket.tn,
ticket.id,
ticket_history.state_id,
ticket_state.name,
ticket_history.change_time AS change_time
FROM ticket_history
LEFT JOIN ticket
ON ( ticket.id = ticket_history.ticket_id )
INNER JOIN (SELECT ticket.tn,
Max(ticket_history.change_time) AS max_change
FROM ticket_history
LEFT JOIN ticket
ON ticket.id = ticket_history.ticket_id
-- AND ticket_state_type.name != "closed"
WHERE ticket_history.history_type_id = 27
or ticket_history.history_type_id =1
GROUP BY ticket.tn) grpmax
ON ticket.tn = grpmax.tn
AND ticket_history.change_time = grpmax.max_change
LEFT JOIN ticket_state
ON ( ticket_state.id = ticket_history.state_id )
LEFT JOIN ticket_state_type
ON ( ticket_state_type.id = ticket_state.type_id )
WHERE ticket_history.history_type_id = 27
or ticket_history.history_type_id = 1
-- AND ticket_state_type.name != "closed"
GROUP BY ticket.id
now i am trying avoid closed ticket ..........
-
- Moderator
- Posts: 10169
- Joined: 04 May 2010, 18:38
- Znuny Version: 5.0.x
- Location: SouthWest Florida, USA
- Contact:
Re: open ticket report monthly
avoid it in the inner join.
OTRS 6.0.x (private/testing/public) on Linux with MySQL database.
Please edit your signature to include your OTRS version, Operating System, and database type.
Click Subscribe Topic below to get notifications. Consider amending your topic title to include [SOLVED] if it is so.
Need help? Before you ask
Please edit your signature to include your OTRS version, Operating System, and database type.
Click Subscribe Topic below to get notifications. Consider amending your topic title to include [SOLVED] if it is so.
Need help? Before you ask
-
- Znuny expert
- Posts: 171
- Joined: 24 Apr 2013, 12:29
- Znuny Version: OTRS3.3
- Real Name: kalyanachakravarthy M P
- Company: TATA Technologies
- Location: Pune,India
- Contact:
Re: open ticket report monthly
finalcrythias wrote:avoid it in the inner join.
Open Ticket :
Code: Select all
SELECT ticket.tn,
ticket.id,
ticket_history.state_id,
ticket_state.name,
ticket_history.change_time AS change_time
FROM ticket_history
LEFT JOIN ticket
ON ( ticket.id = ticket_history.ticket_id )
inner JOIN (SELECT ticket.tn,
Max(ticket_history.change_time) AS max_change
FROM ticket_history
LEFT JOIN ticket
ON ticket.id = ticket_history.ticket_id
LEFT JOIN ticket_state
ON ( ticket_state.id = ticket_history.state_id )
LEFT JOIN ticket_state_type
ON ( ticket_state_type.id = ticket_state.type_id )
WHERE ( ticket_history.history_type_id = 27
or ticket_history.history_type_id =1)
AND ticket_state_id != "closed"
GROUP BY ticket.tn) grpmax
ON ticket.tn = grpmax.tn
AND ticket_history.change_time = grpmax.max_change
LEFT JOIN ticket_state
ON ( ticket_state.id = ticket_history.state_id )
LEFT JOIN ticket_state_type
ON ( ticket_state_type.id = ticket_state.type_id )
WHERE (ticket_history.history_type_id = 27
or ticket_history.history_type_id = 1)
AND ticket_state_type.name != "closed"
GROUP BY ticket.id
Code: Select all
SELECT * FROM ticket where ticket_state_id = 2
Code: Select all
SELECT ticket.tn, ticket.id,ticket_history.state_id,ticket_state.name, max(ticket_history.change_time) AS change_time
FROM ticket_history
LEFT JOIN ticket ON (ticket.id = ticket_history.ticket_id)
LEFT JOIN ticket_state ON (ticket_state.id = ticket_history.state_id)
LEFT JOIN ticket_state_type ON (ticket_state_type.id = ticket_state.type_id)
WHERE ticket_history.history_type_id = 27 AND
ticket_state_type.name ="closed"
GROUP BY ticket.id;
Code: Select all
SELECT * FROM ticket
LEFT JOIN ticket_state
ON ( ticket_state.id = ticket.ticket_state_id )
where ticket_state.name = 'new'