Skip to main content

Frontier · Prompt library

Purview Audit Log Parsing Prompt

Purview Audit Log Parsing

← Back to Prompt Library

📋 Review the schema first. Before running this prompt, confirm your column names and the Copilot/agent action taxonomy against the Schema Documentation.

🗂️DataPurview audit log 📅Min dataAny export 💻LanguageR or Python 📦OutputCleaned flat CSV 🎯LevelBeginner

Last validated June 2, 2026 against vivainsights R 0.7.2 / Python 0.4.2.

Purpose

Parse and clean raw Microsoft Purview audit log exports into a flat, analysis-ready dataset. This is a data engineering prompt that prepares audit log data for downstream analytics (such as the Agent Usage Analysis prompt).

Audience

Data engineers, people analytics teams, IT administrators preparing data for analysis

When to use

Immediately after exporting raw audit logs from Purview, before running any analytical prompts. Purview exports often contain nested JSON fields, inconsistent event types, and mixed schemas that need to be normalized before meaningful analysis can be performed.

Required inputs

  • Raw Purview audit log export (CSV or JSON format)
  • Expected raw fields include: CreationTime, UserId, Operation, Workload, AuditData (a JSON string containing event details)
  • Optional: a list of Copilot-related operation names to filter for (if known for your tenant)

Assumptions

  • The export is from Microsoft Purview unified audit log
  • AuditData is a JSON string column containing nested event details
  • Event types and field structures may vary across different Workload and Operation values
  • Some records may be malformed or have missing fields
  • The vivainsights R or Python package is available but not required for this task

A cleaned, flat CSV file (or DataFrame) with one row per event and consistently named columns, ready for analysis with any downstream prompt or tool.

Quick prompt (short version)

Use this shorter prompt for a fast first pass that adapts to your data.

Help me parse a raw Microsoft Purview audit log export into a clean flat dataset and data dictionary that are ready for downstream analysis.
Start by loading the actual file and printing row count, column names, data types, date range if available, and sample records with direct identifiers such as UserId, UPNs and email addresses masked so you can inspect the real schema before assuming any mappings.
Do not assume exact Purview or AuditData field names, and ask me for the file path or any known Copilot operation names if the mapping or filter logic is unclear.
Parse AuditData defensively with error handling, count malformed rows, and adapt extracted columns to the structures actually present in the data.
Flatten nested JSON only one level deep, keep deeper structures as JSON strings, standardize final names to snake_case, and avoid exposing raw UserIds in printed output.
If the file is large, process it in chunks, then produce both a full cleaned output and a Copilot-focused output when the filter finds relevant events.
Also generate a data dictionary that records source, type, null rate, and examples for each final column so downstream users can understand the schema.
End by listing any assumptions you made about field mappings, event classification, or anonymization.

Use the detailed prompt below for a reproducible, exact-structure output. Use the quick prompt above for a faster first pass that you refine through follow-up turns.

Prompt

You are a data engineer. Your task is to parse and clean a raw Microsoft Purview audit log export into a flat, analysis-ready dataset. Purview audit logs contain nested JSON fields and mixed event schemas, so this task requires careful exploration, parsing, and normalization.

IMPORTANT: Purview audit log schemas vary by tenant and event type. Explore the data before choosing field names from the AuditData JSON.

LANGUAGE CHOICE
Choose R or Python based on what is already installed in your environment to minimize setup.

PHASE 1: INITIAL LOADING AND INSPECTION
1. Load the raw audit log file. Auto-detect the format:
   - If CSV: load with pandas (Python) or readr (R). Handle encoding issues (try utf-8, then utf-8-sig, then latin-1).
   - If JSON: detect whether it is a JSON array, newline-delimited JSON, or a single object. Load accordingly.
   - If the file exceeds 500MB, process it in chunks; for exploration, sample large datasets but use the full data for final counts.
2. Print column names, data types, row count and 3 sample rows with direct user identifiers masked.
3. Identify the core columns:
   - Timestamp: typically "CreationTime" or "CreationDate"
   - User: typically "UserId" or "UserKey"
   - Operation: typically "Operation"
   - Workload: typically "Workload"
   - AuditData: typically "AuditData", a JSON string with event details.
   Print which columns were identified and which are missing.
4. Check for data quality issues:
   a. How many rows have null/empty values in each core column?
   b. How many rows have non-parseable JSON in AuditData (if present)?
   c. Are there duplicate rows (identical across all columns)? Print a summary of these findings.

PHASE 2: PARSING THE AUDITDATA JSON COLUMN
5. If an AuditData column exists:
   a. Parse the JSON string for each row into a dictionary/object. Wrap parsing in a try-except to handle malformed JSON. Log the count of unparseable rows and skip them.
   b. Inspect the parsed JSON structure for a sample of 50 rows. Print the unique top-level keys found and their frequency. Identify which keys are present in most records vs. rare.
   c. Identify common nested objects (keys whose values are dicts or lists). Print masked examples, recursively redacting direct identifiers such as UserId, UPNs and email addresses.

6. Extract top-level fields from AuditData into new columns. Prioritize these fields (but use whatever is actually present in your data):
   - "Id" or "UniqueId" → event_id
   - "CreationTime" → audit_creation_time (may differ from the outer CreationTime)
   - "Operation" → audit_operation (may be more specific than the outer Operation)
   - "UserId" → audit_user_id
   - "ClientIP" → client_ip
   - "Workload" → audit_workload
   - "ObjectId" → object_id (the resource being accessed)
   - "ItemType" → item_type
   - "AppAccessContext" → extract nested fields like "AADSessionId", "CorrelationId"
   - "CopilotEventData" or similar → extract Copilot-specific details (agent name, plugin name, interaction type, prompt length, etc.) Create a new column for each extracted field.

7. For nested objects, flatten one level deep. For example, if AuditData contains: {"CopilotEventData": {"AgentName": "MyAgent", "InteractionType": "Chat"}} Create columns: copilot_agent_name, copilot_interaction_type. Store deeply nested structures (3+ levels) as JSON strings.

PHASE 3: EVENT TYPE CLASSIFICATION
8. Analyze the distinct values in Operation (and audit_operation if different). Print:
   a. All unique Operation values with their counts.
   b. Group operations into categories:
      - Copilot events: operations containing "Copilot", "AI", "Agent", "GPT", "Assist", "Summarize", "CopilotInteraction" (case-insensitive)
      - User activity events: sign-in, file access, mail read, etc.
      - Admin events: settings changes, policy updates, etc.
      - Other/unknown
   c. Create a new column `event_category` with these classifications.
9. Print the count of events per category.

PHASE 4: FILTERING FOR COPILOT EVENTS
10. Create a filtered dataset containing only Copilot-related events (event_category == "copilot").
11. If the Copilot filter produces zero results:
    a. Print the top 30 most common Operation values so the user can identify Copilot events manually.
    b. Check if any AuditData fields contain Copilot-related values even if the Operation name does not indicate it.
    c. Save the full (unfiltered) cleaned dataset and note that manual filtering is needed.
12. If Copilot events are found, print:
    a. Count of Copilot events.
    b. Unique Copilot operation types.
    c. Date range of Copilot events.
    d. Sample of 5 Copilot event rows with direct user identifiers masked.

PHASE 5: DATA CLEANING AND NORMALIZATION
13. Apply the following cleaning steps to the full dataset (or the Copilot-filtered subset):
    a. Parse timestamps to datetime with timezone handling (usually UTC). Report invalid conversions rather than silently coercing them.
    b. Normalize UserId:
       - Strip whitespace and convert to lowercase.
       - If UPNs (email format), keep as-is for joining. Optionally create a hashed_user_id column for anonymized output.
    c. Remove exact duplicate rows.
    d. Remove rows where both Operation and UserId are null.
    e. Create derived columns:
       - event_date: date extracted from the timestamp (UTC date)
       - event_hour: hour of day (0-23)
       - event_weekday: day of week (Monday=0, Sunday=6)
       - event_week: ISO week start date (Monday)

14. Standardize column names:
    - Use snake_case for all columns.
    - Prefix AuditData-derived columns with their source context (e.g., copilot_agent_name rather than just agent_name).
    - Ensure no column name conflicts between outer columns and AuditData-extracted columns.

PHASE 6: OUTPUT
15. Save the cleaned dataset:
    a. Full cleaned dataset → "purview_audit_cleaned_YYYYMMDD.csv"
    b. Copilot events only → "purview_copilot_events_YYYYMMDD.csv"
    c. Print the final schema: column names, data types, non-null counts for each output file.
    d. Print the row counts for each output.

16. Generate a data dictionary as a separate file ("purview_data_dictionary_YYYYMMDD.md"):
    - For each column in the cleaned output, list:
      - Column name
      - Data type
      - Source (outer field, AuditData top-level, AuditData nested)
      - Description (based on observed values)
      - Example values (2-3 examples)
      - Null rate
    Mask or omit direct user identifiers in examples.

17. Print a final summary:
    a. Total raw records loaded.
    b. Records with parse errors (skipped).
    c. Duplicate records removed.
    d. Final clean record count.
    e. Copilot event count.
    f. Date range.
    g. Unique users.

Adaptation notes

  • Your AuditData fields will differ. The field names listed in step 6 (e.g., CopilotEventData, AppAccessContext) are examples. Review the agent’s field mapping if your tenant uses different names.
  • Filtering criteria: If you know your tenant’s Copilot operation names (e.g., from the Purview audit log documentation or prior inspection), prepend them to the prompt: “In my tenant, Copilot events use Operation values: CopilotInteraction, CopilotQuery.”
  • Large files: For exports exceeding 1GB, add: “Process the file in chunks of 100,000 rows using pandas chunked reading.”
  • Multiple export files: If your audit log is split across multiple files (e.g., one per day), add: “Load all CSV files from the directory and concatenate them before processing.”
  • Anonymization requirements: If your organization requires user anonymization before analysis, strengthen step 13b: “Replace all UserId values with a consistent SHA-256 hash. Do not retain the original identifier.”

Common failure modes

  • Agent fails on malformed JSON in AuditData. Some rows may have truncated or malformed JSON. Verify that the agent’s try-except handling logs and skips these rows without crashing the entire parse.
  • Agent creates inconsistent column names. When extracting from AuditData, column naming can become inconsistent across event types. Verify that the final output has standardized snake_case names.
  • Agent loads the entire file into memory. For very large exports, this may cause out-of-memory errors. Watch for memory warnings and switch to chunked processing if needed.
  • Agent flattens deeply nested JSON, creating hundreds of columns. The prompt limits flattening to one level deep, but some agents may over-expand. Verify the output schema is manageable (ideally <50 columns).
  • Copilot event filtering is too narrow or too broad. If zero events match, the prompt instructs the agent to show all Operation values for manual inspection. If too many match, review the filter criteria and narrow them.
  • Encoding issues. Purview CSV exports may use UTF-8 BOM encoding. If the agent encounters parsing errors on the first column name, instruct it to use utf-8-sig encoding.

Responsible use & data privacy

  • Suppress small groups. Apply the stricter of your organisation's minimum group size and the selected workflow's privacy floor to every breakdown, segment and chart.
  • Never expose individuals. Do not print raw UPNs or email addresses. Hash, truncate or report only aggregates. Person Query PersonId is already de-identified, but Purview UserId is not.
  • Report group patterns. Use these outputs to understand cohorts and trends. Do not evaluate, rank or monitor named employees.
  • Keep data in approved environments. Follow your organisation's policies when choosing an agent and deciding which data it may access. Validate every agent output before sharing.
← Previous: Agent Usage Analysis Next: Copilot Causal Toolkit →
Last updated: Nov 9, 2026 Edit this page on GitHub