Hi @Danish11052000,

You are on the right track with the COALESCE approach. The reason for the inconsistency is that different Unity Catalog action types populate different keys in request_params. Here is a breakdown of the key fields and which actions use them, plus a refined query.

WHICH REQUEST_PARAMS KEYS HOLD THE TABLE NAME

1. full_name_arg: This is the most common key for Unity Catalog operations like getTable, updateTable, deleteTable, and getTableSummaries. It contains the full three-part name (catalog.schema.table). The Databricks documentation for the system.access.audit table schema specifically shows full_name_arg as the example key.

2. table_full_name: Used by some DML/write-path events and internal operations.

3. Separate fields (catalog_name, schema_name, table_name): Used by certain granular operations like createTable where the parts are passed individually.

4. name: Some events use a generic "name" key that may hold the full three-part name.

Note: One small correction to your self-answer, the documented key name is full_name_arg (not full_arg_name). Worth double-checking in your environment, but the official docs reference full_name_arg.

RECOMMENDED QUERY

Here is a refined version of the COALESCE approach with filtering for Unity Catalog table events and the aggregation to build your target output:

SELECT
  a.workspace_id,
  w.workspace_name,
  DATE_TRUNC('month', a.event_date) AS month,
  COALESCE(
      a.request_params['full_name_arg'],
      a.request_params['table_full_name'],
      CASE
          WHEN a.request_params['catalog_name'] IS NOT NULL
              AND a.request_params['schema_name'] IS NOT NULL
              AND a.request_params['table_name'] IS NOT NULL
          THEN CONCAT(
              a.request_params['catalog_name'], '.',
              a.request_params['schema_name'], '.',
              a.request_params['table_name']
          )
      END,
      a.request_params['name']
  ) AS full_table_name,
  COUNT(DISTINCT a.event_date) AS refresh_count
FROM system.access.audit a
LEFT JOIN system.information_schema.catalog_tags ct
  ON 1=0  -- placeholder, see workspace join below
LEFT JOIN system.access.workspaces_latest w
  ON a.workspace_id = w.workspace_id
WHERE a.service_name = 'unityCatalog'
  AND a.action_name IN (
      'getTable', 'createTable', 'deleteTable', 'updateTable',
      'getTableSummaries', 'listTables'
  )
  AND a.event_date >= DATE_TRUNC('month', CURRENT_DATE()) - INTERVAL 6 MONTHS
  AND a.response.status_code = 200
GROUP BY 1, 2, 3, 4
HAVING full_table_name IS NOT NULL
  AND full_table_name LIKE '%.%.%'
ORDER BY 1, 3, 4

A few things this query does:

1. Filters to service_name = 'unityCatalog' so you only get UC table events, not notebook or cluster events that also have request_params.
2. Filters to successful responses (status_code = 200) so you are not counting failed lookups.
3. Uses a CASE expression for the CONCAT fallback so it only fires when all three parts are present, avoiding partial names like "catalog_name.null.null".
4. The HAVING clause with the LIKE '%.%.%' filter ensures you only keep valid three-part names.
5. Aggregates by month with COUNT(DISTINCT event_date) to get your refresh_count metric.

DISCOVERING WHICH KEYS ARE AVAILABLE

If you want to see exactly which request_params keys contain table-like values in your environment, this exploration query is useful:

SELECT
  action_name,
  MAP_KEYS(request_params) AS param_keys,
  request_params
FROM system.access.audit
WHERE service_name = 'unityCatalog'
  AND action_name IN ('getTable', 'createTable', 'updateTable', 'deleteTable')
  AND event_date >= CURRENT_DATE() - INTERVAL 7 DAYS
LIMIT 20

This will show you the actual keys present for each action type in your workspace, so you can verify the COALESCE priority order.

DOCUMENTATION REFERENCE

The system.access.audit table schema is documented here:
https://docs.databricks.com/en/admin/system-tables/audit-logs.html

The audit log event reference (which lists service names and action names):
https://docs.databricks.com/en/admin/account-settings/audit-logs.html

* This reply used an agent system I built to research and draft this response based on the wide set of documentation I have available and previous memory. I personally review the draft for any obvious issues and for monitoring system reliability and update it when I detect any drift, but there is still a small chance that something is inaccurate, especially if you are experimenting with brand new features.

If this answer resolves your question, could you mark it as "Accept as Solution"? That helps other users quickly find the correct fix.