Syncing DB instances from D42 to Freshservice

1. In order to sync database instances from Device42 to Freshworks, we will need to modify the mapping.xml file by creating a new asset mapping task. We will also create a relationships task to create a relationship mapping in FS between the database instance and the database server from D42.

Begin by creating a new asset task inside the mapping.xml document:
<!-- Database instances -->

        <task enable="true" name="D42 Database Instances to Freshservice Database" type="asset" description="Copy Database Instances from Device42 to Freshservice">

          <api>

            <target/>

            <resource

              doql="

                     SELECT

                            r.resource_name AS name,

                            r.identifier AS item_id,

                            'Database' AS asset_type,

                            FORMAT('Resource-%s', r.resource_pk) AS device42_id

                     FROM

                            view_resource_v2 r

                     WHERE

                            r.vendor_resource_type = 'Database Instance'              

                   "

            />

          </api>

          <matching>

            <source-1 device42-id="device42_id" item-id="item_id"/>

          </matching>

          <mapping key="name" doql-suffix=" AND r.last_changed > '%s'">

            <!-- General -->

            <field resource="device42_id" source-type="string" target="device42_id" target-type="string" target-header="General"/>

            <field resource="name" source-type="string" target="name" target-type="string" target-header="General" set-space="true" min-length="1" max-length="248" escape="true"/>

            <!-- Database -->

            <field resource="item_id" source-type="string" target="item_id" target-type="string" target-header="Database" not-null="true" error-skip="true"/>

            <field resource="name" source-type="string" target="item_name" target-type="string" target-header="Database" not-null="true"/>

          </mapping>

        </task>

2. The main functionality of the above snippet lies in the doql variable and its contents. Here we make a query to D42 declaring a name and item_id fields which are necessary for the sync to work correctly. Next we set a static asset_type field to ‘Database’, but this is a very important field to set correctly as Database represents the existing type in Freshservice and the asset type to which our database instance details will be mapped to.
   device42_id is next and is set to form Resource-<db instance primary key> format.
   
   
   
3. Next step is to map the database instance fields. This is done using the <mapping> tag above inside the <task> section. Here the resource arg correlates to the field names we’re trying to map in the DOQL query, the source-type should be string, the target arg should correlate to the Freshservice field name, if the field name is space separated like “Item Name” for instance, it needs to become lower case and underscored in the mapping, so it would become item_name. The target-type should be string and the target-header is wherever you want to place the new fields on the Freshservice form, it correlates to the FS form section.
   
   

In Freshservice, in the Asset Types & Fields section you will find the asset field which will be mapped from Device42. Since the database instance example is of type Database, we locate it under Cloud-> Database. Now we can edit the Database asset type by clicking on the pencil icon all the way on the right.

The “target” directive should be set to the Device42 field to be mapped to Freshservice. The Freshservice convention is to use the display name in snake case, occasionally with the ID of the asset type as a suffix. Example: memory_max_usage, memory_reservation_used.The sync process determines the ID.

This is a screenshot of the currently available fields for Database asset type in Freshservice.

4. Run the synchronization in Freshservice’s Device42 application and we should end up with the database instances from Device42 in Freshservice.
   

The next step is to add the relationship mapping so our database instances are mapped to the actual database servers:
       <task enable="true" name="Create relationship between database instance and database server" type="asset_relationship" description="Create relationship between database instance and database server">

          <api>

            <target/>

            <resource

              doql="

                     SELECT

                            r.resource_name AS db_instance_name,

                            FORMAT('Resource-%s', r.resource_pk) AS db_instance_device42_id,

                            d.name AS db_server_name,    

                            FORMAT('Device-%s', d.device_pk) AS db_server_device42_id

                     FROM

                            view_resource_v2 r

                     JOIN view_appcomp_resources_v2 ar ON

                            r.resource_pk = ar.resource_fk

                     JOIN view_appcomp_v1 a ON

                            ar.appcomp_fk = a.appcomp_pk

                     JOIN view_device_v1 d ON

                            a.device_fk = d.device_pk

                     WHERE

                            r.vendor_resource_type='Database Instance'                     

              "

            />

          </api>

          <matching>

            <source-1 device42-id="db_instance_device42_id"/>

            <source-2 device42-id="db_server_device42_id"/>

          </matching>

          <mapping key="db_instance_name" downstream-relationship="Hosted On" upstream-relationship="Hosts" target-key="db_server_name">

          </mapping>

        </task>

Here again we add a custom DOQL query where we have to establish the db instance id in D42 and the db server id as well as their names. We then add the <matching> tag with db_instance_device42_id and db_server_device42_id for source-1 and source-2 respectively. Next we add the <mapping> tag where we map the key of db_instance_name to taget_key of db_server_name and the upstream and downstream relationships. The relationship types list can be found in Freshservice by going to Asset management -> Relationship Types link:

5. We can now run the sync process again and should see a relationship between the database instance and database server established:
   
   
6. Lastly there is a delete asset task and delete asset relationship task that will also need to be updated. For the above case we don’t need to update the Delete assets from Freshservice that do not exist in Device42 task as database instances are mapped as Resource-%s, r.resource_pk which already has a task to be deleted. However, we need to add the proper DOQL to the Delete asset relationships from Freshservice that do not exist in Device42 task. This can simply be done by copying the DOQL we have for the creation of the relationship and using the SQL UNION operator:

<task enable="true" type="asset_relationship" description="Delete asset relationships from Freshservice that do not exist in Device42">

            <api>
<resource>...

… existing code …
union

select 

r.resource_name as dependent_device_name,

format('Resource-%s', r.resource_pk) as dependent_device_device42_id,

'Hosted On' as downstream_relationship,

d.name as dependency_device_name,

format('Device-%s', d.device_pk) as dependency_device_device42_id,

'Hosts' as upstream_relationship

from view_resource_v2 r

 join view_appcomp_resources_v2 ar on r.resource_pk = ar.resource_fk

 join view_appcomp_v1 a on ar.appcomp_fk = a.appcomp_pk

 join view_device_v1 d on a.device_fk = d.device_pk

where r.vendor_resource_type = 'Database Instance'

In the above delete task, it is vital to add specific names as aliases;
1. dependent_device_device42_id is the alias for the primary key of the dependent in the relationship, in this case the database instance.

2. dependency_device_device42_id is the alias for the primary key of the dependency parent of the relationship, in the above example the database server.