How to get audit details from sys.fn_get_audit_file, sys.dm_audit_class_type_map and sys.dm_audit_actions ?

Question

How to get audit details from sys.fn_get_audit_file, sys.dm_audit_class_type_map and sys.dm_audit_actions ?

Yasir Anis Janjua 0

Hi,

I want to extract audit details by using following dmvs

SELECT distinct event_time, succeeded, server_principal_name, database_name, object_name, statement,fng.action_id ,da.name,fng.class_type

FROM sys.fn_get_audit_file('C:\Audit*.sqlaudit', DEFAULT, DEFAULT) fng

join sys.dm_audit_class_type_map dc on dc.class_type = fng.class_type

join sys.dm_audit_actions da on da.action_id = fng.action_id and da.class_desc = dc.class_type_desc

order by event_time desc

There is an event whose action_id = 'CR' and class_type = 'SU'

If we execute query, select * from sys.dm_audit_class_type_map where class_type = 'SU' we will get the following result

User's image

then if we check the above class_type_desc in sys.dm_audit_actions this does not exists

SELECT * FROM sys.dm_audit_actions

where

action_id in ('CR')

and class_desc in ('SQL USER')

Instead of SQL USER it has class_des = 'USER'

User's image

due to this data not matching and query does not return results. Either they need to change the data in class_desc column and add another column "Class_type" in sys.dm_audit_actions.

0 comments

1 answer

Your answer

Answer 1

Hi @Yasir Anis Janjua

You found the mismatch. class_type_desc is the audit class name ("SQL USER"), while sys.dm_audit_actions.class_desc uses the securable class ("USER"). The class type map has a third column for exactly this: securable_class_desc, which Microsoft documents as the column to join with class_desc in sys.dm_audit_actions.

Change the join to use dc.securable_class_desc instead of dc.class_type_desc.
Use LEFT JOINs, because securable_class_desc can be NULL for class types that don't map to a securable, and you don't want to lose those events.

SELECT DISTINCT fng.event_time, fng.succeeded, fng.server_principal_name,
       fng.database_name, fng.object_name, fng.statement,
       fng.action_id, da.name AS action_name,
       fng.class_type, dc.class_type_desc
FROM sys.fn_get_audit_file('C:\Audit\*.sqlaudit', DEFAULT, DEFAULT) AS fng
LEFT JOIN sys.dm_audit_class_type_map AS dc
       ON dc.class_type = fng.class_type
LEFT JOIN sys.dm_audit_actions AS da
       ON da.action_id = fng.action_id
      AND da.class_desc = dc.securable_class_desc
ORDER BY fng.event_time DESC;

Run it and check that your CR / SU event now shows the action name.

Does that row come back with the action name filled in?

If this fixed the query, please consider accepting it as the answer. If some events still show a blank action name, let me know and I'll be happy to keep helping.

Reference:

https://learn.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-audit-class-type-map-transact-sql?view=sql-server-ver17