Overview

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 Inventroy 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. The easiest path into query development is 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, for example for storing your result grid layout. Unexpected behavior may result if you use the same GUID for more than one query. There are a number of tools available to generate GUIDs.

<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 for web output. Note that weblinks 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 that is issued to the database. Use a CDATA definition to allow characters that have a 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="UsereGuid" 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 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 it takes, because you know all your inputs and can hard-code them within the query.