Integrating an OData feed as Customer Source for OTRS

We have been running OTRS as our Ticket/CMDB system for many years now. Until now we have used Database backend with our on-prem MSSQL server with a direct lookup to our on-prem Microsoft Dynamics NAV Server (ERP) Database. After a few upgrade attempts of our on-prem OTRS we decided to go with the OTRS managed service plan and move our Ticket system to the cloud. Although it is theoretically possible to use MSSQL for Customer Company backend, we decided to switch to Webservice calls for Customer Company data.

Our first plan was to trigger a REST call to OTRS whenever a Customer was added or updated in Dynamics NAV. That means we would have to utilize the OnUpdate/OnInsert triggers in the C/Side code of Dynamics NAV. Talking to our NAV provider we got an estimate of coding the needed lines.

Later a new idea emerged, what about using Azure Logic Apps? Getting data out from Dynamics NAV is easy, both SOAP and OData is provided. We published a query containing the fields we needed in Dynamics NAV Customer Table (18) as an OData feed.

Running this OData query in Postman gives us this result

Now that we have the source data, we need to prepare the destination Web services in OTRS. We chose to use REST and configured our Yaml as follows

---
Debugger:
  DebugThreshold: debug
  TestMode: '0'
Description: ''
FrameworkVersion: 7.0.15
Provider:
  Operation:
    CustomerCompany:
      Description: ''
      GroupIDs:
      - '2'
      - '1'
      MappingInbound: {}
      MappingOutbound: {}
      Modules:
        Kernel::System::CustomerCompany: '1'
      Type: Generic::RPC
  Transport:
    Config:
      AdditionalHeaders: ~
      KeepAlive: ''
      MaxLength: '100000000'
      RouteOperationMapping:
        CustomerCompany:
          RequestMethod:
          - GET
          - PATCH
          - POST
          Route: /CustomerCompany
    Type: HTTP::REST
RemoteSystem: ''
Requester:
  Transport:
    Type: ''

With this Web service deployed, we can call the service by using the following url (for managed OTRS): https://COMPANYNAME.managed-otrs.com/otrs/nph-genericinterface.pl/Webservice/CustomerHandler2/CustomerCompany

Please be sure that you have enabled RPC/GenericInterface and the proper module.

And the following REST body will update the customer with new information, please see OTRS API reference for information about parameters, OTRS will not give an error if any missing, so please include all as documented https://doc.otrs.com/doc/api/otrs/6.0/Perl/Kernel/System/CustomerCompany.pm.html

{
  "UserLogin": "Replace with your username",
  "Password": "Replace with your password",
  "Method": "CustomerCompanyUpdate",
  "Object": "Kernel::System::CustomerCompany",
  "Parameter": {
    "CustomerCompanyCity": "Los Angeles",
    "CustomerCompanyName": "Your Security Company Inc",
    "CustomerCompanyStreet": "Bluewater Lane 23",
    "CustomerCompanyZIP": "N3323",
    "CustomerID": "10161",
    "UserID": "1",
    "ValidID": "1"
  }
}

We can also do Customer Add the same way

{
    "UserLogin": "Replace with your username",
    "Password": "Replace with your password",
    "Object": "Kernel::System::CustomerCompany",
    "Method": "CustomerCompanyAdd",
    "Parameter" : {
    	"CustomerID" : "3001",
    	"CustomerCompanyName" : "TestCustomer 3001",
    	"CustomerCompanyStreet" : "Gateadresse 1",
    	"CustomerCompanyZIP": "32000",
    	"CustomerCompanyCity": "Sandefjord",
    	"CustomerCompanyCountry": "Norway",
    	"UserID" : "1",
    	"ValidID" : "1"
    }
}

Now that we have all needed functions, we can go a head and create a Logic App in Azure to tie it all together.

First we create an Recurrance trigger which will ensure we can run when needed. We also need to initialize an variable to use with our OData query as $filter, which ensures we only get the last 24hrs of updated customer data.

Next we fetch all customer data from the last 24hrs (yes I know, more than we need…. but in normal circumstances it should not be to much data anyways…). We parse the JSON result so that we can use it in our loop.

We loop through each customer, first we check if the customer already exists in OTRS, then we decide if we should update or create the Customer Company in OTRS.

Decision time, do we update or do we create ? It is based on the length of the Result from the Get Single Customer query above. We Compose a JSON body for Update or Add for use with our REST query to OTRS

The Update REST Body, it will be similar for Add, just with CustomerCompanyAdd Method instead.

When done with Logic App setup you are ready to have your customer data synced to OTRS. The same project could be used with any OData feed based Customer source. Suggestions and comments are welcome 🙂