 Rank: Member Medals:   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. 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
|
|
|
|
|
|
 Rank: Administration Medals:  Joined: 3/28/2014(UTC) Posts: 2,862  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 |
|
|
|
|
|
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.
