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
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.