The query engine contains a simple template mechanism that can be used to generate sql statements.

Property Expansion

Properties are referenced by using the Ant and UNIX shell inspired ${property_name} syntax.

Property Definition

Properties are defined by the <property> element, usually within in the <initially> or <finally> elements


Properties are read-only once they are set. Exceptions to that rule are properties within the <finally> element and properties automatically generated as a side-effect of dialog or dynamic property execution.

Predefined Properties

OctoSAM Inventory defines a number of properties that you can reference in your queries. Use the Query Inspector tool window to see all predefined properties.

Control Characters

Property Description
${cr} Carriage Return
${ht} Horizontal Tab
${lf} Line Feed
${nl} New Line

Stock Date and Time Formats

Property Description
${formats.defaultexceldate} Default date format for Excel exports
${formats.defaultgriddate} Default date format
${formats.defaultgridtime} Default time format
${formats.defaultgridratio} Default format for ratio columns
${formats.defaultexcelratio} Default ratio format for Excel export

Environment Properties

These properties let you change the behaviour of the query in different environemnts. Use with caution, the expected behaviour is that queries behave the same everywhere.

Property Description
${application} calling application, currently Octopus2, License, OctoUtil or OctoWeb2 depending where the query is executed
${frontend} either web or desktop depending where the query is executed

Settable Environment Properties

These properties allow to customize appearance of the query.

Property Description
${query.title} overrides the query title which is normally taken from the query file name / its path in the repository.
 <column name="LastUsageDate" 
         excelformat="${formats.defaultexceldate}" />

 <column name="Minutes" 
         excelformat="${formats.defaultexceltime}" />

AppSettings Properties

All appSettings keys and values from the application configuration file are available under ${appsettings.key_name}

Object Properties

For queries started from an OctoSAM Inventory Object such as SoftwarePackage or SofwareSignature, the object's fields are available under ${objectname.field_name}

Dynamic Properties

Dynamic properties may return different values at each expansion. They can be seen as simple functions or macros within the engine to transform values etc. Arguments to dynamic properties are separated by either ':' (colon) or '|' (the pipe symbol). The separator used may not be contained in any of the values.



Expression can either be the strings 'true' or 'false' or a comparison expression in the form "string1=string2". If the expression evaluates to 'false', an error message is generated. This can be used to debug complicated logic.



Returns the current local datetime in the specified .net format.



Returns the value of the specified environment variable.



Returns true if the property referenced by propertyname does not exist or contains an empty string.



Returns true if the property referenced by propertyname does exist



Performs a regular expression match of string against pattern. The returned Value is either true or false

Additionally, capturing groups can be defined. The the captured string values will be assigned to properties named name_group_1 ... name_group_n.


Single quotes around 'pattern' are required. The pattern itself cannot contain the quote character.



Replaces all occurrences of string 'toreplace' with 'replacement' in string.



Returns a valid properly quoted SQL string regardless of the contents of the specified string. Can be used to prevent SQL injection.



Tries to transform the specified string into a valid SQL number.



Expression can either be the strings true or false or a comparison in the form of value1=value2. If expression evaluates to true, string1 is returned, otherwise string2.



Returns the specified string all lowercase.



Returns the specified string all upper.



Direction can be either 'left', 'right' or empty which implies trim from both ends. Characters is a string of characters to trim.

<property name="prop1" value="XXXYYZtest" />
<property name="prop2" value="${@trim:left:XYZ:${prop1}}"

Property prop2 will now contain the string 'test'.



Returns the specified string in urlencode format.