Export recipients NPrinting 17+

[khaled_dabbous]

I am using NPrinting April 2018 release and wondering if it is possible to export the recipients, filters and groups in the same format that it can be imported into NPrinting. Could there be a work around?

Thank you!

[kfoudhaily]

there is not a solution that i'm aware of.

[khaled_dabbous]

I believe that the data (recipients, filters and groups) is saved in Postgres, so connecting to the NPrinting Postgres database might be the first step.

[kfoudhaily]

maybe but I have nover done it befor

please let know if you have any luck, I think u can use super admin db that created the base wheninstalling nprinting

[Lech_Miszkiewicz]

Hi,

out of the box there is no such option.

You migh be able to create your own workaround by using API or accessing PG DB directly (steps how to do this below in attached post):

https://community.qlik.com/thread/258632?q=repository%20database

https://help.qlik.com/en-US/nprinting/April2018/APIs/NP+API/index.html

cheers Lech

[khaled_dabbous · Accepted solution]

I have done a work around to export

Run pgAdmin4.exe, the location default location upon installation is the following on a windows server:

C:\Program Files\NPrintingServer\pgsql\pgAdmin 4\bin\pgAdmin4.exe

Connect to the NPrinting Server server with the following credentials:

• Hostname:    localhost or "your server name"
• Port:              4992
• Database:    nprinting
• User name:  postgres
• Password:

Drop down through Severs →‌  nprinting ‌→‌ Schemas

right click on public and select Query Tool

Before executing the following script, you need to ensure that the destination folders of the CSVs (ex: E:/Recepients/input/) to be accessible by postgres.

Also note that the user password is hashed, so has to be changed manually. if a single password applies to all the users, replace 'testuser' below with the password that is used on all the users.

-- FUNCTION: public.export_foo()

-- DROP FUNCTION public.export_foo();

CREATE OR REPLACE FUNCTION public.export_foo(

)

    RETURNS void

    LANGUAGE 'sql'

    COST 100

    VOLATILE SECURITY DEFINER

    ROWS 0

AS $BODY$

Copy (

SELECT

"Name",

"Description",

"App",

"Enabled",

"Connection",

"Values",

"Numeric Values",

"Formulas"

FROM(

SELECT a.name as "Name",

a.description as "Description",

d.name as "App", 

case

when a.enabled = true then upper('TRUE')

    when a.enabled = false then upper('FALSE')

    else  null end as "Enabled",

e.name as "Connection",

case

when evaluate =false and is_numeric ='false' then b.name||'= {'||string_agg(DISTINCT c.value,',')||'}'

    else null

    end as "Values",

case

when is_numeric =true then b.name||'= {'||string_agg(DISTINCT c.value,',')||'}'

    else null

    end as "Numeric Values",

case

    when evaluate =true then b.name||'= {'||ltrim(string_agg(DISTINCT c.value,','),'=')||'}'

    else null

    end as "Formulas"

FROM public.filter a

left join filter_field b on a.id=b.filter_id

left join filter_field_value c on b.id=c.filter_field_id

left join app d on a.app_id=d.id

left join data_connection e on b.data_connection_id=e.id

GROUP BY

a.name,

a.description,

d.name,

    a.enabled,

    e.name,

    evaluate,

    is_numeric,

    b.name

    )aaa

) To 'E:/Recepients/input/Filters.csv' With CSV DELIMITER ',';

Copy (

SELECT 

E_mail,

Username,

Password,

Domain_Account,

Enabled,

Time_Zone,

initcap(Locale)as Locale,

Nickname,

Title,

Company,

Job_Title,

Department,

Office,

string_agg(DISTINCT Filters,',') as Filters,

string_agg(DISTINCT Groups,',') as Groups,

string_agg(DISTINCT Roles,',') as Roles

FROM(

SELECT

b.email as E_mail,

c.name as Username,

'testuser' as Password,

domain_account as Domain_Account,

case

when a.enabled = true then upper('TRUE')

when a.enabled = false then upper('FALSE')

else  null end as Enabled,

timezone as Time_Zone,

locale as Locale,

nickname as Nickname,

title as Title,

company as Company,

job_title as Job_Title,

department as Department,

office as Office,

o.name as Filters,

m.name as Groups,

q.name as Roles

FROM public.usr a

LEFT JOIN email_address as b on (a.email_address_id = b.id)

LEFT JOIN recipient as c on (a.recipient_id=c.id)

LEFT JOIN user_group_to_usr as e on (a.recipient_id=e.user_id)

LEFT JOIN user_group f on (e.user_group_id=f.recipient_id)

LEFT JOIN recipient m on (f.recipient_id=m.id)

LEFT JOIN filter_to_recipient n on (a.recipient_id=n.recipient_id)

LEFT JOIN filter o on(n.filter_id=o.id)

LEFT JOIN role_recipient p on(a.recipient_id=p.recipient_id)

LEFT JOIN role q on (p.role_id = q.id)

)aaa

GROUP BY  E_mail,Username,Password,

Domain_Account,

Enabled,

Time_Zone,

Locale,

Nickname,

Title,

Company,

Job_Title,

Department,

Office

) To 'E:/Recepients/input/Users.csv' With CSV DELIMITER ',';

Copy(

SELECT

m.name as "Name",

Description as "Description"

FROM user_group f

Inner JOIN recipient m on (f.recipient_id=m.id)

) To 'E:/Recepients/input/Groups.csv' With CSV DELIMITER ',';

$BODY$;

ALTER FUNCTION public.export_foo()

    OWNER TO postgres;

After creating the function, you will have to call upon it using the following script:

SELECT public.export_foo();

This will create Groups.csv, Users.csv & Filters.csv

Open an new excel document and save it as Recipients.xlsx

Add 3 tabs into this xlsx and name the tabs as the following in the same order:

1. Filters
2. Users
3. Groups

Open Filters.csv, copy the data and paste it in the first tab of Recipients.xlsx

Insert the following into the first row of the first tab: Name, Description, App, Enabled, Connection, Values, Numeric Values, Formulas

Open Users.csv, copy the data and paste it in the second tab of Recipients.xlsx

Insert the following into the first row of the second tab: E-mail, Username, Password, Domain Account, Enabled, Time Zone, Locale, Nickname, Title, Company, Job Title, Department, Office, Filters, Groups, Roles

Open Groups.csv, copy the data and paste it in the third tab of Recipients.xlsx

Insert the following into the first row of the third tab: Name, Description

Save Recipients.xlsx and you are good to go.

This xlsx should be in a format that NPrinting17+ accepts to be imported.

[SirElton]

Thanks for sharing!

I tried your script but I did get the message "ROWS must be positive". After commenting line "ROWS" I was able to get the filters file but not the other two. 

We only use windows authenticaion, not NPrinting authentication. After taking out the "password" lines, the group file was generated as well but I am still struggeling to get the users file. 

Has anyone experience similar issues? Thanks for your advice!

[blaise]

Why not use the apis like @Lech_Miszkiewicz  suggested, it's only a few get endpoints

[Lech_Miszkiewicz]

that's right, and all required scripts are ready here in NPrinting.qvs library:

https://nprintingadventures.wordpress.com/2019/04/08/nprinting-api-qlik-rest-subroutines/