Properties¶
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
Immutability¶
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 |
Script predefined properties¶
| Property | Description |
|---|---|
| ${script.all_organizations} | returns a list of all defined organization ids from the database |
| ${script.all_software_categories} | returns a list of all defined software category ids from the database |
| ${script.all_software_licensing_types} | returns a list of all defined software licensing type ids from the database |
| ${script.all_group_types} | returns a list of all defined group type ids from the database |
Environment properties¶
These properties let you change the behavior of the query in different environments. Use with caution, since the expected behavior 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 |
Query properties¶
These properties allow to customize appearance of a query.
| Property | Description |
|---|---|
| ${query.guid} | contains the query guid that is uses to identify a query. It corresponds to the value of the guid attribute of the query element. |
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 SoftwareSignature, the object's fields are available under ${objectname.field_name}
Settable environment properties¶
These properties allow the customization of the query's appearance.
| Property | Description |
|---|---|
| ${query.title} | overrides the query title which is normally taken from the query file name / its path in the repository. |
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 ':' (colon) or '|' (the pipe symbol). Values may not contain the separator.
assert¶
${@assert:expression}
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.
datetime¶
${@datetime:format}
Returns the current local datetime in the specified .net format.
env¶
${@env:environmentvariable}
Returns the value of the specified environment variable.
isempty¶
${@isempty:propertyname}
Returns true if the property referenced by propertyname does not exist or contains an empty string.
isset¶
${@isset:propertyname}
Returns true if the property referenced by propertyname does exist
match¶
${@match:name:'pattern':string}
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.
Info
Single quotes around 'pattern' are required. The pattern itself cannot contain the quote character.
replace¶
${@replace:string:toreplace:replacement}
Replaces all occurrences of string 'toreplace' with 'replacement' in string.
sqlstr¶
${@sqlstr:string}
Returns a valid properly quoted SQL string regardless of the contents of the specified string. Can be used to prevent SQL injection.
sqlnum¶
${@sqlnum:string}
Tries to transform the specified string into a valid SQL number.
test¶
${@test:expression:string1:string2}
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.
tolower¶
${@tolower:string}
Returns the specified string all lowercase.
toupper¶
${@toupper:string}
Returns the specified string all upper.
trim¶
${@trim:direction:characters:string}
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'.
urlencode¶
${@urlencode:string}
Returns the specified string in urlencode format.