 Rank: Newbie Medals:  Joined: 12/1/2015(UTC) Posts: 3  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
|
 1 user thanked itjedi42 for this useful post.
|
|
|
 Rank: Newbie Medals:  Joined: 12/1/2015(UTC) Posts: 3  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 reportQuote: 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
|
 1 user thanked itjedi42 for this useful post.
|
|
|
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.