Queries

The queries are used for advanced searches of data in the tree and installation records and for creating tabular reports.

Queries are written textually in a language that is similar to SQL query language. However, unlike SQL, it contains tools for searching in the tree structure of objects.

We will demonstrate how to work with queries by using the example of a query to find all computers in a tree.

1. Select Query - New from the main menu.
2. A window will open on the screen Query Creation Wizard - Step 1 , in which we have to select the query management object. In our simple example, the controlling object is simply the object we are looking for, i.e. the computer. So expand the computer assembly object and select the computer object in it. Then proceed with the Next button.
3. This will take us to the other side of the wizard, i.e. the Query Wizard - Step 2 . Here we select the data that will be displayed in the query result about each object found. In the list on the right, select properties such as Computer Name and Date of Purchase.
4. Button Next will open the page Query Creation Wizard - Step 3. Here we can change the headings and column widths in the output report and add search conditions. In our case, we don't need to change anything and just continue with the Next.
5. On page Query Creation Wizard - Step 4 you can customize the ordering and grouping of items in the query result. Again, we can move on with the Next.
6. On page Query Wizard - Step 5, type the title of the print report and press the Preview button.
7. A window will open on the screen Query parameters , where you can further limit the scope of the search tree.
8. Then press the OK button and a print report will open on the screen with the query result in the Press report.
9. Close the print report window. This returns you to the wizard, where you press Finish.
10. The screen will open a Search - Query, where you can see the text entry for the query we just created. You can use the Save button to save it to a file, from where it can be retrieved later using the Load button.
11. Press the Evaluate button.
12. The window will reappear Query parameters as in the preview view. After the query is confirmed, the query is evaluated and the result is displayed in the Query results, in which you can use the Print Report button to view the print report, or the Export button button to save the result to a text file, which can be further processed e.g. in Microsoft Excel.

Query Language Syntax

Query
[PARAMETERS Parameter [, Parameter]...]

SELECT [Query_Type] [AS [Title=] Dotaz_Id]
[DOWN | UP | DEEP | SHALLOW | NEAREST | ALL]
[Column_Title [, Column_Width %]=][Query_Id.] Property [
 Column_Operators]
[, [Column_Title [, 
 Column_Operators] ...]

[WHERE
[NOT] Filter [AND | OR [NOT] Filter ...]]

[WITH
Question[Question ...]
[HAVING [NOT Query_Id ...]]
ENDWITH]

[GROUP BY 
[Property]

[ORDER BY 
[Property [ASC | DESC] 
[, [Property [ASC | DESC] ...]]

Parameter

Query parameter is a data value that the user enters at the moment of query evaluation.

Parameter= Parameter_Name Parameter_Type

where:

Parameter_Name

Identifies the parameter. Individual parameter names must be unique and must not match column identifiers. If the parameter name is composed of multiple words, it must be enclosed in square brackets.

Parameter_Type

Example 1. Query language - parameter

We want to list the hits on computers in a specified time interval. The program queries the values of the From data parameters before evaluating the query. and To data.

PARAMETERS [From date] T_DATETIME, [To date] T_DATETIME
SELECT AS "Hit Summary"=S "Setup"=@object
WHERE @class LIKE "Computer*"
WITH 
    SELECT HISTORY AS S1 "Made by"=@user,"Description"=@description
    WHERE @date>=[From date] AND @date<=[To date]
HAVING s1
ENDWITH

Query_Type

A query of type HISTORY, NOTICE or SWINST can only be used as a subquery and must not contain other subqueries. In addition, a SWINST query is only meaningful if it is a subquery of a query that returns objects of type "computer".

Example 2. Query language - query type

We want to list all computer object annotations sorted chronologically.

SELECT AS "Objects notes" =S0 WHERE @class LIKE "Computer*" 
WITH 
    SELECT NOTICE AS S1 @date, @user, @description
ENDWITH
ORDER BY S1.@date

Example 3. Query language - query type

We want to list all computers and installed operating systems. You also request that the result be grouped by installed operating systems.

SELECT AS "List of installed operating systems"=S
 "OS"=S1.@product, "Computer"=@object, "Inventory number"=[inventory number],
 "User"=[user]
WHERE @class LIKE "Computer*"
WITH 
    SELECT SWINST AS S1 
    WHERE @category=SW_OS
    HAVING S1
ENDWITH
GROUP BY S1.@product

Title

Output report title.

Example 4. Query language - title

We want to list all computers. The report header will contain the text "Computers".

SELECT AS "Computers"=S0 @object
WHERE @class LIKE "Computer*"

Query_Id

The query or subquery identifier in the WITH section. If two subqueries have properties with the same name, you can use to distinguish them. Query_Id.Property, e.g. "S0.Capacity". If the query identifier is composed of multiple words, write it in square brackets.

Example 5. Query Language - Query Identifier

We want to list all computers and are only interested in the data: Computer Name, Keyboard, Monitor. Properties Name from monitor and keyboard objects should be distinguished by the subquery identifier.

SELECT AS S0 S0.@object, S1.Name, S2.Name
WHERE @class LIKE "Computer*" 
WITH 
    SELECT AS S1 WHERE @class="Keyboard",
    SELECT AS S2 WHERE @class="Monitor"
ENDWITH 

DOWN, UP, DEEP, SHALLOW, NEAREST, ALL

Modifiers determine how to search for objects in subqueries in the WITH section.

The NEAREST and ALL modifiers only make sense when combined with the DEEP modifier, which provides a deep search.

Example 6. Query Language - Modifiers

We want to list computer assemblies and are only interested in the data: assembly name, RAM, HDD. Another limitation is that we are only interested in the RAM located on the motherboard. You specify the PC object as the control object.

• Assembly 1
  • PC
    • RAM
    • HDD

• Set 2
  • PC
    • RAM
    • sound card
      • RAM
    • HDD

From the example of the two computer setups, it can be seen that in computer 2, the sound card houses the memory. To prevent this memory from being dumped, the SHALOW modifier is used in the S2 query to ensure that only the PC object level is searched.

The UP modifier used in query S1 ensures that the Assembly object, which is located above the PC (computer) object, is found.

SELECT AS S0 S1.@object, S2.@object, S3.@object
WHERE @class LIKE "Computer*"
WITH 
    SELECT AS S1 UP WHERE @class="Assembly",
    SELECT AS S2 DOWN SHALLOW WHERE @class="memory RAM",
    SELECT AS S3 DOWN SHALL WHERE @class="Hard disk"
ENDWITH

Column_Title

Sets the column heading in the output report.

Example 7. Query language - column heading

We want to list the computers and are interested in the data: Computer, Hard Disk, RAM, Processor. The columns in the output report will have the headings Computer, HDD, RAM, CPU.

SELECT AS S0 "Computer"=S0.@object, "HDD"=S1.@object, 
"RAM"=S2.@object, "CPU"=S3.@object
WHERE @class LIKE "Computer*"
WITH 
    SELECT AS S1 WHERE @class="Hard Disk",
    SELECT AS S2 WHERE @class="RAM",
    SELECT AS S3 WHERE @class="Processor"
ENDWITH

If you did not specify any column headings, the columns would be titled: S1.Name, S2.Name, S3.Name.

Column_Width

Specifies the width of the column in the output report. The width is specified as a percentage of the width of the entire report.

Example 8. Query language - column width

We want to list the computers and are interested in the data: Report Name, User, Floor.

We also require that the Report Name column be named Computer and be 40% of the print page width and the Floor column be 20% of the page width.

SELECT "Computer",40%=@object, User, 20%=Floor
WHERE @class LIKE "Computer*"

Property

Property Name. The order of the columns in the query corresponds to their order in the output report. If the property name is composed of multiple words, it must be written in square brackets.

As a property, you can also use the following special values:

Special values can only be used in queries of type HISTORY, NOTICE or SWINST.

Column_Operators

Specify one or more operators. Individual operators are separated only by a space.

Example 9. Query language - column operators

Find the average memory capacity of computers.

SELECT @object, S1.Size AVG
WHERE @class LIKE "Computer*"
WITH 
    SELECT AS S1 WHERE @class="RAM"
ENDWITH

At the end of the print report, the average of all values will be listed in the Size column.

Filter

A filter is a boolean expression specifying a condition. The condition is evaluated for each object. An object is selected in the query result if the condition is satisfied for it.

Filter= Property)

where:

Comparison

The OWN() function is satisfied if the property is a custom property of the object. The INHERITED() function is satisfied for inherited properties.

INHERITED([User]) AND [User]="Veronica Palmer"

Example 10. Query Language - Filter

To find all HP printers, we use a filter that restricts the list of printers to printers whose name begins with the character pair "HP".

@class="Printer" AND Name LIKE "HP*"

Example 11. Query Language - Filter

Find all RAM less than 64 MB.

@class="RAM" AND Size<64

Constant

The constant can be a real number or a text string. In a text string, the asterisk notation '*' can be used to specify any number of characters.

Table 1. Constants - History Record Types

Table 2. Constants - types of software products

Table 3. Constants - software product categories

Example 12. Query Language - Constants

Find all RAM less than 64 MB.

@class="RAM" AND Size<64

Example 13. Query language - constants

Find history records of objects removed (moved) after January 1, 2001.

@date>"1.1.2001" AND ( @flag=H_MOVESRC OR @flags=H_SCANMOVESRC )

Example 14. Query language - constants

Find all freeware software products.

@type=SW_NOLIC

Example 15. Query language - constants

Find all unauthorized software products - illegally installed

@licname=NULL

Example 16. Query language - constants

Find all operating systems installed after January 1, 2001.

@category=SW_OS AND @date>"1.1.2001"

WITH ... ENDWITH

A list of subqueries is written between the WITH and ENDWITH keywords. Individual subqueries are separated by commas.

The HAVING clause specifies which subqueries in the list must contain at least one object for the control object to be selected.

Example 17. Query Language - Subqueries

We want to list computers that do not have a hard drive.

SELECT AS "Computers without HDD"=S0 S0.@object
WHERE @class LIKE "Computer*"
WITH 
    SELECT AS HDD WHERE @class="Hard disk"
    HAVING NOT HDD
ENDWITH

GROUP BY

Specifies the column by which the found items will be grouped. The column must be listed in the column list after the SELECT keyword.

Example 18. Query Language - GROUP BY

We want to list the computers and group them by Floor.

SELECT AS "Computers grouped by Floor"=S0 @object, Floor
WHERE @class LIKE "Computer*"
GROUP BY Floor

ORDER BY

It is used to sort column values in the output report. The sort priority is determined by the order of the columns after the ORDER BY keyword. All columns to be sorted must be listed in the column list after the SELECT keyword.

The ASC and DESC modifiers determine the sort direction:

• ASC - ascending
• DESC - descending

Example 19. Query Language - ORDER BY

List of computers sorted by the columns Floor, User.

SELECT AS "Computers sorted by floor and user"=S0
 S0.@object, User, Floor
WHERE @class LIKE "Computer*"
ORDER BY Floor, User

Did not find what you were looking for? Ask our technical support team.