Exporting Complete UDSO Details from Apex Central Database

Apex Central's built-in export for User Defined Suspicious Objects (UDSO) does not include some columns visible on the console, such as "Affected Endpoints/Recipients" count, "Source added by", and "Last modified". To obtain this full information, you need to query the Apex Central SQL database directly.

Step 1: Identify Relevant Tables

The primary tables involved are typically:

• tb_UserDefinedSuspiciousObjects — stores UDSO entries.
• tb_FileHashDetectionLog — logs detections related to file hashes.
• tb_EntityInfo — contains endpoint entity details.
• CDSM_UserLog — logs user activities including additions and modifications.

Note: Table names might vary depending on your Apex Central version. Use the following query to confirm:

SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME LIKE '%Suspicious%' OR TABLE_NAME LIKE '%UDSO%';

For more information about managing the Apex Central database and understanding its structure, see the Administering the Database documentation.

Step 2: Query for Affected Endpoints Count

This query counts distinct endpoints affected by each UDSO entry:

SELECT 
    udso.content AS SuspiciousObject,
    udso.type AS ObjectType,
    udso.scan_action AS ScanAction,
    COUNT(DISTINCT ei.EI_EntityID) AS AffectedEndpointsCount
FROM tb_UserDefinedSuspiciousObjects udso
LEFT JOIN tb_FileHashDetectionLog detection_logs ON 
    (udso.type = 'file_sha1' AND detection_logs.file_hash = udso.content)
LEFT JOIN tb_EntityInfo ei ON detection_logs.entityid = ei.EI_EntityID
WHERE udso.content IS NOT NULL
GROUP BY udso.content, udso.type, udso.scan_action
ORDER BY AffectedEndpointsCount DESC;

Step 3: Query for Source Added By and Last Modified Information

Retrieve who added the UDSO, when, and last modification dates:

SELECT 
    udso.content AS SuspiciousObject,
    udso.type AS ObjectType,
    udso.scan_action AS ScanAction,
    udso.notes AS Notes,
    udso.created_date AS DateAdded,
    udso.modified_date AS LastModified,
    NULL AS SourceAddedBy
FROM tb_UserDefinedSuspiciousObjects udso
WHERE udso.content IS NOT NULL
ORDER BY udso.modified_date DESC;

Step 4: Combine All Information in One Query

For a comprehensive view including affected endpoints count, source added by, and modification dates:

WITH UDSODetails AS (
    SELECT 
        udso.content AS SuspiciousObject,
        udso.type AS ObjectType,
        udso.scan_action AS ScanAction,
        udso.notes AS Notes,
        udso.created_date AS DateAdded,
        udso.modified_date AS LastModified,
        udso.expiration_utc_date AS ExpirationDate
    FROM tb_UserDefinedSuspiciousObjects udso
),
AffectedEndpoints AS (
    SELECT 
        udso.content AS ObjectContent,
        COUNT(DISTINCT ei.EI_EntityID) AS EndpointCount
    FROM tb_UserDefinedSuspiciousObjects udso
    LEFT JOIN tb_FileHashDetectionLog detection ON 
        udso.type = 'file_sha1' AND detection.file_hash = udso.content
    LEFT JOIN tb_EntityInfo ei ON detection.entityid = ei.EI_EntityID
    GROUP BY udso.content
)
SELECT 
    ud.SuspiciousObject,
    ud.ObjectType,
    ud.ScanAction,
    ud.Notes,
    COALESCE(ae.EndpointCount, 0) AS AffectedEndpointsCount,
    NULL AS SourceAddedBy,
    ud.DateAdded,
    ud.LastModified,
    ud.ExpirationDate
FROM UDSODetails ud
LEFT JOIN AffectedEndpoints ae ON ud.SuspiciousObject = ae.ObjectContent
ORDER BY ud.LastModified DESC;

Step 5: Exporting the Data

1. Connect to the Apex Central SQL database using SQL Server Management Studio (SSMS) or your preferred SQL client.
2. Run the combined query above.
3. Export the query results to CSV or Excel format using the client’s export functionality.

For guidance on querying logs and using filters within Apex Central, refer to the Log Queries documentation.

Important Notes

• Backup your database before running queries.
• Ensure you have read permissions on the Apex Central database.
• Query performance may vary depending on data volume; consider running during off-peak hours.
• Adjust table names if your environment differs.

By following these steps, you can retrieve the full UDSO details, including affected endpoints count and modification timestamps, which are not included in the default Apex Central export.

For additional details on managing suspicious object lists and synchronizing them with managed products, see the Suspicious Object List Management guide.