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. If these 2 options are not used, octoutil will connect to the database via appsettings.json OctoSAM connection string.

Commands can have various sub options

Commands

dbinit

dbinit

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

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 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 the object. 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 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.

delete marked

deletes objects that are marked for deletion.

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.

fix

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

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

   octoutil housekeeping reset,keephistory,skipcatalog

skipscripts option may also be specified depending on the nature of defined 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 msgraph

runs the configured msgraph scans and imports data from Azure-AD / 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 the object. For repository items you can also specify the guid. The marked object are later deleted by the Import Service or by issuing
sh 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. 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.

 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. ${script.all_organizations} is a OctoSAM predefined property. Depending on your OS settings this must be escaped from the shell. Valid ranges for the timespan.

 .\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 example exports the visible columns of the 'All Machines' Query except columns Organization and Site but with hidden Column ChassisTypes added to the export.

rbac

rbac sync

Updates the RBAC database with privileges and settings of the current software version. Useful if you work offline with a backup of a database that was created with an earlier version of OctoSAM.

scripts

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

runs the configured housekeeping scripts.

sync

sync device42

Starts a Device42 synchronization via the Device42 REST API

trigger

trigger housekeeping

trigger housekeeping for the next import service cycle

update

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

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

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
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 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" 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.

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
     , Manufacturer
     , 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.Manufacturer} ${in.Model} )">
            <script language="octoutil">
                    delete machine ${in.ID}
            </script>
        </foreach>
    </task>
</plan>