cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
mrrrl
Engaged Sweeper
Were is the agent worked hours stored? Looking to report on closed tickets the hours the agent has submitted.

"Time worked on (notes): 3 hours 30 minutes"

Thanks
1 ACCEPTED SOLUTION
Nick_VDB
Champion Sweeper III
The worked time is stored in the 'htblnotes' table in the timeworked field.

The report below will give back the worked hours of the agent for public posts when the ticket is closed. Instructions for adding the report below to your Lansweeper installation can be found here.
Select Top 1000000 htblticket.ticketid As ID,
htblticket.subject As Subject,
htblusers.name As Agent,
Sum(htblnotes.timeworked) As [Time Worked (Mins)],
htblticketstates.statename
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
Where htblticketstates.statename = 'closed' And htblnotes.notetype = 1
Group By htblticket.ticketid,
htblticket.subject,
htblusers.name,
htblticketstates.statename

View solution in original post

1 REPLY 1
Nick_VDB
Champion Sweeper III
The worked time is stored in the 'htblnotes' table in the timeworked field.

The report below will give back the worked hours of the agent for public posts when the ticket is closed. Instructions for adding the report below to your Lansweeper installation can be found here.
Select Top 1000000 htblticket.ticketid As ID,
htblticket.subject As Subject,
htblusers.name As Agent,
Sum(htblnotes.timeworked) As [Time Worked (Mins)],
htblticketstates.statename
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
Where htblticketstates.statename = 'closed' And htblnotes.notetype = 1
Group By htblticket.ticketid,
htblticket.subject,
htblusers.name,
htblticketstates.statename