Enterprise Recon 2.11.0
SQL Reference
The ER2 ODBC Driver supports common SQL syntax and keywords that can be used to create custom SQL statements, giving you the ability to filter and manipulate the data fetched from the Master Server.
The table below describes the SQL support matrix for the Enterprise Recon ODBC Driver.
Keyword | Description | Example |
---|---|---|
SELECT | Beginning of a row retrieve request. | SELECT * FROM SCANREPORT_SUMMARY |
`column_name` | Specify the column name to retrieve from a specific table. | SELECT `GUID` FROM DATA_GROUP |
`column_name`.`table_name` | Specify the column name to retrieve from a specific table. Used to eliminate column name conflicts across multiple data tables. | SELECT `GUID`.`DATA_TARGET` FROM DATA_TARGET |
* | Alias for all columns. | SELECT * FROM DATA_TARGET |
AS `new_column_name` | Rename a selected column in the output table. | SELECT `GUID`.`DATA_TARGET` AS `GUID` FROM DATA_TARGET |
`column_name`, `column_name` | Separator for selecting multiple columns. | SELECT `TUID`, `GUID` FROM DATA_TARGET |
FROM | Specify one or more tables to retrieve data from. | SELECT * FROM DATA_TARGET |
`table` | Specify table name to retrieve data from. | SELECT * FROM `DATA_TARGET` |
AS `new_table_name` | Rename a selected table. | SELECT `GUID`.`DATA_TARGET` FROM `DATA_TARGET` AS `NEW_TABLE` |
`table_name`, `table_name` | Separator for selecting multiple tables. | SELECT * `FROM DATA_ACTION_LOG`, `DATA_ACTION_TYPES` |
WHERE | Specify one or more conditions to filter the returned data set. To filter the data set based on timestamp (e.g. SCANREPORT_MATCH.START), specify the value in Unix time format. To display match information for scans that started later than
00:00 AM on 1 January 2021 (GMT timezone), use SCANREPORT_MATCH.START > 1609459200.
|
SELECT * FROM DATA_TARGET WHERE TUID = 6622288671022978211 SELECT * FROM SCANREPORT_MATCH WHERE SCANREPORT_MATCH.START > 1609459200 |
AND / && | Separator for multiple conditions. | SELECT * FROM DATA_TARGET WHERE TUID = 6622288671022978211 AND GUID = 2471766758845071288 |
NOT / ! | Include results that do not meet the specified conditions. | SELECT * FROM DATA_TARGET WHERE NOT TUID = 6622288671022978211 |
= | Equality condition. | SELECT * FROM DATA_TARGET WHERE LABEL = 'MY-WINDOWS-MACHINE' |
!= / <> | Inequality condition. | SELECT * FROM DATA_ACTION_TYPES WHERE ACTIONID <> 2 |
< | Less-than condition. | SELECT * FROM SCANREPORT_SUMMARY WHERE MATCHES < 2000 |
<= | Less-than or equal-to condition. | SELECT * FROM SCANREPORT_SUMMARY WHERE MATCHES <= 2000 |
> | Greater-than condition. | SELECT * FROM SCANREPORT_SUMMARY WHERE MATCHES > 2000 |
>= | Greater-than or equal-to condition. | SELECT * FROM SCANREPORT_SUMMARY WHERE MATCHES >= 2000 |
IN | Equality condition with one or more possible values. | SELECT * FROM SCANREPORT_SUMMARY WHERE CUID IN (1,20,25,60) |
LIKE | Equality condition with partial or wildcard matching. | SELECT * FROM SCANREPORT_SUMMARY WHERE ROOT LIKE '%MARIADB%' |
DISTINCT | Return only unique rows from the data set. | SELECT DISTINCT REPORTID FROM SCANREPORT_SUMMARY |
INNER JOIN | Explicit join of two tables. | SELECT * FROM FOO INNER JOIN BAR ON FOO.X = BAR.Y |
ON | Define criteria for explicit join of two tables. | SELECT * FROM FOO INNER JOIN BAR ON FOO.X = BAR.Y |
LIMIT | Limit the number of rows returned from the data set. | SELECT * FROM SCANREPORT_SAMPLE LIMIT 10 OFFSET 20 |
OFFSET | Specify the starting point to return rows from the data set. | SELECT * FROM SCANREPORT_SAMPLE LIMIT 10 OFFSET 20 |