logo

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.

Notification

Icon
Error

Options
Go to last post Go to first unread
Konceptech.net  
#1 Posted : Thursday, August 28, 2014 5:21:22 PM(UTC)
Konceptech.net


Rank: Advanced Member

Medals: Level 1: Random Act of Kindness! Received One Thanks!

Joined: 7/26/2013(UTC)
Posts: 69
Canada
Location: Quebec

Thanks: 10 times
Was thanked: 3 time(s) in 2 post(s)
Hi,

I saw on the latest blog's post a query to obtain duration for all support sesions but it's just for the Support session.

http://blogs.screenconnect.com/post/2014/08/SQL-Queries-Support-Session-Duration.aspx

I will have exactly the same query but including the unattended sessions.

Does anybody know how to do that? I guess i have to modify the AND s.SessionType but i don't know what value to give.

Here is the query:

Quote:
SELECT s.name, sc.ParticipantName, s.GuestLoggedOnUsername,

s.GuestMachineName, min(sce.time) as Start,

((julianday(max(sce.time)) - julianday(min(sce.time))) * 24 * 60)

as 'Duration (min)'

FROM SessionConnection sc

INNER JOIN SESSION s ON s.sessionid = sc.sessionid

INNER JOIN sessionconnectionevent sce ON sce.sessionid = s.sessionid

AND sc.connectionid = sce.connectionid

WHERE sc.ProcessType = 1 AND (sce.EventType = 10 OR sce.EventType = 11)

AND s.SessionType = 0

GROUP BY s.name, sc.ParticipantName, s.GuestLoggedOnUsername,

s.GuestMachineName;


Thanks!

Konceptech.net  
#2 Posted : Thursday, August 28, 2014 9:17:56 PM(UTC)
Konceptech.net


Rank: Advanced Member

Medals: Level 1: Random Act of Kindness! Received One Thanks!

Joined: 7/26/2013(UTC)
Posts: 69
Canada
Location: Quebec

Thanks: 10 times
Was thanked: 3 time(s) in 2 post(s)
Ok i just found! FYI

SELECT s.name, sc.ParticipantName, s.GuestLoggedOnUsername,

s.GuestMachineName, min(sce.time) as Start,

((julianday(max(sce.time)) - julianday(min(sce.time))) * 24 * 60)

as 'Duration (min)'

FROM SessionConnection sc

INNER JOIN SESSION s ON s.sessionid = sc.sessionid

INNER JOIN sessionconnectionevent sce ON sce.sessionid = s.sessionid

AND sc.connectionid = sce.connectionid

WHERE sc.ProcessType = 1 AND (sce.EventType = 10 OR sce.EventType = 11)

AND s.SessionType = 0

OR s.SessionType = 2

GROUP BY s.name, sc.ParticipantName, s.GuestLoggedOnUsername,

s.GuestMachineName;
Jacob  
#3 Posted : Friday, August 29, 2014 8:35:14 PM(UTC)
Jacob


Rank: Administration

Joined: 7/28/2014(UTC)
Posts: 19
Man
United States
Location: Raleigh, NC

Thanks: 1 times
Exactly. I probably need to add a specific document in the blog that specifies what event EventType number or SessionType number corresponds to. In this case:

SessionType = 0 for Support
= 1 for Meeting
= 2 for Access
ScreenConnect Team
Konceptech.net  
#4 Posted : Saturday, August 30, 2014 9:05:51 PM(UTC)
Konceptech.net


Rank: Advanced Member

Medals: Level 1: Random Act of Kindness! Received One Thanks!

Joined: 7/26/2013(UTC)
Posts: 69
Canada
Location: Quebec

Thanks: 10 times
Was thanked: 3 time(s) in 2 post(s)
Nice!

Another ask for you, how do i modify the query to obtain exploitable values in excel.

I mean, now with a csv i can parse the result in different columns.

But the format of the date and of the duration is still not exploitable ...

I cant find a way to tune the query ...


Thanks!

Edited by user Saturday, August 30, 2014 9:06:31 PM(UTC)  | Reason: Not specified

Jacob  
#5 Posted : Tuesday, September 2, 2014 1:13:20 PM(UTC)
Jacob


Rank: Administration

Joined: 7/28/2014(UTC)
Posts: 19
Man
United States
Location: Raleigh, NC

Thanks: 1 times
Can you clarify on what you're wanting to do with the data? Today, I posted the final blog post in the SQL queries series. I use JDBC to run a SQL query inside of Java, and I place the values of the query into an ArrayList. It's not the most elegant of code, but it gets everything into what I find to be a better environment for complex data manipulation. Perhaps, that would work better for you as well.

http://blogs.screenconne...utomatic-Query-Tool.aspx

Edited by user Tuesday, September 2, 2014 3:44:30 PM(UTC)  | Reason: Not specified

ScreenConnect Team
Konceptech.net  
#6 Posted : Tuesday, September 2, 2014 3:50:53 PM(UTC)
Konceptech.net


Rank: Advanced Member

Medals: Level 1: Random Act of Kindness! Received One Thanks!

Joined: 7/26/2013(UTC)
Posts: 69
Canada
Location: Quebec

Thanks: 10 times
Was thanked: 3 time(s) in 2 post(s)
Originally Posted by: Jacob Go to Quoted Post
Can you clarify on what you're wanting to do with the data? Today, I posted the final blog post in the SQL queries series. I use JDBC to run a sequel query inside of Java, and I place the values of the query into an ArrayList. It's not the most elegant of code, but it gets everything into what I find to be a better environment for complex data manipulation. Perhaps, that would work better for you as well.

http://blogs.screenconne...utomatic-Query-Tool.aspx



Hi!

I just want to perform a full audit of the support/access session, for billing purposes.

To track the time each tech passed on each machine.

So With the csv i can do a sort of, but it is not possible for the moment because of the format of the date and the time passed.

Edited by user Tuesday, September 2, 2014 3:51:31 PM(UTC)  | Reason: Not specified

Jacob  
#7 Posted : Monday, September 8, 2014 3:25:04 PM(UTC)
Jacob


Rank: Administration

Joined: 7/28/2014(UTC)
Posts: 19
Man
United States
Location: Raleigh, NC

Thanks: 1 times
I'm sure this is possible using just SQL to query the database, but it's probably not the most efficient way to achieve this. I'll look into seeing if this can be achieve with Extensions. If not, I'll see what I can do about using some standalone code (possibly an executable to put on the desktop of the server).

The whole process might take a while, but I'll be sure to update when I've got something.
ScreenConnect Team
Konceptech.net  
#8 Posted : Wednesday, September 10, 2014 1:36:18 PM(UTC)
Konceptech.net


Rank: Advanced Member

Medals: Level 1: Random Act of Kindness! Received One Thanks!

Joined: 7/26/2013(UTC)
Posts: 69
Canada
Location: Quebec

Thanks: 10 times
Was thanked: 3 time(s) in 2 post(s)
Would be nice, sounds great to have that with an extension!

Thanks!
rvdv  
#9 Posted : Thursday, February 12, 2015 10:19:09 AM(UTC)
rvdv


Rank: Newbie

Joined: 11/12/2013(UTC)
Posts: 8
Location: NL

I just used the query tool and it works fine.
However, the EXE mentioned on that page, I cannot get it to run. It spawns a process and then terminates.
Manually with the SQL Browser works fine, of course.

Still, I can't wait for this function to be implemented in ScreenConnect itself.
This is really useful for our billing purposes.
scremote  
#10 Posted : Friday, July 17, 2015 9:51:04 PM(UTC)
scremote


Rank: Member

Joined: 7/17/2015(UTC)
Posts: 12
Canada

Thanks: 1 times
I saw post info somewhere else (on SC site, I believe) about using "DB Browser for SQLite" program(sqlitebrowser.org) for connecting to the database - but this is only available for Windows and MAC. Are any of you guys aware of an equivalent for Linux?
Paul Moore  
#11 Posted : Monday, July 20, 2015 8:41:34 PM(UTC)
Paul Moore


Rank: Advanced Member

Medals: Level 3: Shirt off your back! Received 25 Thanks!

Joined: 9/16/2011(UTC)
Posts: 334

Thanks: 5 times
Was thanked: 70 time(s) in 44 post(s)
ScreenConnect Reporting - Collects live & historical information including session times.
http://goo.gl/nrF3e9
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.