Properties¶
The query engine includes 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 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}
SQL Server properties¶
| Property | Description |
|---|---|
| ${sqlserver.compatibilitylevel} | Can be used to guard SQL code that relies on a specific SQL Server compatibility level. |
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}
RBAC properties¶
For queries started from the OctoSAM Web interface, the current RBAC userid is available under ${rbac.userid}
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.
length¶
${@length:string}
Returns the length of the specified string.
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 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.
not¶
${@not:expression}
Expression can either be the strings true or false or a comparison in the form of value1=value2.
If expression evaluates to true, falseis returned otherwise true.
replace¶
${@replace:string:toreplace:replacement}
Replaces all occurrences of string 'toreplace' with 'replacement' in string.
sqlid¶
${@sqlid:string}
Sanitizes the string to be a valid ID (bigint) or a list of IDs. Can be used to prevent SQL injection.
select * from Machine where OrganizationID in (${@sqlid:${dialog_organizations}})
Use parameters to pass in values
Supported for backward compatibility. Use the parameters element to insert single ID parameters from user input into a query. From OctoSAM 1.11.7 on you can use user-defined table parameters to pass in a list of values:
select * from Machine where OrganizationID in (select ID from @organizations)
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.
Use parameters to pass in values
Supported for backward compatibility only. Use the parameters element to insert string parameters from user input into a query.
sqlnum¶
${@sqlnum:string}
Tries to transform the specified string into a valid SQL number.
Info
Supported for backward compatibility only. Use the parameters element to insert string parameters from user input into a query.
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 in all lowercase.
toupper¶
${@toupper:string}
Returns the specified string in all uppercase.
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.