Automatic Query Tool

We've spent the past few weeks exploring the ability to obtain extra information from the ScreenConnect database. We obtained a database viewer, and we've run some interesting queries about access machines and support session durations. It's a bit of an understatement to say that we can find some pretty useful information with SQL quieries. But, there are limitations to our current workflow. What if you need a less technical employee to generate reports? Or, what if you have a query that needs to be run on a regular basis?

Forunately, we can take the process one step further with some programming aptitude. There are libraries readily available for handling SQL queries. In this case, I've used my most familiar language: Java. It's possible to easily open and query the database, manipulate the data, and save to a format that can be viewed with Excel. It can even be exported an as executable file.

Below, you'll find two files: the .exe file for duration and the zipped Eclipse project. The program is written specifically for Windows 64-bit machines, but can be easily changed for Unix-based operating systems. You can choose to use my exising .exe file to generate support duration reports (Warning: it requires Java 8), and you can modify the existing code to generate your own reports.

Note: The executable must be run on the server machine. A good strategy is to place the .exe on your server's desktop, install an access client on your server (if you haven't already), and remote to the server anytime you need a report.

Generate Report .exe (3.78 mb)

Eclipse Project .zip (3.72 mb)

Good luck with your customizations, and share any interesting modifications with the forum!

Posted by Jacob Turpin at 3:57 AM       0 Comments       Share
Categories: ScreenConnect

SQL Queries: Support Session Duration

At this point, we've pretty well covered creating machine lists. Powered with your SQL tools and some basic queries, you can create a list of all machines and include some basic information about them. But, we want to take our utilization of the database one step further. We want something that can be used on a yearly, monthly, or perhaps even weekly basis to streamline our workflow and save time. I'm, of course, talking about determining session duration.

A valuable group of our users provide ad-hoc support to customer machines external to their organization. The vast majority of these interactions occur via Support sessions in ScreenConnect. And, these sessions typical occur with some sort of hourly billing rate. Yet, there isn't an easy way to generate usage reports (something we recognize and are looking to bring official support to in the future). Luckily, we can use our SQL tools and a well-crafted query to generate a quick and easy report in Excel. Let's check out the code below:



	
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;


The query above will return the duration for all support sesions that have occured since the database was last cleared. Note: the time return is the total amount of time from when the technician originally created the support session to when the final user exited that session. Make sure this fits in line with how your organization does billing if you intend on using this query. Also important to consider is that, in order to maximize effectiveness, I suggest using the SQLite Database Browser to export the results in CSV format to Excel. From there, it's easy to filter and order data by Start time and/or other data.

Hopefully, this can be implemented into your company's workflow, cut some time out of preparing your billing, and serve as a proper holdover until official Extended Reporting is offered in the ScreenConnect software.

Posted by Jacob Turpin at 2:24 AM       0 Comments       Share
Categories: Development | feature | ScreenConnect

SQL Queries: Listing Your Access Machines

In my previous blog bost, I talked about using freely available SQL tools in order to extract some more detailed information from the ScreenConnect database. It contained some very useful instructions on how to go about the process for those unfamiliar with SQL, but the example query included didn't provide much value for the power user. In this post, I aim to expand upon our original query.

---

For many ScreenConnect customers, the ability to install an unlimited number of unattended access clients is a HUGE selling point. Some of our customers have installed upwards of 5,000 clients in order to fullfill their remote access needs. However, having a large number of machines can make tracking those machines difficult. Quickly generating a report of your "All Access" machines has considerable merit for anyone attempt to audit a large number of machines. Therefore, the query below is intended to return an overview of all machines with an installed Access clients that is still running.



SELECT Name, GuestLoggedOnUsername, GuestMachineName, 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 Like 2
AND GuestInfoUpdateTime != '0001-01-01 00:00:00Z'

GROUP BY Name;


Follow the same set of instructions from the previous blog post in order to generate a neat report in Excel that can be quickly filtered to view your desired machines. Note that the query above returns all extries where:

  1. No End Session event has occurred
  2. A Connect or Disconnect event has occured
  3. The session is registered as an Access session
The additional "WHERE" entry is optional parameter (and primarily meant for any development environments). Additionally, the query can be modified to return Support or Meeting sessions by changing the SessionType parameter to 0 or 1 respectively.

 

Posted by Jacob Turpin at 4:41 AM       0 Comments       Share
Categories: feature | ScreenConnect

Manually Querying the ScreenConnect Database

ScreenConnect offers the ability to audit session history with its basic and advanced auditing features. However, specific queries can often be useful for finding targeted information such as the number of concurrent sessions running at a given time, session durations, comprehensive lists of unattended machines, etc. Fortunately, the database file can be queried directly to receive specific information.

Steps:

Part 1 - Installation: 

The first step necessary in running your own queries with ease is to download a Database Browser tool. This allows you to quickly and easily view the data structure and data entries, while giving you the ability to run queries and view the results immediatley. One of the favorite tools among our team is the free SQLite Database Browser. It's simple to get up and running, but we've included the steps below. 

1. Visit the website for the SQLite Database Browser ( http://sqlitebrowser.org/ )

2. Download the installation file for your appropriate system

3. Follow the Setup Wizard to install the software

---

Part 2 - Executing a Query:

Now that you have the Database Browser software installed, we can begin executing queries. ScreenConnect uses SQLite to store all data to a database consisting of four tables. These tables include Session, Session Connection, and Session Event information. Using the available information at your disposal, a wide variety of information can be reported based on the instructions below.  

1. Open the SQLite Database Browser

2. Select Open Database from the top menu 

 

3. Navigate to the ScreenConnect directory and open the file “Session.db”

      Windows:            \Program Files(x86)\ScreenConnect\App_Data

      Mac:                      /opt/ScreenConnect/App_Data 

4. Select the “Execute SQL” tab in the SQLite Database Browser 

5. Enter your SQL query into the space provided

6. Select the “Run” button to execute the code

7. Click the “Save File” button and select “Export to CSV”

8. Leave setting as default and click the “OK” button

9. Save the file to your desire location

   

A report is now saved in a format that is easily accessed via Microsoft Excel. With the power of SQLite and the Database Browser, you can access a much more specific set of information from the ScreenConnect database. Below is an example query for accessing a list of all unattended clients existing since the last time the database was cleared. Give it a try, and see what else you can determine with your newfound ability.

 

--- 

 

Code: 

 

SELECT Name, GuestLoggedOnUserDomain, GuestLoggedOnUserName, GuestOperatingSystemName, GuestInfoUpdateTime

      FROM Session 

      WHERE SessionType LIKE 2

      ORDER BY GuestInfoUpdateTime DESC;

Posted by Jacob Turpin at 7:30 AM       0 Comments       Share
Categories: ScreenConnect

ScreenConnect 4.4 - Security Improvements and Triggers

Recently we released ScreenConnect 4.4. The new release introduces session event triggers. Event triggers are a way for Administrators to set automatic responses to specific events based upon pre-defined actions. An example would be a notification via email or HTTP sent to a technician alerting them of a chat message sent from an unattended workstation. Before event triggers, the technician would have no indication that a message was sent; now they are instantly notified so they can attend to the workstation.

Additionally, 4.4 packs in a slew of new security measures. Administrators can use a combination of parameters to work in conjunction with each other to secure their environment. In 4.4, such additions include a idle timeout feature so Administrators can configure a lockout to limit risk. New security enhancements include:

  • New per-webpage max idle timeout
  • Two-factor use in conjunction with Windows Authentication
  • Windows Authentication users can logout of ScreenConnect
  • New configurable password requirements
  • Set max invalid password attempts
  • New interface for restricting access via IP address
  • More information added to audit log
  • New tools and interface for setting up Windows auth and LDAP

Lastly, annotation tools were added to make sessions more fun and colorful! But in all seriousness, drawing tools can be very useful when presenting information and harking on points. The tools can be turned on/off and include pen, line, rectangle and ellipse options. During sessions with multiple participants, each user will automatically be assigned a different color.

We hope that our community is enjoying the new release!

Posted by Anna Morgan at 8:00 AM       0 Comments       Share
Categories: Remote Support | ScreenConnect

University Business opens voting for top IT software

ScreenConnect has been nominated for University Business’s top 100 products of 2014

Product nominations and votes are submitted by University Business readers and University IT professionals. If you work for a higher education organization, you can vote for ScreenConnect as your favorite remote support and access solution. The deadline for votes will be on Wednesday, Oct. 1, 2014. The winners will be published in University Business' January 2015 edition.

Some quick insight into the vote: Multiple team members from same university can vote individually. They would like for you to denote how you are using the software (support, access, part of an RMM, meetings, etc)-- they seem very interested in our storiy and  may ask permission to publish.

As always, we truely appreciate your continued support!

Posted by Anna Morgan at 9:44 AM       0 Comments       Share
Categories: Remote Support | ScreenConnect

Preview of ScreenConnect 5.0

We're adding function and form improvements to the upcoming ScreenConnect 5.0, including a more aesthetically pleasing interface for guests and some added functionality that's quickly guest-accessible. Review the video below for a visual preview.

On the surface, we updated the UI on the guest page to give more separation to each joining method. In 4.x, if you had sessions created that used multiple joining methods (public sessions plus named sessions that required an input box, etc.) these sections would be simply stacked on top of each other on the guest page. In 5.0 we've worked to clearly define these parts and give them a bit more visual appeal. You'll also notice background images -- these are fully customizable, of course.

Functionality was added as well. We've added a configurable option to the guest page that allows guests to start a session themselves. The ability for a guest to start a session from the guest page has been a recurring request from our users. In fact, in previous versions, some of our users have been doing this ad-hoc by creating several public sessions and letting their clients join a session on their own time. Now you may accomplish this using the built-in module.

We've also reinserted the footer and placed a link in it to allow quick download of your unattended access client installer.  This can be turned on or off and is placed to be accessible but not prominent on the guest page. 

Posted by John Carey at 5:07 AM       0 Comments       Share
Categories: Development | Remote Support | ScreenConnect

Expanding ScreenConnect with Extensions

While ScreenConnect is fairly extensible, and many of our users modify the app to accomplish their particular goals, we've yet to produce an environment for our users to implement and send packaged code alterations and additions. To harness and encapsulate the creativity and customization that our users are capable of, we'd be well suited to develop a mechanism to do package extensions/plugins. ScreenConnect 5.0 will be our first release to offer this as a page in the administration section. 

An extension, in this case, can be anything from an aesthetic change, 3rd party integration, to a new event trigger. In the video below, a developer shows two example extensions, both of which modify the guest page to add additional functionality. Adding a quick link on the guest page to create a session or to download your client installer could be a matter of installing and activating a plugin, much like the system WordPress and many browsers utilzie today. 

See the demonstration below: 

Posted by Jeff Bishop at 9:11 AM       0 Comments       Share
Categories: Development | ScreenConnect | feature

Security Enhancements in ScreenConnect 4.4 -- Timeouts, Restricting IPs

Security is always a concern for us and our clients, and we're always asked on the forum to implement new features to enhance security. In ScreenConnect 4.4, we've rolled out the ability to use a more intelligent, granular timeout capability to reduce the chance of your site being compromised. We've also made the IP Security module included out of the box (this was an optional modification previously). This module allows admins to restrict access to certain resources (primarily the host and admin pages) to certain IPs. 

You can download the pre-release of ScreenConnect 4.4 here.  

The need: time out inactive hosts/admins to decrease the chance of those resources being compromised. In addressing this, we decided that the general, all-encompassing timeout idea wouldn't fit the the typical usage model. A higher threshold of security would probably be necessary for the admin page, depending on the user's configuration. Thus we introduce resource-specific timeouts.

In the web.config file, you can adjust a few new keys on a per-page level. Take note of "MaxLongestTicketReissueIntervalSeconds," which allows us to set, in seconds, how long the user can remain idle (including mouse activity) while on this resource (in this case, Administration.aspx):

 

 <configuration>
   <location path="Administration.aspx">
      <appSettings>
         <add key="MaxLongestTicketReissueIntervalSeconds" value="1800" />
         <add key="MinAuthenticationFactorCount" value="0" />
         <add key="RestrictToIPs" value="" />
         <add key="BlockIPs" value="" /> 
      </appSettings>
   </location>

   <location path="Host.aspx">
      [........]
   </location> 

 

As you can see, we've included other security features out of the box to give you more flexibility while protecting each resource -- restrict access to a certain page by the user's IP, blocking IPs, etc.

Posted by John Carey at 5:54 AM       0 Comments       Share
Categories: Development | ScreenConnect

ClickOnce Plugin for Chrome: Recommended Update

We know many of you use a ClickOnce browser plugin for Chrome to help speed up the join process, particularly for techs who are entering and exiting sessions regularly. We have previously recommended a plugin to use, but we've recently switched our recommendation to a different one due to the old plugin sneaking in ads. We suggest uninstalling the old one and installing the new one, as detailed in our forum

While we'd prefer to not rely on 3rd party applications, Google is soon to drop support for this type of browser extension anyway, so we've decided not to create one ourselves and to use a 3rd party plugin for this convenience.

Posted by John Carey at 5:31 AM       0 Comments       Share
Categories: Development