Highlighted
Knowledge Partner
Knowledge Partner
225 views

process for exporting "users" from qc 10 siteadmin and importing them into alm 11.52 SA users list

Hi,

 

We are in the process of migrating to new windows 2008 R2 servers  from existing windows 2003 servers, In this effort we are upgrading all the QC 10 projects to ALM 11.52.

 

In QC 10 we have Oracle 10g DB Server, In ALM 11.52 we are going for Oracle 11g DB Server.

 

We are proceeding with the option of creating a new site admin schema in new ALM 11.52 setup.

 

Since we have used the above option the old SA users along with their properties will be wiped out.

 

I am looking for a way/process/best practise methods for exporting "users" along with their properties( name,phone#,email id etc )  from qc 10 siteadmin and importing them into alm 11.52 siteadmin USERS table by comparing each user aganist LDAP and fetching their properties.

 

Is there any OTA API code or useful articles/KB's for performing this activity?

 

Any inputs are highly appreciated, please advise.

 

Regards,

Srihari

 

 

 

 

 

 

0 Likes
14 Replies
Contributor.. Anton_Labachev Contributor..
Contributor..

Re: process for exporting "users" from qc 10 siteadmin and importing them into alm 11.52 S

Hi Srihari,

 

I would like to inform you that there is no existing tool to export import users with their properties.

 

The only way to have the users migrated at this point is to upgrade qcsiteadmin_db schema.

 

Otherwise you have to import the users from LDAP.

Best regards,
Anton Labachev

HP Support
[If this post or any other post helps to resolve your issue or query, mark the thread as solved and give KUDOS to the author for their assistance. ]

If you haven’t tried it yet, come and join us in our entitled forums at http://h30499.www3.hp.com/t5/News/Support-Customer-Forums-now-available/m-p/5610181/message-uid/5610181#U5610181
0 Likes
Honored Contributor.. mark_ford Honored Contributor..
Honored Contributor..

Re: process for exporting "users" from qc 10 siteadmin and importing them into alm 11.52 S

You could write a program or script to extract the users from the old Schema using Site Admin APIs or using SQL directly from the table (if you have that level of access) and insert the users into the new Schema.

0 Likes
Knowledge Partner
Knowledge Partner

Re: process for exporting "users" from qc 10 siteadmin and importing them into alm 11.52 S

Thank you Peers for helping in this regard.

 

Can you please validate the below procedure for this scenario?

 

For upgrading user properties from QC 10 to ALM 11.52 ,  user information will be updated for each project , can you please advise on  how far it is good to go with this approach.

 

Step 1: Say for example we have migrated project "ABC" from QC 10 to ALM 11.52, for updating user properties, login QC 10 DB Server and execute the below query in

        project_ABC_db database.

 

        select A.us_username, B.ACC_IS_ACTIVE, B.full_name, B.email, B.user_password, B.description,

        B.phone_number, B.last_update, B.us_dom_auth, B.US_REPORT_ROLE

        from users A join qcsiteadmin_db.users B

        on us_username = user_name

 

        This will get full details of all the ABC project specific users from QC 10.

 

Step 2: Temporary QC10_USERS  table has been created in qcsiteadmin_db schema of ALM 11.52 for holding the data produced in Step 1.

        So for each project before actually placing the output of Step 1 to this temporary table , we delete the existing content.

 

        delete from qcsiteadmin_db.QC10_USERS

 

Step 3: Copy the Data produced in Step 1 ( QC 10 ) to QC10_USERS table in ALM 11.52

         

Step 4: Run the following query in ALM 12 to ensure that all the user entries are present in the USERS table before updating it. If there are rows, that means that         user entries are missing in the USERS table for the migrated project.

 

        select * from qcsiteadmin_db.QC10_USERS

        where USER_NAME not in (select user_name from qcsiteadmin_db.USERS)

 

Step 5: Run the following query to update the user properties in ALM 11.52

 

        update B

                set B.[ACC_IS_ACTIVE]  = A.[ACC_IS_ACTIVE]

                ,   B.[FULL_NAME]      = A.[FULL_NAME]

                ,   B.[EMAIL]          = A.[EMAIL]

                ,   B.[USER_PASSWORD]  = A.[USER_PASSWORD]

                ,   B.[DESCRIPTION]    = A.[DESCRIPTION]

                ,   B.[PHONE_NUMBER]   = A.[PHONE_NUMBER]

                ,   B.[LAST_UPDATE]    = A.[LAST_UPDATE]

                ,   B.[US_DOM_AUTH]    = A.[US_DOM_AUTH]

                ,   B.[US_REPORT_ROLE] = A.[US_REPORT_ROLE]

                from qcsiteadmin_db.QC10_USERS A inner join qcsiteadmin_db.USERS B

                on A.USER_NAME = B.USER_NAME

 

Regards,

Srihari

 

0 Likes
Honored Contributor.. mark_ford Honored Contributor..
Honored Contributor..

Re: process for exporting "users" from qc 10 siteadmin and importing them into alm 11.52 S

This seems like more than what you need.  I would just copy the info from the USERS table from the old site admin schema into the new site admin schema.  The USERS table in the project schema shouldn't need to be involved.  Unless I'm missing something here.

0 Likes
Knowledge Partner
Knowledge Partner

Re: process for exporting "users" from qc 10 siteadmin and importing them into alm 11.52 S

Hi Mark_Ford,

 

In that case users who are added to projects after performing copy activity will be moved to new qc 11.52 schema without any properties update.

 

The procedure provided in the before post will update the user properties for each project once they are migrated.

 

Also In the new SA which is created there will be 2 to 3 users present PFA, so when we copy the data from QC 10 USERS table and paste it in our QC 11.52 users table it will complain about unique constraint issues, without copying data.

 

 

Please let me know if there is any alternative way for copying the USERS data by which we can exclude unique constraint issue?

 

Regards,

Srihari

0 Likes
Boyan_Angelov Super Contributor.
Super Contributor.

Re: process for exporting "users" from qc 10 siteadmin and importing them into alm 11.52 S

Hello All,

 

Such manual way of exporting/importing directly from the DB may cause corruption on a future stage.

Maybe another option will be to add all users to a newly created proejct from QC 10 side, then migrate the proecjt ot ALM 11.52

After this migration the users will be imported in the SA schema, if no parameter is set to disable the users import.

 

Please try this approach on a test server just to verify it.

 

Hope this helps.

 

Boyan

0 Likes
Absent Member.. Trudy Claspill Absent Member..
Absent Member..

Re: process for exporting "users" from qc 10 siteadmin and importing them into alm 11.52 S

@Boyan,

 

Your solution does not provide the desired result. The author wants all the user properties migrated to the new site. When a project is imported, the only user information that is added to the site is the username.

[If this post solves or helps solve your issue, mark the thread as solved and give KUDOS to the author for their assistance.]

(Opinions expressed in my postings are mine alone, and do not reflect the opinions of my employer.No warranties express or implied for any solution/suggestion posted.)
0 Likes
Knowledge Partner
Knowledge Partner

Re: process for exporting "users" from qc 10 siteadmin and importing them into alm 11.52 S

Hi Boyan,

 

As explained by Trudy my requirement is to update the user properties  after each project migration.

 

Regards,

Srihari

0 Likes
NEW_ALM12 Outstanding Contributor.
Outstanding Contributor.

Re: process for exporting "users" from qc 10 siteadmin and importing them into alm 11.52 S

@Srihari,
I’m in a same boat now. I see this post was from 2015.
I’m going from 11 to 12.21 then to 15. 11 and 12.21 are QC authentication where as 15 is SSO enabled.
Would you please share your detail process how and what you did to update users table in destination?
0 Likes
Knowledge Partner
Knowledge Partner

Re: process for exporting "users" from qc 10 siteadmin and importing them into alm 11.52 S

Hi NEW_ALM12,

Are you using Oracle as backend database ?

Regards,
Srihari
0 Likes
NEW_ALM12 Outstanding Contributor.
Outstanding Contributor.

Re: process for exporting "users" from qc 10 siteadmin and importing them into alm 11.52 S

@Srihari- Yes, Im using Oracle in Source and destination.

0 Likes
Knowledge Partner
Knowledge Partner

Re: process for exporting "users" from qc 10 siteadmin and importing them into alm 11.52 S

Hi @NEW_ALM12 ,

I created a oracle db link named (p2t) between the source and the destination.

Oracle DBA will help creating this DB link.

Post that after each project upgrade and migration, i executed the below query on the destination oracle db server to update the user property details.

 

merge into qcsiteadmin_db.users t1
using (select * from QCSITEADMIN_DB.users@p2t) t2
on (upper(t1.USER_NAME)= upper(t2.USER_NAME))
when matched then update set 
t1.ACC_IS_ACTIVE=t2.ACC_IS_ACTIVE,
t1.FULL_NAME=t2.FULL_NAME,
t1.EMAIL=t2.EMAIL,
t1.USER_PASSWORD=t2.USER_PASSWORD,
t1.DESCRIPTION=t2.DESCRIPTION,
t1.PHONE_NUMBER=t2.PHONE_NUMBER,
t1.US_DOM_AUTH=t2.US_DOM_AUTH,
t1.US_REPORT_ROLE=t2.US_REPORT_ROLE; 
commit;

 

 

Please thoroughly validate this approach in Dev and validation environments, before running in production.

Regards,Srihari 

0 Likes
NEW_ALM12 Outstanding Contributor.
Outstanding Contributor.

Re: process for exporting "users" from qc 10 siteadmin and importing them into alm 11.52 S

@Srihari- , Can I do this process before migrating  a project in a destination?

or is there a simple method to load users detail in destination qcsiteadmin_db.users?

0 Likes
Knowledge Partner
Knowledge Partner

Re: process for exporting "users" from qc 10 siteadmin and importing them into alm 11.52 S

@NEW_ALM12,
setting up the DB link is a one time activity , DBA team will be able to help with this task.

Once DB link is established between old qcsiteadmin users and new qcsiteadmin users table. Only after a project is migrated, You need to execute provided query,it will hardly take 1 to 2 mins for that query to run and update users table.

Microfocus might be having utility for user properties update please raise a sr so they can help on it.

Same is case with orasi support(eyeontesting.com) I remember they also have a tool for this activity.

I am not aware of any other easy validated process that will update complete users table in oracle.

Maybe other peers can provide their experience and thoughts on this.

Regards, Srihari
0 Likes
The opinions expressed above are the personal opinions of the authors, not of Micro Focus. By using this site, you accept the Terms of Use and Rules of Participation. Certain versions of content ("Material") accessible here may contain branding from Hewlett-Packard Company (now HP Inc.) and Hewlett Packard Enterprise Company. As of September 1, 2017, the Material is now offered by Micro Focus, a separately owned and operated company. Any reference to the HP and Hewlett Packard Enterprise/HPE marks is historical in nature, and the HP and Hewlett Packard Enterprise/HPE marks are the property of their respective owners.