Device42 Data Extraction Guide (for SIM Template)

Overview

The exported dataset will align directly with the SIM template columns, allowing you to copy and paste the CSV output into the corresponding SIM tab without modification.

Data Sources

The following Device42 DOQL views contain the required data:

Prerequisites

• Device42 credentials with DOQL access

• Access to either the Device42 Web UI or API

• Permission to export data in CSV format

Query

Use the following DOQL query to extract a clean, deduplicated dataset suitable for the SIM template:

WITH software_entries AS (
  SELECT
    d.device_pk,
    d.name AS device_name,
    s.software_pk,
    s.name AS software_name,
    COALESCE(sd2.version, sd1.details->>'version') AS software_version,
    v.name AS software_vendor,
    GREATEST(
      COALESCE(sd2.last_changed, 'epoch'::timestamptz),
      COALESCE(sd1.last_changed, 'epoch'::timestamptz)
    ) AS last_changed
  FROM view_softwareinuse_v1 siu
  JOIN view_device_v1 d ON d.device_pk = siu.device_fk
  JOIN view_software_v1 s ON s.software_pk = siu.software_fk
  LEFT JOIN view_vendor_v1 v ON v.vendor_pk = s.vendor_fk
  LEFT JOIN view_softwaredetails_v2 sd2 ON sd2.softwareinuse_fk = siu.softwareinuse_pk
  LEFT JOIN view_softwaredetails_v1 sd1 ON sd1.softwareinuse_fk = siu.softwareinuse_pk
)
SELECT DISTINCT
  device_name,
  software_name,
  software_version,
  software_vendor
FROM (
  SELECT *,
         ROW_NUMBER() OVER (PARTITION BY device_pk, software_pk ORDER BY last_changed DESC) AS rn
  FROM software_entries
) t
WHERE rn = 1
ORDER BY device_name, software_name;

Features

• Cross-version compatibility: Works across v1 and v2 schema versions.

• Deduplication: Keeps only the most recent record per device_pk + software_pk pair.

• No cross-device merging: Software is reported per device independently.

• Compatible output: Columns match the SIM template field order exactly.

Exporting to CSV

From the Device42 UI

1. Navigate to Reports → DOQL.

2. Paste the query above.

3. Click Run Query.

4. Select Export → CSV.

From the API

curl -k -s -X POST \
  -d 'output_type=csv' \
  --data-urlencode "query=SELECT ... <paste full query here> ..." \
  -u 'user:password' \
  'https://<your-device42-instance>/services/data/v1.0/query/'

Preparing for SIM Import

1. Open the exported CSV.

2. Confirm headers match: device_name, software_name, software_version, software_vendor.

3. Copy all data (excluding header row) into the Software Inventory tab of the SIM Template.

4. Save the updated SIM file before upload to Licenseware.

👉 Learn more here

Troubleshooting

Example Output