Software Product Docs

User Guide

Overview

The Operaio Excel Add-in for SCSM allows you to export and modify SCSM class instances directly in your Excel worksheet.

You can use this add-in to:

  • Bulk insert, update and remove class instances
  • Use Excel Analyst tools against SCSM class instances

Requirements

  • SCSM 2016 or above
  • Excel 2016 or above
  • .NET Framework 4.5.1
  • SCSM Console installed on the computer

Instead of installing the SCSM Console you may use the SCSM SDK core library installed in GAC

Installation

Execute the setup.exe to install the Operaio Excel Add-in on your computer.

You can remove the Excel Add-in using the Remove Apps & Features menu of your Windows Operating System.

Silent Installation

  1. Start the normal Excel installation as usual
  2. When prompt appeared, click to Operaio GmbH in the Publisher line
  3. Click to Install Certificate in the certificate dialog
  4. Select any location for the certificate and complete saving
  5. Close certificate and installation windows
  6. Open the certificate manager (Local user or machine, depend on where it was saved on step 4)
  7. Find the certificate, then export it as .cer file
  8. Install this certificate to user's Trusted Publishers store for all users who will need this addon (can be done with Group Policy in case of Windows AD domain)
  9. Execute PowerShell script below for each to install add-on:
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.VisualStudio.Tools.Office.Runtime.v10.0")

# must be full path or UNC. Please see notes below
$path = "C:\Users\Administrator\Downloads\Test\Operaio.ExcelSCSMEditor.vsto"
$vsto = [Environment]::ExpandEnvironmentVariables("%CommonProgramFiles(x86)%\microsoft shared\VSTO\10.0\VSTOInstaller.exe")

$rsaKey = "<RSAKeyValue><Modulus>3lRDOzQyyHv8+5HA6gXr++K1h8yrIlA475JoTBAuqnFCU/3Jjp0i6JIzcMvzuCYQFqRjhcmZMBX3MIkVSwyEp4XAXYl0gNCLGS7qEjdJbpJ1caej7PMQC9BXGYBEz4RTXYczh4mrEN3Rz6rDhOJlGUNaaf8XmUCZq8j2I3tOU5G4VDlHgWfvZt7kQJbW+Ff0YeOXsWSFQEy0EX6sYuqxugEMAsXRRoTVYuTSH4Wk4xcf2nqVk7pVn9+DQVqKaVLDi9UmFFKAZXsU8FgWRZYf1NsMEUMmhAx1k6U99YTyEzPkMLrgmew9wOSlNNkQGSP731ipYjpVtJVbkPugaw5DbQ==</Modulus><Exponent>AQAB</Exponent></RSAKeyValue>"

// use this in case of local path
$solutionLocation = new-object Uri("file://$($path.Replace("\", "/"))");
# use this in case of UNC
#$solutionLocation = new-object Uri($path);
$entry = new-object Microsoft.VisualStudio.Tools.Office.Runtime.Security.AddInSecurityEntry($solutionLocation, $rsaKey);
if(![Microsoft.VisualStudio.Tools.Office.Runtime.Security.UserInclusionList]::Contains($entry)){
    [Microsoft.VisualStudio.Tools.Office.Runtime.Security.UserInclusionList]::Add($entry);
}

$p = "/I", $path, "/s"

& $vsto $p

Connect to SCSM

Whenever you want to use the Excel Add-in you must connect it to your SCSM instance first. To do so, open a new excel worksheet and find the SCSM tab.
Next, Press the Connect to SCSM Button, type your SCSM Server name and finally, click Connect

Class instance bulk create

The Excel Add-in allows you to create and import many new class instances in bulk to your SCSM environment.
First, create a new table for the required class as described below:

  1. Click the Select class button in the SCSM ribbon
  2. Next, select the class and the properties to load in the Popup dialog
  3. Finally, click the Create tablebutton.

The Excel Add-in will then create an empty table with the selected properties as columns.
At this point you may start filling in data you want to import later. See Tipps & Tricks section for more details.

When all rows are populated as you please, press the Export to SCSM button in the SCSM ribbon.

A dialog will then notify you about the created class instances. Also, the same dialog will let you know if something is wrong with your data.

Class instance bulk update

To update existing data of your SCSM environment you can import class instances from SCSM, change the data and finally export that modified data back to SCSM.
There are 2 ways to import this data from SCSM:

  1. Follow the steps described in Class instance bulk create, but make sure to check Import all instances checkbox before clicking the Create table button.
  2. Follow the steps described in Class instance bulk create. After these steps are completed, click the Import instances Button in the SCSM ribbon.

Now you can start editing your data in your excel worksheet. As soon as your data is ready you may export it to SCSM by clicking the Export to SCSM button.

Projection instance bulk create (Combination Class)

Projection instances allow you to create class instances including relationships (so called Combination classes in SCSM console). Creating projection instances is very similar to creating class instances:

  1. Expand the dropdown below the Select class button, then click the Select typeprojection button.
  2. Next, select the combination class and the properties to load in the Popup dialog
  3. Finally, click the Create table button.

The Excel Add-in will then create an empty table with the selected properties as columns. Columns with the header starting with Alias: allow you to add objects for the corresponding relationship in the action pane (right side-panel in Excel).

Depending on the relationship configuration you may see a different UI in the action pane:
If a many-to-one or a one-to-one relationship is used then you will get a single instance picker:

In case of many-to-many, or one-to-many relationships you will get a multiple instance picker:

Projection instance bulk update (Combination Class)

To update existing data of your SCSM environment you can import combination class instances from SCSM, change the data and finally export that modified data back to SCSM.
There are 2 ways to import this data from SCSM:

  1. Follow the steps described in Projection instance bulk create (Combination Class), but make sure to check Import all instances checkbox before clicking the Create table button.
  2. Follow the steps described in Projection instance bulk create (Combination Class). After these steps are completed, click the Import instances Button in the SCSM ribbon.

Now you can start editing your data in your excel worksheet. As soon as your data is ready you may export it to SCSM by clicking the Export to SCSM button.

Tipps & Tricks

Reporting

You can use the Excel Add-in to create reports using Excel features like Pivot tables or filters. The Excel Add-in creates a regular Excel table during import, so it can be used to create any kind of reports.

Deal with key properties

Key properties must be filled in. If a key property is auto-incremental then you can use {0} as a placeholder which will be replaced with the auto incremented value. (Usually the placeholder is already added by the Excel Add-in).
For example, to create an auto incremented Id for a new Incident instance, you can use the value IR{0} in the corresponding cell.

Deal with enumerations

To set an enumeration value the Excel Add-in uses the Excel built-in validation UI. You can pick or type the display name of the enumeration.

Note: This may cause issues if you have more than one enumeration with the same display names in the hierarchy. (This is not recommended anyways, even in SCSM console).

Note: Copy pasting data can break the validation UI. When copy pasting data, make sure you do not use Paste values as paste method.

Deal with instance pickers

Columns

By default, the instance picker shows only three columns: Display Name, Time Added and Last Modified. For some classes this might be not enough. In this case you can create a registry value to set the columns of a given class as described below:

Path: HKEY_CURRENT_USER\Software\Operaio\SCSM Excel Add-in\ClassColumns
Key name: class name
Value: list of columns, separated by comma (,)

You can use the following snippet to import records to your registry: 

Windows Registry Editor Version 5.00

[HKEY_CURRENT_USER\Software\Operaio\SCSM Excel Add-in\ClassColumns]
"itnetX.AssetManagement.PurchaseOrder"="PurchaseOrderID,DisplayName,DocumentDate,Status"

Save the snipped as .reg file and import it to the registry.

Note: The class detected by the instance picker is given by the typeprojection's configuration of the given alias. For example, if the typeprojection uses the relationship System.RelatedConfigItem without type constraints, then the instance picker will use the System.ConfigItem class. However, if the alias is configured with a type constraint, then the class specified in this constraint will be used.
Example: <Component Path="$Target/Path[Relationship='itnetX.AssetManagement.ContractIsAssignedToConfigItem' SeedRole='Source' TypeConstraint='itnetX.AssetManagement.HardwareAsset']$" Alias="RelatedHA" >

Remove items from picker

Single picker

To remove a item from a single picker (e.g. Affected User), select the cell you want to clear, then clear the text in the textbox inside the Component picker (right side-panel)

Multi picker

To remove items from a multi picker (e.g. Affected Config Items), select the cell you want to remove items, then select the items to remove in the Component picker (right side-panel), then click the Remove button.

Known limitations

Copy-Pasting values of component pickers not supported

Currently it is not possible to copy-paste any cells that contain relationship objects, such as Affected User. Copy-pasting such a cell will result in an empty target-cell.

Saving and transferring files not supported

You can save the Excel-file with imported instances, but you will not be able to export them back later.

Attachments are not supported

You cannot add attachments using the Excel Add-in

Enumerations with same display name may cause issues

Using enumerations with the same display name in the same hierarchy is not supported in any way.

Exporting to SCSM does not work without key-property column(s)

If you import class instances from SCSM and decide to not import the key properties columns (e.g. ID column of Incident) you will not be able to export this data back to SCSM.

Connection to SCSM is lost when idling

If you do not interact with SCSM trough Excel for a while you will lose connection to SCSM (Timeout, usually 30min.). You will be notified from the Excel Add-in that you have lost connection and need to reconnect.
After reconnecting your excel worksheet can be broken (e.g. enumeration pickers will no longer work). In this case you must re-select your class or typeprojection and then re-create your worksheet.