Database Activity and Web Schema

Database Schema → Database Activity and Web Schema

Web Sessions

These tables are specific for web-browsing activity only.

Table Name	Comments
browsers	Browser-name (includes version). "Edge", "Chrome", etc.
cities	Cities lookup. Names only.
countries	Every country any user has browsed from. Names only.
devices	Devices lookup. PC, mobile, etc. Names only.
hits	Every hit tracked, linked to a parent session. It has various lookup foreign keys.
page_titles	Page-title lookups.
provinces	Provinces lookup. Names only.
operating_systems	Operating system name lookup. Names only.
sessions	A single session that will have 1+ hit. Contains a session ID
searches	A search done by a user. Linked to a search-term and session.
search_terms	Search term lookup table.
page_likes	Which users have liked which pages
page_comments	Comments left on pages by whom. Comments have an optional parent ID if they are replies to another comment.

SharePoint Files

Table Name	Comments
file_metadata_property_values	List properties for files seen in SharePoint. Populated by user web-traffic only to avoid needing application SharePoint site read permissions. Also includes the taxonomy tag ID if the property is of that type. Values longer than 1000 chars are ignored.
file_field_definitions	List of property names used in pages. Field names that are > 100 chars in name length or that start "vti_x005f" (system fields) are ignored.
hits_clicked_elements	Clicks made during a hit – URL, CSS class-names of the link, link title is included. The related hit is part of a session.
hits_clicked_element_titles	Clicked link titles lookup table.
hits_clicked_element_class_names	Clicked link CSS names lookup table.
page_likes	Who liked which URL, and when. If users "unlike" something a "deleted" value is set.
page_comments	Who commented on what page (URL) and when, with sentiment and language detection results if enabled in solution (otherwise null values for sentiment and language). If users delete a comment a "deleted" value is set.

As noted above, only links that don’t suppress the click event are tracked.

Microsoft 365 Activity Tables

There are two types of imports: daily and weekly aggregate.

The daily activity tables will have 1 new record per day, per active user (or Yammer group in the example of Yammer group activity). Each day the system requests statistics per identity for each day (24 hour period) from "today" back UsageReportsDaysBack days (25 by default, and at most 28, because Microsoft Graph keeps about 28 days of these reports), and updates the SQL tables for data received during that period. See Importer tuning.

Weekly aggregate tables refresh when the aggregate statistics are refreshed on the Sunday for the previous week.

Usually there’s up to a 2-3 day delay in activity to it being seen in the import.

Table Name	Comments
sharepoint_user_activity_log	Per user, per day: User activity within SharePoint; online or via client applications (OneDrive etc). Per user: files shared, synced, and viewed/edited. More information: https://learn.microsoft.com/en-us/microsoft-365/admin/activity-reports/sharepoint-activity-ww?view=o365-worldwide
sharepoint_sites_file_stats_log	Per site, per week: Site usage statistics. Total vs active file counts, space used, etc. Statistics table is added to every Sunday for the previous week. More information: https://learn.microsoft.com/en-us/microsoft-365/admin/activity-reports/sharepoint-site-usage-ww?view=o365-worldwide
onedrive_user_activity_log	Per user, per day: User activity within OneDrive; online or via client applications (OneDrive etc). Per user: files shared, synced, and viewed/edited. More information: https://learn.microsoft.com/en-us/microsoft-365/admin/activity-reports/onedrive-for-business-activity-ww?view=o365-worldwide
onedrive_usage_activity_log	Per user, per day: User usage data for users. Active files storage used, etc - https://learn.microsoft.com/en-us/microsoft-365/admin/activity-reports/onedrive-for-business-usage-ww?view=o365-worldwide
outlook_user_activity_log	Per user, per day: User activity within Outlook; online or via client applications (OneDrive etc). Per user: number of emails sent & received; meetings joined & attended. https://learn.microsoft.com/en-us/microsoft-365/admin/activity-reports/email-activity-ww?view=o365-worldwide
yammer_device_activity_log	Per user, per day: Whether a user used: web, Windows Phone, Android, iPhone, iPad, or other devices to access Yammer. https://learn.microsoft.com/en-us/microsoft-365/admin/activity-reports/yammer-device-usage-report-ww?view=o365-worldwide
yammer_group_activity_log	Per group: posts, reads, likes, and members. https://learn.microsoft.com/en-us/microsoft-365/admin/activity-reports/yammer-groups-activity-report-ww?view=o365-worldwide
yammer_user_activity_log	Per user, per day: posts, reads, likes. https://learn.microsoft.com/en-us/microsoft-365/admin/activity-reports/yammer-activity-report-ww?view=o365-worldwide
platform_user_activity_log	Per user, per day: which apps are used on which platforms (e.g. "excel_windows"). Use of app on any platform is also included (e.g. "excel"). https://learn.microsoft.com/en-us/microsoft-365/admin/activity-reports/microsoft365-apps-usage-ww?view=o365-worldwide

Audit Activity Tables

These tables are used exclusively for activity imports. The main tables are covered here – not all.

The population of these tables depends on which Office 365 Management Activity API feeds the importer subscribes to. The installer configures them from the imports you switch on:

• Audit.SharePoint, for SharePoint and OneDrive audit (ActivityLog).
• Audit.General, for Copilot interactions and Power Platform.
• DLP.All, for the optional DLP import (ImportDlp, off by default).

Microsoft publishes Exchange and Entra ID audit records on the Audit.Exchange and Audit.AzureActiveDirectory feeds, which the installer never subscribes to, so with its configuration nothing new is added to event_meta_exchange or event_meta_azure_ad.

All the workload specific tables join back to the common table for common event-data.

Table Name	Comments
audit_events	Common events table to all workloads. Contains user, date-time, etc. operation_id is a nullable FK to event_operations.id. Its supporting index IX_operation_id and FK FK_audit_events_event_operations are restored by the Audit Events FK Upgrade (Stable 1604) for any database that was upgraded through the legacy Audit Log Migration.sql path; databases built from the original Create DB.sql can have the same index and FK as IX_FK_events_event_operations and FK_events_event_operations, which work the same. See Audit Events FK Upgrade before upgrading a large database from a build before 1604.
event_meta_sharepoint	SharePoint specific event data. Foreign-keys to file-extensions, file-names, related SharePoint Web, item-type, and URL.
event_meta_exchange	Exchange specific event data. Only extra data is "object ID".
event_meta_general	General auditing data. Only extra data is "workload".
event_meta_azure_ad	Azure AD auditing events. No extra data.
ignored_audit_events	Events that’ve been ignored (weren’t relevant to import)
processed_audit_events	Legacy — created on a fresh install but not written or read by the current importer. De-duplication is driven from audit_events and ignored_audit_events instead.
event_file_ext	File extension lookup table for audit events. Since Stable 1856 (PR #768): migration 202610221500002_EventFileExtensionUnicode makes extension_name nvarchar(250) instead of varchar(250), so extensions in any script are stored exactly; see Upgrade to Build 1856.
event_file_names	File name lookup table for audit events.
event_operations	Operation name lookup table – FilePreviewed, FileCheckedOut, New-Mailbox, etc.
event_types	SharePoint event item-type lookup table (file, page, web, folder, etc).

Note: the workloads that are imported are defined in "ContentTypesListAsString" configuration key of the activity importer.

Retired tables (RetireUnusedAuditYammerStreamTables)

Four unused feature areas — eight tables in all — were retired by the RetireUnusedAuditYammerStreamTables migration. Nothing in the product read them back, so no report, lookup page or export loses data.

Tables	Why they went
audit_event_prop_names, audit_event_prop_vals, audit_event_exchange_props, audit_event_azure_ad_props	Stored the Activity API's ExtendedProperties bag for Exchange and Entra ID events. Nothing ever read them, and they cost two lookup round-trips plus a row per property per event on the importer's hottest path.
yammer_messages, yammer_msg_to_stream	A legacy Viva Engage (Yammer) message import that no longer exists in the codebase — nothing has written to them for years.
stream_videos, event_meta_stream	Backed Microsoft Stream (Classic) audit events. Stream Classic has been retired by Microsoft. Stream records that still arrive are now imported as generic events (audit_events + event_meta_general, which keeps the raw record JSON), so no audit coverage is lost.

Your historic data is not deleted. Each group is dropped only if every table in that group is empty — i.e. on fresh installs and tenants that never accumulated the data. If any table in a group holds a single row, that whole group is left completely untouched and the migration logs that it was retained. The three groups are evaluated independently, so a tenant with Exchange extended properties still gets the Yammer and Stream tables cleaned up. stream_videos is also referenced by yammer_msg_to_stream, so if Yammer messages are kept, event_meta_stream is still dropped but stream_videos is retained.

Admins who want the storage back can drop any retained table by hand after upgrading. The migration is re-runnable and is a no-op once a group has gone.

This does not affect the Viva Engage (Yammer) usage reports. yammer_user_activity_log, yammer_group_activity_log, yammer_device_activity_log and the yammer_groups lookup are a different import and are unchanged.

Views. Reporting views are only rewritten when the tables they read actually go: events_view_azure_ad and events_view_exchange lose their extended_properties_count column (every other column is unchanged), and events_view_stream is dropped outright. A customer who keeps their data keeps their views working.

Upgrade time: effectively instant. The emptiness test is an EXISTS (not COUNT(*)), and the drop path only ever runs against empty tables — no index build or table rewrite is involved, so no maintenance window is needed.

---

Return to Database Schema · All topics in this guide

Next: Database Collaboration Schema