Skip to content

OctoSAM query development

Support notice

The query engine in OctoSAM Inventory and the query definition format may change with future versions of OctoSAM Inventory. While we try to keep the engine backward compatible with older versions, that may not always be possible. Be prepared to adjust your own queries to future versions of OctoSAM Inventory.

Quick start

Enable developer tools in the OctoSAM Inventory GUI

The developer tools can be enabled under Tools -> Options -> QueryDevelopmentToolsEnabled. If this option is checked, you will get 2 additional windows in the View menu. Use the Query Inspector tool window to view the code of executing queries.

Basic static query example

OctoSAM Inventory query definitions are stored in an XML file. All stock queries are delivered as source code in the OctoSAM Repository .zip file

It's a good idea to start from an existing query and adjust it to your needs.

Let's see how a basic query definition file looks like:

<?xml version="1.0" encoding="utf-8" ?>

The file starts with the typical XML preamble.

Info

Use an editor that can handle the XML encoding properly. Ideally, it should also support xml schema based validation. The file must be UTF-8 encoded.

<query xmlns="http://www.octosoft.ch/schema/query/2020/query.xsd"
       object="Machine"
       guid="{9e0767b0-1c00-11e3-b773-0800200c9a66}">

This starts the query definition. You must provide a new, unique GUID with every query. The GUID is used internally in OctoSAM Inventory to store your result grid layout and other settings. Unexpected behavior may result if you use the same GUID with multiple queries.

<description>
    <p>All machines with their most frequent and last users.</p>
</description>

Use the description element to provide a short description of your query. This is displayed in the Queries selection tree within the OctoSAM Inventory UI. A limited set of HTML elements are available as sub-elements.

<links>
    <link object="Machine" id="MachineID"></link>
    <link object="User" id="MostFrequentUserID"></link>
    <link object="User" id="LastUserID"></link>
</links>

The links element defines how OctoSAM Inventory should provide links to its internal document windows from the selected data. The id attributes must name a column returned by the query.

<weblinks>
    <link object="Machine" column="Machine" id="MachineGuid"></link>
    <link object="User" column="User" id="UserGuid"></link>
</links>
The weblinks element defines links in the Web UI version of the query. Note that web links use the GUID instead of the internal ID of objects to link to.

<select><![CDATA[
    SELECT o.ID AS OrganizationID , o.[Name] AS OrganizationName ...

]]>
</select>

The select element contains the SQL query. Use a CDATA definition to allow characters with special meaning to XML.

    <columns>
        <column name="OrganizationID" visible="false" />
        <column name="MachineID" visible="false" />
        <column name="MachineGuid" visible="false" />
        <column name="UserID" visible="false" />
        <column name="UserGuid" visible="false" />
        <column name="OrganizationDescription" visible="false"/>
        <column name="Custom3" visible="false"/>
        <column name="Custom4" visible="false"/>
   </columns>
</query>

Use the optional columns element to specify the format and initial visibility of columns in the grid. Note that the ID columns need to be selected in the query for linking to other objects but are usually of low interest to the user. This example defined a static query that executes the same SQL query on each invocation. For custom queries, that’s often all that's needed because you know all your inputs and can hard-code them within the query.

Elements

query

<query object="Machine"
        guid="{9e0767b0-1c00-11e3-b773-0800200c9a66}"
        tooltiptables="Machine,SoftwarePackage">
</query>
Attribute Required Default Value Description
desktop N true This query gets listed in the desktop UI (Queries tree and context queries) 1
export N Can specify 'JSON' or 'SQL' to preset the export dialog for queries designed mainly for export to other systems
from N For queries started from an OctoSAM Inventory object, specify the name of the object
guid Y A unique GUID for each query
license N A comma separated list of required license options for this query. If a license option is not installed the query will not be available in the UI.
multidelete N true if set to true, allows to select multiple rows in the output grid for delete operations
multiselect N true if set to true, allows to select multiple rows in the output grid
object N specifies the "main" object that this query returns, for queries that do not return a main object, specify "none"
required N A comma separated list of required tables or views for this query. If a required table or view does not exist at time of import, the query will not be available in the UI.
tooltiptables N A comma separated list of database tables that are used for column header tooltips
verbatim N false Use column names exactly as specified in the query, no OctoSAM casing/spacing. Useful for exports to other systems
web N true This query gets listed in the web UI (Queries tree and context queries for each object) 1

1 desktop and web attributes are not checked for queries that are called internally form the OctoSAM software. They only affect places where a selection of queries is listed.

description

<description>
        <p>All Machines with their most frequent and last users.</p>
</description>

The description element may contain a subset of HTML elements to define a short description of the Query. The description is displayed in the query selection tree in the OctoSAM Inventory GUI.

|HTML Element | |-----------------|----------------- |<b> <strong> |Bold |<br /> |Line Break |<i> <em> |Italics |<li> |List item |<ol> |Ordered list |<p> |Paragraph |<u> |Underlined Text |<ul> |Unordered List

pragma

<pragmas>
        <pragma name="pragma_name" value="pragma_value" />
</pragmas>

The <pragmas> element configures internal query debugging and unit testing settings. These settings are internal to OctoSAM.

Attribute Required Default Value Description
name Y name of the pragma
value Y value of the pragma
<links>
        <link object="Machine" id="MachineID" />
        <link object="User" id="MostFrequentUserID" />
        <link object="User" id="LastUserID" />
</links>

The <links> element establishes links from the grid to specified OctoSAM Inventory object windows.

Attribute Required Default Value Description
object Y OctoSAM object to link to
id Y Column to use for the linked object ObjectID

Tip

Multiple links to the same object type can be defined if a query returns multiple logical objects of the same type on the same row. For example a Query can return both the MostFrequentUser and the LastUser. In this case, OctoSAM Inventory will automatically annotate the different links of the same type in the Select Object dialog.

<weblinks>
        <link object="Machine" column="Machine" id="MachineGuid"  />
            <link object="User" column="MostFrequentUser" id="MostFrequentUserGuid" />
</weblinks>

The <links> element establishes links from the grid to specified OctoSAM Inventory object windows.

Attribute Required Default Value Description
object N OctoSAM object to link to
column Y The column that should be rendered as a hyperlink
id Y The column that contains the guid of the target object
action N ASP.Net MVC action name (do not use for standard objects or queries)
controller N ASP.Net MVC controller name (do not use for standard objects or queries)
query N Guid of a query to call. objectid parameter of the query will be set to the value of the column named in id. Currently, controller and action must be set to "Query" if you use this.

Currently, query to query links are only supported for web queries.

You have to ensure that the column used for the hyperlink is not confusing in the desktop rendering of the query.

Note that web links always start from a specified column, while the traditional links start from the entire row. Action and controller attributes allow for links to a specific part of the application. This is reserved for queries maintained by OctoSAM, since it depends on undocumented internals of the application. To link to the standard starting page of an object, use the object attribute.

initially

The <initially> element allows to define properties at initial load of the query definition. It is executed exactly once when the query is loaded.

input

<input> elements are used within dialogs to define input fields.

finally

Property definitions in the <finally> element are executed right before the select statement is sent to the database. In case of a refresh or Re-Query, this element is executed again. Property values may get overwritten. This is an exception to the rule that properties are read-only once they are set.

<dialog prefix="dialog">
    <input type="checkbox" name="junk" title="Including Junk" />
</dialog>

<finally>
    <property name="junk_condition"
              select-if="${dialog_junk}=false"
              value="WHERE SoftwareCategory.Junk = 0"/>
    <property name="junk_condition"
              select-if="${dialog_junk}=true"
              value=""/>
</finally>

This example shows the typical usage of the <finally> element to construct chunks of SQL code based on dialog input. The property ${junk_condition} would then be used within the <select> element.

Element groups

<groups>
    <group column="Column1" />
    <group column="Column2" />
</groups>
Attribute Required Default Value Description
column Y Column to group

The groups element can be used to pre-define grouping in the result grid.

Info

Standard OctoSAM queries do not define groups. Instead, the user can define and save groups using the Save Layout function. Pre-defined groups may make sense in site-specific queries.

columns

Attribute Required Default Value Description
align N "left" Align the contents of the column
autoellipsis N true If true, the grid displays ellipsis if the value does not fit the visible cell
chooservisible N true If true, the column is available in the column chooser / column menu
defaults N Select OctoSAM default formats for date, time or ratio type columns.
format N "{0}" .net format specification
webformat N "{0}" Telerik UI for ASP.Net MVC format
exportformat N Excel export format
header N Can be used to specify a column header that is different from the column name
measureitem N A string to define the width of the column
name Y Column name
nullable N true Can be set to false to indicate that the column cannot be null. Some UI elements may change to simplify the UI, however this is not required for all UI elements. For example a filter can still offer 'is null' even if the column is declare as not nullable
tooltip N Text of tooltip to display for this column
visible N true Initial visibility of the column
webvisible N true Initial visibility of the column in web interfaces

Tooltips

Tooltips are usually formed using the documentation of the database columns via the tooltiptables attribute in the <query> element. Some often-needed tooltips are defined globally and distributed with the queries. Aggregated and computed columns need custom tooltips defined per column in the tooltip attribute of the column element. The tooltip attribute can also refer to database documentation by using the prefix db: in front of the original column name.

  <column name="Host" tooltip="db:Machine.PrintableName" />

This will use the database documentation of the Machine.PrintableName column.

The select-if attribute

Many elements have an optional select-if attribute. The value of this attribute (after property expansion) can be either ‘true’ or ‘false’ or a comparison expression in the form ‘value1=vaue2’. If the attribute evaluates to false, the element is ignored. This can be used for dynamic selection of elements based on variable data. For example, a query definition may contain multiple <select> elements. A particular select element can be chosen at runtime based on the value of a property.

    <select select-if="${query_type}=quick">
        <![CDATA[

        ]]>
    </select>
    <select>
        <![CDATA[

        ]]>
    </select>

If at runtime the property ${query_type} contains the value “quick”, the first select element gets executed, else the second element will be processed.

The property element also supports the select-if attribute:

<finally>
    <property name="my_property"
              value="${dialog_title}"
              select-if="${@isset:myflag}"/>
</finally>

The property ${my_property} gets initialized only if ${myflag} is set.

Dialogs

The OctoSAM Inventory query engine supports a variety of input controls to pass input to a query.

<dialog>
    <input name="package"
           type="dropdownlist"
           title="Package">
        <select>
            <![CDATA[ SELECT Name , ID FROM SoftwarePackage WHERE MeteringEnabled = 1 ORDER BY [Name] ASC
            ]]>
        </select>
    </input>
    <input name="timespan"
           type="timespan"
           timespan="all"
           title="Usage within"
           minmaxdatesql="SELECT MIN(UsageDate), MAX(UsageDate) FROM UsageDetail"
           conditioncolumn="ud.UsageDate"
           conditionprefix="AND"
           conditionsuffix=""
           errormessage="No suitable usage data in the database" />

    <input type="checkbox" name="unused" title="Include unused installations" />
</dialog>

The input controls fill their values to properties named ${prefix_name} where prefix can be specified as an attribute of the dialog element “dialog” is the default.

Dialog Element

Attribute Required Default Value Description
prefix N 'dialog' Prefix to use to name properties
optional N false The input dialog is optional, query should start with default values and allow optional changes afterwards. At the moment this is implemented for web only

Input Controls

Common Attributes

Attribute Required Default Value Description
name Y Name of the input control
title Y Label text to display
type Y Type of the input control

checkbox

Attribute Required Default Value Description
tooltip N Specify text of a tooltip that is shown when the mouse hovers over the control
value N true or false Initial value

date

Allows to input a date using a calendar control. Note that the initial value of this control is always set to “today”.

Attribute Required Default Value Description
format N .net date format

Select exactly one item from a given list. You can specify the list using

<input name="freespace"
       type="dropdownlist"
       title="Free Space Less than">
    <option title="10 GB" value="10000000000" />
    <option title="5 GB" value="5000000000"/>
    <option title="2 GB" value="2000000000"/>
    <option title="1 GB" value="1000000000"/>
    <option title="500 MB" value="500000000"/>
</input>
<input name="package"
       type="dropdownlist"
       title="Package">
    <select>
        <![CDATA[ SELECT Name , ID FROM SoftwarePackage WHERE MeteringEnabled = 1 ORDER BY [Name] ASC
        ]]>
    </select>
</input>

label

Displays its text attribute in the current position of the input section. Does not collect input.

Attribute Required Default Value Description
align N left align left or right

multiselection

Used to select one or more items from a given list.

Attribute Required Default Value Description
limit N max number of items that can be selected
stockquery N used to load the most commonly used queries predefined in the query engine
<input name="organizations"
       type="multiselection"
       title="Organizations">
    <select select-if="${?sub_select_organizations_count}=users">
        <![CDATA[
        /* display count of users behind organization name */
        SELECT o.NAME +' (' + CAST(COUNT(u.ID) AS VARCHAR) +')', o.ID FROM Organization o INNER JOIN [User] u ON o.ID = u.OrganizationID GROUP BY o.Name,o.ID ORDER BY o.Name,o.ID
        ]]>
    </select>
    <select>
        <![CDATA[
        /* display count of machines behind organization name */
        SELECT o.NAME +' (' + CAST(COUNT(m.ID) AS VARCHAR) +')', o.ID FROM Organization o INNER JOIN Machine m ON o.ID = m.OrganizationID GROUP BY o.Name,o.ID ORDER BY o.Name,o.ID
       ]]>
    </select>
</input>

The example shows how to load a multi selection control with data from the database. Note how two different select statements are defined and the select-if attribute is used to select one of the statements based on the value of ${sub_select_organizations_count}.

numericupdown

Attribute Required Default Value Description
maximum N 100 maximum value
minimum N 0 minimum value

text

Attribute Required Default Value Description
easywildcards N false if true, the control will change * to % and ? to _ for SQL LIKE search
maxlength N max text length
minlength N 0 min text length
likepattern N false this text box accepts a SQL LIKE pattern
regex N .* regex to validate input

timespan

<input name="timespan"
       type="timespan"
       timespan="all"
       title="Logins/Usage within"
       minmaxdatesql="SELECT MIN(muh.Scan), MAX(muh.Scan) FROM MachineUserHistory muh"
       conditioncolumn="muh.Scan"
       conditionprefix=""
       conditionsuffix=""
       errormessage="No suitable usage data in the database">
</input>

The control returns SQL code to include in the where clause of a query in the $

Attribute Required Default Value Description
conditionprefix Y SQL fragment that is inserted before the condition
conditionsuffix N SQL fragment that is inserted after the condition
errormessage N message to display if the minmaxdatesql does not return a suitable timespan
minmaxdatesql N SQL statement that returns the minimum and maximum datetime that can be selected
timespan N all initially selected timespan

Possible Initially Selected Timespans:

  • all
  • custom
  • today
  • last3days
  • last7days
  • last30days
  • last90days
  • currentweek
  • currentmonth
  • currentyear
  • yesterday
  • lastweek
  • lastmonth
  • last3months
  • last6months
  • lastyear

Info

Depending on the available data it may not be possible for the query to select the initially configured timespan.

Debug attributes

Input element attribute names beginning with the string 'debug' are internal to OctoSAM. They mainly indicate some hints to the query unit test driver.