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.