Queries

Queries are used for advanced data search in the tree and in the Installations register, as well as for the preparation of tabular print reports.

Queries are compiled in a textual form and use a language similar to the SQL query language. However, unlike SQL, our language contains tools for searches in the tree structure of objects.

The work with queries will be demonstrated on an example of a query, the purpose of which will be to find all computers in the tree.

1. Go to the main menu and select Query – New.
2. The New Query Wizard – Step 1 window will open where you can select the query controlling object. In our simple example, the controlling object is simply the object we need to find, i.e. a computer. Therefore, expand the computer set and select the "Computer" object. Then click Next to proceed.
3. This will take you to the second Wizard screen, i.e. the New Query Wizard – Step 2 window. This is where you can select the data that will be displayed in the query results for each object found. Use the list on the right to select e.g. the Computer name and Purchase date properties.
4. Click Next to proceed to the New Query Wizard – Step 3 window. You can use this window to edit column headers and to adapt column width for the print reports, as well as add search criteria. We do not need to change anything in our example. Click Next to proceed to the next step.
5. Use the New Query Wizard – Step 4 window to edit the sorting and grouping of items in the query results. Again, click Next to proceed to the next step.
6. Use the New Query Wizard – Step 5 window to enter the print report title and then click on Preview.
7. The Query Parameters window opens where you can further restrict the search range in the tree.
8. Then click OK to open the print report with the query result in the HTML document window.
9. Close the print report window. This will take you back to the Wizard. Click Finish to close the Wizard.
10. The window Searching – Query opens with the text form of the query that you have created in the Wizard. Click on the Save button to save your query to a file. This will allow you to load the query later by clicking the Load button.
11. Click on the Evaluate button.
12. The Query Parameters window opens again, like in the Preview step. Once you confirm this window, the query will be evaluated and the results will display in the Search results window. Here you can click the Print report button to display the print report, or click the Export button to export the results to a text file that you can edit further e.g. in Microsoft Excel.

Query Language Syntax

Query= [PARAMETERS Parameter [, Parameter]...] SELECT [Query_Type] [AS [Title=] Query_Id] [DOWN | UP | DEEP | SHALLOW | NEAREST | ALL] [Column_Header [, Column_Width %]=][Query_Id.] Property [Column_Operators] [, [Column_Header [, Column_Width %]=] [Query_Id.]Property [Column_Operators] ...] [WHERE [NOT] Filter [AND | OR [NOT] Filter ...]] [WITH Query[, Query ...] [HAVING [NOT] Query_Id [AND | OR [NOT] Query_Id ...]] ENDWITH] [GROUP BY [Query_Id.]Property] [ORDER BY [Query_Id.]Property [ASC | DESC] [, [Query_Id.] Property [ASC | DESC] ...]]

Parameter

A query parameter is a data value entered by the user at the moment of the query evaluation.

Parameter= Parameter_Name Parameter_Type

where:

Parameter_Name

Identifies the parameter. The individual parameter names must be unique and must not be identical to the column identifiers. If the parameter name contains multiple words, the parameter name must be enclosed in square brackets.

Parameter_Type

Example 1. Query language – Parameter

We need to display all interventions in computers performed within a selected time range. Before the query is evaluated, the program will ask you to enter the parameter values From date and To date.

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

Query_Type

The query types HISTORY, NOTICE, and SWINST can only be used as subqueries and may not contain further subqueries. Moreover, the SWINST query type is only meaningful if it is a subquery to a query which returns the objects of type "computer".

Example 2. Query language – Query type

We need to display all notes for the "computer" objects chronologically.

SELECT AS "Object 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 need to display an overview of all computers and installed operating systems. We also need to group the results based on the operating systems.

SELECT AS "Overview 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

Report title.

Example 4. Query language – Title

We need to display all computers. The report title will say "Computers".

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

Query_Id

Query or subquery identifier in the WITH section. If two subqueries include properties with identical names, you can make those names unique with this syntax: Query_Id.Property, e.g. "S0.Capacity". If the query identifier contains more than one word, enclose it in square brackets.

Example 5. Query language – Query identifier

We need to display all computers, but we are only interested in the following information: Computer name, Keyboard, and Monitor. The Name property from the objects Keyboard and Monitor need to be distinguished with a 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, and ALL modifiers

Modifiers are used to specify the method of search for objects in subqueries in the WITH section.

The modifiers NEAREST and ALL only make sense if used together with the DEEP modifier which will enable in-depth search.

Example 6. Query language – Modifiers

We need to display all computer sets, but we are only interested in the following information: Set name, RAM, and HDD. We will further narrow down the RAM selection to RAM located on the motherboard. The controlling object will be the PC object.

• Computer Set 1
  • PC
    • RAM
    • HDD

• Computer Set 2
  • PC
    • RAM
    • audio card
      • RAM
    • HDD

You can see in the example of the two computer sets above, that there the audio card in computer 2 contains a memory chip. To prevent this memory from being reported, the S2 query uses the SHALLOW modifier to limit the search to the PC object level only.

The UP modifier used in the S1 query makes sure that the Computer Set object is found that is located above the PC (computer) object.

SELECT AS S0 S1.@object, S2.@object, S3.@object WHERE @class LIKE "Computer*"
WITH SELECT AS S1 UP WHERE @class="Computer Set", SELECT AS S2 DOWN SHALLOW WHERE @class="RAM", SELECT AS S3 DOWN WHERE @class="HDD"
ENDWITH

Column_Header

Specifies the column headers in the report.

Example 7. Query language – Column header

We need to display computers and we are interested in the following information: Computer, Hard drive, RAM, and CPU. The columns in the output report will have the following headers: Computer, HDD, RAM, and 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="HDD", SELECT AS S2 WHERE @class="RAM", SELECT AS S3 WHERE @class="CPU"
ENDWITH

If you specified no column headers, the headers would read as follows: S1.Title, S2.Title, S3.Title.

Column_Width

Specifies the column width in the report. The column width is specified in percent as against the width of the entire report.

Example 8. Query language – Column width

We need to display computers and we are interested in the following information: Report name, User, and Floor.

We also need that the "Report name" column uses the "Computer" header and that its width is 40% of the page. The width of the "Floor" column will be 20% of the page width.

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

Property

Property name. The order of columns in the query corresponds to the order of columns in the output report. If the property name contains multiple words, the parameter name must be enclosed in square brackets.

You can also use the following special values for a Property:

Special values can only be used in query types HISTORY, NOTICE, and SWINST.

Column_Operators

Specify one or more operators. The individual operators are separated with a space.

Example 9. Query language – Column operators

We need to find out the average memory size in our computers.

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

The column "Size" at the end of the print report will include the average of all values.

Filter

A filter is a Boolean expression that specifies a condition. The condition is evaluated for every object. The object is included in the query result if it matches the condition.

Filter= Property Comparison Constant | OWN(Property) | INHERITED(Property)

where:

Comparison

The OWN() function is matched if the property is an own property of the object. The INHERITED() function is matched for inherited properties.

Example 10. Query language – Filter

To find all HP printers, we will use a filter that will limit the list of printers to those printers whose names begin with the two characters "HP".

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

Example 11. Query language – Filter

We need to find all RAM memories smaller than 64 MB.

@class="RAM" AND Size<64

Constant

A constant can be a real number or a text string. You can use an asterisk notation ‘*‘ in the string to identify any number of characters.

Table 1. Constants – history record types

Table 2. Constants – software product types

Table 3. Constants – software product categories

Example 12. Query language – Constants

We need to find all RAM memories smaller than 64 MB.

@class=”RAM” AND Size<64

Example 13. Query language – Constants

We need to find the history records for objects deleted (moved) after 1/1/2001.

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

Example 14. Query language – Constants

We need to find all freeware products.

@type=SW_NOLIC

Example 15. Query language – Constants

We need to find all software products that are not permitted, i.e. that are installed illegally

@licname=NULL

Example 16. Query language – Constants

We need to find all operating systems installed after 1/1/2001.

@category=SW_OS AND @date>"1.1.2001"

WITH ... ENDWITH

You can enter a list of subqueries between the keywords WITH and ENDWITH. The individual subqueries are separated with commas.

The HAVING clause specifies, which subqueries in the list must contain at least one object to select a controlling object.

Example 17. Query language – Subqueries

We need to display all computers without a hard drive.

SELECT AS "Having no HDD"=S0 S0.@object WHERE @class LIKE "Computer*" WITH SELECT AS HDD WHERE @class="HDD"
    HAVING NOT HDD ENDWITH

GROUP BY

Specifies the column to group the search results by. The column must be in the list of columns behind the keyword SELECT.

Example 18. Query language – GROUP BY

We need to display computers and group them by Floor.

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

ORDER BY

Sorts column values in the output report. The sorting priority is specified by the order of columns behind the keyword ORDER BY. All columns that will be used for sorting, must be in the list of columns behind the keyword SELECT.

The ASC and DESC modifiers specify the sorting direction:

• ASC – ascending
• DESC – descending

Example 19. Query language – ORDER BY

The list of computers sorted by the columns Floor and User.

SELECT AS "Computers ordered by floors and users"=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.