XML first contact

With xmlnamespaces (default 'http://schemas.microsoft.com/sqlserver/2008/sqlaudit_data')

SELECT top 5 CONVERT (datetime,SWITCHOFFSET (CONVERT (datetimeoffset, event_time), DATENAME (TzOffset, SYSDATETIMEOFFSET () AS 'date and time when the audit was triggered'

-- b.connect_time

Sequence_number AS 'order of records in a single audit record'

ID' for action_id AS 'operation

Succeeded AS 'whether the action that triggered the event was successful'

Permission_bitmask AS 'permission mask'

Is_column_permission AS 'whether it is a column level permission'

A.session_id AS 'ID' of the session in which the event occurred

Server_principal_id AS 'login context ID' to perform the operation

Database_principal_id AS 'database user context ID' to perform the operation

Target_server_principal_id AS 'server principal performing GRANT/DENY/REVOKE operation'

Target_database_principal_id AS 'database principal performing GRANT/DENY/REVOKE operations'

Object_id AS'ID (server object, DB, database object, schema object) of the entity on which the audit occurred

Class_type AS 'type of auditable entity'

Session_server_principal_name AS 'server principal of the session'

Server_principal_name AS 'current login'

Server_principal_sid AS 'current login name SID'

Database_principal_name AS 'current user'

Target_server_principal_name AS 'target login for the operation'

Target_server_principal_sid AS 'SID' of the target login

Target_database_principal_name AS 'target user for operation'

Server_instance_name AS 'name of the audited server instance'

Database_name AS 'database context in which this operation occurred'

Schema_name AS'Architectural context for this operation'

Object_name AS 'name of the entity being audited'

Statement AS 'TSQL statement (if present)'

Additional_information AS 'unique information for a single event, returned as XML'

File_name AS 'record the path and name of the audit log file from the source'

Audit_file_offset AS 'buffer offset in the file containing audit records'

User_defined_event_id AS 'user-defined event ID' passed as a sp_audit_write parameter

User_defined_information AS 'records any additional information that the user wants to record in the audit log by using the sp_audit_write stored procedure'--

-- b.CLIENT_NET_ADDRESS AS 'ClientIPAddress'-- into MyAudit..Audit_DYDB_UPDL

, additional_information--.value ('(/ action_info/address) [1]', 'varchar (20)')

FROM sys. [FN _ get_audit_file] ('D:\ SqlAudits\ MyAudit_1FE965A7-77D0-41A6-9D40-543162C722F2unknown 131486627855460000.sqlaudit`

DEFAULT, DEFAULT) a-- left join SYS.DM_EXEC_CONNECTIONS b with (nolock)

-- on a.session_id=b.session_id

Where

-- CONVERT (datetime,SWITCHOFFSET (CONVERT (datetimeoffset, event_time), DATENAME (TzOffset, SYSDATETIMEOFFSET () between dateadd (mi,-5) and getdate ()

-- and

Action_id='LGIS'

Declare @ xml xml='00x280000200x0001f4380x00000000409610.20.50.110'

With xmlnamespaces (default 'http://schemas.microsoft.com/sqlserver/2008/sqlaudit_data')

Select @ xml.value ('(/ action_info/address) [1]', 'varchar (20)')

00x280000200x0001f4380x00000000409610.20.50.110