logo
Welcome Guest! To enable all features please Login or Register.

Notification

Icon
Error

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


Rank: Member

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

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

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.

Quote:
SELECT SessionID, SessionType, Name
FROM Session
WHERE (SessionID NOT IN
(SELECT SessionID
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:
Quote:
SELECT Session.SessionID, Session.SessionType, Session.Name, disconnectTbl.disconnectTime
FROM Session
INNER JOIN (
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
(SELECT SessionID
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

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


Rank: Administration

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

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

Thanks: 3 times
Was thanked: 297 time(s) in 255 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.