I have created the following test environment:
USE [OmegaCoreAudit]
GO
/****** Object: Table [dbo].[TEST_TRAIL] Script Date: 6/9/2025 8:02:50 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[TEST_TRAIL](
[TIMESTAMP_STS] datetime2 NOT NULL,
[APP_01] nvarchar NULL,
[APP_02] nvarchar NULL,
[SQL_TEXT] nvarchar NULL,
[LOGIN_NAME] nvarchar NULL,
[MODE] nvarchar NULL
) ON [PRIMARY]
GO
USE [master]
GO
/****** Object: DdlTrigger [ALL_SRV_LOG] Script Date: 6/9/2025 7:39:42 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [ALL_SRV_LOG]
ON ALL SERVER WITH EXECUTE AS 'sa'
FOR LOGON
AS
BEGIN
DECLARE @EventData XML;
declare @v_app_name_01 nvarchar(128);
declare @v_sql_text nvarchar(2000);
declare @v_app_name_02 nvarchar(128);
SET @EventData = EVENTDATA();
set @v_app_name_01 = @EventData.value('(/EVENT_INSTANCE/ApplicationName)[1]', 'nvarchar(128)');
set @v_sql_text = @EventData.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(2000)');
set @v_app_name_02 = APP_NAME();
insert into [OmegaCoreAudit].dbo.[TEST_TRAIL]
(TIMESTAMP_STS, app_01, app_02, sql_text, login_name, mode)
values
(CURRENT_TIMESTAMP, @v_app_name_01, @v_app_name_02, @v_sql_text, ORIGINAL_LOGIN(), 'LOG') ;
END
USE [master]
GO
/****** Object: DdlTrigger [ALL_SRV_DDL] Script Date: 6/9/2025 7:21:26 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [ALL_SRV_DDL]
ON ALL SERVER WITH EXECUTE AS 'sa'
FOR DDL_EVENTS
AS
BEGIN
DECLARE @EventData XML;
declare @v_app_name_01 nvarchar(128);
declare @v_sql_text nvarchar(2000);
declare @v_app_name_02 nvarchar(128);
SET @EventData = EVENTDATA();
set @v_app_name_01 = @EventData.value('(/EVENT_INSTANCE/ApplicationName)[1]', 'nvarchar(128)');
set @v_sql_text = @EventData.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(2000)');
set @v_app_name_02 = APP_NAME();
insert into [OmegaCoreAudit].dbo.[TEST_TRAIL]
(TIMESTAMP_STS, app_01, app_02, sql_text, login_name, mode)
values
(CURRENT_TIMESTAMP, @v_app_name_01, @v_app_name_02, @v_sql_text, ORIGINAL_LOGIN(), 'DDL') ;
END
Left it for a while, and when queried the table, noticed that fields:
APP_01 (populated by EventData.ApplicationName) - is always null. APP_02 (populated by APP_NAME()) - is populated.
I can understand that for LOGON events the EventData.ApplicationName gives no value, as in the following URL: https://schemas.microsoft.com/sqlserver/2006/11/eventdata/events.xsd
... this is somehow indicated.
Question: Why on DDL_EVENT the APP_01 is always empty (while APP_NAME() does give a value)?
best regards
Altin
