Tim.Hartman@Pfizer.com (lunedì, 17 agosto 2015 22:00)

One more query to share...

ALM uses a bitmask stored in the US_GROUP field (within each project) to determine what roles have been assigned to users. The position of each 1 within this field determines what roles have been assigned based on GR_GROUP_ID. Note that if you add and remove groups, the bitmask length will grow, but never shrink.

-- Find users of a particular group using group name.
select us_username "User ID" from users where substr(us_group,to_number((select gr_group_id from groups where gr_group_name='TDAdmin'))+1,1)='1'

Well, since you took the time to build a nice site, I'll give you a bonus query, too. This produces a single delimited field containing the IDs of all site admins.

--Get site admins:
SELECT listagg(''''||user_name||'''',',') WITHIN GROUP (ORDER BY user_name) as SA FROM users where user_id in (SELECT ur_user_id as SA FROM users_roles)

Sample output: (Yes, Oracle returns one row containing one field.)
'bracha','formicok','gudibb','hartmat','kanumk','lightl01','morria28','muppah','obrienc','qualitycenteradmin','selvav02','sriniv16','srvgbl-qcadmin','sundag04','velayr01'

Tim.Hartman@Pfizer.com (lunedì, 17 agosto 2015 21:44)

You've done a nice job extracting the valuable pieces of information from HP's docs and supplementing that with genuinely useful information. I've spent years creating admin utilities and report that should of been part of the core product.

Here's some (Oracle 11g) SQL written for ALM-QC 11.52 you may find useful:

-- Append requirement folder path to req names.
SELECT
rq_req_id AS "ID",
SYS_CONNECT_BY_PATH (rq_req_name, ' / ') "REQPATH",
tpr_name AS "Type",
rq_req_comment AS "Description"
FROM req
JOIN req_type ON rq_type_id = tpr_type_id
START WITH rq_father_id = - 1
CONNECT BY PRIOR rq_req_id = rq_father_id
ORDER SIBLINGS BY rq_req_name;

-- convert XML (as in COMMON_SETTINGS) to columns. Used with the Genilogix (now Avnet) eApprove rules engine.
WITH params AS
(SELECT sys.xmltype (cset_value) AS params_xml
FROM common_settings
WHERE cset_category = 'GLX_Config'
AND cset_name = 'Req'
)
SELECT
p_uiTab,
p_svName,
p_order,
p_uiDisplay,
p_dataType,
p_addlInfo,
p_defaultValue,
p_required,
p_documentation
FROM
params,
xmltable (
'//qcActions/qcActionList[@name="Configure Project Parameters"]//configData'
passing params.params_xml
columns
p_uitab VARCHAR2 (250) PATH '//configData/@uiTab',
p_svname VARCHAR2 (250) PATH '//configData/@svName',
p_order NUMBER PATH '//configData/@order',
p_uidisplay VARCHAR2 (250) PATH '//configData/@uiDisplay',
p_datatype VARCHAR2 (250) PATH '//configData/@dataType',
p_addlinfo VARCHAR2 (250) PATH '//configData/@addlInfo',
p_defaultvalue VARCHAR2 (250) PATH '//configData/@defaultValue',
p_required VARCHAR2 (250) PATH '//configData/@required',
p_documentation CLOB PATH '//configData/@documentation'
) a


-- All template projects plus the # of linked projects.
--NULLS are converted to zeros

SELECT
a.domain_name "Domain",
a.project_name "Project",
CASE WHEN b.numlinks is null THEN 0 ELSE b.numlinks END "Linked"
FROM
projects a
left join (select prl_from_project_uid, count(*) as numlinks from project_links group by prl_from_project_UID) b on a.project_uid = b.prl_from_project_uid
WHERE
is_template='Y'
ORDER BY domain_name, project_name


-- Test Sets to test instances to runs
-- I'm pretty sure this is accurate, but HP's data model doesn't have good referential integrity
SELECT
RUN.RN_CYCLE_ID "Test Set ID",
CYCLE.CY_CYCLE "Test Set Name",
TESTCYCL.TC_TESTCYCL_ID "Test Instance ID",
TESTCYCL.TC_TEST_ORDER "Test Order",
RUN.RN_TEST_ID "Test ID",
TEST.TS_NAME "Test Name",
TEST.TS_USER_TEMPLATE_01 "Test Approval Status",
RUN.RN_RUN_ID "Run ID",
RUN.RN_RUN_NAME "Run Name",
TO_CHAR(RUN.RN_EXECUTION_DATE,'YYYY-MM-DD') "Run Execution Date",
RUN.RN_USER_TEMPLATE_01 "Run Approval Status",
RUN.RN_USER_TEMPLATE_04 "Run Approval Pending",
RUN.RN_USER_TEMPLATE_02 "Run Signatures"
FROM
RUN
JOIN
CYCLE ON RUN.RN_CYCLE_ID = CYCLE.CY_CYCLE_ID
JOIN
TESTCYCL on RUN.RN_TESTCYCL_ID = TESTCYCL.TC_TESTCYCL_ID
JOIN
TEST ON RUN.RN_TEST_ID = TEST.TS_TEST_ID
WHERE
RUN.RN_USER_TEMPLATE_01 LIKE 'Routing%'
ORDER BY
RUN.RN_TESTCYCL_ID, RUN.RN_TEST_ID, TESTCYCL.TC_TEST_ORDER

-- Better site user query. Note this example has a WHERE clause limiting results to a single user (me)
-- Is account active? Is SA? First/last session
SELECT user_id,
CASE WHEN acc_is_active = 'Y' THEN 'TRUE' ELSE 'FALSE' END is_active,
CASE WHEN ur_role_id =10 THEN 'TRUE' ELSE 'FALSE' END is_siteadmin,
user_name,
us_dom_auth AS pxed_ou,
full_name,
email,
description,
TO_CHAR (last_update, 'YYYY-MM-DD') AS last_update,
TO_CHAR ((SELECT MIN (end_time) FROM sessions_history WHERE user_name = 'hartmat'), 'YYYY-MM-DD') AS first_session,
TO_CHAR ((SELECT MAX (end_time) FROM sessions_history WHERE user_name = 'hartmat'), 'YYYY-MM-DD') AS last_session
FROM users LEFT OUTER JOIN users_roles ON ur_user_id = user_id
where user_name = 'hartmat'
ORDER BY is_active DESC, user_name;

Praveen N (martedì, 27 gennaio 2015 20:37)

I am really impressed with the Template & Projects explanation which is really hard to find in HP websites itself. Khudos to the detailed notes and cristal creal documentation.

http://www.northsouth.org/public/main/home.aspx (mercoledì, 24 dicembre 2014 18:26)

Thank you very much for all the effort and make it easy to understand.

I am new in this field, it would be great if you can explain where to write and execute Excel program from out side of Quality Center and what are the basic requirements to use this option. I do not have manual to read OTA API......

Thanks a lot.

samin (sabato, 01 marzo 2014 00:49)

Thanks Massimo! Appreciate all your explanations, easy to understand.

Dan Luevano (martedì, 25 febbraio 2014 17:43)

Very useful, thank you!

Uyen (mercoledì, 09 ottobre 2013 17:57)

Very usefull.
Thanks a lot for your work !

MSmithson (venerdì, 30 agosto 2013 14:44)

Great content and presentation

Vineet (sabato, 24 agosto 2013 05:16)

Well - Done - Very crisp and clear representation of QC

Chetan (martedì, 06 agosto 2013 16:08)

Good Information.
Keep it up!!!

Ramesh (lunedì, 29 luglio 2013 11:12)

Hi,
Its very usefull site.

Thanks for this.
Ramesh