Ticket Report with Total Time Worked per Ticket

[Misha_Yang]

Hello!

I have been trying to create two reports myself but my skills did not quite reach the mark 😞

"Tickets closed this month" report
I need to make a report that brings up all the closed tickets during a specified month.

It contains:

Ticket number
Subject
The date when the Ticket was last set to status closed
The Total Time Worked per ticket
Agents name
Tickets Type (category)

---

And also we need a report for "New tickets created this month"

Ticket number
Created by "email adress"
Subject
Agent name
Tickets Type (category)


Thank you!
//Misha

[SpangA]

Hello,
for the moment many thanks for the answer.
Nevertheless, I believe this my question was misunderstood.
I would need a query not after ticket No. and whole time goes, but Where every single agent is performed to see who has worked how long on which Project. In the report "Totally time Worked by ticket" times are questioned whole.
Is this also possible??

[brigmill]

"Brigmill,

Are you using the hammer icon to set the time that has been worked on the ticket after posting a ticket update?"

No, we weren't. We couldn't figure out how the time is put in. Now we know. Thanks.

[SpangA]

Really cool, functions.
If I see this right, now what will be raised listed all together in times.
Now the next question, sometimes works on a Project several agents. If it is possible now to illustrate in a report Who has worked, on which ticket and how long every single agent has worked on it.
greetings
Andreas

[SpangA]

Is there the possibility the used time where to put down and then to call away about this query??

[SpangA]

if the solution can explain to me please once again, the link is dead
"Instructions for adding the report below to your Lansweeper installation can be found here."

[brigmill]

Thank you for this query, it is great. One thing I do have a question about, the row for the time the ticket was worked shows up, but it's blank for all 175 closed tickets for the month. Does this mean we aren't tracking time correctly within our tickets?

[Leslie_Strooban]

Brigmill,

Are you using the hammer icon to set the time that has been worked on the ticket after posting a ticket update?

SpangA,

The link works for me, but here's the explanation behind the links:

Suzan.A wrote:
To run any of the reports found in the report center, do the following:
Open the report builder in the Lansweeper web console under Reports/Create New Report.
Paste the SQL query (report) found in the report center at the bottom of the page, replacing the default SQL query.
Left-click somewhere in the upper section of the page so the query applies.
Give the report a title.
Hit the Save & Run button to save the report. Export options are listed on the left.

Nick VDB,

Thanks for posting this report!
I've added another ticket in my installation to get an overview of the entire year by leaving out "DatePart(mm, htblticket.updated) = 8 And " on line 17 of the first script.


Edit:
How would one go about adding the "user concerning" to this report?

[Nick_VDB · Accepted solution]

This post has been answered by mail.

Instructions for adding the report below to your Lansweeper installation can be found here.

Tickets closed this month. Change the highligted numbers to set the search month and year.

Select Top 1000000 htblticket.ticketid As ID,
  htblticket.subject As Subject,
  htblticket.updated As [Closed Date],
  Sum(htblnotes.timeworked) As [Time Worked (Minutes)],
  htblusers.name As Agent,
  htbltickettypes.typename As [Ticket Type],
  DatePart(mm, htblticket.updated) As Month,
  DatePart(yy, htblticket.updated) As Year
From htblticket
  Inner Join htblnotes On htblticket.ticketid = htblnotes.ticketid
  Inner Join htblagents On htblagents.agentid = htblticket.agentid
  Inner Join htblusers On htblusers.userid = htblagents.userid
  Inner Join htblticketstates On htblticketstates.ticketstateid =
    htblticket.ticketstateid
  Inner Join htbltickettypes On htblticket.tickettypeid =
    htbltickettypes.tickettypeid
Where DatePart(mm, htblticket.updated) = 8 And DatePart(yy, htblticket.updated)
  = 2016 And htblticketstates.statename = 'Closed'
Group By htblticket.ticketid,
  htblticket.subject,
  htblticket.updated,
  htblusers.name,
  htbltickettypes.typename

New tickets created this month. Change the highligted numbers to set the search month and year.

Select Top 1000000 htblticket.ticketid,
  htblusers.name As [User],
  htblticket.subject,
  htblusers1.name As Agent,
  htbltickettypes.typename,
  htblticket.date As [Creation Date],
  DatePart(mm, htblticket.date) As [Search Month],
  DatePart(yy, htblticket.date) As [Search Year]
From htblticket
  Inner Join htblusers On htblusers.userid = htblticket.fromuserid
  Inner Join htblagents On htblagents.agentid = htblticket.agentid
  Inner Join htblusers htblusers1 On htblusers1.userid = htblagents.userid
  Inner Join htbltickettypes On htblticket.tickettypeid =
    htbltickettypes.tickettypeid
Where DatePart(mm, htblticket.date) = 8 And DatePart(yy, htblticket.date) = 2016

[hannu_swanepoel]

Anyone here a SQL expert, "which I am not" and need assistance please, want the following script to be changed to Closed please:

WITH MyTable AS (
SELECT htblusers.name AS AssignedAgent
,(SELECT Count(htblticket.ticketid) FROM htblticket WHERE htblagents.agentid=htblticket.agentid and (htblticket.date > GetDate() - 1)) AS "Past 24 hours"
,(SELECT Count(htblticket.ticketid) FROM htblticket WHERE htblagents.agentid=htblticket.agentid and (htblticket.date > GetDate() - 7)) AS "Past 7 Days"
,(SELECT Count(htblticket.ticketid) FROM htblticket WHERE htblagents.agentid=htblticket.agentid and (htblticket.date > GetDate() - 30)) AS "Past 30 Days"
,(SELECT Count(htblticket.ticketid) FROM htblticket WHERE htblagents.agentid=htblticket.agentid and (htblticket.date > GetDate() - 60)) AS "Past 60 Days"
,(SELECT Count(htblticket.ticketid) FROM htblticket WHERE htblagents.agentid=htblticket.agentid and (htblticket.date > GetDate() - 90)) AS "Past 90 Days"
,(SELECT Count(htblticket.ticketid) FROM htblticket WHERE htblagents.agentid=htblticket.agentid and (htblticket.date > GetDate() - 365.2425)) AS "Opened this year"
,(SELECT Count(htblticket.ticketid) FROM htblticket WHERE htblagents.agentid=htblticket.agentid and (htblticket.date > GetDate() - 36500)) AS "Total Opened Ever"
From htblticket
Inner Join htblagents On htblagents.agentid = htblticket.agentid
Inner Join htblusers On htblusers.userid = htblagents.userid
Inner Join htblticketstates On htblticketstates.ticketstateid = htblticket.ticketstateid
Where -- (htblticketstates.statename = 'Open' Or htblticketstates.statename =
-- 'In Progress' Or htblticketstates.statename = 'Awaiting Reply' Or htblticketstates.statename =
-- 'Pending 3rd Party' Or htblticketstates.statename =
-- 'Waiting for approval' Or htblticketstates.statename =
-- 'Confirm Resolution')
--htblticketstates.statename <> 'Closed'
htblticket.spam <> 'True'
Group By htblusers.name,
htblusers.userid,
htblagents.agentid
)

SELECT
[AssignedAgent]
,[Past 24 hours]
,[Past 7 days]
,[Past 30 days]
,[Past 60 days]
,[Past 90 days]
,[Opened this year]
,[Total Opened Ever]

FROM MyTable

UNION ALL

SELECT '' as [AssignedAgent]
,SUM([Past 24 hours]) as [Past 24 hours]
,SUM([Past 7 days]) as [Past 7 days]
,SUM([Past 30 days]) as [Past 30 days]
,SUM([Past 60 days]) as [Past 60 days]
,SUM([Past 90 days]) as [Past 90 days]
,SUM([Opened this year]) as [Opened this year]
,SUM([Total Opened Ever]) as [Total Opened Ever]
FROM MyTable
ORDER By AssignedAgent Asc