RAS Reporting Database Schema

The table below describes the RAS Reporting database schema.

To see the visual representation of the schema, please use the following link: https://kb.parallels.com/Attachments/kcs-171407/DB_relations_Diagram.jpg

Database schema

ApplicationConnections

Contains names and IDs of applications that were used in sessions. The table is linked to the RDSession table that contains session information.

ComponentsConnections

This table is used in conjunction with the PublishingAgentConnections table. When a machine is connected, it is connected to a Connection Broker. This table links a machine to a Connection Broker.

DBUpgradeHistory

Contains all upgrade versions of the database.

Devices

Contains information about devices that were used to connect to Parallels RAS.

Disconnections

Contains the disconnected time of every connection.

Farms

Contains the farm information.

Gateways

This table is linked to the GatewayTypes table and shows all gateways and their types.

GatewayTunnelledConnections

This table is linked to the Gateways table and shows the number of connections going through the gateway.

GatewayTypes

Defines gateway types (Normal, Forwarding, Unknown).

Groups

This table contains all groups.

Guests

Keeps record of the hosts that are connected and to which VDI they belong. This table is NOT used in any queries.

HostTypes

Contains all host types used in the VDIHost table.

IdleConnections

Contains the idle time of every connection.

Machines

Stores information about all servers that are connected to the farm. This includes Terminal Servers (TS), Connection Brokers (PA), Secure Gateways (GW), RemotePCs (RPC), VDI hosts (VDIH) and VDI Guests (VDIG).

MachineStateLogs

Keeps a record of various states the machine has been in. For the complete list of states, check the MachineStates table.

MachineStates

Contains all machine states used in the MachineStateLogs table.

MachineType

Defines machine types used in the Machines table.

Members

This table is used in conjunction with tables Groups and Users. It allows to see which users belong to which groups.

MemoryHealth

Stores information about the current server memory usage, which is marked by a Timestamp. For detailed information on how to query this table see the queries section.

Notifications

Contains notifications that were displayed in the RAS Console.

ProcessorHealth

Stores information about the current server processors usage, which is marked by a Timestamp. For detailed information on how to query this table see the queries section.

Protocols

Defines the protocols used in the RDSessions table.

PublishingAgentConnections

This table is used to log for how long a Connection Broker has been active. When a Connection Broker is still active, the Started and Ended fields are the same. When a disconnect occurs, the Ended column value is updated with the new Timestamp.

RDConnections

This table links with the RDSessions table. A session can have multiple states, one of them is connected to have other two states (Disconnected and Idle) a connection must first be established therefore the other two tables that show these states are in a relationship with RDConnections table.

RDSessions

Stores a list of established sessions by clients, along with the protocol used (Console, or RDP), Session type (Desktop, Published Apps, VDI Apps…), the user that made the session (All users are pre populated in the table), Started and Ended time the connection was running through.

RemotePC

This table is a placeholder and currently has no columns, except the PK column.

SessionTypes

Defines session types for the RDSessions table.

Sites

Contains sites that exist in the farm.

TerminalServers

This table is a placeholder and currently has no columns, except the PK column.

TimeZones

Contains all time zones.

Users

Contains all users logged in.

VDIHost

Keeps record of the VDI hosts and their types. This table is NOT used in any queries.

SQL query example

SELECT
	us.Username,
	ac.ApplicationName AS [Application Name],
	COUNT (ac.ApplicationName) AS [NumOf Times Used]
FROM
	dbo.Users us INNER JOIN
	dbo.RDSessions rd ON us.SID = rd.User_fk INNER JOIN
	dbo.ApplicationConnections ac ON ac.Session_fk = rd.Session_ID
GROUP BY
	us.Username,
	ac.ApplicationName

Referring to the database schema and the SQL query above, we can see that the query does the following:

  • Selects usernames from table dbo.Users and gives the "us" alias to the table.

  • Since dbo.Users is not directly related to dbo.ApplicationConnections, it should first join dbo.RDSessions using matches on field SID from dbo.Users and User_fk from dbo.RDSessions

  • After we have joined dbo.RDSessions (alias "rd"), which has link to dbo.ApplicationConnections, we may also join ApplicationConnections itself with alias "ac" and select application names and number of launches from it.

© 2024 Parallels International GmbH. All rights reserved.