DDL triggers/Logon events

Question

Post reply

DDL triggers/Logon events

Animal Magic

SSChampion

Points: 13964
More actions
October 6, 2009 at 5:42 am

#385509

Hi All,
I am trying to capture the application name of each logon through the use of a DDL server trigger and the eventdata() function. however i dont seem to be able to find out how to get the application name. can anyone help me out??
i currently have this code but the application name doesnt pick up anything, however it doesnt fail
create TRIGGER Trigger_ServerLogon
ON ALL SERVER WITH EXECUTE AS 'sa' FOR LOGON
AS
BEGIN
DECLARE @data XML
SET @data = EVENTDATA()
INSERT INTO dbo.ServerLogonHistory
SELECT @data.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(512)')
, @data.value('(/EVENT_INSTANCE/PostTime)[1]', 'datetime')
, @data.value('(/EVENT_INSTANCE/SPID)[1]', 'nvarchar(4)')
, @data.value('(/EVENT_INSTANCE/ServerName)[1]', 'nvarchar(512)')
, @data.value('(/EVENT_INSTANCE/LoginName)[1]', 'nvarchar(512)')
, @data.value('(/EVENT_INSTANCE/LoginType)[1]', 'nvarchar(512)')
, @data.value('(/EVENT_INSTANCE/SID)[1]', 'nvarchar(512)')
, @data.value('(/EVENT_INSTANCE/ClientHost)[1]', 'nvarchar(512)')
, @data.value('(/EVENT_INSTANCE/IsPooled)[1]', 'nvarchar(1)')
, @data.value('(/EVENT_INSTANCE/TextData/ApplicationName)[1]', 'nvarchar(512)')
END
GO
john

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Jack Corbett SSC Guru Points: 184394 More actions · Answer 1

Looks like, based on the EventData schema, you only need:

@data.value('(/EVENT_INSTANCE/ApplicationName)[1]', 'nvarchar(512)')

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

Animal Magic SSChampion Points: 13964 More actions · Answer 2

No luck, still comes through as null in the application name field when doing a connection from SSMS (not tried any others). Maybe its the "FOR LOGON" bit which is wrong and i need to be using something else.

I find the MS documentation and a lot of online documentation quite confusing when it comes to the available option with DDL triggers - although maybe thats just me.

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 3

You might try

SELECT APP_NAME()

But do keep in mind the app sets that so it is possible to masquerade as another app..

CEWII

Jack Corbett SSC Guru Points: 184394 More actions · Answer 4

Sorry about that last post. Believe it or not it looks like the LOGON event does not contain ApplicationName. Here is the list of elements for the LOGON event from the xsd (I am pretty sure :-D):

- <xs:complexType name="EVENT_INSTANCE_LOGON">

- <xs:sequence>

- <!-- Basic Envelope

-->

<xs:element name="EventType" type="SSWNAMEType" />

<xs:element name="PostTime" type="xs:string" />

<xs:element name="SPID" type="xs:int" />

- <!-- Server Scoped DDL

-->

<xs:element name="ServerName" type="PathType" />

<xs:element name="LoginName" type="SSWNAMEType" />

- <!-- Main Body

-->

<xs:element name="LoginType" type="SSWNAMEType" />

<xs:element name="SID" type="SID" />

<xs:element name="ClientHost" type="SSWNAMEType" />

<xs:element name="IsPooled" type="xs:boolean" />

</xs:sequence>

</xs:complexType>

As you can see there is no ApplicationName listed.

So you may have to go with Elliot's suggestion, although I'm not sure what that will return in a trigger.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 5

It does show up.. I have a loggin infrastructure I am going to be releasing soon that uses it..

CEWII

Jack Corbett SSC Guru Points: 184394 More actions · Answer 6

Cool, I haven't had time to try it so I didn't know and it didn't sound like you had tried it in a Logon Trigger either. It is interesting that the EventData() function doesn't include it for the Logon Event though.

I'm glad you included the warning that it can be set by the application. A lot of people don't understand that.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

Animal Magic SSChampion Points: 13964 More actions · Answer 7

Thanks Elliot (and Jack) i will just use the app_name alongside the eventdata data. I just find it odd they dont include it in the eventdata when its one of the main things i would want to see.

Animal Magic SSChampion Points: 13964 More actions · Answer 8

Can confirm that app_name() works perfectly in the logon trigger.

thanks again

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 9

Jack Corbett (10/6/2009)
Cool, I haven't had time to try it so I didn't know and it didn't sound like you had tried it in a Logon Trigger either. It is interesting that the EventData() function doesn't include it for the Logon Event though.
I'm glad you included the warning that it can be set by the application. A lot of people don't understand that.

That and HOST_NAME().

CEWII