Is there a way to trace the IP Address in Sybase IQ

[former_member246062]

Hello All,

Need to track the IP Address of the system where any login tries to attempts to connect to SAP IQ DB server.

Other logins are using Interactive SQL to connect to the server & making logins to be locked everyday . Need to trace the IP address where logins were attempting wrong password attempts.

Thanks

Y S

[markmumy]

And another variation. The key is the CommLinks and NodeAddr parameters.

set temporary option on_error='continue'
;
set temporary option quoted_identifier='on'
;
drop event if exists login_info
;


create event login_info
type "Connect"
handler
begin
        declare uname varchar(256);
        declare my_spid unsigned bigint;
        declare my_commlink varchar(128);
        declare my_nodeaddr varchar(128);


        set temporary option query_plan='off';
        set temporary option query_plan_as_html='off';
        set temporary option index_advisor='off';


        -- ConnectionID property is parent login connection
        -- this is the one that needs to be dropped
        set my_spid = event_parameter( 'ConnectionId' );
        set uname = event_parameter( 'User' );
        select CommLink, NodeAddr into my_commlink, my_nodeaddr from sp_iqconnection() where ConnHandle = my_spid;


message 'UserSPID: '||my_spid to console;
message 'UserName: '||uname to console;
message 'MyComm: '||my_commlink to console;
message 'MyNode: '||my_nodeaddr to console;


end
;

[raymond_lackey · Accepted solution]

You could use a combination of the following events and event parameters to track connections and connection failures. Note that the event parameter 'AppInfo' contains the IP address.

TQ_TrackConnection

CREATE EVENT "DBA"."TQ_TrackConnection" TYPE "Connect"

ENABLE

HANDLER

BEGIN

DECLARE conid unsigned bigint;

DECLARE usr VARCHAR(128);

DECLARE ai varchar (4096);

SET conid = event_parameter ('ConnectionId');

SET usr = event_parameter ( 'User' );

SET ai = event_parameter ('AppInfo');

// Write out the Connection ID, user name, application information

INSERT INTO TQ_AUDIT.TQ_Connection( Conn_ID, user_name,app_info )

VALUES(conid, usr,ai );

COMMIT;

END;

commit;

TQ_ConnFail

CREATE EVENT TQ_ConnFail TYPE ConnectFailed

ENABLE

HANDLER

BEGIN

DECLARE usr CHAR(128);

SET usr = event_parameter( 'User' );

// Put a limit on the number of failures logged.

IF (SELECT COUNT(*) FROM TQ_Audit.ConnectionFailure

WHERE user_name = usr

AND tm >= DATEADD( minute, -30,

CURRENT TIMESTAMP )) < 20 THEN

INSERT INTO TQ_Audit.ConnectionFailure( user_name )

VALUES( usr );

COMMIT;

// Delete failures older than 7 days.

DELETE TQ_Audit.ConnectionFailure

WHERE user_name = usr

AND tm < dateadd( day, -7, CURRENT TIMESTAMP );

COMMIT;

END IF;

END;

commit;