logo

The ConnectWise Control forum has moved to ConnectWise University! This forum has been locked and is in read-only mode. Click here for instructions on how to access the new forum.

Welcome Guest! You can not login or register.

Notification

Icon
Error

Options
Go to last post Go to first unread
itjedi42  
#1 Posted : Tuesday, December 1, 2015 7:03:02 PM(UTC)
itjedi42


Rank: Newbie

Medals: Level 1: Random Act of Kindness! Received One Thanks!

Joined: 12/1/2015(UTC)
Posts: 3
United States
Location: Nashville

Was thanked: 2 time(s) in 2 post(s)
EDIT:

We were able to successfully do this, see second post for instructions and code


Original Post:

Ok, so we are looking for a way (SQL query is fine) to be able to pull the duration of a tech's connection to a system connected via access session for all of our techs/sessions on either a daily, weekly, or monthly schedule (weekly would probably be best).

Thanks in advance!

Edited by user Thursday, December 3, 2015 7:47:41 PM(UTC)  | Reason: Not specified

thanks 1 user thanked itjedi42 for this useful post.
LLCoolWas on 12/21/2015(UTC)

itjedi42  
#2 Posted : Thursday, December 3, 2015 7:45:27 PM(UTC)
itjedi42


Rank: Newbie

Medals: Level 1: Random Act of Kindness! Received One Thanks!

Joined: 12/1/2015(UTC)
Posts: 3
United States
Location: Nashville

Was thanked: 2 time(s) in 2 post(s)
Well,

successfully figured out a way to make it work. For anyone else looking here is the code, requires that you have migrated your screenconnect instance to an SQL backend instead of using the SQLite database.

When it finishes it destroys the SessionConnectionEvents for eventtype 10 and 11 to allow for clean metrics on the next run. Report can be accessed by using

Quote:

Select * from ReportData


Data is displayed as:

System (name of the session), Engineer (tech who connected), HoursConnected (time in hours the tech was connected)


Code to build the report


Quote:

Drop View UsedSessions
Go
Create View UsedSessions as
Select distinct sc.ConnectionID, sc.ParticipantName, sc.SessionID from SessionConnection sc where sc.ParticipantName != ''
Go
Drop View SessionConnectionTimes
Go
CREATE VIEW SessionConnectionTimes AS
With Connections as
(
Select ConnectionID from UsedSessions
)
SELECT a.*, b.Time AS ConnectTime, c.Time AS DisconnectTime
FROM SessionConnection a
INNER JOIN SessionConnectionEvent b ON a.SessionID = b.SessionID AND a.ConnectionID = b.ConnectionID AND b.EventType = 10
INNER JOIN SessionConnectionEvent c ON a.SessionID = c.SessionID AND a.ConnectionID = c.ConnectionID AND c.EventType = 11
Where a.ConnectionID in (Select ConnectionID from Connections) AND a.ParticipantName in (Select ParticipantName from Connections)
Go
DROP VIEW SessionTimers;
Go
Create View SessionTimers AS
Select
sct.SessionID, sct.ParticipantName, s.GuestMachineName,
DATEDIFF(second ,{d '1970-01-01'}, sct.ConnectTime) as StartSeconds,
DATEDIFF(second ,{d '1970-01-01'}, sct.DisconnectTime) as EndSeconds
FROM
SessionConnectionTimes sct
Inner join Session s on s.SessionID = sct.SessionID
Go
drop view ReportData
Go
Create View ReportData as
Select
st.GuestMachineName as System,
st.ParticipantName as Engineer,
Round(((cast(st.EndSeconds as float) - cast(st.StartSeconds as float))/60/60),2) as HoursConnected
From SessionTimers st
Go
Delete from SessionConnectionEvent where EventType = 10 or EventType = 11
Go
Select * from ReportData

thanks 1 user thanked itjedi42 for this useful post.
LLCoolWas on 12/21/2015(UTC)
Users browsing this topic
Forum Jump  
You cannot post new topics in this forum.
You cannot reply to topics in this forum.
You cannot delete your posts in this forum.
You cannot edit your posts in this forum.
You cannot create polls in this forum.
You cannot vote in polls in this forum.