HP ALM Workflow with AuditRecord and AuditProperty to retrieve defect history with old value and new value

HP ALM Workflow with AuditRecord and AuditProperty to retrieve defect history with old value and new value

Why this blog entry? 

I need a solution that allow me to retrieve the latest history changes on defect STATUS (old and new value). Unfortunately, I'm not able to get a quick solution from the net.

The Solution

 Set AuditRecordFactory = TDConnection.AuditRecordFactory
Set AuditRecordFactoryFilter = AuditRecordFactory.Filter
AuditRecordFactoryFilter.Filter("AU_ENTITY_TYPE") = "BUG"
AuditRecordFactoryFilter.Filter("AU_ENTITY_ID") = "10327" 'Defect ID'
AuditRecordFactoryFilter.Order("AU_TIME") = 1
AuditRecordFactoryFilter.OrderDirection("AU_TIME") = 1 'Sort the list in decending order
Set AuditRecordList = AuditRecordFactory.NewList(AuditRecordFactoryFilter.Text)

If AuditRecordList.Count < 10 Then
    For Each audit In AuditRecordList
 
       'Below line will retrieve changes made against Defect Status
       Set AuditPropertyFactoryFilter = audit.AuditPropertyFactory.Filter
       AuditPropertyFactoryFilter.Filter("AP_FIELD_NAME") = "BG_STATUS"
       AuditPropertyFactoryFilter.Filter("AP_ACTION_ID") = audit.ActionID 'This line is needed. Else, you will be retrieving all the records
     
       Set ChangeList = audit.AuditPropertyFactory.NewList(AuditPropertyFactoryFilter.Text)
   
       If ChangeList.Count = 1 Then
           For Each Change In ChangeList
               MsgBox "User [" & audit.UserName & "] performed [" & audit.Action & "] at [" & audit.Time & "] with detail:"
               MsgBox "FieldName: [" & Change.FieldName & "] @ OldValue: [" & Change.OldValue & "] @ NewValue: [" & Change.NewValue & "]"
           Next
         
           Exit For
       ElseIf ChangeList.Count > 1 Then
           MsgBox "Warning!, Audit Property has more than 1 changes on BG_STATUS. Changes count is " & ChangeList.Count
       End If
    Next
Else
    MsgBox "Audit Record Count: " & AuditRecordList.Count
End If

Set tdConnection = Nothing

Happy testing!

QC 10 to ALM 11 Upgrade Diary

The post shows you the detail on how to upgrade the QC 10 to a new machine with existing qcsiteadmin, as
well as migrating huge project (~120GB) to the new machine

Problem

We have a huge QC repository (~120GB) host in a VM environment with can only 4GB RAM made available. Finger cross, we can still 'live' with this very extreme condition. However, it is not something for long term, and at the end, we plan to migrate the QC to a new physical machine with 16GB of RAM.

Here you are my story.

Upgrade highlights:

• Upgrade from Windows 2003 (virtual machine) to 2008 R2 SP1 (physical machine)
• Upgrade from SQL 2005 to SQL 2008
• Communication_security_passphrase problem which cause QC to stop/corrupted (which rollback is not an option) 
• Can't load database; CMSSQLCreator : Unable to create database default_myemptyproject_db; Unable to add user 'td'; [Mercury][SQLServer JDBC Driver][SQLServer]The login already has an account under a different user name.; which lead to fail to create new project in ALM 11
• Facing java.lang.OutOfMemoryError: GC overhead limit exceeded which lead to the need to extend the JBOSS JVM RAM setting
• Error: Failed SQL: /* ~~QC */ sp_updatestats [Mercury][SQLServer JDBC Driver][SQLServer]User does not have permission to perform this action. during last step of project upgrade which cause by incorrect setting of db owner 

Hardware & Software details

QC 10 in detail

• lets called it [M1]
• In VM environment - Windows 2003
• 4GB RAM
• Both QC and Database in one machine
• Windows LDAP configured for QC login
• Database held in SQL 2005
• Database username used: td

ALM 11 in detail

• Lets called it [M2]
• Physical machine - Windows 2008 R2 SP1
• 16 GB RAM
• Both QC and Database in one machine as well (lets assume this)
• Windows LDAP configured for QC login
• Database held in SQL 2008
• Database username used: td, td_db_admin

Solution (Diary content)

Migrate qcsiteadmin from M1 (SQL 2005) to M2 [QL 2008)                                          

• Detach 'dbqcadmin' from M1. Refer to link in case you are new to SQL database migration.
• In [M2], make sure no 'td' or 'td_db_admin' user exists. If yes, please remove it.
• Add user td with Server Roles [public]
• Add user td_db_admin with Server Roles [public], [dbcreator] and [securityadmin]
• Attach qcsiteadmin_db
• Run below query in qc_siteadmin_db
<sqlscript>
EXEC sp_change_users_login 'report'
EXEC sp_change_users_login 'update_one', 'td', 'td'
EXEC sp_changedbowner 'td_db_admin'
</sqlscript>

Note: The above query must be performed. Else, you might failed to setup with user 'td_db_admin' as well as failed update existing scheme. 

Note: I once try to change the owner manually, although the migration is successfully, however, after few minutes run, you will hit into communication_security_passphrase problem. Please be aware and the steps must be performed 

• Install vcredist_x64.exe (in case it is required)
• Install ALM 64 bits
• Run ALM Server Configuration Wizard with administration right

Note: For my case, the user that I used to log-on although has administrator right, however, I failed to proceed with Server Configuration as 'right' not enough. What I did was close the wizard, and fire up the wizard with administration right again, and then it works!

• Proceed with ALM configuration as per your preference.
• Points to take note
• Setup with user 'td_db_admin'
• Choose existing schema
• Start up IE with administration right. 
• In case you prompted to loosen the right for IE, proceed with below
• Tool -> Internet Option -> Security -> Internet
• Custom Level, enabled ALL (easy)
• Access to http://localhost:8080/qcbin/ - Administration 
• Install any .cab needed
• Successfully log-on.
• Congratulation! You have just successfully migrated the qcsiteadmin.

Migrate QC project from M1 to M2                                                                                                                              

• Pre-requisite: Deactivate the project so that we can proceed with
• Copy (rich copy) of Repository
• Copy (detach) of database for project
• Create an empty project and named it [MyEmptyProject] for Default.
• Attach db xxx_xxxxxxxxxx
• Run below query in xxx_xxxxxxxxxx
<sqlscript>
EXEC sp_change_users_login 'report'
EXEC sp_change_users_login 'update_one', 'td', 'td'
EXEC sp_changedbowner 'td_db_admin'
</sqlscript>

Note: The above query must be performed. Else, you will failed to migrate the project.

Note: When you search for solution at the web, usually this line EXEC sp_changeowner 'td_db_admin' will not be there. However, as we are using td_db_admin as the admin user, the line cannot be skip!

• Move xxx_xxxxxxxxxx repository to DEFAULT folder.
• Go to [MyEmptyProject]. Make a copy of dbid.xml (as a backup) and edit the dbid.xml for below sections
• <PROJECT_NAME>** Put the project name **</PROJECT_NAME>
• <DB_NAME>** Put the project schema name **</DB_NAME>
• <PHYSICAL_DIRECTORY>** Put the correct path of the repository folder of this project**\</PHYSICAL_DIRECTORY>
• PROJECT_UID>82a311c5-a440-4ecd-97a2-e97331a447XX</PROJECT_UID> where change the last 2 digit 'XX' to any 2 new number, I usually replaced the XX as 99
• PR_SMART_REPOSITORY_ENABLED should be changed from 'Y' to 'N'
• Restore project with the edited dbid.xml in MyEmptyProject. Project shall restore successfully.
• Here come the tricky part where it is FINE TUNE for big repository (~120GB)
• In QC's Administration, 
• Set WAIT_BEFORE_DISCONNECT = -1
• Enable DEBUG module for easy trouble-shooting in case something went wrong
• Server -> xxx_xxxxxxxxxx -> enable DEBUG log level
• De-activate project QuintiqApplicationSuite
• Upgrade ALM to Patch 13 (not necessary for big repository, however we are advice to upgrade the ALM to latest patch before proceed with upgrade.
• Verified that the Admin module is now with 8092
• JBoss fine-tuning (heap memory size)
• Redeploy JBoss with ALM Deployement Wizard (run as administrator)
• Update JBoss heap memory size (it need at least 10GB for my case). How?
• Bring down HP ALM service
• Access to C:\ProgramData\HP\ALM\jboss\bin
• Update InstallJbossService.bat and run.bat to 12288M
• InstallJbossService.bat -uninstall
• InstallJbossService.bat -c "default"
• Bring up HP ALM service
• Verify in logfile on the heap memory size increase

Note: While I googling, some pages didnt mentioned about uninstall and re-install. If this was not done, then your JBOSS heap memory size will remain as before, which is around 1.3GB. Please refer to HP Administration page as it describe clearly in there. Lesson learnt: Official documents are really helpful.

Note: Facing java.lang.OutOfMemoryError: GC overhead limit exceeded will be solved once heap memory size increased.
• Verify Project
• Repair Project
• Upgrade Project
• The upgrade took about 3 hrs, and total file scanned will reach to 17280000!

Lesson learnt 

1. Official documents are helpful. You are advice to 'consume' the doc before proceed
2. HP support is helpful too. 
3. EXEC sp_changedbowner 'td_db_admin' is needed in case you are using td_db_admin

Happy testing!

Extend HP TestResultsDeletionTool.exe for more advanced test result clean up customization by using OTA API with VB scripting

Extend the TestResultsDeletionTool more for advanced deletion customization such as test plan folder/sub folder deletion, test result count constraint deletion etc by using OTA API with VB scripting

Problem

Test executing via Quality Center, 250 testsets, 400 manual scripts which run bi-weekly, and 1000 automated scripts which run nightly leads to tons of test results (since 2010) has been generated and this has indirectly consumes up to GBs of disk spaces in the running environment.

HP's TestResultsDeletionTool can be use to clean up the mess, however I wish to do something more advances like
- Batch cleaning
- Test Plan folder/sub folder cleaning
- Clean up the test result if it has more than 100 counts (to keep results for low frequent execution test cases)

Solution

By using OTA API with VB scripting. VB script can be downloaded by clicking on vbscript.

As TestResultsDeletionTool is using test case as the primary source to delete its run history, hence we need a way to navigate from [Test Case -> Test Set (Test Lab) -> Test Instance -> Run History] to find its way for the clean up

Explanation 1: Below codes is needed to do a cross filter from Test Set to Test Case in order to list out all Test Set that has an instance of Test Case being assigned to.

'filter by test case idSet TestFilter = TestFact.FilterTestFilter.Filter("TS_TEST_ID") = Chr(34) & oTest.ID & Chr(34)            'setup to retrieve test set listSet TestSetFact = objTDConnection.TestSetFactorySet TestSetTreeManager = objTDConnection.TestSetTreeManagerSet TestSetFilter = TestSetFact.Filter            'Set cross filter to filter by test case idTestSetFilter.SetXFilter "TESTSET-TEST", True, TestFilter.Text
'retreive testset listSet TestSetList = TestSetFact.NewList(TestSetFilter.Text)

Explanation 2: Below code is needed to retrieve the Run History count from each test instance in each test of the selected Test Set

For Each itemTestSet In TestSetList    'print("TestID[" & oTest.ID & "], TestSetName[" & itemTestSet.Name & "], TestSetID[" & itemTestSet.ID & "]")    strTestSetName = itemTestSet.Name    Set TSTestFact = itemTestSet.TSTestFactory    Set TestSetTestsList = TSTestFact.NewList("")    'print(TestSetTestsList.Count)    'Get the total count for the particular test case    'In case total count > 100 (as per define in variable), proceed with the deletion    'Else keep the record, as it is not regularly run                     For Each testInstanceItem In TestSetTestsList          If StrComp(testInstanceItem.TestID, oTest.ID, vbTextCompare)=0 Then              Set runFactory = testInstanceItem.RunFactory              Set runFactoryList = runFactory.NewList("")              'print("TestSetName[" & itemTestSet.Name & "], TestID[" & testInstanceItem.TestID & "], Instances[" & testInstanceItem.Instance & "], runFactoryList.Count[" & runFactoryList.Count & "]" )              intTotalCount = intTotalCount + runFactoryList.Count          End If     NextNext

Explanation 3: Code to trigger TestResultsDeletionTool silently. TestResultsDeletionTool will only works if it is running from the QC Server machine. From observation, if you run from remote machine, nothing will be clean up. Please be aware

The VB script has to call from the machine where the QC

Set wcshell = WScript.CreateObject("WScript.Shell")'Call TestResultsDeletionTool.exestrExeTest = " -Test " & Chr(34) & "[QualityCenter] " & oSubjectNode.Path & "\" & oTest.Name & Chr(34)'print("Deleting test results for --> " & oSubjectNode.Path & "\" & strTestSetName)wcshell.Run strExeDefault & strExeTest, 1, True

Lesson learnt 

N/A

Happy testing!

QTP Java Extensibility - Treat particular class as Internal Frame

Problem

Wish to treat particular class to be internal frame

For example, http://dock.javaforge.com/ where wish to treat particular dockable as internal frame

Solution

</code>

public String class_attr return "mdi" 

</code>

This way, QTP treats the docables as internal-frames

Quality Center - Failed to create MS SQL project with error 'User td already exists in database'

Googling doesn't help much, fortunately HP trouble shooting site provides the solution.

Problem

When creating a new SQL Server project, the user receives the error message, "User td already existsin database" or "User td already exists in current database." When looking in the Master databaseor Security setting of the SQL Server, the "td" user cannot be seen. The "td" user is created in MS SQL Server the first time a project is created on that database server.

Diagnosis

Diagnosis:There may be a "td" user already in the Model and/or Master databases.

Solution

Manually drop the "td" user from the Model and/or Master databasesManually drop the "td" user from the Model and/or Master databases, and you will be able to successfully create projects without getting the "User td already exists in database" error. 1. Use the Enterprise Manager to open the server and go to "Databases". 2. In the Database list, expand the Model database, and highlight "Users." 3. On the right side, you will see a list of users, delete the "td" user. 4. Repeat steps 2 and 3 for Master database. You should now be able to create a new SQL Server project

Lesson learnt 

Visit HP Support Online -> Troubleshoot when you are having problem with HP products.

Quality Center - Query on Manual Execution (Daily/Weekly)

PROBLEM:
You wanted to know how many manual test cases has been executed (i.e. daily/monthly) per testers

SOLUTION:
You need 4 tables (CYCL_FOLD, CYCLE , and TESTCYCL, TEST )

Query1: (Daily)

SELECT DATEPART(YY,TESTCYCL.TC_EXEC_DATE) AS 'Year #', DATEPART(WK,TESTCYCL.TC_EXEC_DATE) AS 'Week #',
DATEPART(DD,TESTCYCL.TC_EXEC_DATE) AS 'Day #', COUNT(DATEPART(DD,TESTCYCL.TC_EXEC_DATE)) AS 'Day Count', TEST.TS_EXEC_STATUS AS 'Execution Status'
FROM cycl_fold
LEFT OUTER JOIN CYCLE ON cycl_fold.cf_item_id = CYCLE.cy_folder_id
LEFT OUTER JOIN testcycl ON testcycl.tc_cycle_id = CYCLE.cy_cycle_id
LEFT OUTER JOIN TEST ON TESTCYCL.TC_TEST_ID = TEST.TS_TEST_ID
WHERE TESTCYCL.TC_TEST_ID = TEST.TS_TEST_ID
AND cycl_fold.cf_item_path LIKE
(
    SELECT ''+a.cf_item_path + '%'
    FROM (SELECT cf_item_path
         FROM cycl_fold
         WHERE TESTCYCL.TC_EXEC_DATE >= '2012-01-01 00:00:00'
         AND cf_item_name = 'Manual Regression') a
)
GROUP BY DATEPART(YY,TESTCYCL.TC_EXEC_DATE), DATEPART(WK,TESTCYCL.TC_EXEC_DATE), DATEPART(DD,TESTCYCL.TC_EXEC_DATE), TEST.TS_EXEC_STATUS
ORDER BY 1 DESC, 2 DESC, 3 DESC

Note: cycl_fold.cf_item_path LIKE ... part is to only try to get test cases running for particular testset folder

Query2: (Daily with Tester)

SELECT DATEPART(YY,TESTCYCL.TC_EXEC_DATE) AS 'Year #', DATEPART(WK,TESTCYCL.TC_EXEC_DATE) AS 'Week #',
DATEPART(DD,TESTCYCL.TC_EXEC_DATE) AS 'Day #', COUNT(DATEPART(DD,TESTCYCL.TC_EXEC_DATE)) AS 'Day Count', TEST.TS_EXEC_STATUS AS 'Execution Status', testcycl.TC_ACTUAL_TESTER AS 'Tester'
FROM cycl_fold
LEFT OUTER JOIN CYCLE ON cycl_fold.cf_item_id = CYCLE.cy_folder_id
LEFT OUTER JOIN testcycl ON testcycl.tc_cycle_id = CYCLE.cy_cycle_id
LEFT OUTER JOIN TEST ON TESTCYCL.TC_TEST_ID = TEST.TS_TEST_ID
WHERE TESTCYCL.TC_TEST_ID = TEST.TS_TEST_ID
AND cycl_fold.cf_item_path LIKE
(
    SELECT ''+a.cf_item_path + '%'
    FROM (SELECT cf_item_path
         FROM cycl_fold
         WHERE TESTCYCL.TC_EXEC_DATE >= '2012-01-01 00:00:00'
         AND cf_item_name = 'Manual Regression') a
)
GROUP BY DATEPART(YY,TESTCYCL.TC_EXEC_DATE), DATEPART(WK,TESTCYCL.TC_EXEC_DATE), DATEPART(DD,TESTCYCL.TC_EXEC_DATE), TEST.TS_EXEC_STATUS, testcycl.TC_ACTUAL_TESTER
ORDER BY 1 DESC, 2 DESC, 3 DESC

Query3: (Weekly)

SELECT DATEPART(YY,TESTCYCL.TC_EXEC_DATE) AS 'Year #', DATEPART(WK,TESTCYCL.TC_EXEC_DATE) AS 'Week #', COUNT(DATEPART(WK,TESTCYCL.TC_EXEC_DATE)) AS 'Week Count', TEST.TS_EXEC_STATUS AS 'Execution Status'
FROM cycl_fold
LEFT OUTER JOIN CYCLE ON cycl_fold.cf_item_id = CYCLE.cy_folder_id
LEFT OUTER JOIN testcycl ON testcycl.tc_cycle_id = CYCLE.cy_cycle_id
LEFT OUTER JOIN TEST ON TESTCYCL.TC_TEST_ID = TEST.TS_TEST_ID
WHERE TESTCYCL.TC_TEST_ID = TEST.TS_TEST_ID
AND cycl_fold.cf_item_path LIKE
(
    SELECT ''+a.cf_item_path + '%'
    FROM (SELECT cf_item_path
         FROM cycl_fold
         WHERE TESTCYCL.TC_EXEC_DATE >= '2012-01-01 00:00:00'
         AND cf_item_name = 'Manual Regression') a
)
GROUP BY DATEPART(YY,TESTCYCL.TC_EXEC_DATE), DATEPART(WK,TESTCYCL.TC_EXEC_DATE), TEST.TS_EXEC_STATUS
ORDER BY 1 DESC, 2 DESC

Solution to 'The QuickTest Java Add-in Extensibility SKD is not supported for 64-bits operating systems

Problem:

As the title clearly mentioned, was hit into trouble when trying to install QuickTest Professional Java Add-in Extensibility SDK in 64 bits machines. Nevermind, here is the solution

Solution:

To make things easy, lets separate the solutions into 2 sections

Preparation:

Unzip skd.zip

Section 1: Install QTP Java Add-in extensibility plugin into Eclipse

• Assume Eclipse is installed in drive D (fullpath :- D:/eclipse)
• Create a new folder to store the QTP related add-in (D:/eclipse-thirdparty)
• In eclipse-thirdparty directory, create a new folder called eclipse (D:/eclipse-thirdparty/eclipse)
• Copy the features and plugins from sdk folder into D:/eclipse-thirdparty/eclipse
• Create a new folder called links in Eclipse (fullpath :- D:/eclipse/links
• In links directory, create a file with link extension (any name will do). For example, quicktest.link with content 'path=D:/eclipse/thirdparty. Refer to more detail on how to deal with plugin Install eclipse plugins the easy way

Section 2: QuickTest Profession (How to manually install add-in in 64 bits operating system)

• Make sure QuickTest Professional is install in C:\Program Files (x86)\HP\QuickTest Professional
• Access to C:\Program Files (x86)\HP\QuickTest Professional\bin\java
• Copy sdk folder into C:\Program Files (x86)\HP\QuickTest Professional\bin\java 

• Access to C:\Program Files (x86)\HP\QuickTest Professional\bin\java\sdk\eclipse
• Double click on deploysdkplugins, then point to D:/eclipse and click OK

Happy testing!