6.8.1 Report variablesTwo types of report variables can be defined in the system: based on measurements and on threshold violations. They differ in the definable query expression, in the possible values to be displayed on report views, as well as in the interpretation of the Interval and the When parameters. On the other hand, the PVSR object hierarchy is almost the same in both cases since the site-equipment level can be found for thresholds, too. The system achieves this by assigning each threshold to its constituting measurements, and thus to the sites and equipments containing these measurements too. The “01. Report Variables” starting page contains a table that simultaneously shows the active – so currently being monitored – and the inactive report variables, too. The meanings of the individual columns of the table are: · Links: These links help to add new variables or to modify the existing ones · Variable name: It uniquely identifies the report variable, when defining the report view we can choose among these names · Measurement name: the value of this field will obviously be determined during creation and cannot be modified later o Threshold: the report variable aggregates threshold violations o Other: The report variable summarizes these types of measurements. It can be an arbitrary interface or other measurement, too · Additional query: A report variable is evaluated for all measurements and thresholds in the PVSR system by default, but with the help of the SQL expression that can be defined here we can restrict the scope, e.g. to a certain equipment type, or site name, threshold name, etc. The specification of the expression is facilitated by accessory elements, their description together with examples can be found at the end of the subchapter. Do not forget that the definition of an expression cannot and must not be started with the “WHERE” keyword! · Detailed summary at equipment level: The evaluation of the report variables occurs in two steps: the first evaluation takes place on the basis of the hierarchical arrangement (site-site-…-equipment-measurement as well as site-site…-equipment-threshold), then a daily evaluation happens at each level. The latter is always stored in the database while in the case of the hierarchical evaluation the picture is mixed: it is never stored for measurements and thresholds since it is done through the normal measurement and threshold evaluation. For sites it is always stored, for equipment, however, only if this column contains ‘yes’. If there are typically only one or two measurement types or thresholds under a piece of equipment – e.g. availability or CPU utilization –, then it is not worth setting this option since so the application needs less database space, the evaluation will be faster, and the functionality of the system will not decrease (see, for example submenu item ‘Measurements of various equipments’ as well as the display of threshold violations under a piece of equipment) · Interval: o Threshold: the report variable executes the evaluation for those thresholds that have the specified time interval. So, contrary to measurement evaluation, it is not possible to “summarize” thresholds of different intervals, as well as one minimal filtering condition always emerges o One report variable is able to evaluate several measurements with different measurement intervals, too. In order to perform this, a “measurement interval” should be given for the report variables as well; this will be the basis to which the application converts all measurement results during the evaluation. The hierarchical summaries (values for equipments and measurements) will be stored according to the interval given here. This also means that if for example measurements below an equipment have collection cycle of one minute and the interval here is five minutes then the maximum value shown in the reports for the equipment will be the maximum of the five minute averages, rather than the maximum of the one minute values. The selected value cannot be modified later · When: o Threshold: in this case, there is no possibility to choose, the evaluation always executed (since windowing can be done at threshold definition level) o When to summarize the variable at site and equipment levels. The statistics on the site and on the equipment level are calculated based on this time period. However the statistics for a measurement: § If it only “belongs” to one report variable or all the report variable to which it “belongs” have the same value for this parameter then its statistics are calculated according to this setting § If it “belongs” to more than one report variable in a way that they have more than one value set for this parameter then its statistics are calculated from 0-24, on every day · Active: Whether the report variable will currently be evaluated or not. The value can be changed in both ways, so an inactive variable can be active again The Interval and the Detailed summary at equipment level parameters are only available for periodic measurements, but not for irregular measurements. By clicking on the Variable adding or editing links, the same fields are displayed, completed with the aforementioned accessory elements that are to help the creation of the desired SQL expression, but the expression itself can be freely edited, independently from this. These elements are: · List of database fields: the list of usable fields depends on whether it is threshold evaluation or not o Threshold: the list contains the most frequently used fields of the threshold objects. The selected element can be inserted into the expression with the help of the Add link. This list is only an aid, because every other field can be used directly or by juxtaposing it with ‘T.’ in front. It is important to note that the threshold alarm level (Critical, Minor, …) can be found in the database in a coded form, the code is also contained by the selectable field name o Other: the list contains the most frequently used fields of the different objects (site, equipment, measurement). The selected element can be inserted into the expression with the help of the Add link. This list is only an aid; all the fields can be used according to the following rule. § Site fields start with ‘S.’ § Equipment fields start with ‘E.’ § Measurement fields start with ‘M.’ · List of relations: It is recommended for those who are not experienced in the world of SQL expressions, the selected element can be inserted with the other Add link. In certain cases (for example, Contains) the text ‘TEXT appears in the substituted string, which needs to be replaced by the desired value · Test: By clicking on the link we can test whether the given expression was successful or not, and we can see either the first 100 results at maximum or en error message together with the entire SQL command that was executed. Warning: if we click on the Back link after the test, and we are in insert mode, then the measurement type will be the default value, i.e. traffic again!!! We can combine the expression elements created so by the logical connectives “AND” and “OR” and group them with parenthesis, by taking into account the order of precedence: AND is evaluated earlier than OR. Examples: 1. Those measurements that are in a. Select the Equipment sysLocation field and press the Add link b. Select the Contains operation and click on the Add link c. Replace the TEXT by “ d. Write “AND” after it e. Select the Equipment address field and click on the Add link f. Select the Begins with operation and click on the Add link g. Replace the TEXT by “1.2.3.”. E.SYSLOCATION LIKE '% 2. The LIKE operator is case sensitive. If we want to eliminate this, then we can use the UPPER(E.SYSLOCATION) LIKE '% 3. Those measurements that can be found directly below a site named TEST: a. Select the Site name field and click on the Add link b. Select the Equal operation and click on the Add link c. Write in ‘TEST’ S.NAME = 'TEST' 4. Those thresholds that are of Critical level and their name begin with TEST T.TR_LEVEL = 4 AND T.NAME LIKE 'TEST%' |