Capturing source session metadata for auditing

[patrickgallucci]

I need to be able to track the SQL Server source security identification number (SID)  for auditing.  I tried using header AR_H_USER in an expression, but the value just comes back blank. I would prefer to have the result of SELECT @@SPID or SELECT SUSER_SID();  Is there a way to use session information or variables in expressions? 

[lyka]

Hello,

The AR_H_USER is the user name, ID or any other informationnthat the source provides about the change
initiator. This is also blank on full load. Have you checked if there is value when using CDC?

Thanks

Lyka

[patrickgallucci]

Hi Lyka,

Ok, based on your answer, I think I need to rephrase my question to: If I want to capture the session SID from SQL Server, using CDC, how can I do this? I understand it is not available for full load. Thank in advance.

Patrick

[MarinaEmbon]

Hi Patrick,

I'm afraid we don't have this supported in Replicate right now, but it could be a great idea to add it. 

could you please open an Idea and we will see if other customers find it useful as well?

Marina.

[john_wang]

Hi @patrickgallucci , @lyka , @MarinaEmbon ,

I've an idea, we can get it. Give me mins let me verify it and come back soon.

Regards,

John.

[john_wang]

Hi @patrickgallucci , @lyka , @MarinaEmbon ,

We can use source_lookup to get it, for example we want to get the DB_NAME, (or DB_ID()) from SQL Server source:

source_lookup('NO_EXPIRATION', 'x', 'y', 'db_name() --', 'z', 'z')

where NO_EXPIRATION means we execute the query (select db_name() ) once only (to minimize the DB load).

              x,y,z is dummy inputs, they are placeholders only. 

              You can get detailed usage about source_lookup() in User Guide.

For your instance, you can replace DB_NAME() by SUSER_SID() , or @@SPID  (to be honest I did not try "@@SPID", you can confirm it by a quick test) or other expressions you wish.

The above option is used for Full Load and CDC. If it's a Full Load ONLY task, you can use VIEW to compose the query and get the information you want, it's more easy and simple to control it.

Hope this helps,

John.

[patrickgallucci]

Perfect! Thanks!

[suvbin]

how can we get the job id, job runid, record updated time, record updated by, record inserted time, record inserted by.  Can we capture  these details with adding extra columns in the existing table , with transformations in the Qlik Replicate UI ?

Any alternate way, please suggest. 

[suvbin]

Any update on this please.

[john_wang]

Hello @suvbin ,

I think some information can be get by source_look(), see my comment of "‎2021-01-05 07:35 AM", for example SPID(); some information can be get from header information of changed rows, eg transaction time "record updated time" AR_H_TIMESTAMP; some information need more investigation. We'd like to suggest contact PS team for detailed solutions.

Regards,

John.