locked
SQL Audit - Pulling Action_ID to a name RRS feed

  • Question

  • Fellow SQLers,

    I am on SQL 2016.

    Have been using SQL Audit a lot recently. I am pulling data with sys.fn_get_audit_file.  Noticed that the logical action name was not there. It is on the live view of audit but not from the file. So, the live audit view must be pulling it.

    I have investigated the sys.dm_audit_actions and sys.dm_audit_class_type_map.

    If I wanted to just show a name for the action-id, thus far, I have simply done something like below:

    SELECT    ab.name, fgaf.*
    FROM    sys.fn_get_audit_file ('C:\Program Files\Microsoft SQL Server\MSSQL13.MYSQL2016\MSSQL\AuditLog\myaudit*.sqlaudit',default,default) as fgaf
     Join (select distinct  action_id,name from sys.dm_audit_actions ) ab
     on fgaf.action_id = ab.action_id

    but I want the Class type too. That apparently it tied into sys.dm_audit_class_type_map but I cannot make class_type map tie back to sys.dm_audit_actions consistently.

    Suggestions?

    Thanks.

    MG

    Wednesday, February 12, 2020 5:12 PM

Answers

  • This is the query I use:

    SELECT
    	[EventDateLocal] = DATEADD(hh,DATEDIFF(hh,GETUTCDATE(), GETDATE()),aud.event_time), 
    	aud.server_instance_name,
    	ActionName = CASE WHEN act.action_id IS NULL THEN act2.name ELSE act.[name] END,
    	cm.class_type_desc,
    	aud.database_name,
    	aud.schema_name,
    	aud.object_name,
    	aud.statement,
    	additional_information = CAST(aud.additional_information AS XML),
    	aud.session_server_principal_name,
    	aud.server_principal_name,
    	aud.database_principal_name,
    	aud.target_server_principal_name,
    	aud.target_database_principal_name,
    	aud.file_name,
    	aud.audit_file_offset,
    	aud.sequence_number,
    	aud.succeeded,
    	aud.session_id
    	--,aud.*
    FROM sys.fn_get_audit_file ('{filename}',default,default) aud
    	INNER JOIN sys.dm_audit_class_type_map cm
    		ON cm.class_type = aud.class_type
    	LEFT OUTER JOIN sys.dm_audit_actions act
    		ON act.action_id = aud.action_id
    			AND act.class_desc = cm.securable_class_desc
    	LEFT OUTER JOIN sys.dm_audit_actions act2
    		ON act2.action_id = aud.action_id
    			AND act2.class_desc = cm.class_type_desc

    • Marked as answer by mg101 Thursday, February 13, 2020 4:27 PM
    Thursday, February 13, 2020 2:30 PM

All replies

  • Hi mg101,

    The class_type column  is also shown in the result. According to your query, you get all the columns from sys.fn_get_audit_file. We can get class_type column from this system table. Please refer to sys.fn_get_audit_file (Transact-SQL).

    sys.dm_audit_class_type_map table that maps the class_type field in the audit log to the class_desc field in sys.dm_audit_actions. Please refer to sys.dm_audit_class_type_map (Transact-SQL).

    Best regards,
    Cathy 

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to  MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Thursday, February 13, 2020 9:47 AM
  • Thanks Cathy but that is not quite my question.

    You should be able to take the audit files class_desc and map that to dm_audit_class_type_map. You can.

    But, in theory, you should be able to then map the dm_audit_class_type_map (a) to dm_audit_actions (b) I believe on the a.securable_class_desc to the b.class_desc and also tied the b.action_id to the audit file's action_id. The results do not comeback  - misses rows

    MG

    Thursday, February 13, 2020 12:56 PM
  • This is the query I use:

    SELECT
    	[EventDateLocal] = DATEADD(hh,DATEDIFF(hh,GETUTCDATE(), GETDATE()),aud.event_time), 
    	aud.server_instance_name,
    	ActionName = CASE WHEN act.action_id IS NULL THEN act2.name ELSE act.[name] END,
    	cm.class_type_desc,
    	aud.database_name,
    	aud.schema_name,
    	aud.object_name,
    	aud.statement,
    	additional_information = CAST(aud.additional_information AS XML),
    	aud.session_server_principal_name,
    	aud.server_principal_name,
    	aud.database_principal_name,
    	aud.target_server_principal_name,
    	aud.target_database_principal_name,
    	aud.file_name,
    	aud.audit_file_offset,
    	aud.sequence_number,
    	aud.succeeded,
    	aud.session_id
    	--,aud.*
    FROM sys.fn_get_audit_file ('{filename}',default,default) aud
    	INNER JOIN sys.dm_audit_class_type_map cm
    		ON cm.class_type = aud.class_type
    	LEFT OUTER JOIN sys.dm_audit_actions act
    		ON act.action_id = aud.action_id
    			AND act.class_desc = cm.securable_class_desc
    	LEFT OUTER JOIN sys.dm_audit_actions act2
    		ON act2.action_id = aud.action_id
    			AND act2.class_desc = cm.class_type_desc

    • Marked as answer by mg101 Thursday, February 13, 2020 4:27 PM
    Thursday, February 13, 2020 2:30 PM
  • HI Tom,

    Thanks. that's it!

    You confirmed my suspicion that it has to work two different ways which is why you had to use the Case Statement.

    And using the audit_actions twice with a different ON condition.

    I was expecting a more direct shot in the relationship where it should be bringing back exactly what I wanted without going thru all of that.

    Regardless, you solved it.

    Thanks again.

    MG

    Thursday, February 13, 2020 4:27 PM
  • This is the query I use:

    SELECT [EventDateLocal] = DATEADD(hh,DATEDIFF(hh,GETUTCDATE(), GETDATE()),aud.event_time), ... ActionName = CASE WHEN act.action_id IS NULL THEN act2.name ELSE act.[name] END, ...

    ...

    Hi Tom. Nice query. Two notes to mention on it, one major and one minor:

    1. (minor) For the [ActionName] field, it might be more readable if simplified to be:

      ISNULL(act2.[name], act.[name]) AS [ActionName]

    2. (major) For the [EventDateLocal] field, please don't use that calculation to get "local" time from UTC. It's overly simplistic and doesn't account for DST correctly. It only knows of the current offset, which might not be correct depend on how far back you are looking. If your data only goes back 1 day, then you will be looking at incorrect values two times each year. The farther back the data goes, the large the scope of the incorrect calculation.

      For anyone using SQL Server 2016 or newer, use AT TIME ZONE . For example:

      CONVERT(DATETIMEOFFSET, aud.event_time) AT TIME ZONE 'Eastern Standard Time' AS [EventDateLocal]

      Just replace "Eastern Standard Time" with the appropriate time zone name. And yes, you do need the "CONVERT" to "DATETIMEOFFSET" so that the initial value in that field is interpreted as being in UTC instead of the desired time zone.

    Take care,
    Solomon...

    _____________________________________________________________
    SQL# https://SQLsharp.com/ ( SQLCLR library of over 340 Functions and Procedures )
    Sql Quantum Lift https://SqlQuantumLift.com/ ( company )
    Sql Quantum Leap https://SqlQuantumLeap.com/ ( blog )
    Info sitesCollations     •     Module Signing     •     SQLCLR
    _____________________________________________________________




    • Edited by Solomon Rutzky Tuesday, February 18, 2020 9:15 PM added CONVERT to DATETIMEOFFSET
    Monday, February 17, 2020 6:54 AM