Edit Advanced Statistics¶
You can insert queries in Querys section. Press on Add New, the first query must have Query Type setted as Query Master, the following queries must have Query Type setted as Query Standard. When you have created the first row, click on Setup on the right, here you can insert your main query, usually you get the identifier field, for example "node_id".
Example of Query Master: SELECT node_id FROM node WHERE node_enabled = 1 AND node_deleted = 0 [[limit_10]]
In Key field enter the name of the id field, in this case "node_id". Order of exec. must be 0 because this is the first query to execute.
Now you can add a Query Standard, usually used to get the fields you want to see in the Advanced Statistics.
Example of Query Standard: SELECT node_name FROM node WHERE node_id = [[node_id]]
In this example we want to get the names of 10 Nodes, so to see the name field you need to inset "node_name" in the query row "Field" and set Field Type as String, if you want to get a field with different data type remembere to set Field Type according to the field type in the database. Set Order of exec. as 1 because this is the second query to execute.
Add a Query Standard for each field you want to see in the Statistic referring to the id you got from the Query Master.
In this example we're going to execute a query on a cluster table, get data from a second table and showing the third column obtained by the Query Master.
If you are making a query using a cluster table you need to check "Use Cluster" and the date filter must be included in the query, for example [[datefilter_range_logcomm_dateedit]].
1)Query Master: SELECT logcomm_id AS logcommid, logcomm_idnode, logcomm_mac FROM logcomm WHERE [[datefilter_range_logcomm_dateedit]] [[limit_10]]
Use Cluster: TRUE; Key: logcomm_idnode; Key 2: logcomm_mac; Field: logcommid; Field Type: Numeric; Order of Exec: 0.
In the following query we're going to use the data obtained from the Query Master to get data from another table. To use the previous data use this sintax: [[Key or Key 2 of previous query]], in this case the previous query has Key value setted as "logcomm_idnode" so to use it we write [[logcomm_idnode]] in our Query Standard.
2)Query Standard: SELECT node_name FROM node WHERE node_id = [[logcomm_idnode]]
Use Cluster: FALSE; Key: ; Key 2: ; Field: node_name; Field Type: String; Order of Exec: 1.
In the following query we're going to use the data obtained from the Query Master and display it, this is useful if in a query you get 2 or 3 fields (3 is the max number of columns you can get from a single query) and you want to show all of them.
We use SELECT on the field we want to show and we assign it a column name using AS.
3)Query Standard: SELECT '[[logcomm_mac]]' AS mac
Use Cluster: FALSE; Key: ; Key 2: ; Field: mac; Field Type: String; Order of Exec: 2.
Here's the list of cluster tables (updated on 2021/01/21), when you get data from one of this tables in one of your query you must check "Use cluster" on the corresponding row, otherwise the query won't work. You also need to add the datefilter in the query with this sintax [[datefilter_range_TABLEDATEFIELD]], for logcomm it is [[datefilter_range_logcomm_dateedit]], and then check "Display Search Data" in section "Search/Input Fields".
Here's a list of all Special Strings you can use in queries with a descrition of what is their purpose.
- [[limit_NUM]], example:[[limit_25]], it limits the recordset to NUM records, using normal SQL LIMIT is allowed only on non cluster tables.
- [[orderby_FIELD_asc/desc]], example:[[orderby_node_name_desc]], it orders the recordset based on the field, using normal SQL ORDER is allowed only on non cluster tables. You can order only by one field.
- [[datefilter_range_DATEFIELD]], example: [[datefilter_range_logcomm_dateedit]], used on cluster tables, filters record by input date (you need to check Display Search Data).
- [[datefilter_DATEFIELDASSIGNEDNAME_DATEFIELDORIGINALNAME]], example: [[datefilter_logcommDate_logcomm_dateedit]], used on cluster tables, filters record by a field obtained from a previous query and declaared in Key or Key 2.
- (%%search_SEARCH/INPUT_FIELDS_LABEL%%), example: (%%search_Node%%), used when you check Display Search Data and add Search/Input Fields rows, in this example the Label value is "Node".
- (%%fromdate%%)/(%%todate%%), used when you check Display Search Data, available only for non cluster tables.
- (%%policy_TABLE_TABLE_ID%%), example: (%%policy_node_node_id%%), filters recordset by table policy for current user.
- (%%policy_TABLE1_TABLE2_IDTABLE1%%), example: (%%policy_logcomm_node_logcomm_idnode%%), filters recordset by table policy for current user using an id from another table.
- (%%checksons_IDNODEFIELD_SEARCH/INPUT_FIELDS_LABEL(of table "Node List")%%), example: (%%checksons_node_idnode_Node%%), available only for Search/Input Fields with table "Node List", if available, Nodes sons are shown too. In the query you need to get the IDNODEFIELD.
Here's the description of all fields of a query row.
Query Type: this tells what type of query you are creating. Query Master is the main query to be executed. Query Standard is a subquery usually based on Query Master data. Query IN is used to get multiple values of a field separated by comma, the result is usually used in other queries that use a IN condition. This type of query can be executed before Query Master so that you can return the values to it.
Use Cluster: it must be checked when using cluster tables, see the list above.
Key/Key 2: these are fields you can use in queries that follow the current query.
Field: this is the field of the current query that is shown in the Statistic.
Field Type: this must follow the Field db type.
Query: click on Setup to enter your query text here.
Order of exec.: this tells in which order the queries are executed. The Query Master usually has 0 while following queries must have a greater value.
This section allows the user to filter the Statistic with some predefined filters. These are: date filter, node filter, gateway filter.
When using these filters the check "Display Search Data" must be checked. Date filter is enabled by only checking this check, the other filters need you to add a row in this section and choose the corresponding table.
Order field is used to order the input fields in the page.
Using Date filter: in the query use (%%fromdate%%) and (%%todate%%) if using a non cluster table, otherwise use the [[datefilter_...]].
Using Node/Gateway filter: in the query use (%%search_SEARCH/INPUT_FIELDS_LABEL%%) with the correct label.
Here's an example of the final result.
You can group rows tha might have the same field value, for example you can get logcomm messages and the node id and group the record by node id.
Field: the field to group by.
Order: this indicates in which order the records must be grouped when you use 2 or more grouping, for example grouping logcomm messages by gateway and subgrouping messages of a gateway by node.
Show Sum: when checked, it shows the sum of a numeric value for that group chosen in Sum Field.
Sum Field: the field on which the sum is executed, it must be a numeric field.