Skip to main content
Announcements
UPGRADE ADVISORY for Qlik Replicate 2024.5: Read More
cancel
Showing results for 
Search instead for 
Did you mean: 
patrickgallucci
Contributor II
Contributor II

Capturing source session metadata for auditing

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? 

Labels (1)
9 Replies
lyka
Support
Support

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
Contributor II
Contributor II
Author

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
Employee
Employee

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
Support
Support

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.

Help users find answers! Do not forget to mark a solution that worked for you! If already marked, give it a thumbs up!
john_wang
Support
Support

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.

Help users find answers! Do not forget to mark a solution that worked for you! If already marked, give it a thumbs up!
patrickgallucci
Contributor II
Contributor II
Author

Perfect! Thanks!

suvbin
Creator III
Creator III

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
Creator III
Creator III

Any update on this please.

john_wang
Support
Support

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 timeAR_H_TIMESTAMP; some information need more investigation. We'd like to suggest contact PS team for detailed solutions.

Regards,

John.

Help users find answers! Do not forget to mark a solution that worked for you! If already marked, give it a thumbs up!