topic Re: Capturing source session metadata for auditing in Qlik Replicate

Capturing source session metadata for auditing

patrickgallucci — Mon, 04 Jan 2021 19:54:13 GMT

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?

Re: Capturing source session metadata for auditing

lyka — Tue, 05 Jan 2021 00:09:32 GMT

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

Re: Capturing source session metadata for auditing

patrickgallucci — Tue, 05 Jan 2021 03:14:50 GMT

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

Re: Capturing source session metadata for auditing

MarinaEmbon — Tue, 05 Jan 2021 10:26:54 GMT

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.

Re: Capturing source session metadata for auditing

john_wang — Tue, 05 Jan 2021 11:11:51 GMT

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.

Re: Capturing source session metadata for auditing

john_wang — Tue, 05 Jan 2021 12:35:36 GMT

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.

Re: Capturing source session metadata for auditing

patrickgallucci — Tue, 05 Jan 2021 16:59:19 GMT

Perfect! Thanks!

Re: Capturing source session metadata for auditing

suvbin — Tue, 28 Feb 2023 05:41:42 GMT

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.

Re: Capturing source session metadata for auditing

suvbin — Wed, 01 Mar 2023 07:13:20 GMT

Any update on this please.

Re: Capturing source session metadata for auditing

john_wang — Wed, 01 Mar 2023 13:41:19 GMT

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.