Know the user who triggered the publish task

[ali_hijazi]

Hello
I got a report in NPRINTING;
In Qlik sense app I put the ON-DEMAND reporting button to trigger the nprinting task
what I need is be able to log / save in a QVD or anyother form of files the time, and the username who clicked on that button or who triggered the publish task
is it possible?

kindly advise

[Lech_Miszkiewicz · Accepted solution]

Hi, 

You can use Audit logs for this 

• https://nprintingadventures.com/2021/02/06/nprinting-audit-logs/
• https://nprintingadventures.com/2019/04/08/nprinting-api-qlik-rest-subroutines/ 
• https://help.qlik.com/en-US/nprinting/May2023/Content/NPrinting/AdministeringQVNprinting/Audit-trail...

There are 2 methods of collecting data - via postgres and via API - both are described in my blog.

cheers

[Lech_Miszkiewicz]

Just as a heads-up - the audit Rest API subroutine will have to be re-written i think. The api has changed little bit since I have written it last time and it may cause errors. 

The overall endpoint is the same but the three is bit different. I will need to get that sorted soo and for now see below code.

RestConnectorMasterTable:
SQL SELECT 
	"totalItems",
	"offset",
	"limit",
	"__KEY_data",
	(SELECT 
		"id",
		"userId",
		"userName" AS "userName_u0",
		"ipAddress",
		"source",
		"target",
		"targetId",
		"targetName",
		"appId",
		"eventTime",
		"action",
		"description",
		"dataType",
		"data",
		"__KEY_items",
		"__FK_items",
		(SELECT 
			"authenticationType",
			"format" AS "format_u1",
			"reportId",
			"reportTitle" AS "reportTitle_u1",
			"requestSourceType",
			"model",
			"userName",
			"licenseKey",
			"organization",
			"host",
			"name" AS "name_u0",
			"status",
			"enabled",
			"version",
			"taskId",
			"taskName",
			"taskType",
			"triggerId",
			"executionId",
			"from",
			"emailId",
			"subject",
			"published",
			"recipientId",
			"recipientEmail",
			"recipientDomainAccount",
			"__KEY_data_u0",
			"__FK_data",
			(SELECT 
				"@Value",
				"__FK_cc"
			FROM "cc" FK "__FK_cc" ArrayValueAlias "@Value"),
			(SELECT 
				"@Value" AS "@Value_u0",
				"__FK_to"
			FROM "to" FK "__FK_to" ArrayValueAlias "@Value_u0"),
			(SELECT 
				"@Value" AS "@Value_u1",
				"__FK_bcc"
			FROM "bcc" FK "__FK_bcc" ArrayValueAlias "@Value_u1"),
			(SELECT 
				"name",
				"size",
				"format",
				"reportTitle",
				"__FK_attachments"
			FROM "attachments" FK "__FK_attachments"),
			(SELECT 
				"@Value" AS "@Value_u2",
				"__FK_failedReports"
			FROM "failedReports" FK "__FK_failedReports" ArrayValueAlias "@Value_u2"),
			(SELECT 
				"@Value" AS "@Value_u3",
				"__FK_skippedReports"
			FROM "skippedReports" FK "__FK_skippedReports" ArrayValueAlias "@Value_u3"),
			(SELECT 
				"size" AS "size_u0",
				"format" AS "format_u0",
				"reportTitle" AS "reportTitle_u0",
				"__FK_embeddedReports"
			FROM "embeddedReports" FK "__FK_embeddedReports"),
			(SELECT 
				"@Value" AS "@Value_u4",
				"__FK_failedVariables"
			FROM "failedVariables" FK "__FK_failedVariables" ArrayValueAlias "@Value_u4")
		FROM "data" PK "__KEY_data_u0" FK "__FK_data")
	FROM "items" PK "__KEY_items" FK "__FK_items")
FROM JSON (wrap off) "data" PK "__KEY_data";

[cc]:
LOAD	[@Value],
	[__FK_cc] AS [__KEY_data_u0]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_cc]);


[to]:
LOAD	[@Value_u0] AS [@Value_u0],
	[__FK_to] AS [__KEY_data_u0]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_to]);


[bcc]:
LOAD	[@Value_u1] AS [@Value_u1],
	[__FK_bcc] AS [__KEY_data_u0]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_bcc]);


[attachments]:
LOAD	[name],
	[size],
	[format],
	[reportTitle],
	[__FK_attachments] AS [__KEY_data_u0]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_attachments]);


[failedReports]:
LOAD	[@Value_u2] AS [@Value_u2],
	[__FK_failedReports] AS [__KEY_data_u0]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_failedReports]);


[skippedReports]:
LOAD	[@Value_u3] AS [@Value_u3],
	[__FK_skippedReports] AS [__KEY_data_u0]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_skippedReports]);


[embeddedReports]:
LOAD	[size_u0] AS [size_u0],
	[format_u0] AS [format_u0],
	[reportTitle_u0] AS [reportTitle_u0],
	[__FK_embeddedReports] AS [__KEY_data_u0]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_embeddedReports]);


[failedVariables]:
LOAD	[@Value_u4] AS [@Value_u4],
	[__FK_failedVariables] AS [__KEY_data_u0]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_failedVariables]);


[data]:
LOAD	[authenticationType],
	[format_u1] AS [format_u1],
	[reportId],
	[reportTitle_u1] AS [reportTitle_u1],
	[requestSourceType],
	[model],
	[userName],
	[licenseKey],
	[organization],
	[host],
	[name_u0] AS [name_u0],
	[status],
	[enabled],
	[version],
	[taskId],
	[taskName],
	[taskType],
	[triggerId],
	[executionId],
	[from],
	[emailId],
	[subject],
	[published],
	[recipientId],
	[recipientEmail],
	[recipientDomainAccount],
	[__KEY_data_u0],
	[__FK_data] AS [__KEY_items]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_data]);


[items]:
LOAD	[id],
	[userId],
	[userName_u0] AS [userName_u0],
	[ipAddress],
	[source],
	[target],
	[targetId],
	[targetName],
	[appId],
	[eventTime],
	[action],
	[description],
	[dataType],
	[data],
	[__KEY_items],
	[__FK_items] AS [__KEY_data]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_items]);


[data_u0]:
LOAD	[totalItems],
	[offset],
	[limit],
	[__KEY_data]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__KEY_data]);


DROP TABLE RestConnectorMasterTable;

[ali_hijazi]

but here with the API calls approach I need to enable audit logging only
then create a connection to the Postgres database
run the script and in the result I should be able to see the username who triggered a specific publish task
I will do it now and give you feedback later

[Chirine]

Dear,

I followed the steps mentioned in URL but didn't figure out how to  enable "listening” from other machines.. 

kindly advice

Regards

[Ruggero_Piccoli]

Hi,

It is not mandatory to create a connection to the PostgreSQL Audit DB, you can use APIs to get Audit data via Json as per @Lech_Miszkiewicz suggestion's or you can use the API https://help.qlik.com/en-US/nprinting/May2023/APIs/NP+API/index.html?page=11 to download a csv file. You can choose one of the 3 approaches. 

Best Regards,

Ruggero

[Ruggero_Piccoli]

Hi,

@Chirine what do you mean with "listening” from other machines? Audit will store the log of user's actions in a dedicated DB in the Qlik NPrinting server. It records the logged in user without the need of installing something on other machines or so.

Best Regards,

Ruggero 

[Lech_Miszkiewicz]

@ali_hijazi - postgres select query although possible it is not really officialy supported way of obtaining that data. You are doing it on your own risk.

Supported way is by use of API which is also super simple  to setup and takes exactly the same amount of effort. I would stick to supported configuration especially if you are fairly new to Qlik and NPrinting.

Also - seing you being flagged as a Qlik Partner you want to make sure you provided fully supported configurations, right?

Good luck - cheers

[Lech_Miszkiewicz]

Hi @Chirine ,

what you are trying to do is not officially supported and is some cases can damage your NPrinting repository. You are doing this on your own risk.

The fact that you dont know what postgress settings you need to change just proves that you are not 100% familiar with it which is not something you should be doing on PROD environment as in this case you are not covered by Qlik support nor will get help if you break something. I hope this is clear and we are on the same page with understanding that.

If you are really stubborn and want to connect to postgres then in my blog post there are step by step instructions so there is nothing else to add really. Reading it you get to the link which i also placed here below. Those are changes in config settings to be done to allow for connection to postgres nprinting repository from other servers:

• my post has another link in it which describes how to allow postgres to listen outside localhost: https://stackoverflow.com/questions/11753296/configure-postgresql-to-work-for-only-localhost-or-spec...

There are all required information there to configure it properly (although still risky and unsupported)!

Bottom line is there is supported API and as a partner you should be using it. Tools are exactly the same - you can use Qlik + Rest Connector to load data and store to QVD as hisotry instead of loading it with postgres connector.

What is the reason that you are not willing to go with the supported way and use of API - at the end result is quite the same and most importantly it is end-to-end supported by Qlik?

cheers

Lech

[Chirine]

Hello,

i am not insisting to connect to postgres. i want the perfect scenario in order to insert data into a table.

the case is: i  created a report in N printing then i link it to the Qlik sense by adding on demand button. what i need is once the user click this button and retrieve the report i want to a add a record with the user information and datetime in the database.

Can you please provide me with the step to connect trough an API and be able to insert into database

Regards