OIC - Automated Report Retrieval Using Twilio SendGrid, Microsoft Graph API, and OCI Functions

Working... will add api details and related info.

Use Case

In many enterprise integrations, reports are not directly exposed via APIs. Instead, systems like Twilio SendGrid generate reports and send secure download links via email. These links are time-bound, redirected, and protected, making manual download inefficient and error-prone.

This use case addresses the need to automatically retrieve a report sent via email, extract the required download link, resolve security redirects, download the file, transform it, and finally deliver it to a target system such as OCI Object Storage or any downstream consumer.

Solution Overview

The solution orchestrates multiple services—Twilio SendGrid, Microsoft Graph API, OCI Functions, and Oracle Integration Cloud (OIC)—to fully automate report extraction and delivery without human intervention.

Solution Steps

Step 1: Trigger SendGrid Report Generation

Invoke the Twilio SendGrid API by passing the required start date and end date.

SendGrid generates the report and sends an email containing the report download information to a configured mailbox.

https://api.sendgrid.com/v3/messages/download?query=(last_event time BETWEEN TIMESTAMP "2025-11-07T16:00:00.0002" AND TIMESTAMP "2025-11-11T23:59:59.9992")

Step 2: Fetch Email Using Microsoft Graph API

Use Microsoft Graph API to read messages from the mailbox.

Filter emails based on:

Sender email address

Subject or timestamp (optional but recommended)

This ensures only the relevant report email is processed.

https://graph.microsoft.com/v1.0/users/{emailUser}/messages

Step 3: Extract Download Link from Email Body

Parse the email body retrieved from Graph API and extract the secure report link embedded in the message content.

Step 4: Resolve Secure Redirect via OCI Function

Invoke an OCI Function, passing the extracted link.

The function handles:

Redirect resolution

Security headers

URL decoding

It returns the final redirected URL required for further processing.

Step 5: Extract UUID from Redirected URL

From the resolved URL, extract the UUID (or unique report identifier).

This UUID is mandatory for subsequent SendGrid API calls.

Step 6: Fetch Final Report Download URL

Call the SendGrid API again using the extracted UUID to retrieve the final report download URL

/messages/download/{download_uuid}

Step 7: Download Report Using No-Security REST Connection

Use a No Security REST Adapter to download the report file directly using the final URL.

This step handles binary file content securely within OIC.

Step 8: Transform Report Data

Apply required transformations based on target system needs:

File format conversion

Data filtering or enrichment

Renaming or metadata adjustments

Step 9: Deliver File to Target System

Send the transformed file to the target system, such as:

OCI Object Storage

SFTP server

Another REST endpoint

From here, the target application can consume the report seamlessly.

Key Benefits

✅ Fully automated, zero manual intervention

✅ Secure handling of email-based report delivery

✅ Scalable and reusable architecture

✅ Ideal for scheduled or event-driven integrations