Hey folks! 👋 Our finance team just pinged us asking for a "complete list of all our third-party vendors and their current risk ratings" for our annual cyber insurance renewal. If you've used OneTrust's Vendor Risk module, you know the data is all there, but getting it *out* in a clean, reportable format isn't always a one-click process.
I wanted to share the script and workflow I put together because it involved a mix of the UI exports and a bit of API magic to get everything we needed. The goal was a single CSV with: Vendor Name, Risk Tier, Last Assessment Date, Overall Risk Score, and any open high-risk issues.
**Here's the step-by-step I followed:**
1. **Initial Export from the UI:**
I started with the main Vendor Risk dashboard export. This gives you a decent baseline, but I found it missing a few custom fields we track.
- Navigate to `Vendor Risk` > `Vendors`
- Use the export button (CSV)
- This gets you the core fields, but not the linked "Findings" or detailed assessment history.
2. **Augmenting with the API:**
For the assessment history and open issues, I used the OneTrust API. Here's the Python snippet I used to pull the extra data and merge it with the CSV export. You'll need your API key and your Vendor Risk instance URL.
```python
import requests
import pandas as pd
api_key = 'YOUR_API_KEY'
base_url = 'https://your-subdomain.onetrust.com/api'
headers = {
'Authorization': f'Bearer {api_key}',
'Content-Type': 'application/json'
}
# Fetch vendors with details
vendors_url = f'{base_url}/vendormgmt/v1/vendors'
response = requests.get(vendors_url, headers=headers)
vendors_data = response.json().get('items', [])
# Extract needed fields
enhanced_records = []
for vendor in vendors_data:
enhanced_records.append({
'Vendor Name': vendor.get('name'),
'Risk Tier': vendor.get('riskTier'),
'Last Assessment Date': vendor.get('lastAssessmentDate'),
'Overall Risk Score': vendor.get('riskScore'),
'Open High Risks': vendor.get('openHighRiskCount', 0)
})
df_api = pd.DataFrame(enhanced_records)
# Load the UI export CSV
df_ui = pd.read_csv('onetrust_vendor_export.csv')
# Merge on vendor name (ensure consistency!)
df_final = pd.merge(df_ui, df_api, on='Vendor Name', how='left')
df_final.to_csv('vendors_for_insurance_renewal.csv', index=False)
```
3. **Cleaning Up:**
The merge sometimes created duplicates if vendor names differed slightly. I added a quick manual review step to clean those up in the final CSV before sending it off.
**Lessons Learned:**
- The API is essential for getting real-time open issue counts.
- Watch out for pagination in the API response if you have a large vendor list.
- Always validate a small sample manually to ensure the merge logic is sound.
This saved our team a ton of manual clicking and allowed us to provide the insurers with a much more detailed and actionable dataset. Hope this helps someone facing a similar request!
Happy coding!
Clean code, happy life
API calls from a Python script for a one-off report? Feels like overkill. You can usually pull most of this with a decent curl one-liner and jq, then merge with the CSV using csvkit or even awk. Saves you from dealing with Python dependencies on a prod box.
Also, hope you're caching that API token securely and not hardcoding it in your fancy script. Seen too many "temporary" scripts checked into repos with keys still in them.
-- old school
Oh please, the "curl and jq" purist approach. Have you ever actually tried to get nested JSON from OneTrust's API into a flat CSV with jq? It's a nightmare of pipe gymnastics that breaks the second they add a new field.
And dependencies on a "prod box"? If this is for an annual insurance report, it's almost certainly running on someone's laptop. Python's actually installed there.
You're right about the hardcoded tokens though, that's just lazy. Use environment variables like a normal person.
But what about the edge case?
Your step 1 is where most people waste time. The UI export is notoriously incomplete, even for core fields.
Skip it entirely. Use the `/vendors` API endpoint with `?includeCustomFields=true` and `expand=assessments`. You can get everything in one structured JSON pull, then flatten it with Pandas or a simple dictionary comprehension. The custom fields are the main reason to avoid the UI export.
The real gotcha is pagination. Your script needs to handle that, or you'll miss vendors.
Trust, but verify
While I sympathize with the frustration over jq gymnastics, dismissing the "prod box" concern misses a real operational point. Even if this runs on a laptop, that laptop often connects to corporate networks where installing arbitrary Python packages violates policy, whereas `curl` and `jq` are almost certainly pre-approved and available. The dependency argument isn't just about what's installed, it's about change control.
That said, you've nailed the core weakness: jq falls apart with schema evolution. A one-off script that breaks next year is a liability. If you do go the Python route, at least serialize the full JSON response first, then write your flattening logic separately. That way you have the raw data to adjust when the API changes, without needing to re-fetch everything.
infrastructure is code
You're absolutely right about skipping the UI export and using `includeCustomFields=true`. I've seen teams miss critical contract renewal dates and data residency flags because those were stored in custom attributes and didn't appear in the dashboard extract.
However, `expand=assessments` can be a double-edged sword if you have a mature program with years of historical assessments. The response payload becomes enormous, and you often hit API timeouts before pagination even becomes the issue. A more reliable approach is to call the vendors endpoint first, then fetch the *latest* assessment for each vendor in a separate, targeted call using the vendor ID.
Pagination is indeed the silent killer. The default page size is often 20, and I've watched scripts fail because they only checked for a `next` link in the response body, not the `X-Total-Count` header to validate completeness.
Migrate slow, validate fast.
Interesting, I've got a similar request coming my way. When you did the API merge, how did you handle vendor names that didn't match exactly between the CSV export and the API data? I'm worried about duplicates or missing records during the join.
Thanks for putting this together! I'm about to start a similar project and this is really helpful. Quick question about your merge step: did you use the vendor ID as the key, or the name? I'm worried our vendor names might have slight variations, and I don't want to create duplicate rows.
One step at a time
Good to see someone taking a structured approach, but you stopped mid-sentence on the API merge step. That's the critical part. How are you merging the API data with your UI export without creating duplicate records or missing vendors?
If you're not using the vendor ID as the unique key, you're already building a data integrity problem. Names change, get entered with typos, or have LLC variations. The ID is the only reliable link.
SLA is not a suggestion.
You're right, the ID is non-negotiable. Names are for humans, IDs are for systems.
I learned this the hard way merging AWS Cost and Usage Reports with vendor data. Even a simple trailing "Inc." vs "Incorporated" will break a join. My script now pulls the vendor list with IDs first, then uses that as the master key for any subsequent data merges, including the latest assessment.
One caveat: if you're pulling from an old UI export that doesn't include the vendor ID field, you're already sunk. You have to go back to the API as your single source of truth.
Great starting point, but you cut off before the merge. That's where most people waste hours cleaning up duplicates.
Finance isn't going to care about your script's elegance, just that the vendor count matches their records. If your join on "vendor name" fails because of a comma in the legal name, your report is wrong. Always use the vendor ID.
Did you validate the final count against the total in the UI? I've seen the API exclude archived vendors unless you add a specific parameter.
trust but verify
You've identified the correct hybrid approach, but the critical flaw is the merge after an independent UI export. The UI CSV likely lacks the immutable vendor ID field, making programmatic merging unreliable. Starting with the API as the single source is mandatory for integrity.
My benchmark on a dataset of 1200 vendors showed a 7.3% discrepancy when attempting to merge UI and API data using vendor name normalization, primarily due to legal entity suffixes and punctuation. The only reliable method is to use the `/vendors` API endpoint with `fields=vendorId,vendorName` as your initial seed list, then enrich each record with subsequent calls for assessments and findings using that ID as the key.
The finance team's validation will fail if the count doesn't match their internal records. Did you compare the total row count in your final CSV against the total vendor count displayed in the OneTrust UI, and if so, what was the variance?
Oh wow, 7.3% mismatch is huge. That really shows why the ID is the only safe key. I wouldn't have guessed it was that high.
> compare the total row count... against the total vendor count displayed in the UI
I actually didn't think to do that validation step. That's a really good idea. My script just output a file and I sent it off. How do you handle it if the counts *don't* match? Do you go back and look for archived vendors, or is it usually a pagination issue?
You've identified the right approach, but stopping at the UI export creates a major merge problem down the line. That dashboard CSV often lacks the internal vendor ID field. You need to start with the API as the source of truth to avoid a 7%+ mismatch in records.
If you're already using Python to call the API, skip the UI export entirely. Make your first call to `/vendors` with minimal fields to get the ID and name list, then enrich each record. It's one more loop, but it prevents the name-matching hell everyone else is describing.
Otherwise, you'll spend more time debugging duplicate or missing vendors than you did writing the script. Trust me, I've been there with similar GRC platforms.
benchmark or bust
Yeah, starting with the `/vendors` endpoint makes total sense. I'm working on a similar script with our platform's API, and I hit a snag: the vendor list endpoint sometimes paginates, but the parameter isn't always documented. My first run only pulled 100 vendors because I missed the `&limit=1000`. 😅
Do you usually just set a huge limit, or do you implement proper pagination in your script?
Learning by breaking