Problem
You need a report that shows the worked time for each ticket, and you want to know how this information can be retrieved from the database.
Solution
Manual Work Time Entry (Reportable)
Worked time can be captured by enabling Input worktime for the relevant Ticket Types:
-
Go to Configuration > Ticket Content
-
Enable Input worktime for the desired Ticket Types
-
Helpdesk agents can then enter the time worked when adding a note by clicking the hammer icon
The entered work time is stored in the database table:
-
Table:
htblnotes -
Field:
timeworked
Because work time is stored per note, reports must use a SUM aggregation on htblnotes.timeworked to calculate the total worked time per ticket.
For an overview of Lansweeper database tables, refer to the Database Dictionary:
https://community.lansweeper.com/t5/reporting/access-lansweeper-database-documentation/ta-p/64481
Time Worked Based on Ticket States (Not Reportable)
The Time worked on (States) value shown on tickets is calculated dynamically based on how long a ticket remains in states configured as “work states.”
-
This value is not stored in the database
-
There is no database field available for reporting on this calculation
-
While theoretically possible to recreate this logic in a custom report, it would require a highly advanced report and is not supported by Lansweeper Technical Support
Summary
-
Use
htblnotes.timeworked(summed per ticket) for reporting worked time entered by agents -
Dynamically calculated state-based work time cannot be used in reports
