Querying entities in Analytics

Suggest Edits

You can use the entity tables to map various properties of Formant entities. For example, the query_numeric table can show you all numeric data points for a particular device_id, but you may want to present that data to the user with a more friendly label, like the device name.

This guide will teach you how to use the entity mapping tables in Formant Analytics.

Mapping table structure

`mapping_account`

Parameter	Usage
`id`	ID of this account.
`organization_id`	ID of the organization to which this account belongs.
`tags`	Tags associated with this account.
`name`	Name of this account.
`time`

`mapping_device`

Parameter	Usage
`id`	ID of this device.
`organization_id`	ID of the organization to which this device belongs.
`tags`	Tags associated with this device.
`name`	Name of this device.
`time`

`mapping_fleet`

Parameter	Usage
`id`	ID of this fleet.
`organization_id`	ID of the organization to which this fleet belongs.
`tags`	Tags associated with this fleet.
`name`	Name of this fleet.
`time`

`mapping_organization`

Parameter	Usage
`organization_id`	ID of this organization.
`tags`	Tags associated with this organization.
`name`	Name of this organization.
`time`

`mapping_team`

Parameter	Usage
`id`	ID of this team.
`organization_id`	ID of the organization to which this team belongs.
`tags`	Tags associated with this team.
`name`	Name of this team.
`time`

`mapping_user`

Parameter	Usage
`id`	ID of this user.
`organization_id`	ID of the organization to which this user belongs.
`tags`	Tags associated with this user.
`first_name`	First name of this user.
`last_name`	Last name of this user.
`time`

Example: list devices by event count

Suppose you want to list all devices in your organization in descending order of event count. You want to include devices which have no events, and you want to present this to the user with a user-friendly device name, instead of the alphanumeric device ID.

In your SQL query, you'll select the mapping_device.name column, which contains the names of all devices in your organization, and LEFT JOIN the query_event table on the mapping_device table. This will return event counts for all matching device_id parameters, while still displaying mapping_device.name values without a corresponding event count:

SELECT
	mapping_device.name,
  COUNT(query_event.device_id) AS event_count
FROM
	mapping_device
LEFT JOIN
	query_event
ON
	mapping_device.id = query_event.device_id
GROUP BY
	mapping_device.name
ORDER BY
	event_count DESC;

Entering the SQL query in the SQL editor.

Displaying the result with user-friendly device names.