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

Notification

Icon
Error

Options
Go to last post Go to first unread
jeromy  
#1 Posted : Thursday, October 12, 2017 1:36:44 PM(UTC)
jeromy


Rank: Guest

Joined: 8/30/2017(UTC)
Posts: 3
United States
Location: Lubbock

While working on a project to gather information, I started digging around and came across this query:

Quote:
SELECT Name, GuestLoggedOnUsername, GuestMachineName, GuestLastActivityTime ,GuestOperatingSystemName, GuestOperatingSystemVersion, SessionType FROM Session
WHERE
NOT EXISTS (SELECT * FROM SessionEvent WHERE SessionID = Session.SessionID AND EventType = 21)
AND
NOT EXISTS (SELECT * FROM SessionConnectionEvent WHERE SessionID = Session.SessionID AND EventType = 21)
AND
EXISTS (SELECT * FROM SessionConnectionEvent WHERE SessionID = Session.SessionID AND (EventType = 10 OR EventType = 11))
AND
SessionType = 0
AND GuestInfoUpdateTime != '0001-01-01 00:00:00Z'

GROUP BY Name;



This does almost exactly what I am looking for, but I had some questions.

1: This query is EXTREMELY slow ( ~4 seconds), and I know it's most likely due to all the subqueries, so I can address that later, but if anyone knows of another reason it is so slow, that would be helpful.

2: Is there a way to filter out all of the sessions that have no current guest connected, and currently have hosts connected.

Thank you in advance
Scott  
#2 Posted : Thursday, October 12, 2017 1:56:11 PM(UTC)
Scott


Rank: Administration

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

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

Thanks: 3 times
Was thanked: 305 time(s) in 262 post(s)
So the SessionManager computes an ActiveConnection object for each session although it does not write that information to the database.

It seems like it might be easier to make a webrequest against an Extension-defined method similar to:

Code:

	public Guid[] FindSessionsWithActiveConnection()
	{
		return SessionManagerPool.Demux.GetSessions()
			.Where(_ => _.SessionType == SessionType.Access)
			.Where(_ => _.ActiveConnections.Count() > 0)
			.Select(_ => _.SessionID).ToArray();
	}


You would want to modify it to fit your exact information, but it likely would be much faster and easier to maintain in the future.
ScreenConnect Team
jeromy  
#3 Posted : Thursday, October 12, 2017 2:29:46 PM(UTC)
jeromy


Rank: Guest

Joined: 8/30/2017(UTC)
Posts: 3
United States
Location: Lubbock

How would I query that from a script? I would have to have access to some sort of API or authenticated web request. I was under the impression there was no such API for me to run queries against.

edit:

Let me be more specific: I can do everything on the scripting side, I just need you to point me to the documentation or example page that shows me how I can run a "webrequest against an Extension-defined method".

Edited by user Thursday, October 12, 2017 2:48:18 PM(UTC)  | Reason: Not specified

Scott  
#4 Posted : Friday, October 13, 2017 11:41:03 AM(UTC)
Scott


Rank: Administration

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

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

Thanks: 3 times
Was thanked: 305 time(s) in 262 post(s)
Depending upon how you're writing the script is what decides how you would invoke it. Powershell can just use a POST webrequest with content type application/json.

To respond directly to your statement about no API, you're kind of correct. ScreenConnect does not come with an out of the box API but instead we have implemented the Extension Framework. This Framework allows you to implement your own API to retrieve just about any information you would. Within the context of this scenario, you would create an Extension that contains a Service class which extends the WebServiceBase, similar to:

Code:

<%@ WebHandler Language="C#" Class="Service" %>

using System;
using System.Net;
using System.Configuration;
using System.Web;
using ScreenConnect;
using System.Threading;
using System.Linq;
using System.Collections.Generic;
using System.Collections;
using System.IO;

public class Service : WebServiceBase
{
	public object[] GetIdleSupportSessionsWithNoHost()
	{
		return SessionManagerPool.Demux.GetSessions()
			.Where(_ => _.SessionType == SessionType.Support)
			.SelectMany(_ => _.ActiveConnections)
			.Where(ac => ac.ProcessType == ProcessType.Guest)
			.Select(ac => ac.ParticipantName).ToArray();
	}
}


You don't need all of those imports, I just copied a sandbox extension class for this example.

Then, you would make the request by combining the filepath to the Service class and the web method, similar to:

Code:

http://my.screenconnectdomain.com:8040/App_Extensions/EXTENSION_ID/Service.ashx/GetIdleSupportSessionsWithNoHost


Just make sure you're POSTing and set the content type to application/json
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.