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