OctoUtil

Introduction

OctoUtil is a command-line tool to execute commands against the OctoSAM Inventory Database.

CommandLine

OctoUtil.exe [options] [command] [arguments]

If no command is specified arguments are given, OctoUtil reads its commands from standard input.

Options

--server servername --database databasename

These two options allow to override the configured database connection. Mainly useful for scripting in a multi-database environment. Only Windows integrated authentication is supported for these connections.

Commands can have various sub options

Commands

delete [machine|user|signature|package|publisher|repository] id
deletes the specified object with the given id. For user objects you can also specify domain/user or domain\user or user principal name, for machine objects you can specify domain/machine or domain\machine. For repository items you can also specify the guid.
delete [machines|users|signatures|packages|publishers] file
reads object ids from the given file (first column of a .csv file) and deletes the specified objects
execute planfile
executes the specified plan file
export repository id file
exports the indicated repository item into the specified file.
export servicenow file
exports OctoSAM information into a compressed .xml file that can be read by a Service Now datasource / transformation
fix relations
checks common potential consistency problems in the software installation database fixes detected inconsistencies.
fix allrelations
checks additional potential consistency problems in the software installation database and fixes them.
Info

The import service also fixes potential consistency problems in the housekeeping phase.

housekeeping options
performs all housekeeping tasks that rely on the database only. housekeeping tasks that connect to active directory or that import external data are not executed.
import codes codefile
import codes into the database. Do not use without consulting Octosoft first. Codes are usually updated with a software catalog update.
import groups groupfile
an alternative to import group definition. Do not use without consulting Octosoft first.
import repository repositoryfile
import zipped queries and reports repository
import catalog catalogfile
import the specified catalog file and perform discovery
query guid file

runs the query identified by its guid and exports the visible fields into a .csv or .xlsx file. This command supports additional options in the form column-:Column1,Column2... to suppress named columns in the output or column+:Column1,Column2... to include hidden columns in the output. Input Parameters referenced in the query can also be supplied in the form parametername=value on the command line. However, it is recommended to construct custom queries that report exactly the expected data. The command can also take the path to a query file (.qry.xml file) to run a query from a local file instead of the OctoSAM Repository.

query 96a2b810-f95c-11e2-b778-0800200c9a66 c:\test\query_output.xlsx \ timespan=90days "organizations=${script.all_organizations}"

In this example the query is called with timespan selection of 90days and all organizatins selected

trigger housekeeping
trigger housekeeping for the next import service cycle
update mapping
updates the organization mapping. This can be useful if you have several integration data loads for organization mapping and need to run a full mapping between the loads. For example, a 2nd load can be dependent od successful or unsuccessful mappings after a 1st load.
update repository id file
updates an existing repository item with the content of the specified file. Use with care for OctoSAM standard repository items as these are typically overwritten with each release. Can be used together with the export repository command to patch a repository item.
update usage
updates package usage from signature usage details
Info

The import service updates usage in the housekeeping phase.

wakeonlan machinename|machineid
sends wakeonlan network packages to the specified machine. OctoSAM WOL is powerful since it uses the collected networking information of the machine. The WOL packets are sent to all configured interfaces and also broadcast via detected router information.

Plan Files

A plan can contain multiple tasks that are executed in order. A task can either contain a simple script or it can contain a script that is executed for each row of a table. Tables can be initialized from a comma separated csv file or from an Excel file (.xlsx).

The plan processor uses the same template engine as the query language.

<?xml version="1.0" encoding="utf-8" ?>
<plan xmlns="http://www.octosoft.ch/schema/octoutil/2020/plan.xsd">

  <!--     
        Loads edited Custom4 Field from native Excel File exported from Query "All Machines"        
  -->

    <property name="infile" value="c:\OctoSAMExports\machines.xlsx" />

    <task name="PurgeOldInfo">
        <!-- clean existing values -->
        <script language="sql">
            UPDATE Machine SET CustomField4 = ''
        </script>
    </task>

    <task name="ImportMachineCustom4">    
        <!-- load excel file and load Custom4 values into Machine table -->        
        <table name="in" file="${infile}" headers="true" trim="true" encoding="utf-8" />
        <foreach table="in" info="import ${in.MachineID} = ${in.Custom4} - ${in.Machine}">
            <script language="sql">
                <![CDATA[
            UPDATE Machine SET CustomField4 = '${@sqlstr|${in.Custom4}}' WHERE ID = ${in.MachineID}     
            ]]>
            </script>            
        </foreach>        
    </task>
</plan>

This example plan imports Machine.CustomField4 from an .xlsx file generated by exporting the 'All Machines' query.

Info

Scripts that only reference data within the OctoSAM database can also be run as housekeeping scripts from the import service directly. You can also schedule the execution of a plan from a housekeeping script.

Elements

table Element

<table name="in" file="c:/files/machines.xlsx" headers="true" trim="true" />
<table name="in" >
     <![CDATA[
        select a b c from mytable;
    ]]
</table>

The <table> element loads a table of data from either a .csv, a Microsoft Excel .xlsx file or from the database.

Attribute Required Default Value Description
encoding N Default Specify encoding of the file. Supported are: default, ascii, utf8, utf-8, windows-1250, windows-1252, unicode
file N Path to the file, if no file path is given, the table element expects an SQL select statement in its inner text, other settings are ignored
headers N true if true, the first row contains the names of the columns
name Y Name of the table. Prefix for properties that refer to cells within the table
delimiter N ,(comma) delimiter to use
skiplines N 0 Number of lines to skip before processing input data. Comment lines starting with # are automatically skipped
trim N true if true, leading and trailing blanks of values are trimmed

foreach Element

<foreach table="in" info="import ${in.MachineID} = ${in.Custom4} - ${in.Machine}">

The <foreach> element iterates over a table and executes the enclosed script for each row. Within the body of the foreach element, a row of the data is loaded into properties prefixed with the table name. If the table did not contain a header row, the columns will be named 'column1' to 'column'

Attribute Required Default Value Description
table Y name of the table
info N a message to display per row

script Element

<script language="sql"">
Attribute Required Default Value Description
expectedrowcount N if set and not met by the sql statement, a log message is produced
language N sql can either be sql or octoutil for scripts containing OctoUtil commands.
name Y sql must be 'sql'
info N a message to be displayed or logged per script invocation

The element contains the SQL or OctoUtil script as inner text, usually within a CDATA section.

Reference Table Data

For each column, properties are generated with the table name and either the column name from the .csv header or generic names column1 - columnX. The generic names are always available, column headers that generate illegal property names are transformed with urlencode and exchanging the urlencode % (perecent) with _ (underscore).