At this point, we've pretty well covered creating machine lists. Powered with your SQL tools and some basic queries, you can create a list of all machines and include some basic information about them. But, we want to take our utilization of the database one step further. We want something that can be used on a yearly, monthly, or perhaps even weekly basis to streamline our workflow and save time. I'm, of course, talking about determining session duration.
A valuable group of our users provide ad-hoc support to customer machines external to their organization. The vast majority of these interactions occur via Support sessions in ScreenConnect. And, these sessions typical occur with some sort of hourly billing rate. Yet, there isn't an easy way to generate usage reports (something we recognize and are looking to bring official support to in the future). Luckily, we can use our SQL tools and a well-crafted query to generate a quick and easy report in Excel. Let's check out the code below:
SELECT s.name, sc.ParticipantName, s.GuestLoggedOnUsername,
s.GuestMachineName, min(sce.time) as Start,
((julianday(max(sce.time)) - julianday(min(sce.time))) * 24 * 60)
as 'Duration (min)'
FROM SessionConnection sc
INNER JOIN SESSION s ON s.sessionid = sc.sessionid
INNER JOIN sessionconnectionevent sce ON sce.sessionid = s.sessionid
AND sc.connectionid = sce.connectionid
WHERE sc.ProcessType = 1 AND (sce.EventType = 10 OR sce.EventType = 11)
AND s.SessionType = 0
GROUP BY s.name, sc.ParticipantName, s.GuestLoggedOnUsername,
The query above will return the duration for all support sesions that have occured since the database was last cleared. Note: the time return is the total amount of time from when the technician originally created the support session to when the final user exited that session. Make sure this fits in line with how your organization does billing if you intend on using this query. Also important to consider is that, in order to maximize effectiveness, I suggest using the SQLite Database Browser to export the results in CSV format to Excel. From there, it's easy to filter and order data by Start time and/or other data.
Hopefully, this can be implemented into your company's workflow, cut some time out of preparing your billing, and serve as a proper holdover until official Extended Reporting is offered in the ScreenConnect software.