1. Creation of record from Powershell by calling Script for execution

    Article: AN0002476Updated: 19.09.2022

    In this example we will show how to create a record of a server in ObjectGears CMDB from Powershell. There are several possibilities how to get data to ObjectGears. If we need an immediate record creation from a Powershell script, online communication by means of web services, that that have various methods for working with data or execution of Script, which is stored in ObjectGears, will do the best job. Execution of a script provides the broadest possibilities and we will use it mainly in situations when we need to do something special, e.g. something that web services do not allow by themselves.

    PowerShell script

    In the first part of the script we are assigning values to the variables that we want to transfer to CMDB. Actually, we will be getting these data according to your need. After that we will insert these data into a body of a web request that we will then send with credentials of the user that is executing this Powershell script.

    [string]$ServerName = "DB09"
    [string]$ServerFQDN = "DB09.qa.int"
    [string]$ServerStatus = "Created"
    [string]$ServerContact = "qa\lee.cooper,qa\jan.smith"
    [string]$ServerManualUpdate = "T"
    [string]$ServerDescription = "Database server"
    [string]$ServerCPU = "2"
    [string]$ServerRAM = "8"
    [string]$ServerCluster = "DB-Cluster-1"
    [string]$ServerRunningOn = ""
    [string]$ServerIP = ""
    [string]$OS = "W2019 Std"
    [string]$ServerWU = "3 - Production"
    [string]$ServerMachineType = "Server"
    [string]$ServerDNS = "DB09.qa.int"
    [string]$ServerEnvironment = "Production"

    $body = @{
    "ServerName" = "$ServerName"
    "ServerFQDN" = "$ServerFQDN"
    "ServerStatus" = "$ServerStatus"
    "ServerContact" = "$ServerContact"
    "ServerManualUpdate" = "$ServerManualUpdate"
    "ServerDescription" = "$ServerDescription"
    "ServerCPU" = "$ServerCPU"
    "ServerRAM" = "$ServerRAM"
    "ServerCluster" = "$ServerCluster"
    "ServerRunningOn" = "$ServerRunningOn"
    "ServerIP" = "$ServerIP"
    "OS" = "$OS"
    "ServerWU" = "$ServerWU"
    "ServerMachineType" = "$ServerMachineType"
    "DNS" = "$ServerDNS"
    "ServerEnvironment" = "$ServerEnvironment"

    Invoke-WebRequest -Uri "http://localhost:85/RunScript.aspx?code=import-new-server"-UseDefaultCredential -Method POST -Body $body

    ObjectGears Script for execution

    On the ObjectGears side we have to have a corresponding Script for execution with authorization settings for the user that is sending the web request (i.e. user that is executing above PowerShell script).

    In the first part of the script we are assigning values from the web request body into variables. Then we create the record and after that we update it with values of Contacts that are in a multiple classlink referring to a user. There are auxiliary functions at the end of the script.

    /* codes */
    var clImport = 'machine';
    var clStatus = 'machine-status';
    var clStatusCol = 'name';
    var clVirtual = 'machine';
    var clVirtualCol = 'name';
    var clRunning = 'device';
    var clRunningCol = 'serial-number';
    var clOs = 'os';
    var clOsCol = 'name';
    var clMachine = 'machine-type';
    var clMachineCol = 'name';
    var clPatch = 'patch-category';
    var clPatchCol = 'name';
    var clEnvironment = 'environment';
    var clEnvironmentCol = 'code';

    /* create record */
    var cl = OGModel.ClassDefs[clImport];
    var dr = OG.DataRow.CreateNew(cl.Id);

    OG.Log.WriteVariableInfo('name', OGParameters['$f_ServerName']);

    /* fill in with data */
    dr['name'] = OGParameters['$f_ServerName'];
    dr['fqdn'] = OGParameters['$f_ServerFQDN'];
    dr['status'] = GetClassLinkValue('$f_ServerStatus', clStatus, clStatusCol);
    dr['server_contacts_x'] = OGParameters['$f_ServerContact'];
    dr['manual_update'] = ConvertToBool(OGParameters['$f_ServerManualUpdate']);
    dr['description'] = OGParameters['$f_ServerDescription'];
    dr['vcpu'] = OGParameters['$f_ServerCPU'] * 1;
    dr['vram'] = OGParameters['$f_ServerRAM'] * 1;
    dr['virtual-on'] = GetClassLinkValue('$f_ServerCluster', clVirtual, clVirtualCol);
    dr['running-on'] = GetClassLinkValue('$f_ServerRunningOn', clRunning, clRunningCol);
    dr['ip'] = OGParameters['$f_ServerIP'];
    dr['os'] = GetClassLinkValue('$f_OS', clOs, clOsCol);
    dr['machine-type'] = GetClassLinkValue('$f_ServerMachineType', clMachine, clMachineCol);
    dr['patch-category'] = GetClassLinkValue('$f_ServerWU', clPatch, clPatchCol);
    dr['dns-name'] = OGParameters['$f_DNS'];
    dr['environment'] = GetClassLinkValue('$f_ServerEnvironment', clEnvironment, clEnvironmentCol);


    /* add contacts */
    var contact = dr['server_contacts_x'];
    OG.Log.WriteVariableInfo('contact', contact);
    if (!OG.IsNull(contact))
      var colContacts = cl.Columns['machine_contacts'];

      //delete current references to person
      var sql = OG.TextUtils.Format('delete from DataRow{0}LinkPerson where ColumnClassDefId = {1} and ReferentionId = {2}',
      colContacts.ParentId, colContacts.Id, dr.Id);

      //parse contacts
      var contacts = contact.split(',');

      for( var i = 0; i < contacts.length; i++)
        OG.Log.WriteVariableInfo('contact[' + i + ']', contacts[i]);
        //insert new contacts
        var sql = OG.TextUtils.Format('insert into DataRow{0}LinkPerson(ColumnClassDefId, ReferentionId, LinkReferentionId)        select {1}, {2}, Id from Person where Code = @personCode and Deleted is null',
        colContacts.ParentId, colContacts.Id, dr.Id);
        OG.Log.WriteVariableInfo('sql', sql);

        var pars = OG.Sql.CreateParameterList();
        pars.AddNvarchar('personCode', contacts[i]);

        OG.Sql.RunSql(null, sql, pars);

    function GetClassLinkValue(parameterName, clName, colName)
      var value = OGParameters[parameterName];
      if (!OG.IsNull(value))
        var dr1 = OG.DataRow.GetDataRowByCode(null, OGModel.ClassDefs[clName], value, colName);
        if ( dr1 != null)
          return dr1.Id;
      return null;

    function ConvertToBool(value)
      if (value == 'T') return true;
      else if (value == 'F') return false;
      return null;

    The created record then looks like this.