OctoSAM housekeeping scripts¶

The import service provides several hooks to implement custom SQL and/or OctoUtil scripts. Such scripts can help integrate with other systems or to transform custom data within the database.

Support notice

The script and query engines in OctoSAM Inventory and the query definition format may change with future versions of OctoSAM Inventory. We try to keep the engines backward compatible with older versions, but that may not always be possible. Be prepared to adjust your site-specific scripts when updating to a newer versions of OctoSAM.

Administering scripts¶

The scripts can be modified using the OctoSAM GUI, menu: Setup - Database - Housekeeping - Scripts

<housekeeping>
  <!-- Site-specific housekeeping scripts, do not change without consulting Octosoft support first. -->
  <scripts>
    <script language="SQL" name="MyScript" run="before">
        .... SQL Commands ....
    </script>
    <script language="SQL" name="MyOtherScript" run="after">
         .... SQL Commands ....
    </script>
    <script language="OctoUtil" name="MyExportScript" run="export">
         .... OctoUtil Commands ....
    </script>

  </scripts>
</housekeeping>

Elements¶

script¶

<script name="myscript" language="SQL" run="before" />

The <script> element defines a script

Attribute	Required	Default Value	Description
language	Y		either `SQL` or `OctoUtil`
run	Y		When to run the script - see table below
name	Y		Name of the script. Use the name to identify the script in log and error messages
updatehints	N	`false`	If set to `true` causes OctoSAM to update the hints fields before the script is run. Set this to true if your SQL references any hint fields.
days	N		Specify comma-separated list of days when the script can run one or more out of "Monday,Tuesday,Wednesday,Thursday,Friday,Saturday,Sunday"
scalar	N	`false`	if true, the script returns a numeric value as ReturnValue.
conditional	N	`false`	if true, the script runs only if the script before is successful and returns a 0 ReturnValue. This allows for example to run OctoUtil commands only if the prior SQL script returned 0.

The run attribute defines when the script will be executed.

Run	Description
before	Run this script at the very beginning of a housekeeping cycle - run at the beginning of full and partial housekeeping cycles.
after	Run this script at the end of housekeeping but before Organization mapping - full and partial housekeeping cycles.
export	Run this script at the very end of a full housekeeping cycle. This is usually the best option for scripts that export data and should run once per day.
beforeImport	Run this script at the beginning of an import batch.
afterImport	Run this script at the end of an import batch. This is usually the best option for scripts that transform data that is used for organization mapping.

Multiple scripts with the same run attribute are executed in the order they are defined in the file.

SQL scripts¶

Can be used to execute arbitrary Transact SQL commands. Use extreme care not to destroy data. Typical use is to transform site-specific data within the database, often in the context of organization mapping or software installation approval.

OctoUtil script¶

Allows to execute OctoUtil commands - especially the execute and the query commands in sync with the import service. This guarantees that the database is in a consistent state when the commands are executed.

Script user context

Note that the housekeeping scripts execute in the context of the service account, which might have different permissions from your interactive user.

Consider the OctoUtil run command as an alternative to the Windows Task Scheduler

The octoutil run command can be used to start external programs from housekeeping scripts. This can be used as an alternative to scheduled tasks with the added benefit that actions get logged to the OctoSAM import service log.

Conditional scripts¶

The parameters scalar and conditional allow to run a script dependant on the result of a prior script.

  <!-- define scalar true and return a scalar numeric value from the query -->
  <script language="SQL" scalar="true" when="before">
      select count(*) from Machine
  </script>
  <!-- the next script depends on the return value of the query, if the return value is 0 then the OctoUtil script will be executed -->
  <script language="OctoUtil" conditional="true" when="before">
      mail message octoadmin@acme.com "There are no Machine records in the database"
  </script>

Delete scripts¶

Due to the complex relations of OctoSAM database entities, it is not advised to delete any records directly in the database using SQL. For Machine, User, SoftwareSignature entities, you can make use of the background delete mechanism. To schedule a background delete, set the entities ObjectStateID to "DELETE". The delete operation will be performed by the import service through its database access layer. Deletes are performed when there are no other updates happening. This helps to prevent locks in the database.

Delete operations are expensive not only because of database engine mechanics but also because of the high number of relations to other entities. Delete time also depends on the set history retention times in Housekeeping settings.

declare @deletestate = (select ID from ObjectState where OctopusID = 'DELETE' );

update Machine 
   set ObjectStateId = @deletestate 
where ( not ObjectStateId = @deletestate ) 
  and <your selection criteria here>

Make sure that you do not update records that are already in 'DELETE' state, as that would give a false rowcount in the logs.

ID	OctopusID	Description
null	null	No special ObjectState is set.
1	OK	The entity is in its normal state.
2	DELETE	The entity is scheduled for background delete.
3	DELETING	The entity is currently being deleted.

Before background delete begins deleting an entity that is in DELETE state, the state is set to DELETING. If the delete operation gets interrupted for any reason, the object will be the first selected after restart of the background delete.

Testing Scripts¶

You can use the OctoUtil scripts command to execute defined scripts.