Skip Navigation LinksALVAO 11.2 / Asset manager guide / Searching, reporting and data analysis / Queries (deprecated)
Queries (deprecated)
Note:
This feature is deprecated and will be retired in future versions of this application.
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.
- Select Query - New from the main menu.
- 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 set object and select the computer object in it. Then proceed with the Next button.
- 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.
- 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.
- 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.
- On page Query Wizard - Step 5, type the title of the print report and press the Preview button.
- A window will open on the screen Query parameters , where you can further limit the scope of the search tree.
- Then press the OK button and a print report will open on the screen with the query result in the Press report.
- Close the print report window. This returns you to the wizard, where you press Finish.
- 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.
- Press the Evaluate button.
- 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
Symbol |
Meaning |
| |
or |
[ ] |
optional |
... |
repeat |
Parameter
Query parameter is a data value that the user enters at the moment of query evaluation.
Parameter= Parameter_Name Parameter_Type
where:
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 |
type |
T_TEXT |
text |
T_DOUBLE |
real number |
T_DATETIME |
date, time |
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
Query_Type |
Description |
HISTORY |
query object history items |
NOTES |
query object notes |
SWINST |
query on installed software products |
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".
Note:
Query of type HISTORY and NOTICE does not work with deleted (hidden) log entries.
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 ticket 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.
Modifier |
Description |
DOWN |
Downward search, i.e. searching the subtree of the control object (default value) |
UP |
upward search, i.e. all objects on the path to the root of the tree are searched |
DEEP |
search all descendants of the controlling object (default value) |
SHALLOW |
search only in direct descendants of the control object (not in descendants of descendants) |
NEAREST |
when the nearest object is found, stop searching in depth (default value) |
ALL |
search the entire subtree of the controlling object |
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 sets and are only interested in the data: set 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.
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 Set 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="Set",
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:
Property |
Description |
@authorized |
the software installation on the computer is confirmed or the computer is licensed |
@category |
category of software product (e.g. operating system) |
@cdkey |
CD key of the software product |
@class |
object type |
@date |
date of history record, object note or sw product installation |
@description |
Query type (Query_Type) |
The meaning of the @description property |
HISTORY |
description of the history record |
NOTES |
note text |
SWINST |
note on installation history record |
|
@detproductname |
detected software product name e.g. operating system name including service pack version |
@detproductver |
detected version of the software product (detailed value of the product version e.g. 5.23) |
@detlastdate |
date of the last software detection of the computer |
@flags |
object history record types (To query object history record types, use special constants, see Constants.) |
@invnumber |
inventory number of the assigned license |
@invoice |
the document label of the assigned license purchase (see License and Installation Overview, document column) |
@language |
language version of the software product |
@licname |
exact name of the software product license |
@nodeid |
unique identifier (number) of the object in the tree. Suitable for sorting - finding the last new objects in the inventory. |
@notice |
object notes |
@object |
object name |
@objectpath |
path to the object in the tree |
@producer |
producer of a software product |
@producer |
name of the software product |
@property |
any object property |
@serialnumber |
serial number of the software license |
@subject |
note title |
@swfrompack |
software is from software package |
@type |
type of software product (e.g. requiring a license, trial, ...) |
@user |
author of a note, object history record, or sw product installation |
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.
Operator |
Description |
SUM |
sum of values |
COUNT |
number of items |
AVG |
average value |
MIN |
minimum value |
MAX |
maximum value |
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.
where:
Comparison |
Description |
= |
equality |
like |
substring occurrence |
<> |
inequality |
< |
smaller |
> |
larger |
<= |
smaller or equal |
>= |
larger or equal |
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.
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
Constant |
Description |
H_MOVEDEST |
I have moved object "x" |
H_MOVESRC |
moved object "x" from here |
H_TO |
object moved to "x" |
H_INSERTNEW |
new object "x" is inserted |
H_TONEW |
object created in "x" |
H_SCANMMOVEDEST |
I have moved object "x" - by scanner |
H_SCANMOVESRC |
object "x" - moved from here by scanner |
H_SCANTO |
object moved to "x" - by scanner |
H_SCANINSERTNEW |
new object inserted into "x" - scanner |
H_SCANTONEW |
object created in "x" - by scanner |
Table 2. Constants - types of software products
Constants |
Description |
SW_NEEDSLIC |
commercial - user must purchase a license to use the product |
SW_NOLIC |
freeware (does not require a license) - the product can be used without a license |
SW_TRIALLIC |
shareware/trial - product can be tested without license |
NULL |
unlicensed - illegally installed |
Table 3. Constants - software product categories
Constants |
Description |
SW_OS |
a software product is an operating system |
Example 12. Query Language - Constants
Find all RAM less than 64 MB.
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.
Example 15. Query language - constants
Find all unauthorized software products - illegally installed
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.