OctoUtil Reference

OctoUtil is a command-line tool that executes commands against the OctoSAM Inventory Database. It can be used from scripts to automate parts of OctoSAM. OctoUtil commands can also be issued from OctoSAM housekeeping scripts.

These commands are for expert OctoSAM admins. Usually, they will be set up by OctoSAM support.

Note

OctoUtil help may show additional commands that are not documented here. These commands are for OctoSAM support only.

CommandLine

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

If no command is specified, 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. If these options are not used, OctoUtil will connect to the database via appsettings.json OctoSAM connection string.

Commands

dbinit

dbinit

initializes the database after creation or schema update. The command inserts required data, such as lookup tables or RBAC privileges.

Info

The import service also initializes the database on startup if the values are not already present. dbinit can be used for example after you have upgraded the schema of a backup copy of an OctoSAM database.

dbreindex

dbreindex [for-read-only]

rebuilds / optimizes the database indexes and updates database statisics. You can specify for-read-only to maximize the optimization for a read-only scenario.

Warning

Your database administrator has most likely already set up database maintenance procedures.

Your database login may not have all privileges required to perform index rebuilds and statistics updates.

Do not run dbreindex without consulting with your DBA first.

delete

delete [machine|user|signature|package|publisher|repository] id

deletes the specified object with the given id or name. For user objects, you can specify the id, guid, domain/user, or domain\user or user principal name, for machine objects, you can indicate the id, guid, domain/machine, or domain\machine. Specification of the domain is optional as long as the database contains only one record with the specified name. If multiple records would be selected, the command prints a warning and does not delete anything. For repository items, you can also specify the guid.

delete [machines|users|signatures|packages|publishers] file

reads object ids, guids or names from the given file (first column of a .csv file) and deletes the specified objects. For delete machines and delete users you can also specify wildcards in the filename. This can be useful for automated decomissioning scenarios - in this case, the partner system is assumed to generate unique filenames. When a wildcard is in the filename, processed files will be copied to an Archive subdirectory.

delete marked

deletes objects that are marked for deletion.

Info

The import service also periodically checks for marked objects and deletes them in the background.

execute

execute planfile

executes the specified plan file

export

export repository id file

exports the indicated repository item into the specified file.

export servicenow [full|incremental] zipfile

exports OctoSAM information into a compressed .xml file that can be read by a ServiceNow datasource / transformation. A full export selects all records, while an incremental export selects all records changed since the last full export.

export roles jsonfile

export the RBAC roles and privileges configuration in a .json file.

fix

Info

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

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.

fix os

fixes Windows OS names

fix printablenames

fixes PrintableName fields in the Machine, User and SoftwareItem tables.

housekeeping

housekeeping options_list

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. This command is intended to run the typical housekeeping tasks on offline copies of OctoSAM databases. Defined housekeeping scripts are executed and may fail if external resources are not available. Use the skipscripts option to skip scripts and configured file transfers. History tables are reorganized by default. Use the keephistoryoption to skip history reorganization.

The resetoption causes housekeeping to re-calculate all consolidated values that can be deferred from base data. The default is to recalculate these values only when needed.

The reindexoption can be used to perform database re-indexing. Additional database privileges may be required. skipcatalog option disables catalog updates.

If you have an offline backup copy of a database and need to migrate to a newer version of the data model, it is recommended to run:

sh octoutil housekeeping reset,keephistory,skipcatalog

skipscripts option may also be specified depending on the nature of defined housekeeping scripts.

Warning

Do not use the housekeeping command in housekeeping scripts.

import

import affinity

run the configured user device affinity imports

import catalog catalogfile

import the specified catalog file and perform discovery

import codes codefile

import codes into the database. Do not use without consulting Octosoft first. Codes are usually updated with a software catalog update. Do not use without consulting Octosoft first.

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 roles jsonfile

import an RBAC definition into OCtoSAM. The import adds or removes configured privileges per role. Roles not in the file will not be removed.

import msgraph

runs the configured msgraph scans and imports data from Entra ID / Microsoft 365

markdelete

markdelete [machine|user] id

marks specified object with the given id or name for later deletion For user objects you can specify the id, guid, domain/user or domain\user or user principal name, for machine objects you can specify the id, guid, domain/machine or domain\machine. Specification of the domain is optional as long as the database contains only one record with the specified name. If multiple records would be selected, the command prints a warning and does not delete anything. For repository items you can also specify the guid. The marked object are later deleted by the Import Service or by issuing

octoutil delete marked

markdelete [machines|users] file

reads object ids, guids or names from the given file (first column of a .csv file) and marks the specified objects for later deletion. For delete machines and delete users you can also specify wildcards in the filename. This can be useful for automated decomissioning scenarios - in this case it is assumed that the partner system generates unique filenames. When there is a wildcard in the filename, processed files will be copied to an Archive subdirectory.

query

query guid file

runs the query identified by its guid and exports the visible fields. The output format is determined by the filename suffix. Supported output formats are .csv, .xlsx, .json and .sql. .csv Export is always comma separated, regardless of windows locale settings.

This command supports additional options in the form option:value.

Suppress specific columns from the output with columns-:Column1,Column2... or include hidden columns with columns+:Column1,Column2.... The special form columns:all exports all columns of the query.

The Option separator:value sets the specified value as column separator. Special forms provided for scripting are separator:comma and separator:semicolon which cover the two most common cases. Note that the .csv standard RFC4180 requires comma (the default setting), you should not change this setting for export to other systems unless the destination system is unable to read the standard. Note that : and = cannot be set as separator.

Option excelsep:true causes octoutil to write a sep= marker at the beginning of the file. This causes Excel to correctly parse the file regardless of regional settings. Make sure that a system importing this understands this Excel specific marker.

Option encoding:enc can be used to change the encoding of the generated .csv file. The default is utf8, supported values are: default, ascii, utf8, utf-8, windows-1250, windows-1252, unicode.

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.

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

In this example the query is called with time span selection of 90days and all organizations selected. Depending on your OS settings this must be escaped from the shell.

OctoUtil query 9e0767b0-1c00-11e3-b773-0800200c9a66 \
         c:\output\query_output.csv columns:all

This example exports all columns of the 'All Machines' Query.

OctoUtil query 9e0767b0-1c00-11e3-b773-0800200c9a66 \
         c:\output\query_output.csv columns:all separator:semicolon

This example exports all columns of the 'All Machines' Query using the semicolon ; as separator.

OctoUtil query 9e0767b0-1c00-11e3-b773-0800200c9a66 \
         c:\output\query_output.csv \
         columns-:Organization,Site columns+:ChassisTypes

This exports the visible columns of the 'All Machines' Query except columns Organization and Site but with hidden Column ChassisTypes added to the export.

run

run [command] [command-arguments]

runs the specified external command

scripts

scripts after|afterimport|before|beforeimport|export|all

runs the configured housekeeping scripts.

Script user context

If OctoUtil does not run in the same user context as the OctoSAM Import Service, this may lead to unexpected results.

sync

sync device42

Starts a Device42 synchronization via the Device42 REST API

test

test device42

Tests if a connection to the configured device42 partner system can be established

test directory

Tests if the configured active directories can be contacted and enumerated

test phonehome

Tests if the connection to the OctoSAM catalog update server can be established

test rbac

Tests if the configured active directory for RBAC can be contacted

test servicenow

Tests if the configured service now instance can be contacted (for 2-way replication)

trigger

trigger housekeeping

trigger housekeeping for the next import service cycle

update

update hints

hints are denormalized fields in the database mostly to speed up user interface enable/disable of features per object. The update hints command updates these fields.

!! note Housekeeping scripts can also force an update of the hints fields by specifying the updatehints option.

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 2^nd load can be dependent on successful or unsuccessful mappings after a 1^st 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.

update statistics

write a new statistics record

wakeonlan

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, an an Excel file (.xlsx) or from SQL.

The plan processor uses the OctoSAM template engine.

<?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

<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
stalewarndays	N	3	if input file is older a stale file warning gets logged - set to 0 to disable stale file check
staleerrordays	N	10	if input file is older a stale file error gets logged - set to 0 to disable stale file check
trim	N	true	if true, leading and trailing blanks of values are trimmed

foreach

<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

Info

A task can contain multiple <foreach> elements but they cannot be nested.

script

<script language="sql" name="MyScript">

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	N		optional name for logging
info	N		a message to be displayed or logged per script invocation
select-if	N		can be used to conditionally select scripts based on property values
verbose	N	false	If true enables verbose logging during script execution

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, SQL statement 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 % (percent) with _ (underscore).

In addition to the column values, the following properties are available:

Property	Contents
tablename.currentrow	number of the current row in the named table starting with 1
tablename.totalrows	number of rows in the named table

Examples

Reorganizing machines

In some cases it is desirable to to remove obsolete machines from the database based on information that is available in the database such as the LastScan timestamp. The following plan shows an idea on how to achieve this. Of course the criteria to remove machines is heavily site-specific.

Info

Best practice is to remove machines on external criteria, for example as part of your decommissioning workflow. Removing machines based on inventory data alone may hide problems with your infrastructure (machines not getting scanned because of configuration problems, network issues etc.) or your system management flows (decommissioning workflow not standardized, OctoSAM inventory not integrated in the workflow, etc. ).

The example shows how to issue octoutil commands to remove machines based on a query. Also note the variable replacement in the info attribute for documentation and logging.

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

    <task name="DeleteVMwareInstantCloneMachines">
        <table name="in">
                <![CDATA[

select top 100
       ID
     , Name
     , OperatingSystem
     , Model
     , LastScan
     , LastModification
from dbo.Machine
where LastScan < dateadd (day, -180, getdate ())
      and OperatingSystemFamily = 'WIN'
      and OperatingSystemServer = 0
      and Description like '%instant%'
order by LastScan asc;

    ]]>
        </table>
        <foreach table="in" info="${in.currentrow}/${in.totalrows}: delete machine ${in.ID} ${in.Name} - ${in.LastScan} ( ${in.Model} )">
            <script language="octoutil">
                    delete machine ${in.ID}
            </script>
        </foreach>
    </task>
</plan>