Importing Extended Table Fields from Service Desk

Overview

Starting with the 4.9.1.4 GA Patch it is now possible to include any extended asset fields not already included in usp_owned_resource.

This is done manually by creating an additional AssetTrack view in the ServiceDesk MDB.

The View

There are two examples below of how the view (at_owned_resource_ext) should look. The first is just the bare minimum requirements in order to import. The second goes into more detail about the best way to import extended CI table fields into AssetTrack so they can be published back to those tables.

Basic Structure

At minimum this is what AssetTrack expects from the view:

CREATE VIEW [dbo].[at_owned_resource_ext]
    AS
    SELECT
     ca.own_resource_uuid as owned_resource_uuid
    FROM
    ca_owned_resource as ca
GO

 Any additional joins can be determined by requirements, and the corresponding custom fields created in AssetTrack.

CI Table Joins

 In order to get all of the extended table fields for a given CI class, it is best to coalesce the fields from each extension table into a single extended field that AssetTrack can import into a single custom field, as illustrated below.

CREATE VIEW [dbo].[at_owned_resource_ext]
    AS
    SELECT
     ca.own_resource_uuid as owned_resource_uuid,
     (select coalesce(tel_cirx.carrier, tel_othx.carrier)) 
		as carrier,
     (select coalesce(har_worx.creation_user, har_serx.creation_user)) 
		as creation_user
    FROM
    ca_owned_resource as ca
    left outer join ci_hardware_workstation as har_worx 
		on ca.own_resource_uuid = har_worx.own_resource_uuid
    left outer join ci_hardware_server as har_serx 
		on ca.own_resource_uuid = har_serx.own_resource_uuid
    left outer join ci_telcom_ciruit as tel_cirx 
		on ca.own_resource_uuid = tel_cirx.own_resource_uuid
    left outer join ci_telcom_other as tel_othx 
		on ca.own_resource_uuid = tel_othx.own_resource_uuid
GO

 Obviously this isn't an exhaustive example of all of the ci_hardware and ci_telecom tables, any additional tables can be added following the same pattern and adding them into the coalesce.

Have more questions? Submit a request

Comments

  • Avatar
    Frank Earnhardt

    Thanks for the write-up!
    Does this release include joining 'usp_owned_resource' to 'at_owned_resource' view?

Please sign in to leave a comment.