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.



Go to last post Go to first unread
#1 Posted : Monday, May 22, 2017 9:18:18 PM(UTC)

Rank: Member

Medals: ScreenConnect Advisor: Focus Group MemberLevel 1: Random Act of Kindness! Received One Thanks!

Joined: 8/7/2014(UTC)
Posts: 22

Was thanked: 3 time(s) in 3 post(s)
I am trying to write a SQL statement that will return a list of support sessions that haven't been ended. I thought this would be pretty simple, but not so far.

When i Look in the web app, I currently have 1196 sessions in "All Sessions" under support. I'd expect the same figure when I query the DB. Ideally, I'd only select sessions where a host is not currently connected.

When I run this, I get 8756 sessions. I was thinking this may be because of data purging, but some how the app knows not to include the session, so there needs to be somewhere else that either the status is stored or another event or flag that I should be looking at.

SELECT SessionID, SessionType, Name
FROM Session
FROM SessionEvent
WHERE (EventType = 21))) AND (SessionType = 0)

Once I get this figured out, I need to identify the last time that a host disconnected from the session. I'm thinking something like this:
SELECT Session.SessionID, Session.SessionType, Session.Name, disconnectTbl.disconnectTime
FROM Session
SELECT SessionConnectionEvent.SessionID, MAX(CONVERT(datetime, SWITCHOFFSET(CONVERT(datetimeoffset, Time), DATENAME(TzOffset, SYSDATETIMEOFFSET())))) as disconnectTime
FROM SessionConnectionEvent INNER JOIN
SessionConnection ON SessionConnectionEvent.SessionID = SessionConnection.SessionID AND SessionConnectionEvent.ConnectionID = SessionConnection.ConnectionID
WHERE (SessionConnectionEvent.EventType = '11') AND (SessionConnection.ProcessType = 1)
GROUP BY SessionConnectionEvent.SessionID
) as disconnectTbl
ON Session.SessionID=disconnectTbl.SessionID
WHERE (Session.SessionID NOT IN
FROM SessionEvent
WHERE (EventType = 21))) AND (SessionType = 0)

Any help or suggestions would be appreciated.

Edited by user Monday, May 22, 2017 9:26:31 PM(UTC)  | Reason: Not specified

#2 Posted : Wednesday, June 14, 2017 12:36:02 PM(UTC)

Rank: Administration

Medals: Level 4: Wise Old Owl! Received 100 Thanks!

Joined: 3/28/2014(UTC)
Posts: 2,862
United States

Thanks: 3 times
Was thanked: 351 time(s) in 303 post(s)
Just so I have a better understanding, what exactly are you trying to accomplish with this whole task?

Also, in your web.config, what value do you have for 'SupportSessionExpireSeconds' ?
ScreenConnect Team
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.