Efecte Query Language Description
Efecte Query Language Description
Efecte Query Language (EQL) is used to query data from Efecte. Web API can be used to integrating Efecte with other systems and this document aims to offer examples for web API services.
Efecte Query Language
Efecte Query Language (EQL) is an SQL-like structured language for querying Efecte through the Web API. It enables performing powerful, flexible searches against the Efecte data model.
In order to use EQL effectively, one needs to know some basic concepts about the underlying data model. The elements of the data model most commonly used with EQL are "entity", "entitydata", "template" and "folder".
Below are simple examples to illustrate the use of EQL. The tables provide reference information about the most important classes and properties of the Efecte data model.
Searchable classes:
Class |
Alias |
|
entity |
folder |
|
template |
Properties of class entity (com.efecte.datamodel.Entity):
Property |
Description |
|
entity |
|
Reference of class template (com.efecte.datamodel.Template) |
|
|
|
Reference of class folder (com.efecte.datamodel.Folder) |
|
|
|
Data cards that has hidden value for visibility, value 0 or 1 |
|
Data cards that have been deleted, i.e. moved to trash bin, value 0 or 1 |
|
A group of objects of type com.efecte.datamode.Metadata |
|
A group of objects of type com.bitmount.equipment.BSSStringEntityData |
|
Collection of integer data of an entity |
|
Collection of float data of an entity |
|
Collection of date data of an entity |
|
Collection of reference data of an entity |
|
|
|
Target data card |
|
Collection of external reference data of an entity |
|
|
|
|
|
Collection of text data of an entity |
|
Collection of static string data of an entity |
|
Collection of static integer data of an entity |
|
Collection of static float data of an entity |
|
Collection of static date data of an entity |
Properties of Class Template
Properties of class Template (com.efecte.datamodel.Template):
Property |
Description |
|
|
|
|
|
|
|
|
|
|
|
|
|
A group of objects of type com.efecte.datamode.Metadata |
|
A group of objects of type com.bitmount.equipment.BSSAttribute |
|
A group of objects of type BSSTemplatePermission |
Properties of Class Folder
Properties of class Folder (com.efecte.datamodel.Folder):
Property |
Description |
|
Database id |
|
|
|
Module code, describes in what module the folder belongs to |
|
|
|
|
|
A code given by administrator |
|
|
|
A group of objects of type com.efecte.equipment.Metadata |
|
A group of objects of type com.bitmount.equipment.BSSTemplate |
|
A group of objects of type com.bitmount.boas.AdminRoles |
|
A group of objects of type BSSEntityGroupPermission |
Date Macros and Time Expressions
EQL queries allows use of date macros and time expressions with date macros. These two can be combined like now+6h or start_month-1M.
Date Macros:
Macro |
Description |
|
Current date time |
|
Start of current day |
|
Start of current week |
|
Start of current month |
|
Start of current quarter |
|
Start of current half |
|
Start of current year |
|
End of current day |
|
End of current week |
|
End of current month |
|
End of current quarter |
|
End of current half |
|
End of current year |
Time Expressions:
Expression |
Description |
|
Year |
|
Month |
|
Day |
|
Hour |
|
Minute |
|
Second |
|
Week |
EQL Examples
Get count of All Data Cards of the Whole Efecte System
Get count of All Data Cards of the Whole Efecte System. This is good way to test that web API is working properly.
SELECT count(id) FROM entity
Search Specific Data from a Data Card
Search the name of the data card, name of the template and the name of the folder for entities that have specific host_name and are in template ‘workstation’
SELECT name, template.name, folder.name FROM entity WHERE $host_name$= 'falco' AND template.code = ‘workstation’
Search All Data Cards of a Single Module
Search all data cards of a single module:
SELECT entity FROM entity WHERE template.systemCode = 'beq'
or using subquery
SELECT entity FROM entity WHERE templateId IN (SELECT id FROM template WHERE systemCode = 'beq')
or using exists query:
SELECT entity FROM entity WHERE EXISTS (SELECT id FROM template WHERE id = entity.templateId AND template.systemCode = 'beq')
Search All incident Data Cards that are not in Deleted or Hidden
Search all data cards that are not hidden or deleted:
SELECT entity FROM entity WHERE template.code='incident' AND entity.deleted=0 AND entity.hidden=0
Search Data Cards and Select Given Attributes in Folder
Select given attributes of data cards in template. Returns attributes efecte_id, subject and status:
SELECT $efecte_id$, $subject$, $status$ FROM entity WHERE entity.template.code = 'incident'
Search Data Cards Which Have Been Created Between Dates
It is possible to use date macros in EQL. Below is example that fetches all incidents that have been created between two months ago and current date time (now):
SELECT entity FROM entity WHERE template.code='incident' AND $created$>'start_month-2M' AND $created$<'NOW'
Search Distinct Data Cards
Finding distinct data cards
SELECT DISTINCT entity.referenceData.target FROM entity WHERE template.code = 'invoice_row'
Search Data Cards Using Reference Value
Find workstation that is owned by user with full name
SELECT entity FROM entity WHERE template.code='workstation' AND $user:full_name$='user test'
Table of Contents