Avalanche powered by Wavelink

SQL Query Statements

Use SQL query statements to create custom reports. SQL is a language for managing and retrieving data in databases and allows you to create reports on any data stored in the Avalanche database tables. This section offers a brief overview of the commands used in Avalanche reports.

The SQL commands used in Avalanche reports include:

Select	Selects columns from the database.
From	Selects the table the data is reported from.
Where	Filters the results to only include rows that meet the defined condition. The following operators can be used with a Where command: = Equal != Not equal > Greater than < Less than >= Greater than or equal <= Less than or equal
Order By	Sorts the results alphabetically or numerically based on one of the columns.
Group By	Used with aggregate functions to group the results based on one of the columns. Available aggregate functions include: COUNT, MAX, MIN, SUM, AVG.

Example Statements

To show device model and serial number:

Select: a.Model as ModelName, b.PropertyValue as SerialNumber

From: MobileDevice as a, DeviceProperty as b

Where: b.PropertyName='SerialNumber'

To show devices with a terminal ID within a specific range:

Select: a.Model, a.TerminalId

From: MobileDevice as a

Where: a.TerminalId>='JCP0000' AND a.TerminalId<='JCP9999'

To show devices ordered by enabler version:

Select: a.Model, b.PropertyValue as EnablerVer

From: MobileDevice as a, DeviceProperty as b

Where: b.PropertyName='EnablerVer'

Order By: b.PropertyValue

To show how many devices you have of each OS type:

Select: COUNT(a.DeviceId), a.PropertyValue as OsType

From: DeviceProperty as a

Where: a.PropertyName='OsType'

Group By: a.PropertyValue