Jira (Self-Hosted) add new fields using DOQL

Sometimes we may need to have more fields mapped from Device42 into Jira, as long as the data is exposed through a DOQL view, we would be able to get it into Jira Server/Data Center by creating a new Configuration Item type

For instance let say we want to have a couple of devices custom fields synced into Jira side.

1. Navigate to Settings > Manage Appsand select “Device42 Configuration” on the sidebar.
2. Select the “Devices” tab and click the pencil icon on the right to modify the DOQL query.
3. Once you have entered a valid query take note of the names of columns you have added and save these settings (example columns: “view_device_v1.name”, “view_device_v1.uuid”).

• Here is an example DOQL query that contains custom device fields from a D42 Instance (Application Notes, Application Support, Business Contact Person, IT Contact Person etc.). Naturally if a device does not contain a value for a value in D42, the field is not displayed, but this query has been modified using a coalesce so that empty fields are still displayed and have the value of an empty string.

SELECT
view_device_v1.device_pk,
view_device_v1.name,
view_device_v1.last_changed,
view_device_v1.asset_no,
view_device_v1.serial_no,
view_device_v1.uuid,
view_device_v1.service_level,
view_device_v1.type,
view_device_v1.tags,
view_customer_v1.name,
view_hardware_v1.hardware_pk,
view_hardware_v1.name,
view_virtual_host.name,
view_rack_v1.rack_pk,
view_rack_v1.name,
view_room_v1.room_pk,
view_room_v1.name,
view_building_v1.building_pk,
view_building_v1.name,
view_ipaddress_v1.ip_address,
coalesce(view_device_custom_fields_flat_v1."Application Notes", '') AS "Application Notes",
coalesce(view_device_custom_fields_flat_v1."Application support", '') AS "Application support",
coalesce(BusinessContactPerson.name, '') AS "Business Contact Person",
coalesce(ITContactPerson.name, '') AS "IT Contact Person",
coalesce(view_device_custom_fields_flat_v1."Criticality", '') AS "Criticality",
coalesce(view_device_custom_fields_flat_v1."Platform support", '') AS "Platform Support"

FROM
view_device_v1
LEFT JOIN view_customer_v1 ON view_device_v1.customer_fk = view_customer_v1.customer_pk
LEFT JOIN view_ipaddress_v1 ON view_ipaddress_v1.device_fk = view_device_v1.device_pk
LEFT JOIN view_hardware_v1 ON view_device_v1.hardware_fk = view_hardware_v1.hardware_pk
LEFT JOIN view_device_v1 view_virtual_host ON view_device_v1.virtual_host_device_fk = view_virtual_host.device_pk
LEFT JOIN view_room_v1 ON view_device_v1.calculated_room_fk = view_room_v1.room_pk
LEFT JOIN view_building_v1 ON view_device_v1.calculated_building_fk = view_building_v1.building_pk
LEFT JOIN view_rack_v1 ON view_device_v1.calculated_rack_fk = view_rack_v1.rack_pk
LEFT JOIN view_device_custom_fields_flat_v1 ON view_device_custom_fields_flat_v1.device_fk=view_device_v1.device_pk
LEFT JOIN view_enduser_v1 AS BusinessContactPerson ON
CASE WHEN  view_device_custom_fields_flat_v1."Business Contact Person" = ''
THEN 0
WHEN view_device_custom_fields_flat_v1."Business Contact Person" IS NULL
THEN 0
ELSE view_device_custom_fields_flat_v1."Business Contact Person"::integer END = BusinessContactPerson.enduser_pk
LEFT JOIN view_enduser_v1 AS ITContactPerson ON
CASE WHEN view_device_custom_fields_flat_v1."IT Contact Person" = ''
THEN 0
WHEN view_device_custom_fields_flat_v1."IT Contact Person" IS NULL
THEN 0
ELSE view_device_custom_fields_flat_v1."IT Contact Person"::integer END = ITContactPerson.enduser_pk
ORDER BY
view_device_v1.device_pk

• Once your new DOQL is saved scroll to the bottom of the page and select the “Add parameter type” button.
• On the parameter type creation form you will need to enter a Parameter Type Key. You will use the added columns names from the DOQL delaminated by underscores (If your column was “name” you will use “view_device_v1_name”).
• The parameter name will be the name of the field that shows up on tagged devices. You may name this whatever you would like (If your column was “name”, it might be best to use “Name”).
• View weight relates to the position of the new field in the tagged device. You may place an item before, after or in between other parameters by supplying a view weight that is either smaller, larger or in between other parameters respectively.
• Lastly, you’ll want the collection index for the new field. The easiest way to retrieve this value is to navigate into D42 and go to Tools > Integrations > Saved DOQL Queries.

• Add and run your DOQL and retrieve the generated CSV file. The collection index is the column number which your custom field shows up in the CSV (Note that the first column starts with index 0).

• Once your parameter has been created you will need to resync data by going into Jira and navigating to “Device42 Connection” on the side bar of the “Manage Apps”
• Once the scan completes navigate to “Device42 Filter Templates” and select the “Devices” tab.
• Here you can see what fields will be displayed for each device when they are tagged to an issue. Select or deselect values that you want displayed or removed. Save this configuration and you should see your new custom fields on attached devices.

Note: If your fields do not show up it is almost always an incorrect index value.